An Error Message Is Displayed When a User Is Created for a DB Instance

Scenario

A user account disappeared from the console, but the account and its password could still be used to connect to the instance.

When a new account with the same name as the missing account was created, the following error was displayed:

ERROR 1396 (HY000): Operation CREATE USER failed for xxx

Possible Causes

1. The account was deleted from the mysql.user table and therefore was not displayed on the console.
2. Because the account and its password could still be used to log in to the instance, the account was deleted using delete from mysql.user. If you use delete from mysql.user to delete an account, you also need to run the flush privileges command to delete related data from the memory. Then, the account can no longer log in to the instance.
3. The reason why a new account with the same name as the missing account could not be created is that there was still related data about the disappeared account in the memory.

   

The correct way to delete an account is using the drop user statement. When running drop user, note that:

• drop user can be used to delete one or more users and revoke their permissions.
• drop user requires the DELETE permission or the global CREATE USER permission on the RDS for MySQL instance.
• If the host name of the account is not specified in the drop user statement, the host name % is used by default.

Troubleshooting example:

After an account is created, the delete statement is used to delete the account. When a new account with the same name as the deleted account is created, error 1396 is reported. After the flush privileges command is executed, an account with the same name can be created.

Solution

• Method 1 (recommended): During off-peak hours, run the drop user user_name command as the administrator to delete the disappeared account and then create an account with the same name.
• Method 2: During off-peak hours, run the flush privileges command as the administrator and then create an account with the same name. You are advised to enable SQL Audit to locate which client deletes the user.