Help Center> GaussDB(for MySQL)> Troubleshooting> SQL Issues> ERROR 1396 Reported When a User Is Created
Updated on 2023-09-15 GMT+08:00
View PDF

ERROR 1396 Reported When a User Is Created

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 disappeared account was created, the following error information was displayed:

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

Possible Causes

  1. The disappeared account has been 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 cannot log in to the instance any more.
  3. The reason why a new account with the same name as the disappeared 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, pay attention to 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 GaussDB(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 disappeared 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 Explorer to locate which client deletes the user.

Parent topic: SQL Issues