Errors after upgrading to 6x1.0

- From which version have you upgraded? (I don't think that query has changed since rc4)
- Which MySQL/PHP versions are you using?
- Have you run update.php (if you were using a version older than rc4 before)

Edit: The query works for me on Mysql 5.1

Very similar error here:

Invalid use of group function query: SELECT pmi.thread_id, MIN(pm.subject) as subject, MAX(pm.timestamp) as last_updated, SUM(pmi.is_new) as is_new, (SELECT GROUP_CONCAT(DISTINCT pmia.uid SEPARATOR ",") FROM teamipxpm_index pmia WHERE pmia.thread_id = pmi.thread_id) AS participants, MIN(pm.timestamp) as thread_started FROM teamipxpm_message pm INNER JOIN teamipxpm_index pmi ON pm.mid = pmi.mid WHERE (pmi.uid = 1) AND (pmi.deleted = 0) GROUP BY pmi.thread_id HAVING (((SELECT pmf.author FROM teamipxpm_message pmf WHERE pmf.mid = pmi.thread_id) = 1 AND COUNT(pmi.thread_id) > 1) OR (SELECT COUNT(*) FROM teamipxpm_message pmf INNER JOIN teamipxpm_index pmif ON (pmf.mid = pmif.mid) WHERE pmif.thread_id = pmi.thread_id AND pmf.author <> 1) > 0) ORDER BY MAX(pmi.is_new) DESC, last_updated DESC LIMIT 0, 25 in /home/pathtowebserverfolder/sites/all/modules/privatemsg/privatemsg.module on line 670.

(If this is different, let me know and I'll raise a separate issue).

- Upgraded from rc4.
- PHP 5.2.6
- mysql 4.1.22-standard
- Yes ran update.php. It didn't pick up that there were any upgrade scripts to run. Went to the Messages screen - got the above error.
Also tried, running the latest upgrade script for privatemsg from update.php (6006 I think?), it ran successfully, but no change to the error on the messages screen.

Result is no messages at all are accessible.

- mysql 4.1.22-standard

I'm pretty sure this is the issue. I assume we're doing something in that query that mysql 4.1 does not support. I'll try to fix this so that it does work on MySQL 4.1 too but if possible, I'd suggest you upgrade to MySQL 5. (After all, mysql 4.1 is unsupported as far as I know).

I think the only change to that query was that we added the MAX() function to the ORDER BY to fix a bug. You can try to run that query in phpmyadmin or a similiar tool and replace "MAX(pmi.is_new)" with just "pmi.is_new". If that works, please report back. You can also do the same in privatemsg.module on line 1183 to have a temporary fix.

Tried changing:

"MAX(pmi.is_new)"
to
"pmi.is_new"

in the privatemsg.module. This worked perfectly. Thanks Berdir.

(After all, mysql 4.1 is unsupported as far as I know).

mysql 4.1+ is supported on Drupal 6.
http://drupal.org/requirements

Correct, but what I meant is official MySQL support by Sun which will end by the end of this year, see: http://www.mysql.com/about/legal/lifecycle/#calendar.

Also, you should be aware that the link you posted is just for Drupal core, contrib modules may but must not support MySQL 4.1. Privatemsg tries to, but I don't have access to neither MySQL 4.1 nor PHP4 so users of these versions are required to test Privatemsg on these platforms on their own.

Anyway, attached is a patch that removes the MAX() for MySQL, please remove your custom change and try this patch. Note however, that you have to live with a small ordering bug that occurs when you have threads with more than one new messages.

No worries. I'm sure there's plenty of people on this platform for the time being that are happy to test it (myself included). I always deploy onto a test instance, so it's not a big deal if something goes wrong.

I'd have upgraded a long time ago, but the site is on a shared hosting platform which currently only provides 4.1. Am in the process of trying to convince them it's time to upgrade.

Thanks for the patch, will test it and report back. We'll live with the ordering bug, guys from my site didn't notice any of the bugs in rc4, let alone this one.

Reverted the change, and applied the patch. Works perfectly.

What's the next step, does it get added to the next release?

Commited to 6.x-1.x and 6.x-2.x, not required on 7.x-1.x

I just fixed the same problem using:

INNER JOIN (select *, max(is_new) as max_is_new from {pm_index} group by mid) pmi ON pm.mid = pmi.mid

and

ORDER BY pmi.max_is_new

I believe this would solve the ordering problem while avoiding the error.

Can you roll a patch so that I can see the difference?

With this and the other patch (the » issue) committed, any chance of a quickfire 1.1 release?

There haven't been any other bug reports so far, so yeah, i think we can do that.