Reputation: 720
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).
Upvotes: 10
Views: 50939
Reputation: 11
you have
CREATE USER 'root'@'localhost' IDENTIFIED BY 'dddddd';
but you dont have:
CREATE USER 'root'@'%' IDENTIFIED BY 'dddddd';
grant all privileges on *.* to 'root'@'%' ;
so try it.
Upvotes: 0
Reputation: 139
Step-1: sudo mysql -u root -p
Step-2: REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user name'@'localhost';
Ex.- REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'localhost';
Step-3: FLUSH PRIVILEGES;
I think it will work.
Upvotes: 1
Reputation: 649
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';
Upvotes: 1
Reputation: 77936
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
Upvotes: 7