我似乎无法重新创建一个我已经删除的简单用户,即使是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)

当前回答

我的经验与这个错误导致我做显式删除mysql。用户,但不是从mysql.db

如果create中的用户名存在于user或db中,就会得到这个错误。通过做 从mysql删除。user == 'user@bar'; delete from mysql.db where User == 'user@bar';

其他回答

如果要删除一个使用sql语句的用户,需要先删除columns_priv、db、procs_priv、tables_priv表中的相关数据。然后执行flush特权;

尝试删除mysql.db where user = 'jack'然后创建一个用户

我的经验与这个错误导致我做显式删除mysql。用户,但不是从mysql.db

如果create中的用户名存在于user或db中,就会得到这个错误。通过做 从mysql删除。user == 'user@bar'; delete from mysql.db where User == 'user@bar';

只需从mysql.db中删除与用户相关的数据(也可以从其他表中删除),然后重新创建两者。

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