How to Disable MySQL Strict Mode in Linux and Windows

Strict mode in MySQL and MariaDB controls the handling of invalid or missing values in data-changing queries. This includes INSERT, UPDATE, and CREATE TABLE statements. With MySQL strict mode enabled, which is the default state, invalid or missing data may cause warnings or errors when attempting to process the query.

When strict mode is disabled, the same query would have its invalid or missing values adjusted and produce a simple warning. This may seem like the preferred result, yet with strict mode disabled, certain actions may cause unexpected results. For instance, when the inserted value exceeds the maximum character limit, it will be truncated to fit the limit.

There are various reasons why MySQL's strict mode may need to be disabled. The most common is when a server is running WHMCS — a requirement of that tool.

Prerequisites

• These instructions are intended specifically for disabling MySQL strict mode on a managed Liquid Web server with cPanel or a Windows server.
• The server should be running either MySQL 5.X or MariaDB 10.X and higher.
• Command line and root-level access via SSH will be necessary to follow this tutorial on Linux servers.

Disable MySQL Strict Mode

Step 1: Backup Your Data

It’s always best practice to take some form of backup before modifying server files. It ensures you have a way to revert changes if something goes wrong. Backups are also beneficial because they help track changes.

Linux

While logged into SSH with the root user on Linux, use the following Bash brace expansion command to make a backup copy of the file in its original Linux directory.

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

Windows

Windows users must locate and backup the my.ini file. From the Start menu, select Settings > Update & Security > Backup > Add a drive, and then choose the external drive or network location where you store your backups.

Step 2: Disable MySQL Strict Mode

Depending on the server and the current configurations, you may need to edit one, or both, of the following files on the server. Generally, the relevant configuration lines are only in one of them. However, it could be in either one without causing issues, so generally, it’s best to check both.

Linux

To edit the files in Linux, you will open the file with your favorite command line editor. This tutorial uses Vim.

vim /usr/my.cnf
vim /etc/my.cnf

In vim, you can press a or i to enter text insertion mode; pressing the Esc key on your keyboard returns you to command mode. For a refresher on editing files with vim, see the New User Tutorial: Overview of the Vim Text Editor.

Within the file, locate the following line.

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

This line is setting the sql_mode variable. Replace it with the following line to disable MySQL strict mode.

sql_mode=""

Once this adjustment is made, or you’ve confirmed the file does not need to be adjusted, save and close the file. Press Esc to enter command mode and type :wq to write and quit the file.

Windows

The process is similar for Windows, but you need to locate and open the my.ini file from File Explorer. The standard location for it is C:\ProgramData\MySQL\MySQL Server X.X. The ProgramData folder is hidden. To show a hidden folder, click the View tab from the Menu Bar, and click the checkbox next to Hidden items in the Show/hide section.
Once you locate the my.ini file, open it with Notepad or Notepad++ and find the following line.

sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Replace this line in the file with the following to change the mode to blank.

sql-mode = ""

Step 3: Restart the MySQL Service

You need to restart the MySQL service to make these changes effective,  as it will only read the configuration files when it initially loads up.

Linux

For CentOS 7 servers, use this command.

systemctl restart mysql

For CentOS 6 and prior, use the following command.

/etc/init.d/mysql restart

After issuing this command on the server, the MySQL service will be restarted and will load the changes made. The MySQL strict mode should now be disabled.

You can run the following command to verify that the process was completed properly.

mysql -e "SELECT @@sql_mode;"

Below is an example output you should receive.

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

Windows

To restart MySQL in Windows, search for and open the Administrative Tools app. Double-click Services. Locate and select MySQL. You are presented with the following options on the left:

• Stop the service: Stop the selected service.
• Pause the service: Pause the selected service.
• Restart the service: Restart the selected service.

Click Restart the service and wait for the status to change back to Started.

Wrapping Up

Disabling Strict Mode in MySQL is a straightforward task, regardless of why an administrator needs to disable it. This guide helps you disable MySQL Strict Mode for Linux or Windows servers.

Are you looking for Linux or Windows hosting for your next website or application? Check out Liquid Web’s VPS Hosting, Cloud Dedicated Servers, or Dedicated Servers. Our sales team is available around the clock to assist you with questions or concerns. Contact them today.