MySQL table types

It is important for users of OpenX to ask the question: if I run OpenX on MySQL, is it best to use the MyISAM or the InnoDB storage engine?

General recommendation

In general, using the MyISAM storage engine is recommended.

However, if performance is important to you and you want to get the most out of your OpenX installation, some knowledge and thought is required. The aim of this section is to provide OpenX administrators with all the information required to make an informed decision regarding which MySQL storage engine to use.

The MyISAM storage engine

The MyISAM storage engine has one big advantage over InnoDB - speed. The MyISAM storage engine is about as fast as it gets, which makes it ideal for simple databases where you want to read and write data very quickly. This is obviously a good thing for OpenX, because fast read speeds mean that data relating to banner delivery can be obtained quickly. Fast write speeds mean that banner delivery logging is quick as well. Both of these add up to fast banner delivery to your web site(s).

However, this speed comes at a cost. Specifically:

• There is no transaction support; and
• Write operations result in table-level locking.

Transaction Support Issues

As a general rule, OpenX has been written to operate without needing transaction support. In areas of the code where they would normally be employed, the code itself has been written to emulate transactions as far as is possible when using the MyISAM storage engine.

However, if you are using the MyISAM storage engine for your OpenX tables and your database crashes while the maintenance script is running, this might result in a situation where the statistics and priority values are inconsistent. If this occurs, once the database is running again delivery may not work correctly until the next time maintenance runs.

N.B. if your database crashes you already have problems with your server. Perfect delivery isn't something that can be guaranteed in this situation.

Table-Level Locking

The issue of table-level locking is the main downside to using the MyISAM storage engine for OpenX. Whenever OpenX needs to write to a table that table is locked, and no other writes can be made to the table at the same time. This means:

• When a banner is being delivered and the impression is being logged to the database, all other banners being delivered at the same time will have to wait before they can try to log their impression to the database. Thus, as you deliver higher and higher volumes of banners the speed of delivery will decrease, as more and more banners have to wait for their turn to write to the database.

• More importantly, when the maintenance engine runs to summarise your delivery statistics all banner delivery will be delayed while the maintenance engine has the raw logging table(s) locked. If the maintenance engine takes minutes to run, especially when summarising your statistics or performing conversion tracking, then your OpenX ad server may effectively be "down" for this period!

Of course, for the average OpenX installation this will never be an issue. Most OpenX installations don't deliver enough banners per second to see the effect of table-level locking, and their maintenance script only takes a few seconds to summarise their statistics. In these instances banner delivery is barely affected when maintenance runs.

This is why it is recommended that the average OpenX user use the MyISAM storage engine.

Further Reading

It is possible to perform appropriate performance monitoring of your server(s) and tuning of OpenX to ensure that, for example, the maintenance engine does not take too long to run. If you intend to use the MyISAM storage engine it is recommended that you read our Performance Tuning section.

See also the MySQL documentation regarding the MyISAM storage engine.

The InnoDB Storage Engine

Reading and writing using the InnoDB storage engine is much slower than with MyISAM. However, there are still some good reasons to consider using InnoDB for your OpenX database tables:

• With appropriate hardware (plenty of power and fast disk drives, preferably in a striped RAID configuration) InnoDB can be more than fast enough for rapid banner delivery, albeit at increased hardware cost compared with using the MyISAM storage engine.

• The fact that InnoDB supports row-level locking means that delivery of banners from OpenX will not suffer in the event that the maintenance process takes a "long" time to run. See the discussion on table-level locking above (in the MyISAM section).

If you intend to run an OpenX installation that is going to:

• Serve several million banner impressions per day;
• Will have a dedicated database server attached to a cluster of web servers; or
• Will be used to carry out conversion tracking

then it is recommended that you consider whether the InnoDB storage engine might be more suitable for your needs.

Ultimately, if you are going to be running a high volume system it is recommended that you prepare a staging environment. This will allow you to experiment with both storage engines alongside your hardware and your delivery configuration to see how well they perform for you.

Further Reading

See the MySQL documentation regarding the InnoDB storage engine.

Has the Recommended Type Changed?

The InnoDB storage engine used to be recommended for "Max Media Manager" users, as earlier versions of OpenX were only intended for very high volume installations. Now that "Max Media Manager" has been merged into the main OpenX branded code base and released to the general public for use by the OpenX community, the recommended table type has been updated to take the average user's requirements into account.

If you are a high volume user please read the section above on the InnoDB storage engine, as you might find that the default recommendation does not meet your needs.

Preparing a test environment

If you create a test environment - to perform an upgrade before you upgrade your live environment - you can:

• Test that after upgrading you can log into OpenX and see all of your data.
• Evaluate bug fixes and new features.
• Test that the delivery of creatives works after the upgrade.
  

  This is recommended when, for example, evaluating OpenX's beta releases.

To test in a test environment:

Creating a Testing Environment

To set up a testing environment:

• Decide on a server to host your testing environment.
• Ideally, this server will permit you to host your testing environment using a different IP address to your live environment. This will allow you to:
• Use your testing environment to ensure that the upgrade process works correctly;
• Test that after upgrading, you can log into OpenX and see all of your data;
• Evaluate bug fixes and new features; and
• Test that the delivery of creatives works after the upgrade.

