In these days I did a clean install of my vps so I had to upload all files and restore a backup of the database. Before this I'm sure that sorting personal messages based on subject and sender worked fine since I did recent tests. Now when I am trying to sort messages based on subject or sender I get a database error:
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'db_xx.pm.subject' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
File: /sources/subs/PersonalMessage.subs.php
Riga: 1036
Nota: la versione del tuo database รจ 1.0.2.
as you can see it also tells me that the version of my database is 1.0.2, but if I check into Admin > Maintenance > Routine it tells that my database version is 1.0.4 which should be latest for 1.0.x version
I wonder why postgre is not complaining... Strange.
Anyway the issue is:
that makes mysql behave more similarly to postgre.
The "quick&dirty" workaround is to add to sources/database/Db-mysql.class.php, more or less where:
self::$_db->query('', '
SET NAMES UTF8',
array(
)
);
is, the following code:
self::$_db->query('', '
SET sql_mode = {string:empty}',
array(
'empty' => ''
)
);
it should make it work again.
@Spuds this is new to mysql 5.7, so I guess we'll have to deal with it as well. :'(
Looks that way >:(
That one is kind of odd as we use max (an aggregate function) in the select, so I don't think its ambiguous which row we want, . For that query is it just changing
GROUP BY pm.id_pm_head
to
GROUP BY pm.id_pm_head, id_pm
the "fix" .... Damn, now I'll have to install mysql 5.7 as well !
In the meantime I found this which should also work :
https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7
=== edit ===
the solution above works for me, I prefer to use it instead of modifying core files.
Thanks for your help
Tracked https://github.com/elkarte/Elkarte/issues/2926