#682 SQL fails in phpAds_logImpression

[Bob Dewhirst]

Hi there,

2.0.11-pr1 running on FreeBSD 6.2, Apache 1.3.37, PHP 5.2.3 and MySQL 5.1.19-beta

Just tracked down why our system was only registering one ad view per banner/zone per hour.

In the function phpAds_logImpression:

$result = phpAds_dbQuery(
"UPDATE ".(false && $phpAds_config['insert_delayed'] ? 'LOW_PRIORITY' : '')." ".
$phpAds_config['tbl_adstats']." SET views = views + 1 WHERE day = NOW()
AND hour = HOUR(NOW()) AND bannerid = '".$bannerid."' AND zoneid = '".$zoneid."'
AND source = '".$log_source."' ");

the update query was failing as no matching rows were found, this is because NOW() returns the full date including hours, seconds etc. where I think you are expecting it to return just the year month date.

I fixed this by replacing day = NOW() with day = DATE(NOW())

And all works again.

Thanks for a great product and I hope this bug report helps it become even better.

Cheers,
Bob.

[David Danier]

Logged In: YES
user_id=165983
Originator: NO

Same problem here, changes described in the report worked great.

I think this bug was triggered by a mysql-update here. So perhaps many people will run into the same problem when distributions provide updates to the mysql-server or hosters do updates. Should be really fixed soon.

[David Danier]

Logged In: YES
user_id=165983
Originator: NO

See https://developer.openads.org/ticket/716

[Remi]

Logged In: YES
user_id=1936581
Originator: NO

same pb, openads doesn't log impressions and clicks since a month. I'll check with the db admin if he made an upgrade.

here is a patch for libraries/lib-log.inc.php

175c175
< $phpAds_config['tbl_adstats']." SET views = views + 1 WHERE day = NOW()
---
> $phpAds_config['tbl_adstats']." SET views = views + 1 WHERE day = DATE(NOW())
183c183
< $phpAds_config['tbl_adstats']." SET clicks = 0, views =1, day = NOW(),
---
> $phpAds_config['tbl_adstats']." SET clicks = 0, views =1, day = DATE(NOW()),
225c225
< $phpAds_config['tbl_adstats']." SET clicks = clicks + 1 WHERE day = NOW() AND
---
> $phpAds_config['tbl_adstats']." SET clicks = clicks + 1 WHERE day = DATE(NOW()) AND
233c233
< $phpAds_config['tbl_adstats']." SET clicks = 1, views = 0, day = NOW(),
---
> $phpAds_config['tbl_adstats']." SET clicks = 1, views = 0, day = DATE(NOW()),