• On your live environment server, create a backup of your live environment database:
  
  mysqldump -u root -p live_openx_database > live_openx_database_backup.sql

• If necessary, copy the live environment database backup to the server where you will be creating your testing environment.

• On the testing environment server, create a new database to host your testing environment.

• "Restore" your live environment database into the new testing environment database. For example, if you are using MySQL:
  
  mysql -u root -p testing_openx_database < live_openx_database_backup.sql

• Set up a new IP-based or name-based host on your web server for your testing environment.
  • If you are creating a new IP-based host for the test environment, set up the site name (i.e. the URL of the site) to be the same as your live environment. For example, in Apache you might have:
    

    # Live OpenX Site
    <VirtualHost 10.0.0.25:80>
        ServerAdmin  me@example.com
        DocumentRoot /var/www/html/openx-live
        ServerName   openx.example.com
    </VirtualHost>
    
    # Testing OpenX Site
    <VirtualHost 10.0.0.26:80>
        ServerAdmin  me@example.com
        DocumentRoot /var/www/html/openx-testing
        ServerName   openx.example.com
    </VirtualHost>
    

  • If you are creating a name-based host you will need to use a new site name (i.e. URL) for your site. So if, for example, you had your live environment set up as "openx.example.com", you might create "testing.example.com".
    

    # Live OpenX Site
    <VirtualHost 10.0.0.25:80>
        ServerAdmin  me@example.com
        DocumentRoot /var/www/html/openx-live
        ServerName   openx.example.com
    </VirtualHost>
    
    # Testing OpenX Site
    <VirtualHost 10.0.0.25:80>
        ServerAdmin  me@example.com
        DocumentRoot /var/www/html/openx-testing
        ServerName   testing.example.com
    </VirtualHost>
    

• If you created a name-based host for your test environment:
  • Edit the OpenX configuration file(s) in the testing environment so that the URLs match the new URLs of the testing environment (that is, change all "openx.example.com" instances in the configuration file(s) to be "testing.example.com"):
    

    [webpath]
    admin                               = testing.example.com/www/admin
    delivery                            = testing.example.com/www/delivery
    deliverySSL                         = testing.example.com/www/delivery
    images                              = testing.example.com/www/images
    imagesSSL                           = testing.example.com/www/images
    

  • Edit the database details to point to your testing environment database:
    

    [database]
    type                                = mysql
    host                                = localhost
    port                                = 3306
    username                            = root
    password                            = password
    name                                = testing_openx_database
    persistent                          = false
    mysql4_compatibility                = false
    protocol			    = tcp
    

Checking the Testing Environment

Once you have logged into your testing site, it may help to customise the UI in some way (e.g. by changing the colours of the installation). This will make it immediately obvious upon logging in whether you are using the testing environment or the live environment, and reduce any chance of confusion.

IP Based Testing Environments

If all of your zones are using JavaScript ot iFrame delivery, when you have created a testing environment with the same URL as your live environment but a different IP address:

• On your desktop computer, configure your host file to point the URL of the current live ad server to the new testing domain.

N.B. When one or more zones are using XML-RPC delivery, the host file entry must also be changed on the web server(s) hosting the XML-RPC site. This will allow those sites to fetch ads from the OpenX server in the testing environment.

• You can now go to the URL you normally use to access OpenX, log onto the OpenX testing environment, and ensure that it works.

• You can also visit your normal website and check that delivery of creatives still works.
  

  While the host file is set up, you will not be able to access your live environment. You will need to remove the host file entry from your desktop computer to be able to do this.

Name-Based Testing Environments

If you have created a testing environment with a different URL to your live environment:

• On your desktop computer, configure your host file to point the URL of the testing environment to the IP address the site runs on.

• You can now go to the testing environment URL, log onto the OpenX testing environment, and ensure that it works.

• If you want to test the delivery of creatives you will need to generate new tags. Alternatively, you can tag copies of your existing tags from your live site and edit them so that the URL used in the tags is from the testing site.

Upgrading the Testing Environment

Once your testing environment has been created and is working correctly, you can now test the upgrade of OpenX.

Download the latest version of OpenX and follow the upgrade instructions - but perform the upgrade on your testing environment.

Once the upgrade is completed, you can return to the Checking the Testing Environment section above and repeat your testing, to ensure that the upgrade has worked correctly.

Upgrading the Live Environment

When you are satisfied that the upgrade of OpenX has worked, and you are happy to upgrade your live setup, you can now repeat the upgrade process with your live site.

Don't forget to backup your live environment database before you upgrade, just in case!

Preparing for an upgrade

Before you start the upgrade, prepare your OpenX environment:

Backup your system

Important: Backup your OpenX directory and your database before starting the upgrade.

Prepare the database

The upgrade process guides you through synchronizing your old and new databases. But there can be additional preparations:

• Lowercase table prefixes: If your existing database contains prefixes that use capital letters, you must change all prefixes to lower case before you perform the upgrade.
• Large database: During the upgrade OpenX copies each affected table, which can be very time-consuming for large databases. To avoid this process, create a file named "NOBACKUPS" in the var/ directory for your new version of OpenX. When the Install Wizard detects this file, it does not copy tables.
  
  Note: This procedure is described in Step 3 of the 'Copy OpenX to your server' section.

Download OpenX

Download the latest version of OpenX from the OpenX website to your local computer.

