How to Disable Strict SQL Mode in MySQL 5.7

In this article I will show how you can disable strict SQL mode in MySQL 5.7 or later version.

Disable Strict SQL Mode

To disable strict SQL mode you need to edit mysqld.cnf on your server, you can also create a separate configuration file to disable strict SQL mode in MySQL server.

# vim /etc/mysql/conf.d/disable_mysql_strict_mode.cnf

Now add the below lines and save the file.

[mysqld] 
sql_mode="IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Next restart the MySQL service to apply the changes.

Verify SQL Mode

Now lets verify the SQL mode is disable or not to do this you will need to login to MySQL console and type the below query to find the values of sql_mode variables.

mysql> SELECT @@sql_mode;

You can see query out put in the below screen-shot. There are no “ONLY_FULL_GROUP_BY” and “STRICT_TRANS_TABLES” in the result.