Skip to main content ↑ ↓
Topic: Database error when sorting personal messages (Read 2168 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Database error when sorting personal messages

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:

Code: [Select]
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
sorry for my bad english

Re: Database error when sorting personal messages

Reply #1 –

I wonder why postgre is not complaining... Strange.
Anyway the issue is:
Quotesql_mode=only_full_group_by
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:
Code: [Select]
		self::$_db->query('', '
			SET NAMES UTF8',
			array(
			)
		);
is, the following code:
Code: [Select]
		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. :'(
Bugs creator.
Features destroyer.
Template killer.

Re: Database error when sorting personal messages

Reply #2 –

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
Code: [Select]
GROUP BY pm.id_pm_head
to
Code: [Select]
GROUP BY pm.id_pm_head, id_pm
the "fix" .... Damn, now I'll have to install mysql 5.7 as well !

Re: Database error when sorting personal messages

Reply #3 –

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
Last Edit: January 05, 2017, 02:21:11 pm by radu81
sorry for my bad english