Solve the mysql user deletion bug

When the author was using MySQL to add a user, he found that the username was written incorrectly.

I have obsessive compulsive disorder and I have to change it, but I found that when I deleted the user, the command was successful.

However, when creating a user with the same name, an error will be reported. I checked a lot on the Internet and found that this is an official bug of MySQL. I don't know whether the author is not good enough to understand the bug or has really solved this problem. I will share it with you below. Everyone is welcome to discuss it together.

After deleting a user, you cannot create the same username again.

In the MySQL database, there is a user table, which can be used to query all users and user information. By deleting the user information and the permissions you have granted to the user, you can completely delete the user.

Workaround

Note that after deleting multiple users' permissions several times, I found that when I created this user, % allowed remote connections, so the last deletion was successful.

Refresh User Permissions

FLUSH PRIVILEGES;

At this time, if you create a user with the same name again, mysql will not report an error.

Supplement: mysql delete user (two implementation solutions)

drop

drop user XXX; deletes an existing user. By default, the user 'XXX'@'%' is deleted. If there are other users such as 'XXX'@'localhost', they will not be deleted together. If you want to delete 'XXX'@'localhost', you need to add the host when using drop, that is, drop user 'XXX'@'localhost'.

delete

delete from user where user='XXX' and host='localhost'; XXX is the user name and localhost is the host name.

the difference

Drop will not only delete the data in the user table, but also delete the contents of other permission tables. Delete only deletes the content in the user table, so after deleting a user with delete, you need to execute FLUSH PRIVILEGES; to refresh the permissions, otherwise an error will be reported the next time you use the create statement to create a user.

Supplement: Solution to mysql user creation error [Err] 1396 - Operation CREATE USER failed for 'test'@'%'

question:

Execute the mysql create user statement CREATE USER test IDENTIFIED BY 'test'; an error is reported, the error message is

[Err] 1396 - Operation CREATE USER failed for 'test'@'%'

Solution steps:

1. Create a test user.

2. If the query shows that there is no such user, execute FLUSH PRIVILEGES; then re-execute the creation statement.

3. If an error message is still displayed, execute drop user 'test'@'%'; and then re-execute the create statement.

4. Creation successful.

Possible causes:

1. The user already exists.

2. There is no refresh permission after deleting the user using the delete statement.

3. The user does not exist, but the user's permission information exists.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.