The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

disable mysql strict mode

Discussion in 'Database Discussions' started by durangod, Aug 21, 2014.

  1. durangod

    durangod Well-Known Member

    Joined:
    May 12, 2012
    Messages:
    251
    Likes Received:
    10
    Trophy Points:
    18
    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
     
  2. server9host

    server9host Well-Known Member

    Joined:
    Sep 18, 2013
    Messages:
    160
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    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
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  4. durangod

    durangod Well-Known Member

    Joined:
    May 12, 2012
    Messages:
    251
    Likes Received:
    10
    Trophy Points:
    18
    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.
     
  5. durangod

    durangod Well-Known Member

    Joined:
    May 12, 2012
    Messages:
    251
    Likes Received:
    10
    Trophy Points:
    18
    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?


     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  7. server9host

    server9host Well-Known Member

    Joined:
    Sep 18, 2013
    Messages:
    160
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello,

    Great to hear you solve the issue at your end.

    always use sql_mode

    Thanks
     
  8. durangod

    durangod Well-Known Member

    Joined:
    May 12, 2012
    Messages:
    251
    Likes Received:
    10
    Trophy Points:
    18
    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
     
    #8 durangod, Aug 22, 2014
    Last edited by a moderator: Nov 15, 2016
  9. durangod

    durangod Well-Known Member

    Joined:
    May 12, 2012
    Messages:
    251
    Likes Received:
    10
    Trophy Points:
    18
    cPanel Access Level:
    Website Owner
    Support ticket 5371437 submitted
     
  10. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  11. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
  12. Thanneeru

    Thanneeru Registered

    Joined:
    Nov 3, 2016
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    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
     
  13. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

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

    Thank you.
     
  14. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    what is the output of:

    Code:
    mysqladmin var
    
     
Loading...

Share This Page