The system failed to connect to the MySQL database issue

[steven_h]

hey guys, I was just trying an update of our cPanel image and ran into some SQL issues. I managed to fix it by hand but wanted to share the problem and solution here in case anyone needs it. We'll do some further digging on our end to see if this is a result of our install script, or something we should escalate.

The problem:

I couldn't create a new user account. It'd show me the error:

The system failed to connect to the “MySQL” database “mysql” because of an error: ER_ACCESS_DENIED_ERROR (Access denied for user 'root'@'localhost' (using password: YES)) at /usr/local/cpanel/Whostmgr/Accounts/Create.pm line 1197.

First attempt to fix it:

I then proceeded to generate a new root SQL password in WHM. It mentioned it failed and then forcibly created a new SQL password. The new password was not updated in /root/.my.cnf and I was still getting errors. mysql -u root -p also generated an error.

The actual fix:

Long story short, I set a new password by hand. If you have this problem perform the steps below through command-line. Please note this was done on CentOS 7 and the steps may differ per installation (do these steps quickly as it reduces your security until you finally start mysqld again):

1) systemctl stop mysqld
2) systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
3) systemctl start mysqld
4) mysql -u root
5) UPDATE mysql.user SET authentication_string = PASSWORD('yournewpass') WHERE User = 'root' AND Host = 'localhost';
6) FLUSH PRIVILEGES;
7) quit
8) systemctl stop mysqld
9) systemctl unset-environment MYSQLD_OPTS
10) vi or nano /root/.my.cnf and adjust the passwords to your new pass
11) systemctl start mysqld
12) test your pass: mysql -u root -p

 

[cPanelLauren]

Hi @steven_h

This is fantastic, thanks for sharing it.

We'll do some further digging on our end to see if this is a result of our install script, or something we should escalate.

While this could be a part of an internal case we have open right now I would really like to see the outcome of your investigation. The internal case I believe it may relate to is CPANEL-22372. It's fixed in a future version of the product already.

Thanks!

 

[steven_h]

Hey Lauren, you're indeed correct: We also confirmed that this is caused by CPANEL-22372. We managed to confirm this by tailing the SQL log (the log file that includes the hostname) through command-line while resetting the root SQL password. WHM would report a failure to change the password, then force a reset, and that new password would then be visible in the SQL log .

The problem is that it does not update ~/.my.cnf so mysql will not work, but mysql -u root -p with the password from the SQL log will work.

Oddly enough, yesterday I used a new installation with v68 on CentOS 7 which I then updated to the latest 74 build, to doublecheck my findings before posting here. The SQL root password reset would succeed now, even though it generated failures in 100% of our tests last friday (that server had CentOS 7 with a new installation of cPanel/WHM of 74). The command mysql would work now, but mysql -u root -p would not accept the new password. It turned out that in this case yesterday, only the [client] section is updated in ~/.my.cnf Updating the other fields manually 'fixes' the issue.

I hope this will be able to help. If more in depth information such as screenshots could help, please let me know.