Missing Statistics in OpenX Again – This Time in 2.8.7

After upgrading to OpenX 2.8.7 from 2.4.1 our statistics suddenly seemed to have vanished. Debugging an issue like this isn’t just straight forward, but after digging through google searches, wiki pages at OpenX and, well, reading the source (brrrrrrrrr), I think I’ve nailed it.

After upgrading to 2.8.7 the DeliveryLog plugin didn’t get installed – which meant that no delivery / clicks / impressions were logged. After discovering that this had been moved to a plugin I tried simply unzippping the plugin and copying the files to the plugins/-directory. This seemed to make OpenX recognize the plugin if I went to “groups” in the plugin menu, but not under the “plugin” menu. Another problem was the fact that it didn’t actually log anything, which could be considered a problem.

All the Google searches had shown that OpenX had changed the logging format to a new table structure (named buckets), but they don’t provide of restoring / creating the bucket tables if they don’t exist, and they don’t give any error about the bucket tables missing if the plugin doesn’t load. I couldn’t find anything at all about how the tables should look and which tables should be installed, but I finally tried to simply install the plugin through the web interface (Log in as Administrator -> Select Plugins in the top menu) by uploading the zip file directly, and then FINALLY the post install script ran. That created the tables (I’ll dump the definitions later if someone needs them), and after reloading the ads the bucket tables started getting values.

Now we’ll just have to hope that they actually gets aggregated into something useful as well..

PS: I’m less than impressed by the OpenX upgrade procedure, it always seem to fsck up some detail that leaves your installation in limbo, without being able to detect that something has gone wrong and provide a way to resolve the issue. I understand that they need to – and want to – focus on their pay product, so well, I’ll keep having to fix things manually for a while, but Google’s Doubleclick for Small Businesses may see a new customer soon.

Followup on The Missing Statistics in OpenX

After my previous post about the missing OpenX statistics because of crashed MySQL-tables, I got a very nice and helpful comment from one of the OpenX developers. To put it one single word: awesome. If you’re ever going to run a company and have to look after your customers (even if you release your project as open source), simply do that. People will feel that someone are looking out for them.

Anyways, as promised, this were supposed to be a follow up. We didn’t manage to get the impressions statistics back, but the missing clicks returned after repairing the tables. The tip from Arlen didn’t help either, but I have a few suggestions for how to make the script easier to use.

I were kind of perplexed about how I could give the dates for the time interval it was going to rebuild the statistics. The trick was to change two define()-s in the top of the code. Not very user friendly, so I made a small change to use $argc and $argv instead. That way I could do:

    php regenerateAdServerStatistics.php "2008-06-01 10:00:00" "2008-06-01 10:59:59"

instead of having to edit the file and changing the defines every time. After doing this simple change, I could also write a small helper script that ran the regenerateAdServerStatistics.php file for all the operation intervals within the larger interval (an operation interval is an hour, while my interval were several days).

So, here it is, regenerateForPeriod.php:

 ");
    }

    $start = $argv[1];
    $end = $argv[2];

    $start_ts = strtotime($start);
    $end_ts = strtotime($end);

    if (!$start_ts || !$end_ts || ($start_ts >= $end_ts))
    {
        exit("Invalid dates.");
    }

    $current_ts = mktime(date('H', $start_ts), 0, 0, date('m', $start_ts), date('d', $start_ts), date('y', $start_ts));

    while($current_ts < $end_ts)
    {
        system('php regenerateAdServerStatistics.php "' . date('Y-m-d H', $current_ts) . ':00:00" "' . date('Y-m-d H', $current_ts) . ':59:59"');
        $current_ts += 3600;
    }
?>

This runs the renegerateAdServerStatistics.php script for each operation interval. If your ad server uses a larger interval than 3600 seconds, change the setting to a more appropriate value. Before doing this, you’ll want to remove the sleep(10) and the warning in regenerateAdServerStatistics.php, so that you don’t have to wait 10 seconds for each invocation of the script. I removed the warning and sleep altogheter, but hopefully someone will commit a command line parameter to regenerateAdServerStatistics.php that removes the delay. I didn’t have time to clean up the code and submit an official patch today, but if there is interest, leave a comment and I’ll consider it.

Debugging Missing Statistics in OpenAds (OpenX)

Our statistics in OpenAds had suddently gone missing in action, and I started suspecting a few errors we’d gotten earlier about fubar-ed MyISAM-tables. First, check out debug.log (or maintenance.log if you’re running a newer version than us) in the var-directory of your Openads-installation. The easiest thing to do here is to search for the string ’emergency’, which will be posted to the log each time something fails in MySQL. The MDB2 error message that is included will show you the error message from MySQL in one of the fields (about 15-25 lines down), which will give you the reason for the error (if MySQL is to blame).

Some tables had been marked as crashed in our MySQL-installation, so we had to find out what to fix. A quick run with myisamchk in the MySQL-data directory for the database gave us a few hints:

myisamchk *.MYI > /tmp/myisamcheckoutput

By redirecting the normal output you’ll just get the error messages to stderr (Openads has quite a few tables, so your console will fill up quite quick otherwise) (as stdout will be redirected to /tmp/myisamcheckoutput). You’ll also be able to check the output by using less on /tmp/myisamcheckoutput.

If any tables are having problems, you can run:

REPAIR TABLE ;

in your MySQL console, and the table should be repaired in the background. After doing this, it’s time to get maintenance back up and running again.

Run the maintenance.php file manually (or wait until it gets triggered within the next hour):

php /scripts/maintenance/maintenance.php