Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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

Discussion in 'Database Discussion' started by vicos, Jan 28, 2019.

  1. vicos

    vicos Well-Known Member

    Joined:
    Apr 18, 2003
    Messages:
    73
    Likes Received:
    1
    Trophy Points:
    158
    Just tried to launch phpmyadmin from cpanel. It didn't authenticate properly and instead threw up a login screen --- which can't be used because password authentication is disabled.

    Problem: the mysql user password for the database contained a tilde (~)

    As soon as I changed the password to include only alpha/numeric, the problem went away. I suspect that more than one special character will cause this issue.
     
  2. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @vicos


    I've been trying to recreate the initial issue you were experiencing. Here is what I did from my cPanel UI:

    1. Created a DB
    2. created User with a password that included a tilde dZ~{[email protected]*
    3. Assigned that user to the DB and allowed all privileges
    4. Attempted to log into PHPMyAdmin with no issues

    The thread here details some of the information that makes it extremely helpful for us to provide an accurate response to you:
    Guide To Opening An Effective Forums Thread
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. vicos

    vicos Well-Known Member

    Joined:
    Apr 18, 2003
    Messages:
    73
    Likes Received:
    1
    Trophy Points:
    158
    Ok, what immediately comes to mind is that this domain was just moved from a Cpanel 76.0.18 server. The domain itself has existed for many, many years and undergone many server migrations and versions of Cpanel/mySQL. So, it may be a legacy data issue.

    It's not a problem for me as I resolved the issue by changing the password. Just trying to save someone some future trouble. :)
     
  4. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @vicos

    For sure! I just want to make sure there's not an ongoing problem with the software that isn't being addressed. If you're able to replicate this behavior with any other account please let us know!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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.
     
  6. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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.
     
  7. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    *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.
     
  8. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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.
     
  9. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    We do have a case that is somewhat similar to this CPANEL-25656 - "cpapi2 MysqlFE listdbs" changes the cPanel user's MySQL password string if REMOTE_PASSWORD environment variable is set

    Though you're not using cpapi2 at any point during this which makes me wonder if its related.

    I did try and recreate this below is the hash before and after restore - though my test server is using MariaDB not MySQL

    Code:
    MariaDB [mysql]> select password from user where user='passtest_one';
    +-------------------------------------------+
    | password                                  |
    +-------------------------------------------+
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    +-------------------------------------------+
    3 rows in set (0.00 sec)

    Code:
    MariaDB [mysql]> select password from user where user="passtest_one";
    +-------------------------------------------+
    | password                                  |
    +-------------------------------------------+
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    +-------------------------------------------+
    What MySQL version are you running?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  10. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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.
     
  11. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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?
     
  12. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    I think I lost sight of what I was looking for there for a second but I'm still unable to recreate the issue

    Code:
    Database changed
    MariaDB [mysql]> select password from user where user="passtest";
    +-------------------------------------------+
    | password                                  |
    +-------------------------------------------+
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    +-------------------------------------------+
    3 rows in set (0.00 sec)
    Code:
    Database changed
    MariaDB [mysql]> select password from user where user="passtest";
    +-------------------------------------------+
    | password                                  |
    +-------------------------------------------+
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    +-------------------------------------------+
    3 rows in set (0.00 sec)


    Good point and quite possible it's reading something cached

    Could you possibly open a ticket so that we can look into this further I believe there's a good case here if we can replicate it successfully. Once it's open please update this thread with the ticket ID so we can provide the outcome here.


    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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?
     
  14. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    Your Support Request ID is: 11491161
     
  15. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    I was too, and yes I used your instructions to replicate.

    All done in within the same shell session

    Thanks for that, I'm going to add some notes there and hopefully we can get to the bottom of it. I'll update here when there's more info.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  16. sparek-3

    sparek-3 Well-Known Member

    Joined:
    Aug 10, 2002
    Messages:
    1,923
    Likes Received:
    177
    Trophy Points:
    343
    cPanel Access Level:
    Root Administrator
    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.
     
  17. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @sparek-3


    The analyst handling the ticket was able to replicate the issue and knowing now that the first time it's done it works as intended I tried it again and I was also able to replicate:

    Code:
    [[email protected] etc]# mysql mysql -e "select password from user where user=\"passtest\""
    +-------------------------------------------+
    | password                                  |
    +-------------------------------------------+
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    | *04767CED8B57A0FE278E86E7C488E6AD5160E5A9 |
    +-------------------------------------------+
    [[email protected] etc]#
    Code:
    [[email protected] etc]# mysql mysql -e "select password from user where user=\"passtest\""
    +-------------------------------------------+
    | password                                  |
    +-------------------------------------------+
    | *3AC350368F1F3E1B2A642AB5802777EE0637797F |
    | *3AC350368F1F3E1B2A642AB5802777EE0637797F |
    | *3AC350368F1F3E1B2A642AB5802777EE0637797F |
    +-------------------------------------------+


    He opened a case for this CPANEL-25785 and used your replication steps/notes in the case. I'll update you as to the progress of the case as it becomes available. Thank you for opening the ticket!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  18. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    6,459
    Likes Received:
    503
    Trophy Points:
    263
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi Guys,

    I just wanted to let everyone know that this issue is marked as resolved in v78.0.13 of cPanel/WHM per the change logs here: 78 Change Log - Change Logs - cPanel Documentation

    Please let us know if you continue to experience any further issues with this.


    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice