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

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

我的案例:用户'jack'之前存在，但我从mysql中删除了它。用户才能重新创建它。我在那张桌子上没看到这东西的痕迹。如果我对其他一些随机用户名执行这个命令，比如'jimmy'，它就能正常工作(就像它最初对'jack'所做的那样)。

我做了什么破坏用户'jack'，我如何撤销这个破坏，以重新创建'jack'作为MySQL安装的有效用户?

参见下面的示例。(当然，最初，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)手动在mysql.user中插入麻烦的用户提供的必填字段值

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

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

3)。

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)。

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

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)

希望这能有所帮助。

2020-04-12 23:54:17

其他回答

似乎您需要为数据库创建用户，并为创建的用户授予特权

——>为数据库创建用户

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

创建用户“root”@“%”，用户名为“root”;

——>授予特权

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

授予mydb上的所有特权。*到“root”@“%”;

2020-07-26 02:49:28

您不应该以这种方式手动删除用户。MySQL有REVOKE语法用来删除权限，DROP USER语法用来删除权限:

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

最好使用提供的工具，而不是在后台瞎忙活。

2011-04-05 16:49:44

是的，这个bug就在那里。然而，我找到了一个小小的变通办法。

假设用户在那里，那么删除用户删除用户后，需要刷新mysql权限现在创建用户。

这应该能解决问题。假设我们要创建admin @ localhost用户，下面的命令如下:

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

2011-06-13 16:00:44

我今天遇到了这个问题，我通过以下步骤解决了它:

1)手动在mysql.user中插入麻烦的用户提供的必填字段值

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

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

3)。

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)。

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

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)

希望这能有所帮助。

2020-04-12 23:54:17

我也犯了同样的错误。但是命令“FLUSH PRIVILEGES;”不起作用。我喜欢这样:

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

2015-01-08 20:45:59

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

推荐文章

最新文章

标签