How to disable MYSQL strict mode

By | March 23, 2020

Sometimes while working on an application we got an error like “In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘column_name’; this is incompatible with sql_mode=only_full_group_by”. It means this is an issue of mysql strict mode. In the newest version of mysql, it is defaulted on in mysql. So here in this article we will learn how to disable MYSQL strict mode.

According to the need we can disable mysql mode for the current session or permanent. Let’s see how can we do this.

Disable MYSQL strict mode for the current session

Here, there are two options one is disable only “ONLY_FULL_GROUP_BY” variable. You can do this by this query

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

 

And in the second option you can completely remove all the strict mode variables by using the query below –

SET SESSION sql_mode='';
SET GLOBAL sql_mode='';

Disable MYSQL strict mode completely for MYSQL installation permanently

In this method we will need to do some changes in my.conf file which can  be found in one of a few locations (depending on which distribution you’re using). The most common locations are /etc/my.cnf and /etc/mysql/my.cnf.

 

Open my.cnf file, look for a heading like [mysqld] and then look for the value of sql_mode like below –

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

You can change the value of sql_mode to only “NO_ENGINE_SUBSTITUTION” to completely disable strict mode or can replace all the content of sql_mode with blank like below –

sql_mode=""

If sql_mode isn’t available, you can add it under the [mysqld] heading (If heading is also not available you can add [mysqld] on the top of the file) then save the file, and restart MySQL. Now it’s been disabled permanently.

You can check with the following query

SELECT @@sql_mode;

If there is no data or only NO_ENGINE_SUBSTITUTION is in the result then you can be sure sql strict mode has been disabled from your machine.