Copy OpenX to your server

To copy OpenX to your server:

1. Create a new directory with the same directory name as your previous installation, but add the newfiles suffix to the end of the directory name. For example: directoryname_newfiles.
2. Unpack the latest release of OpenX into this new directory.
3. Copy the configuration file from the current working location to the directoryname_newfiles/var directory of the new version.
   

   The Configuration file locator provides the location of the configuration file in all versions you can upgrade from.

4. Copy your banner images from currentdirectory/www/images to directoryname_newfiles/www/images
   
   More information: The banner images could be stored in a different location if you changed your banner settings. To check the location you have set for images, search for the webDir parameter in the configuration file.
5. If you have a large database and do not want to wait while OpenX copies all database tables affected by the upgrade, create a file named "NOBACKUPS" in the var directory, such as directoryname/var/NOBACKUPS.
6. Add the _backup suffix to the end of the name of your existing working directory, to rename it as directoryname_backup.
7. Rename directoryname_newfiles by removing the newfiles suffix.

The following table shows where to find the configuration file in OpenX:

Configuration file locator

Next step: Upgrade wizard

When you are satisfied that you have fully prepared the system, open a web browser and visit your OpenX website to start the upgrade wizard.

Running the upgrade wizard

The OpenX install wizard guides you through each stage of the install or upgrade process. The wizard performs both new and upgrade installations because it checks if a previous version exists and performs additional upgrade tasks if an earlier version is detected. This section describes the upgrade process.

To start the installer, open a web browser and navigate to the /var directory where OpenX is stored, for example http://www.example.com/openx.

If the installer does not start automatically, perhaps your web server does not allow PHP files to be used as a directory index. To start the installer manually, navigate to the index.php file in the admin directory, for example /openx/admin/index.php.

The wizard guides you, step-by-step, through the following processes:

Welcome

The Welcome page tells you what version of OpenX you are going to install and links to the documentation and support available.

Click Continue to proceed to the Terms page.

Terms

The Terms page provides important information about the OpenX licence. Before you continue with the installation, you are advised to read and agree to the terms and conditions of the GPL licence.

Click Accept to proceed to the Policy page.

Policy

The Policy page describes the OpenX Privacy and Data Usage policy. Please review this information to learn about the data that OpenX collects and how it is used. You must agree with this policy before continuing with the upgrade. In the Synchronisation settings section, tick the Check for Updates checkbox if you want your copy of OpenX to alert you to software updates.

Click I Agree to proceed to the System Check page.

System Check

The System Check evaluates whether the PHP setup is correct, that correct file permissions are set for OpenX folders, and whether a previous version of OpenX exists. All issues are reported in the PHP, File permissions and Application Check sections at the bottom of the page. You must resolve all reported issues and run another system check before you can proceed.

PHP setup

If you prepared your system correctly there will be no issues with the PHP setup.

See also: System requirements for information about the PHP requirements.

Permission to write to OpenX folders

OpenX needs permission to write to many files in the system. The system check verifies the permissions set for application folders and lists folders with incorrect permissions in the File Permissions section. The list includes shell commands you can enter to correct these permissions. Alternatively, in an FTP client, change the CHMOD setting to 0777 for each problematic folder. If you cannot find where to set these values in your FTP client, look for a menu entry for permissions or properties, or consult the documentation for the application.

Schema check

If a previous version of OpenX is detected, the wizard checks the database schema to ensure that it has all the correct tables, columns and indexes for this version of OpenX. If any elements are missing, a general description is provided and you can find the names of the missing elements in the /var/install.log file.

Completing the system check

You must resolve all reported issues before you are allowed to proceed. When the system is configured correctly, you can click Continue to proceed.

Login

In the Login page, type the administrator username and password of your existing system in the appropriate fields and click Continue to proceed to the Database Setup page.

Database Setup

The Database Setup page displays database settings detected from your existing installation. Check the settings to ensure they are correct. If the settings are not correct, amend them in your configuration file and go back to the start of the upgrade process. When you are satisfied the settings are in order, click Continue to start the process of altering your database. This process can take some time. When the process is finished the screen refreshes to report that your database has been upgraded successfully.

Click Continue to open the Configuration Setup page.

Configuration Setup

The Configuration Setup page provides some extra configuration options. Most publishers do not need to change any of these options. However, the upgrade wizard will be using the 'openx_new' folder you are currrently upgrading with. You most likely want to change this to the 'openx' folder of your current installation.

N.B. following the upgrade, if you have a problem with your admin path and did not edit the Configuration tab settings as described above, this means you must edit your config file to ensure it uses the correct paths.

After you have upgraded using 'openx_new' and then renamed the folder to 'openx', you must edit your openx/var/www.example.com.conf.php config file so that the paths use the 'openx' folder name as opposed to 'openx_new'.

Review the settings to ensure that they are correct and click Continue to proceed.

Finished

When the installation is complete, the OpenX login page is displayed.

Upgrading time zones

Audience

This section is only relevant to OpenX administrators who are upgrading from OpenX 2.4 or earlier to OpenX 2.6 or later. If you are installing OpenX for the first time, or you are performing a different upgrade, you do not need to read this.

The OpenX upgrade wizard will alert you and direct you to this page if it is relevant to your upgrade.

The Problem with Time Zones

