While upgrading to MySQL 5.6.x on a cPanel server, one of our sysadmins noticed that MySQL automatically enabled Strict Mode on this new version. This made a few webpages offline while requesting SQL data. This pages needed to have sql stric mode off in order to work. Today we will learn how to Disable Strict Mode in MySQL on cPanel servers.
Since MySQL 5.0, an important introduction was made in order to prevent out of range queries, they introduced two strict sql_mode options:
STRICT_ALL_TABLES - Work same as the SQL standard and generate errors when data is out of range. STRICT_TRANS_TABLES - Work same as the SQL standard and generate errors when data is out of range, but only on transactional storage engines like InnoDB.
On MYSQL 5.6 STRICT_TRANS_TABLES is enabled by default for all new installations, probably using a configuration like this for my.cnf file:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
But this has caused many issues to some users who doesn’t have their code updated to match the new MySQL strict mode.
Create a backup for MYSQL config file
rsync -avpr /etc/my.cnf /etc/my.cnf.bak rsync -avpr /usr/my.cnf /usr/my.cnf.bak
Disable Strict Mode in MySQL
Edit two MySQL configuration files:
Very important: sql-mode variable MUST BE placed inside [mysqld] code block, and not outside of that variable, otherwise it will generate SQL errors while generating mysql dumps.
nano -w /etc/my.cnf
Search for a line called “sql_mode”, then if it has content, delete all the assigned variables and let it empty, as you see below:
sql-mode=""
Save the file.
nano -w /usr/my.cnf
By default it may look like:
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Change it to be:
sql-mode=""
Save the file.
Restart MYSQL service by running:
service mysql restart
After this, your Strict Mode should be disabled. If you see any errors, try to restore the original backup files and restarting MySQL again, that should get you right back before the start.
How can I test MySQL Strict Mode is fully disabled?
Finally to test it run this command:
[[email protected] ~]# mysql -e 'select @@GLOBAL.sql_mode;' +----------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +----------------------------------------------------------------------------------------------------+ | +----------------------------------------------------------------------------------------------------+
If your output looks like the one you see here, then it’s fully disabled as expected.
Further reading: MySQL Strict Mode