Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*dfdfdfdf' WITH GRANT OPTION |
| 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
    
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
add comment

1 Answer

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

mysql> FLUSH PRIVILEGES;

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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