In OpenX 2.4 and earlier, the logging of delivery data (requests, impressions, clicks and conversions) was performed using the time zone of the server that OpenX had been installed on.

This approach had a number of issues, including:

• The OpenX administrator might not have had the required privileges to change the time zone of the server OpenX is installed on. (For example, OpenX might have been installed on a server provided by an ISP, where the OpenX administrator did not have "root" access.) This might have resulted in OpenX displaying statistics in a time zone that was not the time zone desired by the user.

• If the server time zone was in a location where the time changed twice a year (for example, between Standard Time and Daylight Saving Time) then twice a year, when clocks go forward or backward, there would be a gap or duplication of one hour's statistics.

• If the OpenX installation needed to be moved to a new server where the time zone of the server was different, or if the server administrator wanted to change the time zone, not only would there be a similar problem to the above (a gap or duplication in logging), but also all past statistics would not be migrated to the new time zone.

Addressing the Issue in OpenX 2.6

To address this issue, in OpenX 2.6 the logging of delivery data is now performed using Coordinated Universal Time (UTC). This means that:

• The time zone of the server does not matter; as long as the time zone and current time are set correctly on the server, all requests, impressions, clicks and conversions will be correctly logged in UTC.

• On servers where the time zone results in the clock changing between Standard Time and Daylight Saving Time, the logging of data will not be affected.

• The time zone of a server can be changed without affecting the logging of data.

• An OpenX installation can be migrated between servers in different time zones without affecting the data.

• It is possible for different Manager Accounts in OpenX to set different time zones as a preference, and view their statistics in a time zone that is relevant to them, rather than every Manager Account having to use just the one time zone.

Migration of Past Statistics

The fact that the logging of delivery data has changed from the server timezone to UTC means that if OpenX is upgraded from OpenX 2.4 (or earlier) to OpenX 2.6 (or later), then the past statistics will no longer be in the correct time zone. (Unless, of course, the server timezone was already UTC.)

The upgrade wizard does not convert your past statistics to UTC. You will need to read the following sections and decide which course of action suits you best.

Do Nothing

For most users of OpenX, it is probably not that important to worry about the migration of past statistics to UTC. The fact that the logging of data was not always correct in the past (for example, when time zones changed between Standard Time and Daylight Saving Time) means that you will gain little by carrying out the complex process of trying to migrate all your past data to UTC.

