Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I had to completely re-install MySQL on one of the slaves and then rebuild it via a sql dump (we have 1 master and 2 slaves). I tried to SHOW GRANTS for one of the users, but keep getting the following error:

ERROR 1141 (42000): There is no such grant defined for user 'USER' on host '%'

I can SHOW GRANTS for root though. SHOW GRANTS needs a SELECT privilege for the mysql database, which root has:

mysql> show grants;
| Grants for root@localhost                                                             |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                           |
2 rows in set (0.01 sec)

Btw, the privileges themselves exist, as I can select them from mysql.user table:

mysql> select update_priv, delete_priv from mysql.user where user like 'USER%';
| update_priv | delete_priv |
| Y           | Y           |
1 row in set (0.00 sec)

What am I missing?

Edit by RolandoMySQLDBA 2013-01-15 21:06 EDT

1) Please run SELECT VERSION(); to tell me what version of MySQL you are using

2) Please run SELECT COUNT(1) from information_schema.columns WHERE table_schema='mysql' AND table_name='user';

3) Please tell me from what version of mysql did the mysqldump come from.

share|improve this question

closed as off-topic by RolandoMySQLDBA, Phil, Max Vernon, Mark Storey-Smith, ypercubeᵀᴹ Jun 12 '14 at 20:22

If this question can be reworded to fit the rules in the help center, please edit the question.

can you add the output of SELECT User, Host FROM mysql.user WHERE User='USER' ? – Derek Downey Jan 15 '13 at 20:15
Please answer the three questions I posted inside your question. I could then proceed to answer... – RolandoMySQLDBA Jan 16 '13 at 20:50
The question has not been addressed by the OP in almost 18 months. They could have solved it themselves by uninstalling and reinstalling MySQL and didn't tell anybody. There are other posts that address this issue anyway. This post should be euthanized. – RolandoMySQLDBA Jun 12 '14 at 16:11

Entries existing in the mysql.user table and privileges being recognized by the MySQL server are two different things that are only loosely-coupled.


Any time you change the content of the mysql.user table, other than by issuing a GRANT ...-type statement, including restoring from a backup, you need to issue the FLUSH PRIVILEGES command before MySQL will realize those changes have happened. This should make the users visible.

Or, by now you may have restarted the server, which would also fix it.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.