I get a SQL error w/ PostgreSQL 8.3.3 accessing /admin/logs/visitors, the fix is to cast uid to text in the following CONCAT()...

modules/statistics/statistics.module, line 321:
$sql_cnt = "SELECT COUNT(DISTINCT(CONCAT(uid::text, hostname))) FROM {accesslog}";

Comments

pierce’s picture

pierce CreditAttribution: pierce commented
Priority: Normal » Critical

i've seen no response to this. its still a problem. again, adding ::text to uid in that statement fixes it for PostgreSQL 8.3, but I have no idea if that works in MySQL.

drumm’s picture

drumm
he/him
Location NY, US
CreditAttribution: drumm commented
Version: 5.10 » 7.x-dev

No, ::text would not work in MySQL. I would say just do COUNT(DISTINCT(uid, hostname)), but "PostgreSQL currently does not support DISTINCT with more than one input expression." according to http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html.

The same query is present in the development version, in statistics.admin.inc. This will have a better chance of getting attention in that version and can be backported once solved. This query was introduced by http://drupal.org/node/48415.

pierce’s picture

pierce CreditAttribution: pierce commented

Will MYSQL support...

 $sql_cnt = "SELECT COUNT(DISTINCT(CONCAT(CAST (uid AS text), hostname))) FROM {accesslog}";

?

thats valid standard SQL, but I'm not very familiar with mysql's behavior here with regards to casting.

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented
Status: Active » Closed (duplicate)

Marking this as a duplicate of #229051: Top Visitor Page problem with PostgreSQL 8.3.