WHM Mysql Config not Applying to MySQL [SOLVED]

Bill S

• December 23, 2023 00:45
• Edited

cPanel Version 116.0 (build 7)
Apache Version 2.4.58
Perl Version 5.26.3
MySQL  8.0.35

The problem: Changes applied to either WHM or my.cnf do not actually apply to mySQL.

See the only reply below for a solution.

I know newer modes/options are for better standards and efficiency, I just need to get this old sites running and it's not mission critical.

I have moved 3 sites to a new host, one of them is very old and attempting to insert into datetime columns throws an invalid datetime format for now(). In reading up on it for hours, it's looking like this may be related to modes NO_ZERO_IN_DATE and/or NO_ZERO_DATE.

In WHM -> SQL Services -> mySQL Configuration -> SQL Mode I have removed the above so it is just

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

I checked /etc/my.cnf and WHM correctly updated the config:

sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Rebooted the server completely.

Logged in to mySQL as root, executed

SHOW VARIABLES LIKE 'sql_mode'

Result is not correct.

+---------------+----------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                              |
+---------------+----------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------------------------------------------+

 

What am I doing wrong, how can I remove these modes?

EDIT: Still at it, I also found that phpMyAdmin has a table that contains these values. When I apply the changes to these values without restarting mysql, the queries insert normally, but this is only per session. Restart mysqld and they come right back to the values that seem to come out of nowhere.

There is no other .cnf file in the system other than /etc/my.cnf.

/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

outputs

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

Only the first exists.

• 

  Bill S

  • December 23, 2023 04:20
  • Edited

  Good grief, that was pretty much a nightmare, been all day at it. For anyone coming to this post looking for a solution that matches the environment mentioned above, here it is. I hope you came here first, most of what you read in general searches  are possibly outdated and incorrect.
  You will read that there are four locations from which mysql gets configs:

  
  /etc/my.cnf

  /etc/mysql/my.cnf

  /usr/etc/my.cnf

  ~/.my.cnf 

  What I never found in any documentation or post is that there is a fifth, and it's entirely possible this is a WHM or PhpMyadmin thing, I don't know (and frankly don't care at this point.)

  /var/lib/mysql/mysqld-auto.cnf
  
  This is a JSON file, so be careful. Open it, look for mysql_dynamic_variables -> sql_mode -> Value . . . . there you will find the sql mode values that load and probably overwrite my.cnf.

  systemctl restart mysqld and you're good to go.
  
  Disclaimer: I'm always suspicious of .json files, they are usually generated by another program. It's entirely possible some action may overwrite the changes. Enough for today though, I'll monitor it and see.

   

  0

Please sign in to leave a comment.