How to disable MySQL Strict Mode?

Q: I’m going to start handling my own data validation before it is committed to the MySQL database. Is there a way to disable MySQL’s strict mode?

This is enabled by default, but you can disable it in one of a couple ways.

First, verify which mode(s) MYSQL is running with:

$ mysql -u root -p -e "SHOW VARIABLES LIKE 'sql_mode';"

You’ll need to replace root with whatever username has superuser permissions on your server (but, it’s usually just root). You’ll also be prompted for the password.

This will print out something like this (but much prettier):

sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Full documentation for setting MySQL modes is available on the Server SQL Modes page in the MySQL Documentation. A description of all of the modes is also available on that page.

1. To Disable Strict Mode via SQL:

You can disable strict mode on your MySQL server by running the following command on your Linode’s command line:

$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

You can set other values for sql_mode as well. See sysvar sql_mode in the MySQL Documentation for a list.

Then, you can verify that the mode is set by running the following:

$ mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;"

2. Disable Strict Mode via my.cnf:

Disable it by setting your own SQL_MODE in the my.cnf file, then restart MySQL.

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

Inside my.cnf, look for a heading like [mysqld] and then look for the value of sql_mode. It might look like this (the actual value of sql_mode may vary):

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

You can change the value of sql_mode to NO_ENGINE_SUBSTITUTION to completely disable strict mode, but you may want to look up each mode that is configured before disabling it.

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

1. To Disable Strict Mode via SQL:

2. Disable Strict Mode via my.cnf:

Similar Posts: