How to: Disable Strict Mode in MySQL 5.6 on cPanel/WHM servers

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

About the Author: Martin Keler

Leave a Reply

Your email address will not be published. Required fields are marked *