Oct 232008
 

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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

  One Response to “openx upgrade issues”

  1. Things I’ll want to do before I move this to production:

    – update freebsddiary.org
    – update freshports and freshsource
    – update the Phorum code for ads
    – update Burst
    – update AdSense
    – update adsl and broadband archives