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

monkey64

Well-Known Member
Nov 6, 2011
121
5
68
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?
 

cPanelLauren

Product Owner
Staff member
Nov 14, 2017
13,296
1,271
313
Houston
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!
 

monkey64

Well-Known Member
Nov 6, 2011
121
5
68
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
 
Last edited:

rpvw

Well-Known Member
Jul 18, 2013
1,101
462
113
UK
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
 
  • Like
Reactions: cPanelLauren