Unable to repair mysql database: Operation CREATE USER failed

Created:

2016-11-16 12:55:28 UTC

Modified:

2017-08-08 13:24:41 UTC

0

Was this article helpful?


Have more questions?

Submit a request

Unable to repair mysql database: Operation CREATE USER failed

Applicable to:

  • Plesk 12.5 for Linux

Symptoms

  • Unable to repair mysql:

    # plesk repair mysql -v

    The user user1 is absent from the database server ............... [ERROR]
    Recreate the user user1 with default permissions and default hosts? [Y/n] y
    Recreating the user user1 ..................................... [FAILED]
    - Operation CREATE USER failed for 'user1'@'%'

    Error messages: 1; Warnings: 0; Errors resolved: 0

    exit status 1
  • Unable to create a user manually:

    CREATE USER 'user1'@'%' IDENTIFIED BY 'password';
    ERROR 1396 (HY000): Operation CREATE USER failed for 'user1'@'%'

Cause

  • MySQL 1396 error means that such user already exists.

Or:

Resolution

  1. Create mysql database dump:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin mysql > /root/mysql_db_backup_`date +%F.%s`.sql
  2. Make sure there is no such user in mysql.user table, remove it if it exists:

    mysql> SELECT * FROM mysql.user WHERE User = 'user1';
    mysql> DROP FROM mysql.user WHERE User = 'user1';
  3. Make sure no permissions left for this user in mysql.db table, remove it if it is found:

    mysql> SELECT * FROM mysql.db WHERE User = 'user1';
  4. If steps above did not help, try to delete this user using the following MySQL query (workaroung for MySQL bug):

    mysql> DROP USER user1;
Have more questions? Submit a request
Please sign in to leave a comment.