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
| Comment | File | Size | Author |
|---|---|---|---|
| #10 | drupal_6.x-pgsql_error-1171630-10.patch | 1.07 KB | moskito |
| #5 | userblock_pgincomopatibility-1171630-4.patch | 1.07 KB | ben coleman |
| #3 | user-1171630-3.patch | 1.08 KB | moskito |
| 6.22.patch | 996 bytes | maschi |
Comments
Comment #1
moskito commentedSame issue here, using Postgres 8.4.5
Patch submitted works fine, but should have more reviews.
Comment #3
moskito commentedModification made by maschi repatched against 6.x-dev.
Comment #4
ben coleman commentedmoskito's patch works fine here. PostgreSQL 8.4.7
Comment #5
ben coleman commentedHowever, 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).
Comment #6
interestingaftermath commentedsubscribe
Comment #7
moskito commentedSolution 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.
Comment #8
_Mitto_ commentedI 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
Comment #9
hawk259 commentedsubscribe
Comment #10
moskito commentedSolution 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.
Comment #11
gábor hojtsyWho tested this on MySQL?
Comment #12
moskito commentedI 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.
Comment #13
moskito commentedIt 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?
Comment #14
bdmc commentedI 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.
Comment #15
moskito commentedI guess it is time to get it commited.
Comment #16
bendiy commentedJust ran into this in 6.26. #5 worked great.
Comment #17
licebaseadmin commentedStill can confirm warning in Drupal 6.29, PostgreSQL 8.4. Patch from #10 works, position moved from line 800 to 814.
Comment #18
licebaseadmin commented10: drupal_6.x-pgsql_error-1171630-10.patch queued for re-testing.