MariaDB/MySQL group by error

MariaDB/MySQL group by error

After we upgraded our MySql/MariaDB installation to 5.7.16 our CiviCRM gave us some “unknown DB errors”. We traced it back to a group by error thrown by MySql.

As of version 5.7.5 the default SQL modes includes the ONLY_FULL_GROUP_BY option.

To fix this issue you can of course change your code, but if that isn’t an option, you can remove this mode setting.

First select your existing modes:

mysql> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

This will output all enabled modes.
Copy both outputs (one for session, one for global) and remove ONLY_FULL_GROUP_BY.
For instance:

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Your code, in our case CiviCRM, should work without errors now.

To make this change survive a reboot, add this to my.cnf (or make a seperate config file in mysql.conf.d).

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Leave a Reply

Your email address will not be published. Required fields are marked *