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

Question

I seem to be unable to re-create a simple user I've deleted, even as root in MySQL.

My case: user 'jack' existed before, but I deleted it from mysql.user in order to recreate it. I see no vestiges of this in that table. If I execute this command for some other, random username, say 'jimmy', it works fine (just as it originally did for 'jack').

What have I done to corrupt user 'jack' and how can I undo that corruption in order to re-create 'jack' as a valid user for this installation of MySQL?

See example below. (Of course, originally, there was much time between the creation of 'jack' and his removal.)

mysql> CREATE USER 'jack'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+------------------+-----------------+
| user             | host            |
+------------------+-----------------+
| root             | 127.0.0.1       |
| debian-sys-maint | localhost       |
| jack             | localhost       |
| root             | localhost       |
| root             | russ-elite-book |
+------------------+-----------------+
5 rows in set (0.00 sec)

mysql> delete from user where user = 'jack';
Query OK, 1 row affected (0.00 sec)

mysql> select user,host from user;
+------------------+-----------------+
| user             | host            |
+------------------+-----------------+
| root             | 127.0.0.1       |
| debian-sys-maint | localhost       |
| root             | localhost       |
| root             | russ-elite-book |
+------------------+-----------------+
4 rows in set (0.00 sec)

mysql> CREATE USER 'jack'@'localhost' IDENTIFIED BY 'test123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'
mysql> CREATE USER 'jimmy'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;
+------------------+-----------------+
| user             | host            |
+------------------+-----------------+
| root             | 127.0.0.1       |
| debian-sys-maint | localhost       |
| jimmy            | localhost       |
| root             | localhost       |
| root             | russ-elite-book |
+------------------+-----------------+
5 rows in set (0.00 sec)

Jamikajamil

This explains the reason excellently: https://mcmap.net/q/87259/-error-1396-hy000-operation-drop-user-failed-for-39-user-39-39-localhost-39 – Stress

Answer 1

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. 

Maraud

I had found this suggestion and tried it earlier without success, but perhaps something else was wrong. Trying it just now again, then re-creating the situation with another user, I found that this did in fact do the trick. Of course, the formal answer today was "not to be stupid" and to use REVOKE and DROP USER to do it right. I'm indebted to all three answers (this one just happens to be the one that bailed me out of my present predicament). – Jamikajamil

It's 2016, mysql is at v14.14, and it is still broken. – Retort

I first needed to drop the user as per @Unblock answer. – Fuchsin

I'm seeing the same problem with 10.1.21, but "flush privileges" didn't make any difference. I drop the user, flush, and then create, which fails with "ERROR 1396 (HY000) at line 7: Operation CREATE USER failed for ...". I wish I could get mysql/mariadb to elaborate a little bit on that error, like WHY it failed. – Phosphatize

I got the error even when I try to drop the user too, 'flush privileges' did not work for me, after doing a bit of research, I found the solution by revoking all access and dropping the user, I don't think so, flush operation will help in similar cases, I have shared my detailed explanation on this page rathishkumar.in/2018/10/… – Brig

Worked like a charm! Thanks. I'm using MySQL mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper – Orsino

It's 2019, mysql is still broken. – Rebuttal

You will get this error if you try to remove the user deleting the user record from the mysql.user (delete from user wehre user =...) table instead of calling drop user statement. To fix the problem, just remove de user with the drop command and then execute "flush privileges" and you are done. – Malacostracan

This answer did not work for me, but @Rathish, your answer worked like a charm! Thank you! – Haunt

