How can I enable/disable only_full_group_by in MySQL?

Linode Staff

I want to enable (or disable) only_full_group_by in MySQL. How can I do this?

4 Replies

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.

I'm new to Linode. Where can I input the above command to disable the mode? Thanks!

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:

Linode Getting Started: Connect to Your Linode via SSH

Using the Linode Shell (Lish)

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:

Using MySQL

Hope that helps!

I already did the steps but it keeps sending me the error
"Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated"


Please enter an answer

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct