错误1396 (HY000):“jack”@“localhost”操作创建用户失败

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

我似乎无法重新创建一个我已经删除的简单用户，即使是MySQL中的root用户。

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

我的情况是:用户“jack”以前存在过，但是我把它从mysql中删除了。用户为了重新创建它。我在那张桌子上没有看到这一点的痕迹。如果我为其他用户执行这个命令，随机用户名，比如“jimmy”，它就可以正常工作(就像它最初为“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?

我做了什么来破坏用户“jack”，我怎样才能消除这种破坏，以便重新创建“jack”作为这个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)

19 个解决方案

#1

165

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.

尝试使用刷新特权。这个MySQL错误贴在错误代码上的帖子似乎报告了一些成功的案例，类似于你在冲洗厕所后的情况。

#2

395

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

是的，这个bug在那里。然而，我找到了一个解决办法。

Assume the user is there, so drop the user
假设用户在那里，因此删除用户
After deleting the user, there is need to flush the mysql privileges
删除用户后，需要刷新mysql特权
Now create the user.
现在创建用户。

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

这应该解决它。假设我们想要创建用户admin @ localhost，以下是命令:

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

Cheers

干杯

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

这个错误自2007年以来就一直存在于bugmysql.com上，而这个线程主要是对所有错误答案的重复，甚至一年前也是如此。

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.

根据MySQL文档，像CREATE USER、GRANT、REVOKE和DROP USER这样的命令不需要后续的FLUSH PRIVILEGES命令。如果你读了文档，你就会明白为什么。这是因为直接修改MySQL表不会将信息重新加载到内存中;然而，针对这个bug的大量解决方案声称，刷新特权就是答案。

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

这也可能不是一个bug。这是一个文档阴谋——文档从版本到版本都有不同的地方。

13.7.1.2. DROP USER Syntax

13.7.1.2。降低用户的语法

...

…

DROP USER user [, user] ...

删除用户用户[，用户]…

...

…

DROP USER 'jeffrey'@'localhost';

降低用户“杰弗里”@“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.

删除MySQL 5.0.0中的用户，只删除没有特权的帐户。在MySQL 5.0.2中，它也被修改为删除帐户特权。这意味着删除帐户的过程取决于您的MySQL版本。

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

对于MySQL 5.0.2，您可以删除一个帐户及其特权如下:

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

我得到这个错误的唯一时间是当我删除用户;就像doc建议的那样，但是MySQL并没有将“%”作为通配符来处理，这样会使所有主机上的所有用户都删除。它其实并没有那么疯狂。或者，当它删除localhost用户并试图删除%的用户时，它有时可以工作。

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.

很明显，当它试图删除%的用户时，它会发出错误消息并退出。随后在localhost中创建用户将失败，因为本地主机用户从未被删除。似乎没有必要像一张海报所建议的那样浪费时间在寻找鬼魂的捐款单上。

I see 7 votes for:

我看到7票赞成:

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

把用户“jack@localhost”;//完全删除账号

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

将其解释为DROP用户“jack@localhost”@“%”;#错了

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.

实际上，似乎有一个真正的错误生成了相同的错误消息，但是它与第一个创建的用户(在新的mysql服务器安装之后)被删除有关。这个bug是否被修复了，我不知道;但是我不记得最近发生了什么，我现在已经是5.5.27了。

#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';

如果在mysql上使用DELETE语句。用户表试图删除一个用户，然后试图用CREATE user重新建立用户，您将得到一个1396错误。通过运行DROP用户'username'@'host'消除此错误;

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

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

(如果您试图重新创建jack，将会得到1396个错误)

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

(Get out of this situation by running DROP USER)

(通过运行DROP用户来摆脱这种情况)

DROP USER 'jack'@'localhost';

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

(我认为，刷新特权不会造成伤害，但一定要先删除用户。)

#5

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

您不应该以这种方式手动删除用户。MySQL有删除特权的吊销语法和删除特权的删除用户:

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.

