错误1396（HY000）：操作CREATE USER对于&#39；jack&#39；@&#39；localhost&#39；失败

本文翻译自：ERROR 1396 (HY000): Operation CREATE USER failed for 'jack'@'localhost'

I seem to be unable to re-create a simple user I've deleted, even as root in MySQL. 我似乎无法重新创建一个已删除的简单用户，即使以root用户身份在MySQL中也是如此。

My case: user 'jack' existed before, but I deleted it from mysql.user in order to recreate it. 我的情况是：用户'jack'曾经存在，但是我从mysql.user中删除了它以重新创建它。 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'). 如果我对其他随机用户名（例如“ 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.) （当然，最初，在创建“ jack”和将其删除之间有很多时间。）

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)

---

#1楼

参考：https://stackoom.com/question/njc4/错误-HY-操作CREATE-USER对于-jack-localhost-失败

---

#2楼

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

---

#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年以来，此错误一直位于bugs.mysql.com上，并且该线程主要只是直到一年前所有这些错误答案的模仿。

According to the MySQL documentation, commands like CREATE USER , GRANT , REVOKE , and DROP USER do not require a subsequent FLUSH PRIVILEGES command. 根据MySQL文档，诸如CREATE USER ， GRANT ， REVOKE和DROP USER命令不需要后续的FLUSH PRIVILEGES命令。 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; 这是因为直接更改MySQL表不会将信息重新加载到内存中。 yet the plethora of solutions to this bug claim that FLUSH PRIVILEGES is the answer. 但是，针对该错误的众多解决方案都声称FLUSH PRIVILEGES是答案。

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. 13.7.1.2。 DROP USER Syntax 删除用户语法

... ...

DROP USER user [, user] ... DROP USER用户[，用户] ...

... ...

DROP USER 'jeffrey'@'localhost'; 删除用户'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. MySQL 5.0.0中的DROP USER仅删除没有特权的帐户。 In MySQL 5.0.2, it was modified to remove account privileges as well. 在MySQL 5.0.2中，已对其进行了修改以删除帐户特权。 This means that the procedure for removing an account depends on your version of MySQL. 这意味着删除帐户的过程取决于您的MySQL版本。

As of MySQL 5.0.2, you can remove an account and its privileges as follows: 从MySQL 5.0.2开始，您可以按照以下步骤删除帐户及其特权：

DROP USER user; DROP 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 ; 我唯一遇到此错误的时间是当我执行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. 就像文档所建议的那样，但是MySQL不会以将所有主机上的所有用户都丢弃的方式将'％'视为通配符。 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 %. 或者，可能是它有时在删除localhost用户然后尝试删除％1的用户时起作用。

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. 后来在localhost的CREATE USER将失败，因为从未删除过localhost用户。 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: 我看到7票赞成：

DROP USER 'jack@localhost'; 删除用户'jack @ localhost'; // completely delete the account //完全删除帐户

Which is interpreted as DROP USER 'jack@localhost'@'%'; 这被解释为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. 实际上，似乎确实有一个错误会生成相同的错误消息，但它与删除的第一个创建的用户（在安装了新的mysql服务器之后）有关。 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. 但我不记得最近发生过这种情况，目前我的版本是5.5.27。

---

#4楼

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

---

#5楼

I know this is old, but since it is the first result in Google I figured I should add my solution. 我知道这很旧，但是由于它是Google的第一个结果，所以我认为我应该添加解决方案。 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." 以我为例，删除用户可以正常工作，但是重新创建用户给我一个“错误2013（HY000）：在查询过程中丢失与MySQL服务器的连接”和“错误2006（HY000）：MySQL服务器已消失。” 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. 就我而言，错误是由于MySQL从5.1-> 5.6升级而引起的。 Viewing the error logs, I noticed that it said to run mysql_upgrade. 查看错误日志时，我注意到它说要运行mysql_upgrade。 Did that and my create user statement worked fine! 做到了吗，我的create user语句运行正常！

---

#6楼

This post MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES) is useful. 这篇文章MySQL错误1045（28000）：用户'bill'@'localhost'的访问被拒绝（使用密码：YES）很有用。 Sometimes, there is an anonymous user ''@'localhost' or ''@'127.0.0.1'. 有时，会有一个匿名用户“ @'localhost”或“ @'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. 刷新权限。