Solve the problem of deleting user bugs in MySQL

Time:2021-11-29

When adding users using mysql, the author found that the user name was written wrong for me

I had to change my obsessive-compulsive disorder, but when I found that the user was deleted, the command was successful

However, when creating a user with the same name here, an error will be reported. I checked a lot on the Internet and said that this is an official MySQL bug. I don’t know whether the author’s level is not enough to understand the bug or whether he has really solved the problem. Let’s share it with you and welcome you to discuss it together

After deleting the user of user, you cannot create the same user name again

There is a user table in the MySQL database, which can query all users and user information. Deleting the user information and the permissions you give to the user can completely delete the user

resolvent

Note that after deleting the permissions of multiple users for many times, I found that when I created this user, it was% allowed to connect remotely, 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 schemes)

drop

drop user XXX; Delete an existing user. By default, ‘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 host when using drop to delete, that is, drop user’ xxx ‘@’localhost’.

delete

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

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 contents in the user table, so you need to execute flush privileges after using delete to delete users; Refresh permissions, otherwise the next time you use the create statement to create a userreport errors

Supplement: MySQL create user reported an error [err] 1396 – operation create user failed for ‘test’ @ ‘%’

Question:

Execute 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. Test user created.

2. If no such user is found after query, execute flush privileges; Then re execute the create statement.

3. If an error is still reported, execute drop user ‘test’ @ ‘%’; Then re execute the create statement.

4. Created successfully.

Possible causes:

1. User already exists.

2. You do not have refresh permission after deleting a user with the delete statement.

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

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer. If you have any mistakes or don’t consider completely, please don’t hesitate to comment.

Recommended Today

On the mutation mechanism of Clickhouse (with source code analysis)

Recently studied a bit of CH code.I found an interesting word, mutation.The word Google has the meaning of mutation, but more relevant articles translate this as “revision”. The previous article analyzed background_ pool_ Size parameter.This parameter is related to the background asynchronous worker pool merge.The asynchronous merge and mutation work in Clickhouse kernel is completed […]