Disable MySQL Strict mode in MySQL and MariaDB controls the handling of invalid or missing values in data-changing queries.Strict mode in MySQL is a configuration setting that influences the way the database handles invalid or missing data in various data-changing operations, such as INSERT, UPDATE, and CREATE TABLE statements. When strict mode is enabled, which is the default state, MySQL becomes less lenient regarding data integrity, and it may generate warnings or errors when encountering problematic data during query execution. This stricter approach aims to ensure data accuracy and prevent potential issues caused by ambiguous or unreliable data. Developers often choose to enable strict mode to enhance the robustness and reliability of their database applications.
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.
Prerequisites
- These instructions are intended specifically for disabling MySQL strict mode on a managed 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}
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.
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.
Within the file, locate the following line.
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=""
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"
sql-mode = ""
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.
For CentOS 7 servers, use this command.
systemctl restart mysql
/etc/init.d/mysql restart
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.