How can I enable/disable only_full_group_by in MySQL?
The only_full_group_by in MySQL is the new default mode, which changed in version 5.7.5. This mode is enabled in an effort to enforce adding a
group by, therefore avoiding including aggregated values with non-aggregated values and causing further errors. To disable this mode completely, you'll need to run the following SQL command:
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This will remove this mode from MySQL without affecting the others. You may need to edit your /etc/my.cnf file as well to ensure this change holds. Look for the line that says
sql_mode= and remove
only_full_group_by, then restart MySQL.
Conversely, if this mode is disabled and you want to enable it, just follow these steps in reverse. The MySQL command will be:
mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
You'll also want to add in or uncomment
only_full_group_by to /etc/my.cnf and restart MySQL.
Hey @Worleyrh! The commands that @jyoo posted were MySQL commands that should be entered through a terminal after connecting to your Linode server. If you don't have access to a terminal app, you can also enter the commands after logging in through the Lish console in the Cloud Manager. I'm linking a couple of our guides below on how to do both:
Friendly reminder that you'll probably need to also start MySQL after connecting to your server so that you can enter the commands. I'm not sure what distribution you're using, but I'm also including a guide below on installing and using MySQL on a Debian system. The command to start MySQL should be pretty universal:
Hope that helps!