Skip to main content

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 : [CODE=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,

Comments

12 comments

Date Votes
  • 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.
    0
  • cPRex Jurassic Moderator
    @kodeslogic 's suggestion is the best thing to check here. We have some additional details on that process here that you may find helpful:
    0
  • 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
    0
  • cPRex Jurassic Moderator
    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.
    0
  • 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 ?
    0
  • cPRex Jurassic Moderator
    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.
    0
  • 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= ''; ?
    0
  • cPRex Jurassic Moderator
    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?
    0
  • 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 :)
    0
  • cPRex Jurassic Moderator
    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!
    0
  • manokiss
    Just curious about what is a pros and cons having enabled or disabled ONLY_FULL_GROUP_BY and strict_mode Thanx!
    0
  • cPRex Jurassic Moderator
    MySQL 5.7 changed the default mode, so it might be worth reading though their documentation here to get more details on the specifics:
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post