Server always change SQL mode to be ONLY_FULL_GROUP_BY

@NitiphoneJan 20.2022

hi all, 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’,”));“`
So any way can I make it not change the mode?

to post a comment

@miamarshalJan 20.2022 — #Server always change SQL mode to be ONLY_FULL_GROUP_BY. This is to make sure that it will not perform any other type of query. When there are many group by queries in the database, it would be very inefficient to do this for all of them.

@NitiphoneauthorJan 20.2022 — #@miamarshal#1641722 Yes, I understood that but it's impact to my customer project. the project is very old project difficult to modify it to be good command. so should I change default of sql_mode?

@ZorgJan 22.2022 — #You would need to change it in the mysql config file also, it won't persist the next time mysql restarts.

In mysql terminal run this to get the sql_mode

``sql<i>
 </i>select @@sql_mode;<i>
 </i>`</CODE>
 Result will be something like:<br/>
 <C>ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</C>
 
 Copy and paste everything except ONLY_FULL_GROUP_BY into the mysql config file
 
 <C>STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</C>
 
 Config File:<C> /etc/mysql/my.cnf</C>
 <CODE lang="sql">`sql<i>
 </i>[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<i>
 </i>`</CODE>
 
 Finally, restart Mysql. <br/>
 <C>sudo service mysql restart`

reply?

Server always change SQL mode to be ONLY_FULL_GROUP_BY

3 Comments(s) _↴

`Success!`

Social

Version

Server always change SQL mode to be ONLY_FULL_GROUP_BY

3 Comments(s) ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

3 Comments(s) _↴

`Success!`

`The web is an endless sea of information. Don't miss the boat... Subscribe!`