Tech Tips

There are 2 steps we can follow to turn off MySQL Strict Mode. These 2 methods work in most versions of MySQL and MariaDB.

1. Disable strict Mode with my.cnf / my.ini

This method disables it by changing the SQL_MODE value to a my.cnf (Linux) file OR my.ini (Windows server) file and restart the MySQL server.

my.cnf file can be found in one of the few locations (depending on which distribution you are using). The most common places are /etc/my.cnf and /etc/mysql/my.cnf

NOTE: Make the following changes to my.ini (Windows Operating System) or my.cnf (Linux Operating System). Inside that file, look up the [mysqld] heading and check the sql_mode value. It may look like this (actual sql_mode value may vary):

See the following line:

SQL-mode = “STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION”

You can change the sql_mode value to NO_ENGINE_SUBSTITUTION to completely disable strict mode, but you may want to check each default mode before disabling it or simply change it to:

SQL-mode = “” (i.e. Empty)

If sql_mode is not set, you can add it under the heading [mysqld], then save the file, and restart MySQL.

Restart MySQL service.

OR

2. Disable Strict Mode with SQL

This method allows you to disable strict mode on your MySQL server using the following command.

$ MySQL -u root -p -e “SET GLOBAL sql_mode = ‘NO_ENGINE_SUBSTITUTION’;”

Now, you can verify that the mode is set using the following:

$ MySQL -u root -p -e “CHOOSE @@ GLOBAL.sql_mode;”

At Velan, our server support engineers can set up MySQL servers and help to disable strict mode. If you are interested in our service, please fill out the quick connect form to get in touch with us.