MySQL "mysql there is no such grant defined for user"

Question

This error happened when I granted all privileges to a new root account I just created.

Steps to produce the problem:

CREATE USER 'root'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SHOW GRANTS for 'root'@'localhost';

After "show grants" I got the error "mysql there is no such grant defined for user 'root' on host 'localhost'". There were no errors after executing the first three commands. The new user was created successfully.

How do I solve this problem?

More info: I'm running MySQL 5.7 on my MacOS laptop(OSX 10.10.5).

Answer 1

There is nothing wrong with your posted code but as guess try with wildcard symbol % like

SHOW GRANTS for 'root'@'%';

(OR)

As an alternative, login with your created user 'root'@'localhost' and just use SHOW GRANTS. See Documentation

Answer 2

I don't think mysql allows you to create another root account. So the create causes an error.

CREATE USER 'root'@'localhost';

ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'

You should check for the existing root account in the user table and you'll find the wildcard to be '%' which should mean you do not need to create a localhost root user.

select * from user where user = 'root';

Asking to show grants on root localhost should work, and does work for me.

show grants for 'root'@'localhost';