How to Disable Strict SQL Mode in MySQL

Question – How to Disable Strict SQL Mode in MySQL? How do I disable MySQL strict mode? How to set sql_mode option globally to disable strict mode my MySQL server?

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. This can be for any reason like if strict mode is enabled, division by zero produces an error. Date as ‘0000-00-00’ is not permitted and produce an error. This tutorial will help you for disabling the strict SQL mode in MySQL server 5.7 or later versions.

Advertisement

Disable Strict SQL Mode

You can do it by editing mysqld.cnf on your server. But for this tutorial, I will create a separate configuration file to disable strict SQL mode in MySQL server.

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

Add the below content and save 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" 

Now restart MySQL service to apply changes.

sudo systemctl restart mysql.service

Verify SQL Mode

Login to the MySQL console and execute below query to find the values of sql_mode variables.

You will find results like below screenshot. Make sure ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES are not there in the result.