Turn off Mysql Strict Mode

psytanium

Well-Known Member
Jun 6, 2014
275
13
68
Lebanon
cPanel Access Level
Root Administrator
Hello,

After I migrated to a new VPS server, some websites started to show sql errors. I discovered I have to turn off Strict Mode.

Strick Mode can be turned off only from SSH ? or is it available in cPanel settings ?
Should I edit the file etc/my.cnf only or /usr/my.cnf too ?
Should I changed sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES to
1 - sql_mode =""
2 - sql_mode =''
3 - sql_mode=”NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

?

Thank you
 

SysSachin

Well-Known Member
Aug 23, 2015
604
48
28
India
cPanel Access Level
Root Administrator
Twitter
Hello,

Please try with the following steps.

1) Edit /etc/my.cnf and add
Code:
[mysqld]
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"
2)verified that the mode was previously set
Code:
[email protected] [~]# mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
3) Restart MySQL
Code:
[email protected][~]# /scripts/restartsrv mysql
Waiting for mysql to restart...............finished.
4) Check MySQL
Code:
[email protected] [~]# mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
[email protected] [~]#
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,909
2,222
463
Hello,

You may also want to verify that /usr/my.cnf does not exist on the system, as it will overwrite your /etc/my.cnf file settings. If it does exist, try disabling this file and restarting MySQL:

Code:
mv /usr/my.cnf /usr/my.cnf.backup1
/scripts/restartsrv_mysql
Thank you.