How to Disable Strict Mode in MySQL

Strict Mode in MySQL/Mariadb controls how to handle invalid or missing values in INSERT, UPDATE and CREATE TABLE queries. In other words, it will throw a warning or error in case of invalid or missing values, for queries that change data. By default, strict mode is enabled in MySQL & Mariadb. When strict mode is disabled, it automatically adjusts invalid & missing values in queries, and only produces a warning. Although it looks tempting to disable strict mode, sometimes, this can produce unexpected results. For example, if your value exceeds the character length of a column, then it will be truncated to fit the column, without any error message. On the other hand, there are some tools that require you to disable strict mode. So in this article, we will look at how to disable strict mode in MySQL.

How to Disable Strict Mode in MySQL

Here are the steps to disable strict mode in MySQL. Please note, if you get ‘permission denied’ error with the above commands, add ‘sudo’ keyword at the beginning of both the commands and run them again.

1. Backup configuration files

First of all, take a backup of your configuration files. This will be helpful in case something goes wrong after making the changes. Open terminal and run the following command to do so. They will create backup of config files, with .strict.bak extension.

$ cp -a /usr/my.cnf{,.strict.bak}
$ cp -a /etc/my.cnf{,.strict.bak}

2. Disable Strict Mode

Depending on your server and installation, you may need to edit one or both the above files. Open both these files, one by one, in text editor.

$ vim /usr/my.cnf
$ vim /etc/my.cnf

Look for the following line in these files.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Change it to

sql_mode=""

Save and close the file.

3. Restart Server

Restart MySQL/Mariadb to apply the above changes.

$ systemctl restart mysql
OR
$ /etc/init.d/mysql restart
OR
$ sudo service mysql restart

If there are no errors in the configuration, MySQL will restart with strict mode disabled. You can verify it with the following command.

$ mysql -e "SELECT @@sql_mode;"

You will see the following output, if strict mode is disabled.

+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_AUTO_CREATE_USER                        |
+--------------------------------------------+

Also read:

How to Fix Stdin : Not in GZIP Format
How to Find & Kill Zombie Process in Linux
How to Disable Strict Host Key Checking in SSH
How to Create Superuser in Django
How to Print in Same Line in Python