disable mysql strict mode

durangod

• August 21, 2014 01:13

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

  • August 21, 2014 11:23

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

  0
• 

  cPanelMichael

  • August 21, 2014 13:25

  Hello :) What version of MySQL is installed on your system? Thank you.

  0
• 

  durangod

  • August 21, 2014 17:02

  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.

  0
• 

  durangod

  • August 21, 2014 17:19

  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 [COLOR="silver">- - - 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? [QUOTE] +------------------------------------------------------------------------------------------------------------------------------------------------------+ | @@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 | +------------------------------------------------------------------------------------------------------------------------------------------------------+
  

  0
• 

  cPanelMichael

  • August 21, 2014 17:58

  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.

  0
• 

  server9host

  • August 22, 2014 06:19

  Hello, Great to hear you solve the issue at your end. [QUOTE=some sites say its sql_mode and some say its sql-mode, which is the correct way]
  always use sql_mode Thanks

  0
• 

  durangod

  • August 22, 2014 08:27

  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

  0
• 

  durangod

  • August 22, 2014 15:22

  Support ticket 5371437 submitted

  0
• 

  cPanelMichael

  • August 22, 2014 19:39

  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.

  0
• 

  ruzbehraja

  • August 26, 2016 10:40

  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

  0
• 

  Thanneeru

  • November 03, 2016 22:22

  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

  0
• 

  cPanelMichael

  • November 04, 2016 19:25

  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.

  0
• 

  ruzbehraja

  • November 09, 2016 10:37

  what is the output of: mysqladmin var
  

  0

Please sign in to leave a comment.