The important thing to remember is that your total number of requests, impressions, clicks and conversions will not be affected if you do nothing. You can still rely on these values if you need to look at past data. All that it will mean is that if you view your past data at the daily or hourly level, the exact day/hour that statistics are reported may change a little (depending on the difference between your server's time zone and UTC).

When you are looking at past statistics OpenX will warn you when the exact day/hour may be slightly off, so there's no need to worry about remembering to account for this.

If this is acceptable you can simply carry on with your OpenX upgrade. Do not select the option that indicates you have upgraded your past data. If you do so, when you are looking at past statistics that might be affected by the upgrade to logging in on UTC, you might not be shown the warning on statistics screens and in reports.

Upgrade Your Past Statistics Before Upgrading

If it is absolutely vital for you and your business that past statistics are not displayed in a slightly incorrect day/hour format in any way, then it is possible for you to manually upgrade your past statistics to ensure that it is in UTC. However, this process may take some time (potentially days!) depending on the size of your database. This is why the OpenX upgrade wizard does not attempt to do this for you. You will need to have access to run commands directly in your database to perform these changes.

Preparing for the UTC Update

In order to upgrade your past statistics to UTC you must stop the logging of new data in OpenX. This allows all past data to be correctly converted without new data being logged in the local server time zone.

To do this:

Disable Request, Impression, Click and Tracker Data Logging

• Disable all creatives linked to your zones so that no creative impressions or clicks are logged; and
• If you are using OpenX 2.4 or Max Media Manager v0.3, then in your installation disable the logging of requests and tracker impressions.
  

  Alternatively, if you only have OpenX tags in a limited number of places you could also simply remove all the tags from your sites.

Ensure All Existing Data is Summarised

• If you are using OpenX 2.4 or Max Media Manager v0.3, wait for the maintenance script to run and summarise all of your data.

Disable Maintenance

• If you are using OpenX 2.4 or Max Media Manager v0.3, disable your maintenance cronjob if you have one set up. During the upgrade process no new data is being logged, so there is no need to run the maintenance script.

Backup Your OpenX Database

• In case you need to, please make sure your backup is known to be good and that you know how to restore it.

Determine the Difference Between the Server Timezone and UTC

• You can use free tools like The World Clock to check the exact difference between your local time zone and UTC.
• You can determine your server's time zone by looking at its date/time information. However, it can help to see which time zone PHP thinks your server is in. To do so, put the following code in a PHP file on the web server that OpenX is installed on and view this page via a web browser:
  

  <?php
      $calculated = false;
      if (version_compare(phpversion(), '5.1.0', '>=')) {
          // Great! The PHP version is >= 5.1.0, so simply
          // use the built in date_default_timezone_get()
          // function, and know it's all good
          $tz = date_default_timezone_get();
      } else {
          // Boo, we have to rely on the dodgy old TZ
          // environment variable stuff
          $tz = getenv('TZ');
          if ($tz === false || $tz === '') {
              // Even worse! The user doesn't have a TZ
              // variable, so we have to try and calcuate
              // the timezone for the user
              $calculated = true;
              unset($tz);
              $diff = date('O');
              $diffSign = substr($diff, 0, 1);
              if ($diffSign == "+") {
                  $diffHour = (int) substr($diff, 1, 2) - date('I'); // minus 1 hour if date in DST
              } else {
                  $diffHour = (int) substr($diff, 1, 2) + date('I'); // add 1 hour if date in DST
              }
              $diffMin  = (int) substr($diff, 3, 2);
              $offset = (($diffHour * 60) + ($diffMin)) * 60 * 1000; // Milliseconds
              $offset = $diffSign . $offset;
              global $_DATE_TIMEZONE_DATA;
              if (!isset($_DATE_TIMEZONE_DATA)) {
                  include('Date/TimeZone.php');
              }
              reset($_DATE_TIMEZONE_DATA);
              foreach (array_keys($_DATE_TIMEZONE_DATA) as $key) {
                  if ($_DATE_TIMEZONE_DATA[$key]['offset'] == $offset) {
                      $tz = $key;
                      break;
                  }
              }
          }
      }
      echo "Your server timezone is: $tz<br />\n";
      if ($calculated) {
          echo "Note that this timezone is calculated. You should set up your timzone correctly.";
      }
  ?>

UTC Upgrade Process

Upgrade for OpenX 2.4

The following examples assumes that your server is in New York, or 5 hours behind UTC. Obviously you will need to adapt the examples below for your server's time zone.

OpenX 2.4 Conversion Tracking Steps

If you are performing conversion tracking with your Openx 2.4 installation, you will need to upgrade your existing raw data:

• If you perform conversion tracking against impressions, update the ox_data_raw_ad_impression table

UPDATE
  ox_data_raw_ad_impression
SET
  date_time = date_time + INTERVAL 5 HOUR;

• If you perform conversion tracking against clicks, update the ox_data_raw_ad_click table

UPDATE
  ox_data_raw_ad_click
SET
  date_time = date_time + INTERVAL 5 HOUR;

• Update the ox_data_intermediate_ad_connection table

UPDATE
  ox_data_intermediate_ad_connection
SET
  tracker_date_time = tracker_date_time + INTERVAL 5 HOUR,
  connection_date_time = connection_date_time + INTERVAL 5 HOUR;

• Update the ox_data_intermediate_ad_variable_value table

UPDATE
  ox_data_raw_tracker_variable_value
SET
  date_time = date_time + INTERVAL 5 HOUR;

OpenX 2.4 Common Steps

Regardless of whether you use your OpenX 2.4 installation for conversion tracking or not you will need to:

• Update the ox_data_intermediate_ad table

UPDATE
  ox_data_intermediate_ad
SET
  day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
  hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
  operation_interval_id = MOD(operation_interval_id + 5, 167),
  interval_start = interval_start + INTERVAL 5 HOUR,
  interval_end = interval_end + INTERVAL 5 HOUR;

• Update the ox_data_summary_ad_hourly table

UPDATE
  ox_data_summary_ad_hourly
SET
  day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
  hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR);

• Update the ox_data_summary_ad_zone_assoc table

UPDATE
  ox_data_summary_ad_zone_assoc
SET
  operation_interval_id = MOD(operation_interval_id + 5, 167),
  interval_start = interval_start + INTERVAL 5 HOUR,
  interval_end = interval_end + INTERVAL 5 HOUR,
  created = created + INTERVAL 5 HOUR,
  expired = expired + INTERVAL 5 HOUR;

• Update the ox_acls table

Any targeting criteria (delivery limitations) that you have set up that target creatives to a certain hour need to be converted to UTC. This is easiest to do with a simple PHP script:

<?php

  $host     = 'localhost';
  $port     = 3306;
  $username = 'user';
  $password = 'password';
  $database = 'openx_db';

  $prefix   = 'ox_';
  $table    = 'acls';

  $hours    = 5;

  $rConnection = mysql_connect("$host:$port", $username, $password);
  if (!$rConnection) {
    die('Could not connect: ' . mysql_error());
  }

  $result = mysql_select_db($database, $rConnection);
  if (!$result) {
    die('Could not use database: ' . mysql_error());
  }

  $query = "
    SELECT
      *
    FROM
      $prefix$table
    WHERE
      type = 'Time:Hour'";
  $rResult = mysql_query($query, $rConnection);
  if (!$rResult) {
    die('Could not select targeting criteria for update: ' . mysql_error());
  }

  while ($aRow = mysql_fetch_assoc($rResult)) {
    echo "Updating time-based targeting criteria for creative ID: {$aRow['bannerid']}\n";
    // Prepare the start of the update message
    $message  = "  Changed time-based targeting crieteria from:\n";
    $message .= "    '{$aRow['data']}'\n";
    $message .= "  to\n";
    // Prepare the new time-based data
    $aTimeData = explode(',', $aRow['data']);
    foreach ($aTimeData as $key => $value) {
      $aTimeData[$key] = ($value + 5) % 24;
    }
    $newData = implode(',', $aTimeData);
    // Update the database
    $updateQuery = "
      UPDATE
        $prefix$table
      SET
        data = '$newData'
      WHERE
        bannerid = '{$aRow['bannerid']}'
        AND
        logical = '{$aRow['logical']}'
        AND
        type = '{$aRow['type']}'
        AND
        comparison = '{$aRow['comparison']}'
        AND
        data = '{$aRow['data']}'
        AND
        executionorder = '{$aRow['executionorder']}'";
    $result = mysql_query($updateQuery);
    if (!$result) {
      die('Failed to execute query: ' . mysql_error());
    }
    // Complete and display the results of the update
    $message .= "    '$newData'\n";
    echo $message;
  }

  mysql_close($rConnection);

