Cannot access database with PHPmyadmin or restore it: Access denied for user ‘jdoe’@’localhost’ (using password: YES)

Symptoms

Cannot open database via phpMyAdmin at Domains > example.com > Databases:

PLESK_ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user ‘jdoe’@’localhost’ (using password: YES)

Cannot restore backup in Domains > example.com > Backup Manager, the same above error is shown with randomly generated “backup_XXX” username.

Rows with empty “User” values in the “mysql.user” table can be found:

# SELECT User, Host FROM mysql.user where user=”;
+———–+—————–+
| User | Host                |
+———- +——– ——–+
|         | example.com  |
|         | localhost         |
+———-+——————+

Cause

Extra rows with empty “User” values in the mysql.user table.

Resolution

1. Connect to the server via SSH;

2. Create Plesk and MySQL database backup;

3. Login to MySQL:

   # plesk db

4.  Switch to MySQL database:

   MYSQL_LIN: use mysql;

5.  Delete rows with empty “user”:

   MYSQL_LIN: delete from user where user=”;

6.  Perform a flush-privileges operation:

   MYSQL_LIN: flush privileges;