What is ONLY_FULL_GROUP_BY and why its just temporary

[psytanium]

Hello,

I'm running an application based on Codeigniter, 1 of the pages produce an error, the app author asked me to run this SQL to fix it :

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

The error is fixed but for few days, then I have to run the same SQL again and again.

Can I know what does it mean and why it work temporary ?

All the best,

 

[kodeslogic]

Does /etc/my.cnf file contain the line that says sql_mode= ? If yes, then ensure to remove only_full_group_by, then restart MySQL for changes made to hold.

Note: Before making these changes make sure you take the backup of /etc/my.cnf file.

 

[cPRex]

@kodeslogic 's suggestion is the best thing to check here. We have some additional details on that process here that you may find helpful:

How do I disable ONLY_FULL_GROUP_BY in MySQL?

Question How do I disable ONLY_FULL_GROUP_BY in MySQL? Answer To disable ONLY_FULL_GROUP_BY in MySQL, remove ONLY_FULL_GROUP_BY from the sql-mode setting in the MySQL configuration file, /etc/my....

support.cpanel.net

but let us know if that doesn't take care of the issue.

 

[psytanium]

Does /etc/my.cnf file contain the line that says sql_mode= ? If yes, then ensure to remove only_full_group_by, then restart MySQL for changes made to hold.

Note: Before making these changes make sure you take the backup of /etc/my.cnf file.

The file etc/my.cnf does not contain sql_mode= etc...
This is the content :
[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=268435456
open_files_limit=40000
bind-address=127.0.0.1

 

[cPRex]

You can always add that value to the file manually. Before you do that, though, I'd check and make sure there is not a /usr/.my.cnf on the system as well, as that can sometimes cause confusion.

 

[psytanium]

You can always add that value to the file manually. Before you do that, though, I'd check and make sure there is not a /usr/.my.cnf on the system as well, as that can sometimes cause confusion.

/usr/.my.cnf is empty, how do I add that value manually ? in which file ?

 

[cPRex]

That's good - you actually don't want the other configuration in place on the machine so you can just completely remove the /usr/.my.cnf

Inside the /etc/my.cnf you pasted your [mysqld[ section. Just add the sql-mode line to the bottom of that section, save the file, and restart MySQL, and this should now be permanently in place on the system. Can you try that and let me know how it goes?

For extra caution, it's always a good idea to make a full backup of the file when changing the configuration.

 

[psytanium]

That's good - you actually don't want the other configuration in place on the machine so you can just completely remove the /usr/.my.cnf

Inside the /etc/my.cnf you pasted your [mysqld[ section. Just add the sql-mode line to the bottom of that section, save the file, and restart MySQL, and this should now be permanently in place on the system. Can you try that and let me know how it goes?

For extra caution, it's always a good idea to make a full backup of the file when changing the configuration.

You mean I add this line : sql_mode= '';
?

 

[cPRex]

You would use the full mode with the options you want. This is an example configuration line from the guide I linked earlier:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The best way to get a proper configuration is to run this command first to get your current settings:

mysql -sse "SELECT @@GLOBAL.sql_mode;"

and then use the output from that after "sql-mode=" in your configuration line. Just make sure to remove the ONLY_FULL_GROUP option if that is still present.

Does that help clear things up?

 

[psytanium]

You would use the full mode with the options you want. This is an example configuration line from the guide I linked earlier:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The best way to get a proper configuration is to run this command first to get your current settings:

mysql -sse "SELECT @@GLOBAL.sql_mode;"

and then use the output from that after "sql-mode=" in your configuration line. Just make sure to remove the ONLY_FULL_GROUP option if that is still present.

Does that help clear things up?

Apparently your solution helped, but will wait few days to see if the error will appear again. Thank you very much

 

[cPRex]

If the change is still working well after MySQL was restarted I would expect that to stick, but let me know if that isn't the case!

 

[manokiss]

Just curious about what is a pros and cons having enabled or disabled ONLY_FULL_GROUP_BY and strict_mode

Thanx!

 

[cPRex]

MySQL 5.7 changed the default mode, so it might be worth reading though their documentation here to get more details on the specifics:

MySQL :: MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes

dev.mysql.com