?>

• Update the ox_log_maintenance_statistics table

UPDATE
  ox_log_maintenance_statistics
SET
  start_run = start_run + INTERVAL 5 HOUR,
  end_run = end_run + INTERVAL 5 HOUR,
  updated_to = updated_to + INTERVAL 5 HOUR;

• Update the ox_log_maintenance_priority table

UPDATE
  ox_log_maintenance_priority
SET
  start_run = start_run + INTERVAL 5 HOUR,
  end_run = end_run + INTERVAL 5 HOUR,
  updated_to = updated_to + INTERVAL 5 HOUR;

• Update the ox_userlog table

UPDATE
  ox_userlog
SET
  timestamp = timestamp + 5 * 3600;

• Update the ox_upgrade_action table

UPDATE
  ox_upgrade_action
SET
  updated = updated + INTERVAL 5 HOUR;

Upgrade for Openads 2.0.11-pr1

The following examples assumes that your server is in New York, or 5 hours behind UTC. You will, of course, need to adapt the examples below for your server's time zone.

Openads 2.0.11-pr1 Compact Statistics Mode Steps

If you are running Openads 2.0.11-pr1 with "Compact" statistics:

• Update the phpads_adstats table

UPDATE
  phpads_adstats
SET
  day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
  hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR);

Once you have completed this, proceed to the Openads 2.0.11-pr1 Common Steps section below.

Openads 2.0.11-pr1 Verbose Statistics Mode Steps

If you are running Openads 2.0.11-pr1 with "Verbose" statistics:

• Update the phpads_adviews table

UPDATE
  phpads_adviews
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR;

• Update the phpads_clicks table

UPDATE
  phpads_adclicks
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR;

Once you have completed this, proceed to the Openads 2.0.11-pr1 Common Steps section below.

Openads 2.0.11-pr1 Common Steps

Once you have updated your statistics in either the Openads 2.0.11-pr1 Compact Statistics Format or Openads 2.0.11-pr1 Verbose Statistics Format sections above:

• Update the phpads_acls table

Any targeting criteria (delivery limitations) that you have set up which target creatives to a certain hour need to be converted to UTC. This is easiest to do with a simple PHP script:

<?php

  $host     = 'localhost';
  $port     = 3306;
  $username = 'user';
  $password = 'password';
  $database = 'openads_db';

  $prefix   = 'phpads_';
  $table    = 'acls';

  $hours    = 5;

  $rConnection = mysql_connect("$host:$port", $username, $password);
  if (!$rConnection) {
    die('Could not connect: ' . mysql_error());
  }

  $result = mysql_select_db($database, $rConnection);
  if (!$result) {
    die('Could not use database: ' . mysql_error());
  }

  $query = "
    SELECT
      *
    FROM
      $prefix$table
    WHERE
      type = 'time'";
  $rResult = mysql_query($query, $rConnection);
  if (!$rResult) {
    die('Could not select targeting criteria for update: ' . mysql_error());
  }

  while ($aRow = mysql_fetch_assoc($rResult)) {
    echo "Updating time-based targeting criteria for creative ID: {$aRow['bannerid']}\n";
    // Prepare the start of the update message
    $message  = "  Changed time-based targeting crieteria from:\n";
    $message .= "    '{$aRow['data']}'\n";
    $message .= "  to\n";
    // Prepare the new time-based data
    $aTimeData = explode(',', $aRow['data']);
    foreach ($aTimeData as $key => $value) {
      $aTimeData[$key] = ($value + 5) % 24;
    }
    $newData = implode(',', $aTimeData);
    // Update the database
    $updateQuery = "
      UPDATE
        $prefix$table
      SET
        data = '$newData'
      WHERE
        bannerid = '{$aRow['bannerid']}'
        AND
        logical = '{$aRow['logical']}'
        AND
        type = '{$aRow['type']}'
        AND
        comparison = '{$aRow['comparison']}'
        AND
        data = '{$aRow['data']}'
        AND
        executionorder = '{$aRow['executionorder']}'";
    $result = mysql_query($updateQuery);
    if (!$result) {
      die('Failed to execute query: ' . mysql_error());
    }
    // Complete and display the results of the update
    $message .= "    '$newData'\n";
    echo $message;
  }

  mysql_close($rConnection);

?>

• Update the phpads_userlog table

UPDATE
  phpads_userlog
SET
  timestamp = timestamp + 5 * 3600;

Upgrade for Openads 2.0.11-pr1 for PostgreSQL

Community suggestions welcome!

Upgrade for Max Media Manager v0.3 (Openads 2.3)

If you are running Max Media Manager v0.3 (aka Openads 2.3), please follow the INTR:upgrade steps for OpenX 2.4. Note, however, that you may need to adjust the database prefix. Also note that the ox_upgrade_action table does not exist in MMM v0.3, so you will not need to upgrade this table.

