Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

SOLVED Server restart loses custom SQL Mode and ignores my .my.cnf file

Discussion in 'Database Discussion' started by monkey64, May 20, 2018.

  1. monkey64

    monkey64 Well-Known Member

    Joined:
    Nov 6, 2011
    Messages:
    94
    Likes Received:
    3
    Trophy Points:
    58
    cPanel Access Level:
    Root Administrator
    I want to turn off strict mode in Mariadb so I can support some older applications.
    I have been successful in turning this off by editing /root/.my.cnf and adding the following in the [mysqld] secion:

    Code:
    sql_mode=NO_ENGINE_SUBSTITUTION
    Whenever I select "SQL Server (MySQL)" from the Restart Services section of WHM, MySQL restarts using the sql mode "NO_ENGINE_SUBSTITUTION":

    Code:
    SELECT @@GLOBAL.sql_mode;
    NO_ENGINE_SUBSTITUTION
    
    As a final test I thought I would perform a Graceful Reboot from within the WHM panel. To my total shock, sql mode defaulted back to the original strict setting which prevented my old applications from working:

    Code:
    SELECT @@GLOBAL.sql_mode;
    STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    When WHM starts MySql after a reboot it does so without reading the contents .my.cnf file. Why does this happen and is it possible to permanently set sql mode after a reboot?
     
  2. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,059
    Likes Received:
    213
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @monkey64

    Is there a specific reason why you're making modifications to /root/.my.cnf? This isn't the proper location to make MySQL configuration changes. If you make your modifications to /etc/my.cnf they should not be overwritten.

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. monkey64

    monkey64 Well-Known Member

    Joined:
    Nov 6, 2011
    Messages:
    94
    Likes Received:
    3
    Trophy Points:
    58
    cPanel Access Level:
    Root Administrator
    In my haste, I incorrectly added
    Code:
    sql_mode=NO_ENGINE_SUBSTITUTION
    to the [mysqld_safe] section of /etc/my.cnf! Because it didn't work when the server rebooted, I searched for other .cnf files. Correctly adding it to the [mysqld] section of /etc/my.cnf works perfectly.

    Thanks
     
    #3 monkey64, May 22, 2018
    Last edited: May 22, 2018
  4. cPanelLauren

    cPanelLauren Forums Analyst II
    Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    3,059
    Likes Received:
    213
    Trophy Points:
    173
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @monkey64

    I'm really glad to hear that! Thanks for updating us as well :)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice