How to Disable MySQL Strict Mode in Linux and Windows
MySQL and MariaDB support strict modes that allow the controlling and handling of invalid or missing values in data-changing queries. This has the following statements INSERT, UPDATE, and CREATE TABLE.
When strict mode is enabled in MySQL then invalid or missing data may cause warnings or errors when trying to move the query.
When strict mode is disabled, a similar query would have its invalid or missing values adjusted and produce a simple warning.
This may seem like the preferred result, but though strict mode is disabled, certain actions may lead to unexpected results. For example, when the character limit is exceeded, it will be shortened to fit the limit.
There are plenty of reasons why MySQL’s strict mode may require to be disabled. The frequent reason is when a server is operating WHMCS – a requirement of the tool.
Necessities
- Their guide is specifically for disabling MySQL strict mode on a managed server with cPanel or a Windows server.
- The server should be accessible to MySQL 5.X or MariaDB 10.X and higher.
- Command line and root-level access via SSH will be compulsory to follow this tutorial on Linux servers.
Disable MySQL Strict Mode
Step 1: Backup Your Data
It’s always better to have a backup before modifying server files. You have to revert changes if something goes wrong. Backups are also needed to help track changes.
Linux
When logged into SSH with the root user on Linux, with 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 identify and take a backup of the my.ini file. In the start menu, go to Settings > Update & Security > Backup > Add a drive, and then opt for the external drive or network location where you are allowed to store the backups of your machine.
Step 2: Disable MySQL Strict Mode
Based on the server and the current configurations, you may be required to edit one, or both, of the following files on the server. Generally, relevant configuration lines are available. However, it could be in either one without causing issues, so generally, it’s good to go with both.
Linux
In Linux, you can edit the files and open the file with your favorite command line editor. This guide uses Vim:
vim /usr/my.cnf
vim /etc/my.cnf
In vim, you can type a or i to enter text insertion mode; pressing the Esc key on your keyboard will take back you to command mode. For a refresher on editing files with vim, you can check articles on the web.
Within the file, check 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=""
After adjusting or you’ve to confirm the file that doesn’t need an adjustment, save and close the file. Press Esc to enter command mode and type:wq to write and end the file.
Windows
The step is similar for Windows, but don’t forgot to locate and open the my.ini file from File Explorer—the standard location for its C:\ProgramData\MySQL\MySQL Server X.X.
The escape folder is ProgramData. To show a hidden folder, go to the view tab from the menu bar, and click the checkbox next to hidden items in the Show/hide section. Once you identify the my.ini file, open it with Notepad or Notepad ++ and search the following line.
sql-mode = “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
Change this line in the file to get the mode to blank.
sql-mode = “”
Step 3: Restart the MySQL Service
You have to restart the MySQL service to make these changes, 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, write this command,
/etc/init.d/mysql restart
After furnishing this command on the server, the MySQL service will restart and load the changes. The MySQL strict mode is now disabled.
You can operate the following command to verify that the process was done properly.
mysql -e “SELECT @@sql_mode;”
Check the example of the output:
+——————————————–+ | @@sql_mode +——————————————–+ | NO_AUTO_CREATE_USER +——————————————–+
Windows
To restart MySQL in Windows, look for and open the Administrative Tools app. Just Double – Click Services. Identify and select MySQL. You are shown 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 on Restart the service which will redirect to change back to Started.
Conclusion
Disabling Strict Mode in MySQL is a painless task, regardless of why an administrator is required to disable it. Check this article to disable the MySQL Strict Mode for Linux or Windows servers.