Like others, it is important not to simply DELETE the user from the user table, but to REVOKE and DROP USER 'user'@'host'; then FLUSH PRIVILEGES; then do the CREATE USER ... I was doing this because I wanted to use mysql_native_password so I could enable remote access for this user (MySQLWorkbench doesn't support the new password encoding yet) – Distiller

Answer 2

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'

Unblock

Flush privileges alone didn't work for me. dropping the user fixed everything. Thanks. – Fatality

Same for me. I had to drop the user first. – Fanchan

@QuantumMechanic solution didn't work for me but drop then flush did. – Volkslied

In my case, just dropping user was enogh, without flushing privileges. – Abbreviation

This was a HUGE life-saver - as with @Fatality above the key part was the drop user with the localhost part – Tu

In my case I deleted the user manually using something like "DELETE FROM mysql.user WHERE user = 'myUser'". And using "DROP USER 'myUser'" fixed the error even when the user didn't exist. – Bondman

This also works on MariaDB 10.4 if getting error 1396 (Operation CREATE USER failed). – Monobasic

same here,flush privileges didn't work for me.after drop user its working properly.Thank you. – Dissatisfactory

Just wanted to point out this works regardless of the client, apparently. I am using SQLElectron and was running into the same error in a MySQL hosted in AWS RDS. This fixes it. – Uranyl

same as @Scadge : it worked just with drop and create – Adalineadall

Nov, 2021 MySQL 8 and this bug is still here! (MySQL 8.0.27-0ubuntu0.20.04.1) – Glengarry

@Unblock do you have any reference to this "bug"? – Repertory

In my case, doing DROP USER 'myuser' and DROP USER 'myuser'@localhost gave different behavior. Adding @localhost, along with "flush privileges;" was the solution for me – Flaw

I'm surprised they haven't patched this out yet. Just ran into this on a fresh install for my home use. – Tonatonal

2024 and this still persists. Maybe it's not a bug after all but an intended feature! – Unblock

Answer 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.

Homomorphism

Are you a MySQL doc writer? – Indoors

This was the answer for me. Mariadb has inherited this bug. – Conradconrade

Why is this not the accepted answer :/. Anyway, thank you @user1969061. This certainly worked for me in mariadb-server-5.5.60-1.el7_5.x86_64 – Glucose

Just ran into the issue on MySQL 8.0.22 and the solution from @Unblock worked for me. That bug's an old town elder by now. – Squamulose

Answer 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.)

Thurman

This fixed it for me. I had accidentally used delete from user, instead of drop user, and flush privileges; still wouldn't fix it. – Abduct

Answer 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.

Barracoon

This was the real answer, but not to the problem I was having (which was created by my stupidity). Thank you very much for setting me straight! – Jamikajamil

@Marc, Then, why did they even invent flush privileges in the first place? The official MySQL docs do talk about stuff like delete from user where user = 'jack'; and flush privileges. Why do you say that they are not part of the tools provided? – Indoors

As @Homomorphism points out, 'jack@localhost' should probably be 'jack'@'localhost' here. – Crosscheck

Answer 6

try delete from mysql.db where user = 'jack' and then create a user

Gill

This was useful in helping me to understand how I had corrupted my installation. Thank you very much. – Jamikajamil

Read further down to Fely's comment. There are more tables involved. – Worthwhile

Answer 7

Drop the user, flush the privileges; then, create the user. It does work!

Masquer

Flushing privileges alone did not work until I dropped the user. Thanks. – Sheff

Answer 8

Check if is

'user'@'%'

or

'user'@'localhost'

Demonstration

Answer 9

In MySQL 5.6 using Drop user userid; does not work. Use: Drop user 'userid'@'localhost'; and/or Drop user 'userid'@'%';. In this way I was able to drop the user and recreate it.

Danelledanete

Answer 10

If you want to delete a user with sql, you need to delete the related data in these tables: columns_priv, db, procs_priv, tables_priv. Then execute flush privileges;

Prandial

Nice answer!! Your answer exposes the 'behind the scenes' issue as to what's actually happening rather than just "Use this patch and you're good to go". I like both types of answers included all together so I can understand it from soup to nuts. Now I know how much work it is to do it this way if you're going to do it right - and that further reinforces why one should use the methods outlined by others. Awesome! Although many answers above were good, I'm giving you my vote because I know what it's like to be a newcomer to this site. They make it really hard to help others when you're new. – Worthwhile

This should be an alternate accepted answer. I'm using mysql 5.5 and DROP USER IF EXISTS is not yet available so I have to use the DELETE FROM user syntax (I have to test user existence first). Deleting from the user table alone didn't work. Nor did FLUSH PRIVILEGES. You have to delete from the other tables above. – Shelby

Answer 11

two method 
one :
setp 1: drop user 'jack'@'localhost';
setp 2: create user 'jack'@localhost identified by 'ddd';

two:
setp 1: delete from user where user='jack'and host='localhost';
setp 2: flush privileges;
setp 3: create user 'jack'@'localhost' identified by 'ddd';

Zusman

Answer 12

