ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'

Solution 1:

yes this bug is there. However, I found a small workaround.

Assume the user is there, so drop the user
After deleting the user, there is need to flush the mysql privileges
Now create the user.

That should solve it. Assuming we want to create the user admin @ localhost, these would be the commands:

drop user admin@localhost;
flush privileges;
create user admin@localhost identified by 'admins_password'

Cheers

Solution 2:

Try doing a FLUSH PRIVILEGES;. This MySQL bug post on that error code appears to report some success in a case similar to yours after flushing privs.

Solution 3:

This bug has been sitting on bugs.mysql.com since 2007 and this thread is mainly just a parroting of all those wrong answers even up to a year ago.

According to the MySQL documentation, commands like CREATE USER, GRANT, REVOKE, and DROP USER do not require a subsequent FLUSH PRIVILEGES command. It's quite clear why, if one reads the docs. It's because altering the MySQL tables directly does not reload the info into memory; yet the plethora of solutions to this bug claim that FLUSH PRIVILEGES is the answer.

This also may not even be a bug. It is a documentation conspiracy - docs vary in one critical place from version to version.

13.7.1.2. DROP USER Syntax

...

DROP USER user [, user] ...

...

DROP USER 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name part of '%' is used.

DROP USER as present in MySQL 5.0.0 removes only accounts that have no privileges. In MySQL 5.0.2, it was modified to remove account privileges as well. This means that the procedure for removing an account depends on your version of MySQL.

As of MySQL 5.0.2, you can remove an account and its privileges as follows:

DROP USER user;

The statement removes privilege rows for the account from all grant tables.

The only time I get this error is when I do DROP USER user; like the doc suggests, but MySQL does not treat the '%' as a wildcard in a way that would drop all users at all hosts. It's not so wild after all. Or, it may be that it sometimes works when it deletes the localhost user and then tries to delete the one at %.

It's clear to me that when it tries to delete the user at %, it issues an error message and quits. Subsequent CREATE USER at localhost will fail because the localhost user was never deleted. There seems to be no need to waste time digging in the grant tables looking for ghosts as one poster suggested.

I see 7 votes for:

DROP USER 'jack@localhost'; // completely delete the account

Which is interpreted as DROP USER 'jack@localhost'@'%'; # wrong

There actually seems to be a real bug that generates the same error message, but it has to do with the first created user (after a new mysql server install) being dropped. Whether that bug has been fixed, I don't know; but I don't recall that happening lately and I'm up to ver 5.5.27 at this time.

Solution 4:

If you use a DELETE statement on the mysql.user table in an attempt to remove a user, then attempt to re-establish the user with CREATE USER, you will get a 1396 error. Get rid of this error by running DROP USER 'username'@'host';

DELETE 
  FROM mysql.user 
 WHERE user = 'jack';

(You will get 1396 errors if you attempt to re-create jack)

CREATE USER 'jack'@'localhost' IDENTIFIED BY PASSWORD '*Fi47ytFF3CD5B14E7EjkjkkC1D3F8086A5C0-krn';

(Get out of this situation by running DROP USER)

DROP USER 'jack'@'localhost';

(I suppose FLUSH PRIVILEGES can't hurt, but definitely drop the user first.)

Solution 5:

You shouldn't be manually deleting users that way. MySQL has REVOKE syntax for removing privileges and DROP USER for deleting them:

REVOKE priv1,priv2,priv3,etc... FROM 'jack@localhost'; // remove certain privileges
DROP USER 'jack@localhost'; // completely delete the account

Best to use the tools provided rather than mucking around in the background.