Tutorial - How do I disable Strict Mode in MySQL? | Vevioz Developers

Tutorial How do I disable Strict Mode in MySQL?

DevOps

Linux Systems Engineer
Joined
Oct 18, 2019
Messages
211
Reaction score
20
Points
18
Location
Novosibirsk, Russia
Website
addons.vevioz.com
Offline
Please follow the instructions given below to turn off the MySQL Strict Mode.

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):

  1. Look for the following line:
    Code:
    sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  2. 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.
  3. Restart the MySQL Service.
    Code:
    sudo service mysqld restart
To Disable Strict Mode via SQL
This method allows you to disable the strict mode on your MySQL server by running the following command.

$ 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:
$ 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)
 
Last edited:
Top Bottom