The MySQL ERROR 1396 occurs when MySQL failed in executing any statement related to user management, like CREATE USER or DROP USER statements.
This error frequently appears when you run statements to create or remove users from your MySQL database server.
MySQL has a bug that triggers this error when you remove a user without using the DROP USER statement.
This bug prevents you from re-creating a user previously deleted using the DELETE statement.
For example, suppose you create and then delete the developer account as shown below:
CREATE USER `developer` IDENTIFIED BY "developer";
DELETE FROM mysql.user WHERE user = 'developer';
Then the next time you create the user developer in your database server, you will trigger the error as follows:
mysql> DELETE FROM mysql.user WHERE user = 'developer';
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'
To fix this, you need to run a DROP USER statement for the same user account.
MySQL will respond with the same error, but after that you can create the user again.
Take a look at the following example:
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'
mysql> DROP USER `developer`;
ERROR 1396 (HY000): Operation DROP USER failed for 'developer'@'%'
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
Query OK, 0 rows affected (0.01 sec)
Even though the DROP USER statement above throws an error, the same user can be created using the CREATE USER statement after that.
The error hasn’t been fixed up to MySQL version 8.0.26 as of today.
Other ways the error can be triggered
The error can also occur when you run the CREATE USER statement for an already existing user:
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER `developer` IDENTIFIED BY "developer";
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'%'
The same error could happen when you run the DROP USER or ALTER USER statement for a non-existing user account:
mysql> DROP USER `notuser`;
ERROR 1396 (HY000): Operation DROP USER failed for 'notuser'@'%'
mysql> ALTER USER dev@localhost IDENTIFIED BY 'newPassword';
ERROR 1396 (HY000): Operation ALTER USER failed for 'dev'@'localhost'
To list all existing users in your database server, you need to query the user table in your mysql database.
SELECT the user and host column from the table as follows:
SELECT user, host FROM mysql.user;
Please note that you may have different values between % and localhost in the host column.
Here’s an example from my database:
+------------------+-----------+
| user | host |
+------------------+-----------+
| developer | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| nathan | localhost |
| root | localhost |
+------------------+-----------+
The % value in the host column is a wild card that allows the user account to connect from any host location.
The localhost value means that you need to connect from the localhost only.
MySQL treats two identical user account with different hosts value as different users.
When you don’t specify the host value in the CREATE USER statement, it will default to the % wild card.
-- Create developer@% account
CREATE USER `developer` IDENTIFIED BY "developer";
-- Create developer@localhost account
CREATE USER `developer`@localhost IDENTIFIED BY "developer";
The statements above will create two developer accounts with different hosts:
+------------------+-----------+
| user | host |
+------------------+-----------+
| developer | % |
| developer | localhost |
+------------------+-----------+
When you trigger the ERROR 1396 that’s not caused by the bug above, be sure to check out the users you have in your database first.