Upgrade for Max Media Manager v0.1

The following example assumes that your server is in New York, or 5 hours behind UTC. Obviously, you will need to adapt the examples below for your server's time zone.

Max Media Manager v0.1 Conversion Tracking Steps

If you are performing conversion tracking with your Max Media Manager v0.1 installation you will need to upgrade your existing raw data:

• If you perform conversion tracking against impressions, update the max_adviews table

UPDATE
  max_adviews
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR;

• If you perform conversion tracking against clicks, update the max_clicks table

UPDATE
  max_adclicks
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR;

• Update the max_adconversions table

UPDATE
  max_adconversions
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR;

• Update the max_conversionlog table

UPDATE
  max_conversionlog
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR,
  action_t_stamp = action_t_stamp + INTERVAL 5 HOUR;

• Update the max_variablevalues table

UPDATE
  max_variablevalues
SET
  t_stamp = t_stamp + INTERVAL 5 HOUR;

Max Media Manaver v0.1 Common Steps

Regardless of whether you use your Max Media Manaver v0.1 installation for conversion tracking or not you will need to:

• Update the max_adstats table

UPDATE
  max_adstats
SET
  day = DATE(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR),
  hour = HOUR(TIMESTAMP(day) + INTERVAL hour HOUR + INTERVAL 5 HOUR);

• Update the max_log_maintenance table

UPDATE
  max_log_maintenance
SET
  start_run = start_run + INTERVAL 5 HOUR,
  end_run = end_run + INTERVAL 5 HOUR;

• Update the max_acls table

Any targeting criteria (delivery limitations) you have set up that target creatives to a certain hour need to be converted to UTC. This is easiest to do with a simple PHP script:

<?php

  $host     = 'localhost';
  $port     = 3306;
  $username = 'user';
  $password = 'password';
  $database = 'max_db';

  $prefix   = 'max_';
  $table    = 'acls';

  $hours    = 5;

  $rConnection = mysql_connect("$host:$port", $username, $password);
  if (!$rConnection) {
    die('Could not connect: ' . mysql_error());
  }

  $result = mysql_select_db($database, $rConnection);
  if (!$result) {
    die('Could not use database: ' . mysql_error());
  }

  $query = "
    SELECT
      *
    FROM
      $prefix$table
    WHERE
      type = 'time'";
  $rResult = mysql_query($query, $rConnection);
  if (!$rResult) {
    die('Could not select targeting criteria for update: ' . mysql_error());
  }

  while ($aRow = mysql_fetch_assoc($rResult)) {
    echo "Updating time-based targeting criteria for creative ID: {$aRow['bannerid']}\n";
    // Prepare the start of the update message
    $message  = "  Changed time-based targeting crieteria from:\n";
    $message .= "    '{$aRow['data']}'\n";
    $message .= "  to\n";
    // Prepare the new time-based data
    $aTimeData = explode(',', $aRow['data']);
    foreach ($aTimeData as $key => $value) {
      $aTimeData[$key] = ($value + 5) % 24;
    }
    $newData = implode(',', $aTimeData);
    // Update the database
    $updateQuery = "
      UPDATE
        $prefix$table
      SET
        data = '$newData'
      WHERE
        bannerid = '{$aRow['bannerid']}'
        AND
        logical = '{$aRow['logical']}'
        AND
        type = '{$aRow['type']}'
        AND
        comparison = '{$aRow['comparison']}'
        AND
        data = '{$aRow['data']}'
        AND
        executionorder = '{$aRow['executionorder']}'";
    $result = mysql_query($updateQuery);
    if (!$result) {
      die('Failed to execute query: ' . mysql_error());
    }
    // Complete and display the results of the update
    $message .= "    '$newData'\n";
    echo $message;
  }

  mysql_close($rConnection);

?>

• Update the max_userlog table

UPDATE
  max_userlog
SET
  timestamp = timestamp + 5 * 3600;

Post-UTC Upgrade

Upgrade to OpenX 2.6

Once all your existing data has been converted to UTC by following one of the above processes you will be ready to upgrade to OpenX 2.6 (or later) via the Upgrade Wizard. During the upgrade, select the option that indicates you have upgraded your past data.

Re-Compile Delivery Targeting

Once you have completed the upgrade to OpenX 2.6 (or later) via the Upgrade Wizard, log in with an Admin User Account and use the Banners option to re-compile the delivery targeting (delivery limitations). This will ensure that all of your delivery targeting rules are updated to use the new UTC based rules you set with the above SQL commands.

Restore or Set Up Maintenance

If you were running OpenX 2.4 or Max Media Manager v0.3, don't forget to re-enable your maintenance script. If you have upgraded from Openads 2.0 it is recommended that you set up a maintenance script. If you have upgraded from Max Media Manager v0.1, the recommended way of setting up a maintenance script has changed.

Re-enable Creatives

If you disabled your creatives and/or disabled request and tracker impression logging, you can now re-enable the creatives and logging of data as required. Alternatively, if you simply removed your tags from your website(s) you can now restore them - although you might want to consider generating new tags with the new version of OpenX for improved preformance and more options.

Upgrading Character Encoding

Audience

This section is only relevant to OpenX administrators who are upgrading from OpenX 2.4 or earlier to OpenX 2.6. If you are installing OpenX, or you are performing a different upgrade, you do not need to read this.

