ERROR 1062 (23000): Duplicate entry ‘%-root‘ for key ‘user.PRIMARY‘

一、报错

MySQL执行命令：

UPDATE user SET host = '%' WHERE user = 'root';

报错：

ERROR 1062 (23000): Duplicate entry '%-root' for key 'user.PRIMARY'

mysql> UPDATE user SET host = '%' WHERE user = 'root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'user.PRIMARY'

二、原因 

查看所有的用户

select host, user from user;

发现，有两个root用户，我们将host 为host的那条数据删除。

 

mysql> DELETE FROM user WHERE user = 'root' AND host = 'host';
Query OK, 1 row affected (0.01 sec)

 再次进行更新

mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再次查看用户数据

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

此时HOST已经变成了%

最后使用更改生效

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)