disable mysql strict mode

durangod

Well-Known Member
May 12, 2012
505
46
78
cPanel Access Level
Website Owner
Hi, in my search i only found one thread on this which seemed pretty good but when i tried it, my mysql crashed on restart.

Here is what i tried.

i opened etc/my.cnf in ssh and added

Code:
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"
then restarted mysql, it failed

so i removed that line and restarted again, and it came up fine.

I wonder why it does not like that line of code. Isnt that how you change the mode?

when i checked the mode it gave me this

Code:
 mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
which i have no clue what that means lol
 

server9host

Well-Known Member
Sep 18, 2013
160
0
16
India
cPanel Access Level
Root Administrator
Hello,

Please post the mysql log using command /var/lib/mysql/{SERVER_NAME}.err when try to start mysql aftre editing my.cnf file
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,884
2,243
463
Hello :)

What version of MySQL is installed on your system?

Thank you.
 

durangod

Well-Known Member
May 12, 2012
505
46
78
cPanel Access Level
Website Owner
hi sorry folks i had to get some sleep.... I currently have mysql 5.5.37 installed apache 2.2.27 and php 5.4.31 and openssl 1.0.1 and modssl 2.2.27

my post above i copied the command right from the ssh window and it looks to be ok but not sure why mysql does not like it. I can do it again if you like and send you the fail message if you like.
 

durangod

Well-Known Member
May 12, 2012
505
46
78
cPanel Access Level
Website Owner
here is what is in my cnf now


Code:
[/etc]# cat my.cnf
[mysqld]
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=50000
#added by dave per firewall instructions
local-infile=0
innodb_buffer_pool_size=134217728
sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER"
some sites say its sql_mode and some say its sql-mode, which is the correct way

starting msql now to see what happens, this time i typed it in and not paste

- - - Updated - - -

wow how about that, it started ok... when i copied and pasted this i put it in programmers notepad first and then copied it to the file so i was sure no hidden characters, but maybe one got past me. Also last night there was a update running that i did not catch until i got the update report and about the same time, so maybe that was it.

so now it gives me this, so that means im ok now right?


+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@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,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,884
2,243
463
I am happy to see you were able to resolve the issue. The output you provided matches the value you entered in /etc/my.cnf.

Thank you.
 

durangod

Well-Known Member
May 12, 2012
505
46
78
cPanel Access Level
Website Owner
well i spoke too soon it didnt work...

the issue is that it still shows strict..



Code:
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,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+

whmcs requires that no strict be in there like

I specifically have STRICT_TRANS_TABLES and STRICT_ALL_TABLES being returned. These are both "strict mode" variables.

so how do i get rid of them ?


the issue is that clients cant register, i cant send or save emails either. So i need to get this fixed asap please..


i know i dont have this version but maybe there is an ini file as well.. I was reading this..
blogs.oracle.com/supportingmysql/entry/mysql_server_5_6_default
 
Last edited by a moderator:

durangod

Well-Known Member
May 12, 2012
505
46
78
cPanel Access Level
Website Owner
Support ticket 5371437 submitted
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,884
2,243
463
To update, the following entry was used in /etc/my.cnf and WHMCS works well:

Code:
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Thank you.
 

Thanneeru

Registered
Nov 3, 2016
1
0
1
india
cPanel Access Level
Root Administrator
general error: 1364 field doesn't have a default value
i changed to
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

but no luck
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,884
2,243
463
i changed to
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Hello,

Did you restart MySQL after making this change? Also, does /usr/my.cnf exist on this system?

Thank you.