最好使用所提供的工具，而不是在后台混日子。

#6

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

删除用户，刷新权限;然后,创建用户。它确实工作!

#7

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

试着删除从mysql。db中用户= 'jack'然后创建一个用户。

#8

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.

在MySQL 5.6中使用Drop用户userid;不工作。使用方法:把用户“标识”@“localhost”;和/或删除用户“标识”@“%”;。通过这种方式，我可以删除用户并重新创建它。

#9

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';

#10

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';

#11

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,

这个post MySQL错误1045(28000):拒绝用户“bill”@“localhost”(使用密码:YES)的访问是有用的。有时，有一个匿名用户“@'localhost'或“@'127.0.0.1'”。为了解决这个问题，

first drop the user whose 'create user' failed.

首先删除“创建用户”失败的用户。
Create new user.

创建新用户。
Grant required privileges to the new user.

向新用户授予所需的特权。
Flush privileges.

冲洗的特权。

#12

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;

如果您想用sql删除用户，需要删除这些表中的相关数据:columns_priv、db、procs_priv、tables_priv。然后执行刷新权限;

#13

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.

关于这个问题的简单研究。由于“delete”命令只删除“mysql”数据库“user”表中的用户记录，我们可以将其添加回去，然后完全删除用户。然后可以创建同名用户。

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

步骤1。在mysql数据库中找到用户表的记录格式。

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.

步骤2。根据step1中显示的列，用用户名创建一个哑记录。例如，将它插入到表中，提醒您用您的用户名替换“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.

步骤3。删除用户。

drop user username;

Now you are able to create user with same name.

现在您可以创建具有相同名称的用户。

#14

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

mysql >删除从mysql。db中用户= 'jack'

Restart the server:

重新启动服务器:

# mysql.server restart

# mysql。服务器重启

Then do your CREATE USER command.

然后执行创建用户命令。

#15

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

MySQL服务器使用—skip-grant-tables选项运行，因此无法执行此语句

#16

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.

我知道这已经过时了，但由于它是谷歌的第一个结果，我想我应该添加我的解。在我的例子中，删除用户很好，但是重新创建用户给了我一个“ERROR 2013 (HY000):在查询期间与MySQL服务器失去连接”和“ERROR 2006 (HY000): MySQL服务器已经消失。”我尝试了刷新特权——>删除用户解决方案，但是仍然有相同的错误。

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!

在我的例子中，错误是由于mysql从5.1升级到> 5.6。查看错误日志时，我注意到它说要运行mysql_upgrade。做到了，我的创建用户声明工作得很好!

#17

I recently got this error.

我最近犯了这个错误。

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

我的工作是检查mysql工作台的用户和特权，实现用户仍然存在。

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

从那里删除之后，我可以重新创建用户。

#18

-1

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

只需从mysql中删除用户相关数据。db(可能也来自其他表)，然后重新创建这两个表。

#19

-1

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:

我也遇到过同样的问题，在搜索了几次之后，我找到了一个适合我的解决方案。我希望它能帮到你。由于您已经创建了用户，现在尝试在您的Mysql控制台中执行刷新特权。这个问题已经在MySql bug贴子里了。你也可以看看这个。现在，在刷新之后，您可以创建一个新用户。遵循以下步骤:

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.

现在您可以看到Mysql控制台。

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.

你可以用你想要的用户名代替用户名。如果您正在本地机器上运行Mysql，那么输入“localhost”而不是主机，否则给出您想要访问的服务器名称。

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

例:创建由“hello”标识的用户smruti@localhost;

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

现在创建新用户。如果您想提供所有的访问权限，请输入

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

授予*的所有特权。*“分类列出”@“localhost”;

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

现在你可以通过输入\q退出MySQL了。再次登录。

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

mysql -u newusername -p，然后按Enter。你可以看到一切。

Hope this helps.

希望这个有帮助。

#1

165