How can i disable MySQL strict mode ?

CentOS, Linux / July 12, 2022 / ashishpsk / 0

To Disable Strict Mode via SQL

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

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

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

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.

find / -name "*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):

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

[mysqld]
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.

Alternate Method:

Step 1: Create a new .cnf file.

nano /etc/mysql/conf.d/strict_mode.cnf

Step 2: Paste the following code into that 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

Step 3: Save and close the file. And then restart MySQL server.

sudo service mysql restart