I had the same error. But command "FLUSH PRIVILEGES;" didn't help. I did like that:

CREATE USER 'jimmy'@'localhost' IDENTIFIED BY 'test123';
UPDATE mysql.user SET USER='jack' WHERE USER='jimmy';

Commune

This works for me, and I can also change the host of the user. – Danelle

Answer 13

Seems you need to create user for your database and grant privileges for created user

--> create user for Data base

CREATE  USER <'username'>@'%'IDENTIFIED BY <'password'>;

ex - CREATE USER 'root'@'%'IDENTIFIED BY 'root';

--> Grant Privileges

FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON <db name>.* TO '<username>'@'%';

ex- GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'%';

Mcglone

Answer 14

BUG of MySql Solved: Error Code-1396

• Whenever you had try to run query which create a User as Shown Below. MySql> CREATE USER 'springstudent'@'localhost' IDENTIFIED BY 'springstudent'; GRANT ALL PRIVILEGES ON * . * TO 'springstudent'@'localhost';
• But when you try to run query which create user within all previleges, Due to bug it create gives previleges without showing user
• Because of this Problem we need to use this script for flush priviledges MySql> drop user 'springstudent'@'localhost'; flush privileges; create user admin@localhost identified by 'admins_password'
• After deleting the user, there is need to flush the mysql privileges
• Then Create User again

Gotchaaa.... Solved....

Gnosis

Answer 15

A simple work around on this issue. As "delete" command only removes the user record in "user" table of "mysql" database, we could add it back and then drop the user completely. Then you could create user with same name.

Step 1. find the record format of user table in mysql database

use mysql;
select * from user;

Step 2. According to the columns showed in step1, create a dummy record with the user name. Insert it into the table, for example, be reminded to replace the "username" with your username.

Insert into user value ('%','username','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',
'N','N','N','N','N','N','N','N','N','','','','','0','0','0',
'0','mysql_native_password',
'*52C5E3AC6BC5E2E0BFF86978BF62A1481AC79D58','N',
'2016-12-10 23:59:12',null,'N');

Note: sometimes you may encounter issues in inserting, just change the data to make it work.

Step 3. Drop the user.

drop user username;

Now you are able to create user with same name.

Rosie

Try DESCRIBE <tablename> – Worthwhile

Answer 16

Funnily enough the MySQL workbench solved it for me. In the Administration tab -> Users and Privileges, the user was listed with an error. Using the delete option solved the problem.

Incuse

Answer 17

This post MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) is useful. Sometimes, there is an anonymous user ''@'localhost' or ''@'127.0.0.1'. So, to solve the problem,

1. first drop the user whose 'create user' failed.

2. Create new user.

3. Grant required privileges to the new user.

4. Flush privileges.

Klaipeda

Thanks for this. I forgot to run mysql_secure_installation that deleted the anonymous user. – Erode

Answer 18

I faced this issue today, and I resolved it by doing the following steps:

1) manually inserting that troubling user providing value of mandatory fields into mysql.user

mysql> insert into user(Host, User, Password, ssl_type) 
   values ('localhost', 'jack', 'jack', 'ANY');

2)

mysql> select * from user where User = 'jack';
   1 row in set (0.00 sec)

3) A.

mysql> drop user jack;
Query OK, 0 rows affected (0.00 sec)

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

C. mysql> create user 'jack' identified by 'jack';
Query OK, 0 rows affected (0.00 sec)

D. mysql> select Host, User, Password, ssl_type  from user where User = 'jack';
+-----------+-----------+-------------------------------------------+----------+
| Host      | User      | Password                                  | ssl_type |
+-----------+-----------+-------------------------------------------+----------+
| localhost | jack      | jack                                      | ANY      |
| %         | jack      | *45BB7035F11303D8F09B2877A00D2510DCE4D758 |          |
+-----------+-----------+-------------------------------------------+----------+
2 rows in set (0.00 sec)

4) A.

mysql> delete from user 
 where User = 'nyse_user' and 
       Host = 'localhost' and 
       Password ='nyse';
Query OK, 1 row affected (0.00 sec)

B.

