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

created at 09-16-2021 views: 2

At that time, I wanted to use SQLyog to remotely connect to the database, but found that I could not connect, and the following error occurred:

Host:'xx.xx.xx.xx' is not allowed to connect to this MySQL server

This error is because your MySQL database does not have remote access permissions.
Enter MySQL

mysql -uroot -p

selecte database

use mysql;


select host,user from user where user='root';

query result

It is found that there are two users corresponding to two host values.
If you use the change statement

update user set host='%' where user='root';

There will be an error like the title:

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

This is because there are two host attributes corresponding to user
We delete one of the hosts

delete from user where host='';

Use the query statement to find that there is only one left:

query statement

Use the update statement, success!

update statement success

Refresh permissions

flush privileges;

Successful external connection

created at:09-16-2021
edited at: 09-16-2021: