MySQL ERROR 1396 (HY000): Operation CREATE USER failed

While creating a new user in MySQL today I accidentally pressed the return button while in the middle of entering a password via the command line client, which looked like this:


mysql> CREATE USER 'userguy'@'10.20.30.40' IDENTIFIED BY '
'> ;
'> '''';;
'> ';
Query OK, 0 rows affected (0.00 sec)

Which isn’t what I wanted. Terminating the command didn’t work and the command succeeded, not what I wanted. So I had to fix it.

I issued a

DROP USER

command and was greeted with:

mysql> drop user userguy;
ERROR 1396 (HY000): Operation DROP USER failed for 'userguy'@'%'

That didn’t work either. Instead I did this:

delete from mysql.user where User = 'userguy';
delete from mysql.db where User = 'userguy';

And when I tried to create the user again I was still met with ERROR 1396.

How to solve MySQL ERROR 1396 (HY000): Operation CREATE USER failed

Once those user entries are removed from the User and db tables, you just have to flush privileges:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Now when you enter your CREATE USER command (with the correct syntax and a valid single line password), it should succeed.

And stop working when you are tired!

3 thoughts on “MySQL ERROR 1396 (HY000): Operation CREATE USER failed”

  1. Thank you for your solution. It worked for me. It still puzzles me how many differences are there between Oracle and MySQL, and how difficult is to create a simple deployment script.

    cheers

    1. If your hosting piovrder is using an up-to-date version of cPanel/WHM, simply login and in the Databases section of the interface, click MySQL(R) Database Wizard and this will walk you through the entire process automatically.If your cPanel interface doesn’t have neatly grouped icons, instead it just looks like myriad of icons, contact your hosting piovrder and tell them to upgrade to the latest version of cPanel/WHM. The upgrade is of no cost to them.

Comments are closed.