Login

Disable Strict Mode in MySQL 5.6 on cPanel/WHM 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 for users who don’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:

[root@server.cpaneltips.com ~]# 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.


Leave a Reply

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

Please type the characters of this captcha image in the input box

Please type the characters of this captcha image in the input box

Your email address will not be published.