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:
    96
    Likes Received:
    4
    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:
    5,088
    Likes Received:
    371
    Trophy Points:
    233
    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:
    96
    Likes Received:
    4
    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:
    5,088
    Likes Received:
    371
    Trophy Points:
    233
    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...
  5. medicakare

    medicakare Registered

    Joined:
    Dec 22, 2018
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    egypt
    cPanel Access Level:
    Root Administrator
    i have the same problem but i can't find my.cnf file
     
  6. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,088
    Likes Received:
    371
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hello @medicakare

    The location of the my.cnf file is clearly stated in this thread numerous times. You should have no problem finding it at
    Code:
    /etc/my.cnf
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. medicakare

    medicakare Registered

    Joined:
    Dec 22, 2018
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    egypt
    cPanel Access Level:
    Root Administrator
    upload_2019-1-23_18-17-31.png
    my.cnf file not here
     
  8. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    1,087
    Likes Received:
    437
    Trophy Points:
    113
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    @cPanelLauren told you to look for the my.cnf file in the /etc (under root) folder - your screenshot would indicate you are looking in the /home/medicaka/etc folder :(

    You can not see the root /etc folder from a cPanel file manager - you will need to log in as root using a SSH terminal (there is one built into WHM under the Server Configuration option) or some other file explorer protocol like fish
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    cPanelLauren likes this.
  9. medicakare

    medicakare Registered

    Joined:
    Dec 22, 2018
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    egypt
    cPanel Access Level:
    Root Administrator
    thanks i found it by winscp program
     
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