mysql> select Host, User, Password, ssl_type  from user where User = 'jack';
+------+-----------+-------------------------------------------+----------+
| Host | User      | Password                                  | ssl_type |
+------+-----------+-------------------------------------------+----------+
| %    | jack      | *45BB7035F11303D8F09B2877A00D2510DCE4D758 |          |
+------+-----------+-------------------------------------------+----------+
1 row in set (0.00 sec)

Hope this helps.

Breaker

Worked for me. Best answer, well explained! – Mcatee

Answer 19

I had the same problem as OP, and the accepted answer did not work for me. In the comments of the accepted answer, @Rathish posted a solution which worked for me, I wanted to call attention to it.

Here's the link:

https://www.rathishkumar.in/2018/10/Error-1396-HY000-Operation-CREATE-DROP-USER-failed-for-user-host.html

Rathish's solution is to revoke access for all users:

REVOKE ALL ON *.* FROM 'user'@'host';
DROP USER 'user'@'host';
FLUSH PRIVILEGES;

And he also helpfully points out that you can query the following tables by selecting "user" and "host" to determine whether you have a vestigial user left-over from a previous operation:

mysql.user: User accounts, global privileges, and other non-privilege columns
mysql.db: Database-level privileges
mysql.tables_priv: Table-level privileges
mysql.columns_priv: Column-level privileges
mysql.procs_priv: Stored procedure and function privileges
mysql.proxies_priv: Proxy-user privilege

Thank you!

Haunt

Answer 20

replace localhost by 127.0.0.1

it works for me

Koala

thanks, this worked for me. Does anyone know why it does? – Blue

That may not be a long term solution. jack@localhost may be regarded as a different user to [email protected]. Whatever caused the problem may reoccur. – Unfeeling

Answer 21

Step 1) Open MySQL Command Line Client.

Step 2) Check all the users using : select user, host from mysql.user;

Step 3) Delete the user using : drop user username@localhost;

Step 4) Again create user using MySQL GUI.

Step 5) Problem is solved now.

Levitate

Answer 22

The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Indivertible

Answer 23

I know this is old, but since it is the first result in Google I figured I should add my solution. In my case dropping the user worked fine, but recreating the user gave me a "ERROR 2013 (HY000): Lost connection to MySQL server during query" and "ERROR 2006 (HY000): MySQL server has gone away." I tried the flush privileges -> drop user solution, but still had the same error.

In my case the error was due to a mysql upgrade from 5.1 -> 5.6. Viewing the error logs, I noticed that it said to run mysql_upgrade. Did that and my create user statement worked fine!

Faber

Answer 24

I recently got this error.

What worked for me is checking in the mysql workbench 'Users and Privileges' and realizing user still existed.

After deleting it from there, I was able to recreate the user.

Schneider

Answer 25

mysql> DELETE FROM mysql.db WHERE user = 'jack'

Restart the server:

# mysql.server restart

Then do your CREATE USER command.

Vincentia

This isn't useful. The error was encountered on CREATE after DELETE. – Imco

Answer 26

My experience with this error resulted from me doing explicit delete from mysql.user, but not from mysql.db

Turns out if the user name in the create exists is in either user or db, you get this error. Solve by doing delete from mysql.user where User == 'user@bar'; delete from mysql.db where User == 'user@bar';

Electron

Answer 27

The reason for that error (ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost') was because the user has already existed in the database, to solve this change the user else to other user

Footfall

Answer 28

Just delete the user related data from mysql.db(maybe from other tables too), then recreate both.

Obfuscate

Answer 29

I had also faced the same issue, after few searches, I found a solution that worked for me.I hope it will help you. As you have already created users, now try to do a FLUSH PRIVILEGES on your Mysql console. This issue is already in MySql bug post.You can also check this one.Now after flushing, you can create a new user. follow below Steps:

Step-1: Open terminal Ctrl+Alt+T
Step-2: mysql -u root -p  , it will ask for your MySQL password.

Now you can able to see Mysql console.

Step-3: CREATE USER 'username'@'host' IDENTIFIED by 'PASSWORD';

Instead of username you can put username you want. If you are running Mysql on your local machine, then type "localhost" instead of the host, otherwise give your server name you want to access.

Ex: CREATE USER smruti@localhost IDENTIFIED by 'hello';

Now new user is created. If you want to give all access then type

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Now you can quit the MySQL by typing \q.Now once again login through

mysql -u newusername -p , then press Enter. You can see everything.

Hope this helps.

Vagina