openx upgrade issues

I’ve been running ads on my websites since early 1999, so nearly 10 years. It was roughly May 2002 that I started using phpPgAds. This was the PostgreSQL version of phpAdsNew. This project became OpenAds and is now known as OpenX.

Recently I decided to upgrade to the latest version of OpenX. There was a upgrade path from Openads-PgSQL-2.0.11-pr1, the latest releast of OpenAds to use PostgreSQL, to OpenX 2.6.

The first problem I encountered was related to timezones. The fix was easy, but difficult to obtain. It look like this, and affected lib/OA/Upgrade/Login.php in two places.

- $oDbh->exec("SET TIMEZONE TO 'UTC'");
+ $oDbh->exec("SET TIMEZONE TO 'GMT'");

The problem: UTC is not a valid PostgreSQL timezone. GMT is.

I posted full details in the OpenX forum.

Later, I started seeing this error in my logs:

ERROR:  invalid input syntax for type timestamp: "%8-%m-%5 %H:00:00"
STATEMENT:
                       INSERT INTO
                           tmp_ad_impression
                           (
                               date_time,
                               operation_interval,
                               operation_interval_id,
                               interval_start,
                               interval_end,
                               ad_id,
                               creative_id,
                               zone_id,
                               impressions
                          )
                      SELECT
                         DATE_FORMAT(drad.date_time, '%Y-%m-%d %H:00:00')::timestamp AS day_and_hour,
                          60 AS operation_interval,
                          98 AS operation_interval_id,
                          '2008-10-23 02:00:00'::timestamp AS interval_start,
                          '2008-10-23 02:59:59'::timestamp AS interval_end,
                          drad.ad_id AS ad_id,
                          drad.creative_id AS creative_id,
                          drad.zone_id AS zone_id,
                          COUNT(*) AS impressions
                      FROM
                          "phpads_data_raw_ad_impression" AS drad
                      WHERE
                         drad.date_time >= '2008-10-23 02:00:00'
                         AND
                         drad.date_time < = '2008-10-23 02:59:59'
                      GROUP BY
                          day_and_hour, drad.ad_id, drad.creative_id, drad.zone_id

I got onto the OpenX IRC channel and started asking questions. Eventually, Matteo Beccati came to my rescue. Matteo and I go way back. I was one of the first users of the PostgreSQL version of this product. I chose it *because* it worked with PostgreSQL. I was not interested in the MySQL version.

We found the cause of the problem quickly enough:

\df date_format
  Schema |    Name     | Result data type |        Argument data types        
 --------+-------------+------------------+-----------------------------------
  public | date_format | text             | timestamp with time zone, text
  public | date_format | text             | timestamp without time zone, text

The solution:

drop function date_format(timestamp, text)

My database had been upgraded and upgraded over the years. So this functions lingered. He suggested I look at https://svn.openx.org/openx/trunk/etc/core.pgsql.php and compare it to what I had. Most of their testing had been upgrades a fresh 2.0.11 install.

Searching through the database, function by function, I found the following issues:

openx=# \df HOUR
                       List of functions
 Schema | Name | Result data type |     Argument data types     
--------+------+------------------+-----------------------------
 public | hour | integer          | timestamp with time zone
 public | hour | smallint         | timestamp without time zone
(2 rows)

openx=# drop function hour(timestamp without time zone);
DROP FUNCTION


openx=# \df TO_DAYS
                         List of functions
 Schema |  Name   | Result data type |     Argument data types     
--------+---------+------------------+-----------------------------
 public | to_days | bigint           | timestamp without time zone
 public | to_days | integer          | timestamp with time zone
(2 rows)

openx=# drop function to_days(timestamp without time zone);
DROP FUNCTION



openx=# \df UNIX_TIMESTAMP
                            List of functions
 Schema |      Name      | Result data type |     Argument data types     
--------+----------------+------------------+-----------------------------
 public | unix_timestamp | bigint           | timestamp without time zone
 public | unix_timestamp | integer          | timestamp with time zone
(2 rows)

openx=# drop function unix_timestamp(timestamp without time zone);
DROP FUNCTION

Hopefully that will fix any hidden problems.

I’m still not running this in production yet. I’m waiting for OpenX 2.6.2 to hit the FreeBSD ports tree.