Hi there,

after updating to 6.22 the following error message appeared:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: column "s.timestamp" must appear in the GROUP BY clause or be used in an aggregate function in /var/www/drupal6/avk_sozialprojekte_de/includes/database.pgsql.inc on line 138.
    * user warning: query: SELECT u.uid, u.name, MAX(s.timestamp) AS timestamp FROM drupal6_users u INNER JOIN drupal6_sessions s ON u.uid = s.uid WHERE s.timestamp >= 1306564137 AND s.uid > 0 GROUP BY u.uid, u.name ORDER BY s.timestamp DESC LIMIT 10 OFFSET 0 in /var/www/drupal6/avk_sozialprojekte_de/modules/user/user.module on line 789.

I am using Postgres 8.3.

Attached there is a patch.

regards,
Maschi

CommentFileSizeAuthor
#10 drupal_6.x-pgsql_error-1171630-10.patch1.07 KBmoskito
#5 userblock_pgincomopatibility-1171630-4.patch1.07 KBBen Coleman
#3 user-1171630-3.patch1.08 KBmoskito
6.22.patch996 bytesmaschi
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

moskito’s picture

moskito CreditAttribution: moskito commented
Status: Active » Needs review

Same issue here, using Postgres 8.4.5
Patch submitted works fine, but should have more reviews.

Status: Needs review » Needs work

The last submitted patch, 6.22.patch, failed testing.

moskito’s picture

moskito CreditAttribution: moskito commented
Version: 6.22 » 6.x-dev
Status: Needs work » Needs review
FileSize
user-1171630-3.patch1.08 KB

Modification made by maschi repatched against 6.x-dev.

Ben Coleman’s picture

Ben Coleman CreditAttribution: Ben Coleman commented

moskito's patch works fine here. PostgreSQL 8.4.7

Ben Coleman’s picture

Ben Coleman CreditAttribution: Ben Coleman commented
FileSize
userblock_pgincomopatibility-1171630-4.patch1.07 KB

However, the attached patch also fixes it. The difference is that instead of adding s.timestamp to the GROUP BY phrase, I'm changing the ORDER BY field to timestamp. Both fix the PostgreSQL syntax error. However, it looks to me like the last time this issue was dealt with (#148974: "who's online" block broken), the final code used something like the 'ORDER BY timestamp' solution. I'm not sure it that ever made it into core or not. The link in the final comment points to a no-longer-existing site.

I don't yet know enough about PostgreSQL to say how the data output would be different between the two versions (I know even less about MySQL).

interestingaftermath’s picture

interestingaftermath CreditAttribution: interestingaftermath commented

subscribe

moskito’s picture

moskito CreditAttribution: moskito commented

Solution proposed by #5 also seems to work here. At least, no more warnings.

I am not a pg speciallist, but this seems to me that do not order the query in the right way. I think that it will order it by the maximum s.timestamp, since timestamp is a alias to MAX(s.timestamp), witch is a constant in the query.

I will investigate it further later.

_Mitto_’s picture

_Mitto_ CreditAttribution: _Mitto_ commented
Version: 6.x-dev » 6.22

I get this warning after updateing 6.19 to 6.22
Solution proposed by #5 also seems to work here. At least, no more warnings.
PostgreSQL 9.0.1

hawk259’s picture

hawk259 CreditAttribution: hawk259 commented

subscribe

moskito’s picture

moskito CreditAttribution: moskito commented
Version: 6.22 » 6.x-dev
Status: Needs review » Reviewed & tested by the community
FileSize
drupal_6.x-pgsql_error-1171630-10.patch1.07 KB

Solution in #5 seems to work perfectly. I think it had time to have enough reviews.

I appended the #5 solution patched against 6.x-dev.

Gábor Hojtsy’s picture

Gábor Hojtsy
he/him
Primary language Hungarian
Location Hungary
CreditAttribution: Gábor Hojtsy commented
Status: Reviewed & tested by the community » Needs review

Who tested this on MySQL?

moskito’s picture

moskito CreditAttribution: moskito commented

I just tested it updating a drupal 6.19 installation in a mysql 5.0.77 server to the 6.x-dev + patch #10.

No errors and "who's online" block seems to work as designed.

moskito’s picture

moskito CreditAttribution: moskito commented

It passed in the bot test, I already tested it under mysql and postgres environment and 1 month without bad test report. Is it time to set it as tested?

bdmc’s picture

bdmc CreditAttribution: bdmc commented

I just encountered this issue after upgrading to PostgreSQL 8.4 and Drupal 6.26.

In response, I also developed the same patch as the original poster, and have been successful, in the PostgreSQL environment, with both that patch and the one in #5.

moskito’s picture

moskito CreditAttribution: moskito commented
Status: Needs review » Reviewed & tested by the community

I guess it is time to get it commited.

bendiy’s picture

bendiy CreditAttribution: bendiy commented
Issue tags: +PostgreSQL

Just ran into this in 6.26. #5 worked great.

LiceBaseAdmin’s picture

LiceBaseAdmin CreditAttribution: LiceBaseAdmin commented

Still can confirm warning in Drupal 6.29, PostgreSQL 8.4. Patch from #10 works, position moved from line 800 to 814.

LiceBaseAdmin’s picture

LiceBaseAdmin CreditAttribution: LiceBaseAdmin commented

10: drupal_6.x-pgsql_error-1171630-10.patch queued for re-testing.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, 10: drupal_6.x-pgsql_error-1171630-10.patch, failed testing.

Status: Needs work » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.