SOLVED [CPANEL-25785] phpMyAdmin won't connect if mysql user password has special char

Yea... I'm seeing this issue as well. Pulling my hair out with it too.

Unfortunately I am not able to duplicate it when I try to replicate the circumstances that lead to this issue. But like @vicos this is happening on accounts that were transferred over this past weekend. ... Or at least that's a common theme between my experience and @vicos's - at this point I really can't rule anything out.

I really don't have any helpful information at all in regards to this. Like @vicos the password in question contained two special characters. But I really don't think that's the issue, because the restore process doesn't rely on actually knowing the account's password (at least I assume?). Instead it uses GRANT information in the mysql.sql file in restore, at least that would be my assumption. I suspect that just resetting the account's password causes it to get back in sync. But I do not know what causes it to get out of sync.

So... right now, all I can offer is... me too.

 

Actually...

How does /scripts/restorepkg do MySQL passwords?

I was able to replicate this issue, and I enable MySQL query logging so I could see what was happening.

The restore process correctly grants access to the user with the correct password:

GRANT USAGE ON *.* TO 'theuser'@'localhost' IDENTIFIED BY PASSWORD '*86DF961E288706245C97EB616C49DE5E6DDCD5C1';

*86DF961E288706245C97EB616C49DE5E6DDCD5C1 is the correct password hash - this is stored in plain sight in the mysql.sql file in the cPanel backup package.

But then just a few queries later:

GRANT USAGE ON *.* TO <<a mysql sub user>>,'theuser'@'theserver' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83','<<a mysql sub user>>','theuser'@'theip' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83','<<a mysql sub user>>','theuser'@'theoldserver' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83','<<a mysql sub user>>','theuser'@'theoldip' IDENTIFIED BY PASSWORD '*AEAF3F9FD323547325382B833194D44BF4CF0F83'; /* _addhosts */

Where is this password hash coming from?

*AEAF3F9FD323547325382B833194D44BF4CF0F83 is ultimately the password hash that is left in the mysql user table. But where did that password come from? What is it? Where is this query coming from?

This is on cPanel 11.76.0.20.

 

*AEAF3F9FD323547325382B833194D44BF4CF0F83 appears to be the temporary password that is set for the user when restoring databases.

I assume...

In the transfer session log:

Granting “theuser” access to “theuser_database” with temporary password

Is actually this query:

GRANT USAGE ON *.* TO 'theuser'@'theip' IDENTIFIED BY 'thepassword' , 'theuser'@'theserver' IDENTIFIED BY 'thepassword' , 'theuser'@'localhost' IDENTIFIED BY 'thepassword';
GRANT ALL ON `theuser\_database`.* TO 'theuser'@'theip' IDENTIFIED BY 'thepassword' , 'theuser'@'theserver' IDENTIFIED BY 'thepassword' , 'theuser'@'localhost' IDENTIFIED BY 'thepassword' /* _dbowner_to_all_without_ownership_checks */

And for whatever reason

Restoring MySQL grants

restores the password back to the CORRECT password hash. But then

Restoring MySQL access hosts

restores the password back to this temporary password.

/scripts/restorepkg is compiled code, so I can read it. But it would seem to me that it needs some cleaning up in how it's setting temporary passwords and then going back and setting original passwords.

 

OK, I think I've finally been able to come up with steps to duplicate this issue. I don't know if these steps will specifically lead to the root cause, but it at least shows the problem in a consistent manner.

Create an account:

# /usr/local/cpanel/bin/whmapi1 createacct username=%user% domain=%domain% password=%password%

Now it's important in this step to include a password, it can be a simple password with no non-alphanumeric characters, just numbers and letters. In my tests, if you don't include a password and let the system create a password automatically, then this doesn't work. I don't know why - this is puzzle #1.

Now create a database for that user and populate it with some test information

# /usr/local/cpanel/bin/uapi --user=%user% Mysql create_database name=%user%_one
# mysql %user%_one -e "create table one (one int(10), two int(10))"
# mysql %user%_one -e "insert into one (one, two) values (1,2), (3,4)"

This is puzzle #2 - the account has to have at least one database WITH information in it.

Now, note the password hash for %user%'s mysql user:

# mysql mysql -e "select password from user where user=\"%user%\""
+-------------------------------------------+
| password |
+-------------------------------------------+
| *DFA2021762E74350DFBA9AE8E8BC5372F36AF126 |
| *DFA2021762E74350DFBA9AE8E8BC5372F36AF126 |
| *DFA2021762E74350DFBA9AE8E8BC5372F36AF126 |
+-------------------------------------------+

Back up this user:

# /scripts/pkgacct %user% /backup backup --nocompress

Delete this user:

# /scripts/removeacct --force %user%

Restore this user:

# /scripts/restorepkg /backup/%user%.tar

Now look at the user's mysql user password hash:

# mysql mysql -e "select password from user where user=\"%user%\""
+-------------------------------------------+
| password |
+-------------------------------------------+
| *2096C6FEC9ECE4CAA311F45103A0A4A101347C94 |
| *2096C6FEC9ECE4CAA311F45103A0A4A101347C94 |
| *2096C6FEC9ECE4CAA311F45103A0A4A101347C94 |
+-------------------------------------------+

This should be the same as it was above, before you deleted it and restored it - but it's not.

Because this only happens when the %user% has databases with information in them, I have to believe that this is something to do with restoring those databases during the restore process - the restore process creates a random password for the user to run the restore with that user.

But what is REALLY strange, if you don't set a password when you create an account (and thereby let the system create create a random password) then all of this works as it should. The database gets restored and the user's MySQL hash gets restored correctly.

Hoping someone from cPanel can duplicate this and give a bit more information.

 

This is with MariaDB 10.1

Make sure you are checking the system user's hash

select password from user where user='passtest';

not

select password from user where user='passtest_one';

As far as I can tell, SQL sub users (users you create in the user's cPanel.. users with an underscore in them) are not affected by this, their hashes remain the same. It's related to the system user - the cPanel username.

I'm still running all kinds of tests to make sure this is able to be duplicated. I'm in the process of upgrading a server to cPanel 78 to see if that might fix it.

The environment variable angle is interesting, and in some ways that might make some sense... still probably a bug that needs to be cleaned up. But as you say, I'm not really sure if that's the same issue I am seeing.

 

There definitely seems to be some credence to the environment variable issue - although, I'm not specifically seeing any environment variables like you mentioned.

But if you change

/scripts/pkgacct %user% /backup backup --nocompress

to

(/scripts/pkgacct %user% /backup backup --nocompress)

and

/scripts/restorepkg /backup/%user%.tar

to

(/scripts/restorepkg /backup/%user%.tar)

essentially executing these commands in their own sub shell. That does seem to resolve the issue. But you have to do it for both commands. I don't know why. And printenv isn't showing anything to suggest any rogue environment issues. Are these commands retrieving stored variables from some where else?

 

Well, I don't really have a test server at my disposal right now. I'm testing all of this on a production server so I'm having to be delicate but being that it's me doing the testing I can afford myself that nimbleness.

I was really hoping you'd be able to duplicate this on your server. Are you following the procedure exactly as I layed out? I tried this on a few different servers and it was always replicated.

Are you doing all of the steps within the same shell session?

 

Your Support Request ID is: 11491161

 

With a bit more testing... it looks like the first time you follow these steps everything works correctly.

But deleting the account and performing the steps again shows the issue.

It almost has to be a variable some where that is not being cleared properly.