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

Symptoms

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

  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 error as above is shown with a randomly generated backup_XXX username.

• Website example.com is not accessible with:

  Error establishing a database connection

• Installation of the WordPress instance fails with:

  Error: Error establishing a database connection. This either means that the username and password information in your wp-config.php file is incorrect or we can’t contact the database server at localhost:3306. This could mean your host’s database server is down.

Cause

Extra rows with empty "User" values in the mysql.user table:

# plesk db "SELECT User, Host FROM mysql.user where user=''"
+-----------+-----------------+
| User | Host |
+---------- +-------- --------+
| | example.com |
| | localhost |
+----------+------------------+

Resolution

1. Connect to the server via SSH.

2. Create Plesk and MySQL database backup.

3. Open MySQL console:

   # plesk db

4. Switch to MySQL database:

   use mysql;

5. Delete rows with empty user:

   delete from user where user='';

6. Perform a flush-privileges operation:

   flush privileges;

7. Exit the database:

   exit