1. Disable Strict Mode via my.cnf/my.ini
This method disables it by changing the value of SQL_MODE in my.cnf file (for Linux) OR my.ini file (for windows server) and restarting the MySQL server. 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 .
NOTE: Make the following changes in the my.ini (for Windows Operating System) or my.cnf (for Linux Operating System). Inside that file, 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):
- Look for the following line:
sql-mode = “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” - 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 or you can simply change it to:
sql-mode=”” (i.e. Blank)
If sql_mode isn’t set, you can add it under the [mysqld] heading, then save the file, and restart MySQL. - Restart the MySQL Service.
OR
2. To Disable Strict Mode via SQL
This method allows you to disable the strict mode on your MySQL server by running the following command.
|
1
|
$ mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';" |
Now, you can verify that the mode is set by running the following:
|
1
|
$ mysql -u root -p -e "SELECT @@GLOBAL.sql_mode;" |
That’s all! 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.
- Applicable To: Standard Edition, Standard (Multi-Language) Edition, Enterprise Edition (MySQL), Enterprise Multi-Language Edition (MySQL), Enterprise Edition (SQL Server), Enterprise Multi-Language Edition (SQL Server)
Leave a Reply