mysql UI database configuration not working
Answered
Need assistance with a mysql issue.
Just installed a new cpanel server, everything is working except getting mysql to actually use the my.cnf file. When I edit/save the /etc/my.cnf file I actually see those changes/edits appear in the UI "database configuration" page - but when I restart the mysql database server none of those changes/edits make it into the running version of mysql.
I am having to manually set the changes from the mysql cli - but those changes are only good until the server restarts itself (which has happened twice now) and I have to manually go into the cli and set those global variables again. The mysql daemon is not reading the parameters fro the "my.cnf/UI database configuration".
Additionally, when I try to make an edit directly in the UI "database configuration" and save the edit, I get the following error:
"Error (XIM mppn9x) The "Mysql" service failed to start. Restarting database with the original configuration. No changes were made to the sql configuration. Database restarted successfully"
...and then I have to go back into the mysql cli and manually do "set global" on a few settings to get things working again. What is the root config file that mysql is using? Because its def not using the my.cnf file right now....how do I edit the root or get mysql to actually use the my.cnf file?
OS: AlmaLinux v9.5.0 STANDARD kvm
cPanel Version: 124.0.21
-
Hey there! Can you let me know what specific changes you're trying to make? Some of the configuration values may not be able to be placed directly in /etc/my.cnf, or it's possible you have another configuration file in /var (I've seen that happen on some systems, although I wouldn't expect that to be the case on a new machine)
0 -
Sure, below is the my.cnf file. When I edit these changes manually from CLI all the edits do appear in the UI "database configuration" page, but the changes never actually get pulled into mysql after restarting the daemon. In particular is the "sql_mode" - the default setting breaks many of my scripts due to the use of "GROUP BY" - so I have to manually apply the following change from the mysql cli: SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Trying to edit the UI "database configuration" page, the submit errors out and mysql restarts using the default configuration which forces me to go back in to mysql cli and issue the "set global" command again.
This my.cnf was copied verbatim from my old server (just retired two weeks ago) so I assume all my configs are good. But, the old server was running mysql 5.7 and the new one is now running v8 - so maybe something in that orig config file is breaking/causing issues for mysql 8. Don't know though. I looked in "/var" and don't see any other config files there...unless its deeper in a sub-directory.
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
performance-schema=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
general_log=1
general_log_file="/var/lib/mysql/mysqlQuery.log"
innodb_buffer_pool_size=54525952
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
local-infile=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBST
ITUTION
query_cache_type=1
max_heap_table_size=33554432
tmp_table_size=33554432
long_query_time=4
slow_query_log=10 -
Thanks for the additional details. I can confirm that configuration file will not work with MySQL 8, so copying that over directly isn't something I'd recommend. Specifically, the sql_mode line has invalid variables there. The following will throw fatal errors if added to the configuration:
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
You'll likely still get other warnings, but the service will refuse to start with those added.
I'd recommend trying your configuration options one at a time to find the cause of the issue.
0 -
hmmm, interesting, are those no longer supported values for mysql 8? didn't occur to me that variables would be different. I will test one at a time to see which ones are causing the issue.
0 -
I honestly can't say for sure - I was just deleting one thing at a time on my end to test as well :D
0 -
For anyone that comes across this similar issue, the problem that I had is that the following sql_mode is no longer supported in your my.cnf for MySql v8+
NO_AUTO_CREATE_USER
I had copied my older my.cnf verbatim into my new MySql v8 configuration file and `NO_AUTO_CREATE_USER` was a part of that older my.cnf `sql_mode= ....` line. Removing it fixed everything.
0 -
I'm glad you were able to narrow it down!
0
Please sign in to leave a comment.
Comments
7 comments