What is ONLY_FULL_GROUP_BY and why its just temporary

psytanium

Well-Known Member
Jun 6, 2014
319
19
68
Lebanon
cPanel Access Level
Root Administrator
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 :

SQL:
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

Well-Known Member
PartnerNOC
Apr 26, 2020
423
201
118
IN
cPanel Access Level
Root Administrator
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

Jurassic Moderator
Staff member
Oct 19, 2014
9,796
1,529
313
cPanel Access Level
Root Administrator
@kodeslogic 's suggestion is the best thing to check here. We have some additional details on that process here that you may find helpful:


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

psytanium

Well-Known Member
Jun 6, 2014
319
19
68
Lebanon
cPanel Access Level
Root Administrator
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
 

psytanium

Well-Known Member
Jun 6, 2014
319
19
68
Lebanon
cPanel Access Level
Root Administrator
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

Jurassic Moderator
Staff member
Oct 19, 2014
9,796
1,529
313
cPanel Access Level
Root Administrator
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

Well-Known Member
Jun 6, 2014
319
19
68
Lebanon
cPanel Access Level
Root Administrator
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

Jurassic Moderator
Staff member
Oct 19, 2014
9,796
1,529
313
cPanel Access Level
Root Administrator
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:

Code:
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?
 
  • Like
Reactions: psytanium

psytanium

Well-Known Member
Jun 6, 2014
319
19
68
Lebanon
cPanel Access Level
Root Administrator
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:

Code:
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 :)
 

manokiss

Well-Known Member
Mar 31, 2002
576
1
318
Just curious about what is a pros and cons having enabled or disabled ONLY_FULL_GROUP_BY and strict_mode

Thanx!
 
  • Like
Reactions: psytanium
Thread starter Similar threads Forum Replies Date
lightbeing Account Administration 1
L Account Administration 1