Skip to Content

How can i disable MySQL strict mode ?

253  Manu Chacko July 19, 2024  Tweaks & Configuration

Overview: This article explains how to disable MySQL strict mode. You can either run a SQL command to set sql_mode to 'NO_ENGINE_SUBSTITUTION' or adjust the sql_mode setting in the my.cnf file. After making these changes, restart MySQL to apply them.

MySQL strict mode is enabled by default. You can disable it in a couple of ways.

Step 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;"

Step 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.
Related Articles
Rating: 3.57 (14 Votes)