How to Fix MySQL ERROR 1396 (HY000) When Recreating a Deleted User?

Recreating Deleted MySQL Users: Resolving ERROR 1396 (HY000)

When attempting to recreate a user in MySQL, users may face the "ERROR 1396 (HY000): Operation CREATE USER failed" issue. This occurs despite having administrative privileges and proper configuration.

To understand and resolve this issue, it's essential to delve into the problem scenario. The issue arises when a user (e.g., 'jack') is deleted from the 'user' table and attempts are made to recreate it. However, the operation fails, suggesting a corruption or an inconsistency within the user database.

To rectify this issue, a workaround involving three steps has been identified:

1. Drop the Existing User: Assuming the user still exists, drop it from the 'user' table using the "DROP USER" command.
2. Flush Privileges: Following the user deletion, it's necessary to flush the MySQL privileges using the "FLUSH PRIVILEGES" command. This clears any cached information regarding user permissions and ensures that the database has an up-to-date record of user accounts.
3. Create the User: Now that the existing user has been dropped and privileges have been flushed, recreate the user using the "CREATE USER" command with appropriate username, host, and password.

For example, to recreate the user 'jack' with the password 'test123':

drop user jack@localhost;
flush privileges;
create user jack@localhost identified by 'test123'

This workaround effectively removes the corrupted or inconsistent record from the 'user' table and allows for the successful recreation of the desired user.

The above is the detailed content of How to Fix MySQL ERROR 1396 (HY000) When Recreating a Deleted User?. For more information, please follow other related articles on the PHP Chinese website!