phpMyAdmin locked out a user, ER_PASSWORD_NO_MATCH
I was logged in to phpMyAdmin and performing some rather slow operations (changing indexes on tables with a lot of rows), and suddenly PMA just went to a blank page. I tried to log back in, but nothing... I just get a blank page.
I went to that domain's account and tried to log in to PMA through cPanel. It shows an error for a brief second, then redirects to a blank page again. After several attempts, I was finally able to get a screenshot of the error. It says:
(Please excuse any typos, I had to manually type that from a screenshot) I can log in to the account via SSH and run MySQL queries, so the issue is just with phpMyAdmin. But I CAN log in to PMA as root, so it apparently only affects this user account (I haven't checked other user accounts). I found someone with this problem in 2012, and they solved it by changing the user password, then logging in as root and FLUSH PRIVILEGES. I did that, but it had no impact. I also rebooted MySQL, then when that had no impact I rebooted the server. Neither had any apparent impact. Using cPanel, I went to see if I could update the User Privileges... it looks like I could remove some, but when I tried to apply ALL back I got an error... and then the user didn't have ANY privileges! Which inadvertently killed my website, so I had to quickly create a new user and grant it all privileges (which gave the same error, but for some reason worked where granting privileges to the other username didn't work). Oddly, though, that new user doesn't show up under "Privileged Users" in cPanel... it shows up under "Current Users", though, and it's working, so I'm not messing with it. Last but not least, while logged in to PMA as root, under mysql > user, I see that all of the users other than "root" have all Privileges as "N". That can't be right, can it? I really use PMA a lot, and I have a lot of sites that use this username that I've inadvertently broken, so any help on how to fix this would be appreciated!!
The system will redirect you in a moment. Error from MySQL query: (XID cgc3zf) The system received an error from the "MySQL" database "mysql": ER_PASSWORD_NO_MATCH (Can't find any matching row in the user table) The system could not perform GRANT statements for the user "example" due to an error: (XID cgc3zf) The system received an error from the "MySQL" database "mysql": ER_PASSWORD_NO_MATCH (Can't find any matching row in the user table)(Please excuse any typos, I had to manually type that from a screenshot) I can log in to the account via SSH and run MySQL queries, so the issue is just with phpMyAdmin. But I CAN log in to PMA as root, so it apparently only affects this user account (I haven't checked other user accounts). I found someone with this problem in 2012, and they solved it by changing the user password, then logging in as root and FLUSH PRIVILEGES. I did that, but it had no impact. I also rebooted MySQL, then when that had no impact I rebooted the server. Neither had any apparent impact. Using cPanel, I went to see if I could update the User Privileges... it looks like I could remove some, but when I tried to apply ALL back I got an error... and then the user didn't have ANY privileges! Which inadvertently killed my website, so I had to quickly create a new user and grant it all privileges (which gave the same error, but for some reason worked where granting privileges to the other username didn't work). Oddly, though, that new user doesn't show up under "Privileged Users" in cPanel... it shows up under "Current Users", though, and it's working, so I'm not messing with it. Last but not least, while logged in to PMA as root, under mysql > user, I see that all of the users other than "root" have all Privileges as "N". That can't be right, can it? I really use PMA a lot, and I have a lot of sites that use this username that I've inadvertently broken, so any help on how to fix this would be appreciated!!
-
Hello, Could you let us know if there's any output to /usr/local/cpanel/logs/error_log when attempting to access phpMyAdmin via cPanel when logged in with the account username? Also, do any .my.cnf files exist under the account's home directory? Thank you. 0 -
Could you let us know if there's any output to /usr/local/cpanel/logs/error_log when attempting to access phpMyAdmin via cPanel when logged in with the account username?
Yes, there are! Thanks for showing me the location. I'm not sure if this will be much more help, but here's what I found:[2017-04-13 04:04:15 -0400] warn [cpmysql] Error from MySQL query: (XID rsg4bc) The system received an error from the "MySQL" database "mysql": ER_PASSWORD_NO_MATCH (Can't find any matching row in the user table) at /usr/local/cpanel/Cpanel/Mysql.pm line 1586. Cpanel::Mysql::_has_error_handler(Cpanel::Mysql=HASH(0x198fc98), Cpanel::Exception::Database::Error=HASH(0x19e10b8)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 689 Cpanel::Mysql::_dbowner_to_all_without_ownership_checks(Cpanel::Mysql=HASH(0x198fc98), "method", "GRANT", "users", HASH(0x1259e40), "database", undef) called at /usr/local/cpanel/Cpanel/Mysql.pm line 584 Cpanel::Mysql::_dbowner_to_all_with_ownership_checks(Cpanel::Mysql=HASH(0x198fc98), "method", "GRANT", "users", HASH(0x1259e40)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 802 Cpanel::Mysql::updateprivs(Cpanel::Mysql=HASH(0x198fc98)) called at bin/admin/Cpanel/cpmysql.pl line 384 [2017-04-13 04:04:15 -0400] warn [cpmysql] The system could not perform "GRANT" statements for the user "[MYUSERNAME]" due to an error: (XID rsg4bc) The system received an error from the "MySQL" database "mysql": ER_PASSWORD_NO_MATCH (Can't find any matching row in the user table) at /usr/local/cpanel/Cpanel/DBAdmin.pm line 169. Cpanel::DBAdmin::_log_error_and_output_return(Cpanel::Mysql=HASH(0x198fc98), Cpanel::LocaleString=ARRAY(0x19e1340)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 721 Cpanel::Mysql::_dbowner_to_all_without_ownership_checks(Cpanel::Mysql=HASH(0x198fc98), "method", "GRANT", "users", HASH(0x1259e40), "database", undef) called at /usr/local/cpanel/Cpanel/Mysql.pm line 584 Cpanel::Mysql::_dbowner_to_all_with_ownership_checks(Cpanel::Mysql=HASH(0x198fc98), "method", "GRANT", "users", HASH(0x1259e40)) called at /usr/local/cpanel/Cpanel/Mysql.pm line 802 Cpanel::Mysql::updateprivs(Cpanel::Mysql=HASH(0x198fc98)) called at bin/admin/Cpanel/cpmysql.pl line 384
That's repeated dozens of times, but stops at 5:12am, so I'm guessing that these were all every time I tried to access PMA. I went to bed right after posting here, so it makes sense that the errors stopped when I stopped. PMA is still inaccessible today, though. I also found several of these in /tmp/mysqld.error.log:[Warning] 'user' entry '[MYUSERNAME]@localhost' has both a password and an authentication plugin specified. The password will be ignored.
Also, do any .my.cnf files exist under the account's home directory?
No, only in the /etc/ directory.0 -
I Googled that second error (both a password and an authentication plugin specified...) and found a few references, but only one potential solution: I found I needed to clear the plugin method. In 5.6, I could do: sudo mysql -u root use mysql; [mysql] update user set plugin='' where User='root'; [mysql] flush privileges;
In 5.7, I found I needed to:set plugin='mysql_native_password'
I haven't tried this yet, though, because (A) my problem is with a user account, not root; and (B) I'm running version 5.5.54-cll, where the fix was for 5.6 or 5.7, and I don't want to mess anything up that will crash my site again.0 -
Just an update, I tried the above but changing "User='root'" to "User='[MYUSERNAME]'". But that resulted in an error: ERROR 1046 (3D000): No database selected
It was a long shot, anyway. But as of now, almost 24 hours later, I still can't access PMA through my regular account, and cPanel still has the same errors with setting and modifying users as I described earlier.0 -
Hello, Could you open a support ticket using the link in my signature so we can take a closer look? You can post the ticket number here so we can update this thread with the outcome. Thank you. 0 -
My server provider submitted a ticket for me, so I'm afraid that I don't know the ticket number. I was going to post it myself, but the site said I would get a faster response if they did it. The issue has been resolved now, though. In January 2016 I had added skip_name_resolve to my.cnf to prevent a bunch of DNS errors that were showing up in my error log. The cPanel support team removed this and restarted MySQL... problem solved! Now, as to WHY that suddenly became a problem after a year and 3 months, I have no idea. But either way, I'm back in PMA now! :-) Thanks for all of your help! 0 -
I have same problem now. Can i please ask what was the problem and solution? 0 -
New I have same problem now. Can i please ask what was the problem and solution?
Hi, This was answered in the earlier post:In January 2016 I had added skip_name_resolve to my.cnf to prevent a bunch of DNS errors that were showing up in my error log. The cPanel support team removed this and restarted MySQL.
Thank you.0
Please sign in to leave a comment.
Comments
8 comments