Turn off Mysql Strict Mode

[psytanium]

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]

Hello,

Please try with the following steps.

1) Edit /etc/my.cnf and add

[mysqld]
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"

2)verified that the mode was previously set

[email protected] [~]# mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+

3) Restart MySQL

[email protected][~]# /scripts/restartsrv mysql
Waiting for mysql to restart...............finished.

4) Check MySQL

[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]

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:

mv /usr/my.cnf /usr/my.cnf.backup1
/scripts/restartsrv_mysql

Thank you.