Plesk only_full_group_by error

0
votes

Version: Plesk Onyx v17.8.11_build1708180301.19 OS: Ubuntu 16.04.4 LTS

In my logs I have this error:

AH01071: Got error 'PHP message: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'base.count_traffic.type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' in

mysql -V -> mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper

And SQL_MODE settings are: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Same settings and same Pleask version on another server and all is working, but on this, no!

1
mysqlpleskplesk-onyx

1 Answers

0
votes

This is related to sql_mode behavior in MySQL. You need to change this parameter globally and on session and remove only_full_group_by:

SET GLOBAl sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

And on session:

SET session sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Note that for existing connections in the database this will not have any effect. The configuration will be applied only for new connections. If needed, you can edit your my.cnf and restart the database. Here is an example:

[mysqld]
sql-mode='NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'