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

monkey64

• May 20, 2018 02:18

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: 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": 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: 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?

• 

  cPanelLauren

  • May 21, 2018 17:05

  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!

  0
• 

  monkey64

  • May 22, 2018 05:58

  In my haste, I incorrectly added 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

  0
• 

  cPanelLauren

  • May 22, 2018 12:48

  Hi @monkey64 I'm really glad to hear that! Thanks for updating us as well :)

  0
• 

  medicakare

  • January 23, 2019 10:38

  i have the same problem but i can't find my.cnf file

  0
• 

  cPanelLauren

  • January 23, 2019 12:53

  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 /etc/my.cnf
  

  0
• 

  medicakare

  • January 23, 2019 15:19

  56601 my.cnf file not here

  0
• 

  rpvw

  • January 23, 2019 15:43

  @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

  0
• 

  medicakare

  • January 23, 2019 17:09

  thanks i found it by winscp program

  0

Please sign in to leave a comment.