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'@'%'

• The migration might fail with the following error:

  PLESK_ERROR: stderr: Unable to add database user: Operation CREATE USER failed for 'user'@'%'

Cause

Such user already exists or also it 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 "+%b-%d-%Y-%H-%M-%S").sql

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

   # plesk db

   MYSQL_LIN: mysql> use mysql;

4. Remove the user mentioned in the error message:

   MYSQL_LIN: mysql> DROP USER 'user'@'localhost';

5. Flush privileges from MySQL:

   MYSQL_LIN: mysql> flush privileges;

6. Try again the operation of repairing or user creation.

Additional information

Stack Overflow thread