3 Easy Steps To Turn Off MySQL Strict Mode on Linux and Windows

This guide will teach you to Turn Off MySQL Strict Mode on Linux and Windows. MySQL strict mode is a set of SQL modes that control the handling of invalid or missing values in MySQL. By disabling strict mode, you configure MySQL to handle invalid, incomplete, or out-of-range data more softly. This can allow certain types of data to be inserted or updated in the database that would otherwise cause errors under strict mode.

When strict mode is disabled, MySQL becomes more permissive in handling data that doesn’t strictly conform to column definitions or constraints. Now you can follow the steps below to see how you can disable or turn off MySQL Strict Mode on Linux and Windows.

Steps To Turn Off MySQL Strict Mode on Linux and Windows

At this point, we assumed you have a running MySQL 5.X or MariaDB 10.X or higher. Also, you should have root SSH access to your Linux server. Then, follow the steps below to see how to Turn Off MySQL Strict Mode on Linux and Windows.

Step 1 – Back Up MySQL Data

To turn off MySQL Strict Mode on Linux and Windows, it is recommended to back up your data at first.

In Linux Servers, you can use the following commands to back up your data:

# cp -a /usr/my.cnf{,.strict.bak}
# cp -a /etc/my.cnf{,.strict.bak}

In Windows, you must locate and back up the my.ini file. To do this, you can select Settings > Update & Security > Backup > Add a drive. Then, choose the external drive or network location where you store your backups.

Step 2 – Disable MySQL Strict Mode on Linux and Windows

At this point, you can easily Turn Off MySQL Strict Mode on Linux and Windows.

In Linux servers, you must edit the MySQL config file by using your favorite text editor like Vi Editor or Nano Editor:

# sudo vi /usr/my.cnf
# sudo vi /etc/my.cnf

Then, you need to add the following line under the [mysqld] section:

sql_mode="NO_ENGINE_SUBSTITUTION"

Once you are done, save and close the file.

In Windows, the standard location of my.ini file is C:\ProgramData\MySQL\MySQL Server X.X.

After locating my.ini file, open it with Notepad or Notepad++ and add the line under the [mysqld] section (if there is no [mysqld] section, create it):

sql_mode="NO_ENGINE_SUBSTITUTION"

Save and close the file, when you are done.

Step 3 – Restart MySQL Service on Linux and Windows

To apply the changes of Turn Off MySQL Strict Mode on Linux and Windows, you must restart your MySQL service.

In Linux server, you can use the following command:

systemctl restart mysql

To restart MySQL in Windows, you need to search for and open the Administrative Tools app. Double-click Services. Locate and select MySQL. Then, Stop the service and Restart the service. Wait for the status to change back to Started.

That’s it, you are done. To get more information, you can visit the Server SQL Modes official website.

Conclusion

At this point, you have learned to disable or turn off MySQL strict mode on both Linux and Windows servers. This process can be done by editing the MySQL config file and adding the mentioned SQL mode line to the file. Finally, restart the MySQL service to apply the changes.

Hope you enjoy it. Also, you may like to read the following articles:

Duplicate a MySQL Database with a Different Name in Linux

Fix Unknown collation utf8mb4_0900_ai_ci in MySQL

Disable Remote Access in MySQL Database

Find MySQL Configuration File Location on Linux