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.

Struggling to turn off Strict SQL

Discussion in 'Database Discussions' started by Harry-Sm, Aug 8, 2016.

  1. Harry-Sm

    Harry-Sm Registered

    Joined:
    May 26, 2016
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    United Kingdom
    cPanel Access Level:
    Root Administrator
    Any advice?

    Struggling to turn it off. I've got WHM 58 and adjusted the /etc/my.cnf and /usr/my.cnf both the same but to no avail. It's driving me nuts.
     
  2. Harry-Sm

    Harry-Sm Registered

    Joined:
    May 26, 2016
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    United Kingdom
    cPanel Access Level:
    Root Administrator
    To add to this:
    [root@vps usr]# mysql -e 'select @@GLOBAL.sql_mode;'
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    | |
    +-------------------+
    [root@vps usr]#


    It's showing as disabled, however it's not in the WHMCS upgrade tool.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

    Please let us know if last post on the following thread is helpful:

    disable mysql strict mode

    Thank you.
     
  4. Harry-Sm

    Harry-Sm Registered

    Joined:
    May 26, 2016
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    United Kingdom
    cPanel Access Level:
    Root Administrator
    Hi,

    Unfortunately not, followed and still no luck. WHMCS still reporting as not working even with
    sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     
  5. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    867
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    In /etc/my.cnf try adding this:
    Code:
    sql_mode=""
    Then restart mysql
     
  6. ruzbehraja

    ruzbehraja Well-Known Member

    Joined:
    May 19, 2011
    Messages:
    383
    Likes Received:
    7
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Most probably the culprit is the server default settings in /usr/my.cnf

    Those defaults will override your /etc/my.cnf everytime mysql restarts.

    cat /usr/my.cnf should show you something like this for it to work:

    Code:
    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
    
    [mysqld]
    
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    # These are commonly set, remove the # and set as required.
    # basedir = .....
    # datadir = .....
    # port = .....
    # server_id = .....
    # socket = .....
    
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    
    sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    
     
    James Moreland likes this.

Share This Page