How To Disable MySQL Strict Mode

In this tutorial, we will show you how to disable MySQL strict mode on your CentOS 7 server. For those of you who didn’t know, Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons.

This article assumes you have at least basic knowledge of Linux, know how to use the shell, and most importantly, you host your site on your own VPS. The installation is quite simple and assumes you are running in the root account, if not you may need to add ‘sudo‘ to the commands to get root privileges. I will show you through the step-by-step turn-off or to disable the MySQL strict mode on a CentOS 7 server.

Prerequisites

• A server running one of the following operating systems: CentOS Linux.
• It’s recommended that you use a fresh OS install to prevent any potential issues.
• A non-root sudo user or access to the root user. We recommend acting as a non-root sudo user, however, as you can harm your system if you’re not careful when acting as the root.

Disable MySQL Strict Mode

Step 1. First, let’s start back up MySQL conf.

This ensures you have a way to revert changes if something goes awry, cp -a /usr/my.cnf{,.strict.bak}
cp -a /etc/my.cnf{,.strict.bak} it’s also beneficial because it helps track when and what changes were made:

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

Step 2. Edit the MySQL configuration files.

Open the file with your favorite command-line editor. In this example, we use ‘nano’:

nano /etc/my.cnf

Find the following lines:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

If you find a line similar to the above that is setting the `sql_mode` variable then you will need to replace it with the following line to disable MySQL strict mode:

sql_mode=""

Finally, Restart MySQL with this command:

systemctl restart mysql

Step 3. Confirming Strict SQL Mode Is Disabled.

To verify that the process was completed properly you can run the following:

mysql -e "SELECT @@sql_mode;"

The output may look similar to the following:

+--------------------------------------------+
| @@sql_mode
+--------------------------------------------+
| NO_AUTO_CREATE_USER
+--------------------------------------------+

Congratulations! You have successfully disabled the MySQL strict mode. Thanks for using this tutorial to turn off or to disable the MySQL strict mode on CentOS 7 system. For additional help or useful information, we recommend you check the official MySQL website.

Save

Save