Database

How to Disable Strict SQL Mode in MySQL 5.7

disable-mysql-strict-mode
mm
Written by Santosh Prasad

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.

disable-string-mode-in-mysql

Thank you! for visiting Look Linux.

If you find this tutorial helpful please share with your friends to keep it alive. For more helpful topic browse my website www.looklinux.com. To become an author at Look Linux Submit Article. Stay connected to Facebook.

About the author

mm

Santosh Prasad

Hi! I'm Santosh and I'm here to post some cool article for you. If you have any query and suggestion please comment in comment section.

Leave a Comment