Unable to repair MySQL using Plesk repair utility: Operation CREATE USER failed

Symptoms

• Repairing MySQL using Plesk repair utility fails:

  # plesk repair mysql -v
  
  The user user is absent from the database server ............... [ERROR]
  Recreate the user user with default permissions and default hosts? [Y/n]
  Recreating the user user ..................................... [FAILED]
  - Operation CREATE USER failed for 'user'@'%'
  
  Error messages: 1; Warnings: 0; Errors resolved: 0
  
  exit status 1

• It is not possible to create a user manually:

  # plesk db
  mysql> CREATE USER 'user'@'%' IDENTIFIED BY 'password';
  mysql> ERROR 1396 (HY000): Operation CREATE USER failed for 'user'@'%'

Cause

Such user already exists.

Also, the error can be caused by the MySQL bug: https://bugs.mysql.com/bug.php?id=28331

Resolution

1. Connect to the server using SSH.

2. Create a dump of the database mysql:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin mysql > /root/mysql_db_backup_date +%F.%s.sql

3. Access MySQL server and switch to the database mysql:

   # plesk db
   mysql> use mysql

4. Remove the user mentioned in the error message from the table mysql.user:

   MYSQL_LIN: mysql> DROP FROM user WHERE User='user';

5. Remove the permissions for this user from the table mysql.db:

   MYSQL_LIN: mysql> SELECT * FROM db WHERE User='user';

6. If steps above did not help, delete this user as a workaround for the MySQL bug:

   MYSQL_LIN: mysql> DROP USER user;