I am not a huge fan of online ads. But I recognize they are a necessary part of some business models, including those of some of my clients. As such, it is my obligation to offer — and support — a solution.

Knowing what I now know, I would not be likely to recommend OpenX to any client. In 2008, however, I was new to the world of ad server software, and took someone else’s recommendation to go with OpenX. Much has changed since 2008, but — for better or worse, and I’m leaning toward worse, mainly because I’ve come to realize how bad OpenX is, and that there’s probably not any better alternative — that client is still relying on OpenX for their web ads.

The intervening years have brought a handful of challenges where OpenX is concerned. Besides its excessive complexity (143 data tables! most of which by this point either contain 0 records or over 2 million!) and inscrutable structure (why are statistics a “plugin”? and why are the built-in plugins not actually installed automatically?), it is notoriously susceptible to security exploits.

Such was the case about a year ago, the first time I ran into the problem of missing statistics, when I ran the version 2.8.8 upgrade to patch a hacked installation.

This time around, I had only myself to blame. An errant maintenance script in my own CMS had inadvertently deleted all of the files in the openx directory, effectively wiping out OpenX, though thankfully leaving the precious data tables untouched. I had an outdated backup that contained most of the ad assets, and the client was able to provide replacements for the newer ones, so within the same day we were back up and running, serving ads on the website again.

Except… no statistics.

My second mistake was that I hadn’t made notes of the solution I had eventually discovered the first time we ran into this problem, so I was forced to spend several days once again trying in vain to hunt down the solution on Google. Today I finally did, so I would like to share it here, with the deepest gratitude to its author, Frederik Schøning.

Before I detail precisely what did, at long last, restore statistics to our OpenX installation, I want to describe a few of the things I did before that, which did not solve the problem, but which may have been critical to my ultimate success. (I’ve learned over the years that, when debugging, one problem may obscure another, and that there’s rarely only one thing wrong.)

First, installation itself. For quite a while, OpenX required you to jump through a number of frustrating hoops just to get the actual installer. Recently, they’ve cut the crap and provided a direct download link. I ran the installation, snagging briefly on the fact that my data tables already existed. The installer sort of accounts for this situation, however, allowing you to upgrade the existing data tables instead of creating a brand new database. (This, I believe, is where my problems with statistics began. More on that in a minute.)

After installation, I went through several iterations of repairing data tables, fixing file permissions, copying files from my old installation (be sure to keep that around, if possible!), etc. Lather, rinse, repeat. I spent a lot of time in the Maintenance section of the OpenX admin system, trying to get the plugins to stop reporting errors.

Here’s a key problem: statistics in OpenX require the openXDeliveryLog plugin (possibly among others). Although it comes with the installer, it is not installed by default. There are empty folders under plugins where the plugins should be. Instead, the plugins are all zipped, inside the etc/plugins directory. Fortunately, when running OpenX as the administrator account, you can go to the Plugins tab and install them. Unfortunately, they still probably won’t stop reporting errors, because not all of the files end up in the right places. I had to extract openXDeliveryLog.zip and manually place the files for the deliveryDataPrepare plugin, a prerequisite for openXDeliveryLog.

A few more lather-rinse-repeat cycles and I got the plugins to stop reporting errors. But I still wasn’t getting stats. That’s when I discovered Frederik Schøning’s blog post, and was reminded that last time this happened, the problem was a second administrator account in the database. As he suggests, last time I was convinced this happened when the system got hacked. This time, I’m pretty sure there was no hack; the duplicate administrator account seems to have been created while I was running the installation/upgrade process (as hinted at above).

If this is the case, then the OpenX installer is broken on upgrade, and it creates a situation that breaks statistics reporting. Fortunately, it’s pretty easy, if you have direct database access, to remove the duplicate administrator account. Check the ox_accounts table for more than one record with account_type set as ADMIN:

SELECT * FROM `ox_accounts` WHERE `account_type` = 'ADMIN';

One of these should have account_id equal to 1 since it’s the first account you create. Delete the other:

DELETE FROM `ox_accounts` WHERE `account_type` = 'ADMIN' AND `account_id` != 1;

(Be sure to change 1 in that query to whatever the lower numbered account ID is, if it’s not 1. Or you could make the last bit = 80 or whatever the higher numbered account ID is. In my case, it was 80, but I suspect that will vary.)

Next, you need to make the proper admin account the system’s configured admin account again. That’s in the ox_application_variable table. As per Frederik Schøning:

UPDATE ox_application_variable SET `value` = 1 WHERE `name` = 'admin_account_id';

(Again, if your correct admin account’s ID is not 1, change the 1 to the appropriate value.)

As soon as I made these changes in the database, statistics were instantly working again. And I could stop thinking about OpenX… until the next crisis hits.

But before I stop thinking about it entirely, I want to pose a couple of questions to ponder:

1. Why, when updating an existing installation, does OpenX create a new administrator account?

2. Why are broken statistics the only (readily apparent) problem resulting from the presence of that second administrator account?