0

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!

Digital Beko
  • 3
  • 3

1 Answers1

0

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'
Vinicius Grippa
  • 56
  • 4
  • any idea @ViniciusGrippa – Digital Beko Jun 19 '18 at 21:14
  • Do you have the query? Please share the table structure as well from all tables involved in the query using this command: `SHOW CREATE TABLE ;` And your MySQL version: `select @@version;` I'll perform some tests. – Vinicius Grippa Jun 20 '18 at 12:44
  • http://codepad.org/37MfEadO I have a procedure in database that I call from a cron! It is very strange because on other server is working with same settings! mysql version: 5.7.22 – Digital Beko Jun 20 '18 at 19:20
  • I don't see any problem with the procedures @DigitalBeko, but I would like to check these statuses: `show session variables like '%sql_mode%';` `show global variables like '%sql_mode%';` I suspect that the default configuration might be override. – Vinicius Grippa Jun 21 '18 at 03:24