Missing table after upgrading OpenX

OpenX is an open source ad server. There are two versions: a paid Enterprise edition, which includes full-support, and a free, Community support edition. This post relates to the Community edition.

An upgrade from version 2.6.3 to the latest version 2.8.5 (at time of writing) resulted in a database connection error on the ‘Statistics’ tab. OpenX was looking for a table called ox_ext_market_stats, which didn’t exist in the new database. Checking the original version of the database, it didn’t exist there either.

Googling for the table name returned only three hits, all of them Japanese language sites. However, one of these did include a CREATE TABLE statement to create the missing table. The statement didn’t work, but did provide the basis for putting together the following statement which does work:

CREATE TABLE `ox_ext_market_stats` (
`date_time` datetime NOT NULL,
`market_advertiser_id` char(36) default NULL,
`website_id` mediumint(9) NOT NULL,
`ad_width` smallint(6) NOT NULL,
`ad_height` smallint(6) NOT NULL,
`zone_id` mediumint(9) default NULL,
`ad_id` mediumint(9) default NULL,
`impressions` int(11) NOT NULL,
`clicks` int(11) default NULL,
`requests` int(11) default NULL,
`revenue` decimal(17,5) NOT NULL,
UNIQUE KEY `ox_ext_market_stats_unique_row` (`date_time`,`website_id`,`zone_id`,`ad_id`,`ad_width`,`ad_height`,`market_advertiser_id`),
KEY `ox_ext_market_stats_date_time` (`date_time`),
KEY `ox_ext_market_stats_ad_id_date_time` (`ad_id`,`date_time`),
KEY `ox_ext_market_stats_zone_id_date_time` (`zone_id`,`date_time`),
KEY `ox_ext_market_stats_website_id_date_time` (`website_id`,`date_time`)
) ENGINE = MYISAM CHARACTER SET latin1;