SOLVED MySQL server reported that no user with this name exists

[Nahoo]

I've just upgraded MySQL from version 5.5 to 5.6.34. And now all the MySQL users are no longer present!

The cPanel equivalent is there, but this isn't much use without the actual corresponding MySQL user.

On all websites with a database, there is the error:

Warning: mysql_connect(): Access denied for user 'user_alias'@'localhost' (using password: YES) in /home/user/public_html/some_config.php on line 2

and this is what happens when I try to reset the same user in cPanel | MySQL...

(XID <randomcode>) Although the system’s database map includes a MySQL user named “user_alias”, the MySQL server reported that no user with this name exists. Contact your system administrator.

I don't want to delete and readd hundreds of MySQL users and their permissions! There must be a better way to fix this!

(I've changed some of the details here.)

 

[Nahoo]

I've checked the table: mysql.users and it is empty (besides the new users I've just added).

 

[cPanelMichael]

Hello,

Did you make a backup of your databases before upgrading MySQL? If not, please open a support ticket using the link in my signature so we can take a closer look.

Thank you.

 

[Nahoo]

Well, I have started on my own make-shift solution (starting with WordPress websites)...

In Shell:

grep -E "define\('DB_(USER|PASSWORD)'" /home/*/public_html/wp-config.php;

Then copy and paste the results into your text editor (that supports Regex)...

Find:

[^:]+:define\('DB_USER', '(([^_']+_?)[^']*)'\);
[^:]+:define\('DB_PASSWORD', '([^']+)'\);

Replace with:

CREATE USER '$1'@'localhost' IDENTIFIED BY '$3'; GRANT ALL PRIVILEGES ON `$2%`.* TO '$1'@'localhost';

And back to Shell:

mysql

And then paste the resulting code back into there to create the MySQL users.

 

[cPanelMichael]

Hello @Nahoo,

Here are some documents you may find helpful if you are handling this manually:

The dbmaptool script - Documentation - cPanel Documentation
How to Restore Database Grants - Documentation - cPanel Documentation
The dbstoregrants Script - Documentation - cPanel Documentation

Thank you.

 

[Nahoo]

I've gone through all the databases, checked their accounts and added the user listed in the config files with the relevant permissions. The WordPress config search really helped get the restoration underway, but the remaining accounts took some time.

According to cPanel tech support, no one else seems to have experienced this problem...