web server always change mode to "ONLY_FULL_GROUP_BY"

As I reinstall old project for my customer and the service is working fine but I found sometimes it can't query some information. from checking it's because @@Global.sql_mode and @@SESSION.sql_mode set to "ONLY_FULL_GROUP_BY"  the step to solve it need to run command

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); nowadays it's happen 3 times per week.

So any way can I fix it permanent?

If you have control over the server instance then edit the config file to turn off ONLY_FULL_GROUP_BY.

SELECT @@sql_mode;

The above will probably return something similar to this -

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then you can add the following to your config file (same list as above with ONLY_FULL_GROUP_BY removed) -

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Depending on your CakePHP version, you could add a settings key to your db config -

        'settings' => array(
            '@@SESSION.sql_mode' => "'TRADITIONAL'",
        ),

web server always change mode to "ONLY_FULL_GROUP_BY"

Related

Recent Posts