Additionally, if all manager users (formerly known as Admin/Agency accounts) in your OpenX installation have been using the interface in English (or another language with a pure ASCII character set) then this change will not affect you.

The Problem with encoding

In OpenX 2.4 and earlier, language packs were provided for a number of languages. Each language pack was encoded in a different character encoding, and set the user's browser to use that encoding (in the case of 2.0.11 and lower some languages did not set the encoding to use, and relied on the browser to set the encoding).

The encoding used by the browser was used as the encoding for the data stored in user-entered fields in the database

In OpenX 2.6 we have had to change the encoding of these language packs to UTF-8. This was required by (among other things) the change to users and accounts; you might now have the situation where items could be edited by different users who may have different selected languages. Without switching to UTF-8 encoding, any data created by one user and then modified by another with different language settings would have become corrupted.

We have now converted all OpenX language packs to UTF-8 encoding, so it shouldn't matter if multiple users (with different languages) edit each others' data.

What this means

For the majority of users the upgrade and data conversion should be completely automatic.

In cases where automatic detection is not possible we do not modify any user-data. However, we have provided a tool which can be used to change the encoding of user data after the upgrade.

Do I need to do anything?

Probably not. The upgrader should have converted any applicable data in your database into UTF-8 encoding.

Appendix

The upgrade system will apply the following conversion rules based on the manager's selected language:

Preparing for an installation

If you have not already done so, review the system requirements and have the following information ready to provide during the installation:

• If you intend to use an FTP client to upload OpenX to the server you'll need:
  • the name of the FTP server
  • the FTP account username
  • the FTP account password
• For the OpenX database you'll need:
  • Database server name
  • Port number
  • Database username
  • Database password

Download OpenX

Download the latest version of OpenX from the OpenX website to your local computer. Unpack the archive (the unpacked directory will be named something similar to 'OpenX vX.X.XX'). Remove the numbers from the filename to make it easier to navigate to OpenX, and also to simplify future upgrades.

Copy OpenX to your server

Upload the OpenX directory in the standard way you load files to your server, for example FTP or SCP. Load the directory to a location on your server that is accessible from a browser. For example, if your web server structure is /var/www/html/ you can place your extracted folders and files in /var/www/html/openx, then access it with the www.example.com/openx URL.

When you have uploaded OpenX to the server, open a web browser and visit your OpenX website to launch the installation wizard.

Running the installation wizard

The OpenX install wizard guides you, step-by-step, through each stage of the install process.

To start the installer, open a web browser and navigate to the directory where OpenX is stored. For example: www.example.com/openx.

If the installer does not start automatically, perhaps your web server does not allow PHP files to be used as the index file in a directory. To start the installer manually, navigate to the index.php file in the admin directory. For example /openx/admin/index.php.

The wizard guides you through the following sections:

Welcome

The Welcome page tells you which version of OpenX you are going to install and links to the available documentation and support.

Click Continue to proceed to the Terms page.

Terms

The Terms page provides important information about the OpenX licence. Before you continue with the installation you are advised to read and agree to the terms and conditions of the GPL license.

Click Accept to proceed to the Policy page.

Policy

The Policy page describes the OpenX Privacy and Data Usage policy. Please review this information to learn about the data that OpenX collects and how it is used. You must agree with this policy before continuing with the upgrade. In the Synchronisation settings section tick the Check for Updates checkbox if you want to receive software update alerts in OpenX.

Click I Agree to proceed to the System Check page.

System Check

The System Check checks that the PHP setup is correct, that correct file permissions are set for OpenX folders, and whether a previous version of OpenX exists. All issues are reported in the PHP, File permissions and Application Check sections at the bottom of the page. You must resolve all reported issues and run another system check before you can proceed.

PHP setup

If you prepared your system correctly, there will be no issues with the PHP setup.

See also: System requirements for information about the PHP requirements.

Permission to write to OpenX folders

OpenX needs permission to write to many files in the system. The system check verifies the permissions set for application folders and lists folders with incorrect permissions in the File Permissions section. The list includes shell commands you can enter to correct these permissions. Alternatively, in an FTP client, change the CHMOD setting to 0777 for each problematic folder. If you cannot find where to set these values in your FTP client look for a menu entry for permissions or properties, or consult the documentation for the application.

Completing the system check

You must resolve all reported issues before you are allowed to proceed. When the system is configured correctly, you can click Continue to proceed.

Database Setup

In the Database Setup page, enter the details necessary to connect to your database. If you do not know these details, contact your website administrator or ISP.

Click Continue to open the Configuration Setup page.

Configuration Setup

The Configuration Setup page provides some extra configuration options. Most publishers do not need to change any of these options. Review the settings to ensure that they are correct and click Continue to proceed.

Admin setup

Enter a username, password, and contact email address for the administrator. This account sets up the "super admin" user, who will have full control over your OpenX installation. Ensure that the username and password are memorable and keep a secure record of them. Click Continue when you are finished.

The Password Recovery section in Managing passwords explains how to reset your password if you forget it.

Data setup

In the Data Setup page, you can choose to add a sample inventory to your database. This is a useful way to learn the relationship between advertisers, campaigns, and banners, and to see how zones are set up. Click Continue to proceed.

Finished

When the installation is complete, the OpenX login page is displayed.