0

I installed latest openX ad server(publisher server) with postgres 9.x version.

After successful installation and configuration i login and click at "Statistics" tab i see the following error.

i don't see this error when i installed openX server with mysql any quick fix ?

PEAR Error

MDB2 Error: unknown error _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT m.clientid AS advertiser_id,d.campaignid AS placement_id,s.ad_id AS ad_id,SUM(s.impressions) AS sum_views,SUM(s.clicks) AS sum_clicks,SUM(s.revenue) AS sum_revenue, m.campaignid || IF( LENGTH(market_advertiser_id) > 0, ('_' || market_advertiser_id || ''), '') || ad_width || ' x ' || ad_height AS ad_id,( m.campaignid || IF( LENGTH(market_advertiser_id) > 0, ('_' || market_advertiser_id || ''), '') || ad_width || ' x ' || ad_height ) AS pkey FROM "ox_ext_market_stats" AS s INNER JOIN "ox_banners" AS d ON (d.bannerid=s.ad_id) INNER JOIN "ox_zones" AS z ON (z.zoneid=s.zone_id) INNER JOIN "ox_campaigns" AS m ON (m.campaignid=d.campaignid) INNER JOIN "ox_affiliates" AS p ON (p.affiliateid=z.affiliateid) INNER JOIN "ox_clients" AS a ON (a.clientid=m.clientid) WHERE s.ad_id IN (1,2) AND a.type = 1 AND s.zone_id <> 0 AND s.date_time>='2011-02-22 00:00:00' AND s.date_time<='2011-02-22 23:59:59' GROUP BY advertiser_id,placement_id,pkey]

[Native message: ERROR: column "s.ad_id" must appear in the GROUP BY clause or be used in an aggregate function at character 65]

PEAR Error

MDB2 Error: unknown error _doQuery: [Error message: Could not execute statement] [Last executed query: SELECT m.clientid AS advertiser_id,d.campaignid AS placement_id,s.ad_id AS ad_id,SUM(s.impressions) AS sum_views,SUM(s.clicks) AS sum_clicks,SUM(s.revenue) AS sum_revenue, m.campaignid || IF( LENGTH(market_advertiser_id) > 0, ('_' || market_advertiser_id || ''), '') || ad_width || ' x ' || ad_height AS ad_id,( m.campaignid || IF( LENGTH(market_advertiser_id) > 0, ('_' || market_advertiser_id || ''), '') || ad_width || ' x ' || ad_height ) AS pkey FROM "ox_ext_market_stats" AS s INNER JOIN "ox_banners" AS d ON (d.bannerid=s.ad_id) INNER JOIN "ox_campaigns" AS m ON (m.campaignid=d.campaignid) INNER JOIN "ox_clients" AS a ON (a.clientid=m.clientid) WHERE s.ad_id IN (1,2) AND s.zone_id = 0 AND a.type = 1 AND s.date_time>='2011-02-22 00:00:00' AND s.date_time<='2011-02-22 23:59:59' AND s.zone_id = 0 GROUP BY advertiser_id,placement_id,pkey]

[Native message: ERROR: column "s.ad_id" must appear in the GROUP BY clause or be used in an aggregate function at character 65] enter image description here

d-man
  • 57,473
  • 85
  • 212
  • 296
  • Does not look like PostgreSQL query to me. PostgreSQL requires all query attributes to be grouped or used in aggegate functions. Also IF used like in the query is MySQL syntax. – jmz Feb 25 '11 at 07:08

1 Answers1

2

Ok, i have found a solution. To fix the problem, must add this code after line 1031 in lib/max/SqlBuilder.php .

$aGroupColumns[] = "s.ad_id";

d-man
  • 57,473
  • 85
  • 212
  • 296
  • 1
    it seems that some times you need to reinstall the openX server after this change into source. – d-man Feb 28 '11 at 04:33
  • This was very helpful hint! Thank you very much! It saved me lot of time... the line in latest version (2.8.10) is AFTER 1032, I have it as last line in the condition as line 1034. I also copy the note about OpenX not reflecting the change immediately - I restarted apache and cleared the cache (delete everything except README.txt from `var/cache`) - I think the latter is enough. – Kamil Šrot Nov 29 '12 at 09:13
  • You can also find [this toppic](http://forum.openx.org/index.php?showtopic=503480711) helpful – Kamil Šrot Nov 29 '12 at 09:19