MySQL is an open-source tool that is used to manage servers and databases. It is a popular relational database management system, which makes data storing, editing, operating, and manipulating simpler. When working with MySQL, you may get the error message “Error 1396 (Hy000): operation create user failed for”. Let’s figure out the reasons and the fixes
Fix the Exception “Error 1396 (Hy000): Operation Create User Failed For”
There are a few reasons that are causing the error to pop up. If you avoid it, you will not get the error warning. Check out the reasons
Reason 1 – CREATE USER for existing user
The error appears when executing the statement CREATE USER for the user that already exists. Check out the example
mysql> CREATE USER `scientist` IDENTIFIED BY "scientist";
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER `scientist` IDENTIFIED BY "scientist";
ERROR 1396 (HY000): Operation CREATE USER failed for 'scientist'@'%'Reason 2 – Recreate a deleted user
If you encounter the error 1396 (Hy000), the reason may be this one as you are recreating a deleted user. The user you have deleted using the command DELETE can’t be recreated. You encounter an error if you delete a user without using DROP USER.
For instance, a user account is created and then deleted. Like this:
CREATE USER `scientist` IDENTIFIED BY "scientist";
DELETE FROM mysql.user WHERE user = 'scientist';When you try to create the user scientist in the database, you get the following error:
mysql> DELETE FROM mysql.user WHERE user = 'scientist';
Query OK, 1 row affected (0.00 sec)
mysql> CREATE USER `scientist` IDENTIFIED BY "scientist";
ERROR 1396 (HY000): Operation CREATE USER failed for 'scientist'@'%'To make it work, you have to execute a statement DROP USER for the same user you want to create. It resolves the issue. Have a look at the example below
mysql> CREATE USER `scientist` IDENTIFIED BY "scientist ";
ERROR 1396 (HY000): Operation CREATE USER failed for 'scientist '@'%'
mysql> DROP USER `scientist `;
ERROR 1396 (HY000): Operation DROP USER failed for 'scientist '@'%'
mysql> CREATE USER `scientist` IDENTIFIED BY "scientist ";
Query OK, 0 rows affected (0.01 sec)Using CREATE USER statement followed by the DROP USER statement can fix the error.
Reason 3 – DROP or ALTER USER for the user does not exist
Another cause of the error is the use of the statement DROP USER or ALTER USER for the user not existing. Check out the example
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'You need to check the list of all the users existing on the database server to fix the error. It makes you check if you use the one by mistake. It resolves the error.
Conclusion
We highlighted the reason and the fixes to handle the error warning “Error 1396 (Hy000): operation create user failed for”. I hope you find it helpful.
I wish you luck!