disable mysql strict mode

[durangod]

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

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

 mysql -e 'select @@GLOBAL.sql_mode;'
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+

which i have no clue what that means lol

 

[server9host]

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]

Hello

What version of MySQL is installed on your system?

Thank you.

 

[durangod]

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]

here is what is in my cnf now

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

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.

 

[server9host]

Hello,

Great to hear you solve the issue at your end.

always use sql_mode

Thanks

 

[durangod]

well i spoke too soon it didnt work...

the issue is that it still shows strict..

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

 

[durangod]

Support ticket 5371437 submitted

 

[cPanelMichael]

To update, the following entry was used in /etc/my.cnf and WHMCS works well:

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Thank you.

 

[ruzbehraja]

You have to ensure that the MySQL defaults in the /usr/my.cnf do not override the /etc/my.cnf settings.

Otherwise, everytime MySQL restarts, it will go back to strict mode.

More: MySQL :: MySQL 5.6 Reference Manual :: 5.1.2.2 Using a Sample Default Server Configuration File

 

[Thanneeru]

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]

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.

 

[ruzbehraja]

what is the output of:

mysqladmin var