CREATE command denied for user that has GRANT ALL PRIVILEGES

Question

I am trying to set up a MySQL replication slave, and am having a very difficult time running LOAD DATA FROM MASTER;. Yes, I know it is deprecated, but I am running MySQL 5.1, and that isn't my problem at the moment.

For some reason MySQL keeps telling me the CREATE command is denied, but a check of SHOW GRANTS says otherwise. Check this out:

mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'%' IDENTIFIED BY PASSWORD '*ABCDEFABCDEFABCDEFABCDEFBLAHBLAHBLAHBLAH' |
+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA FROM MASTER;
ERROR 1142 (42000): CREATE command denied to user 'replicator'@'localhost' for table 'aggregate'
mysql>

What I thought was odd here is that when calling LOAD DATA FROM MASTER, it thinks I am 'replicator'@'localhost', yet SHOW GRANTS says 'replicator'@'%'. Just to be safe, I gave the same privs to 'replicator'@'localhost' as well.

mysql> SHOW GRANTS FOR 'replicator'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for replicator@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'localhost' IDENTIFIED BY PASSWORD '*ABCDEFABCDEFABCDEFABCDEFBLAHBLAHBLAHBLAH' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

So, any thoughts on why this is all messed up? Yes, I did FLUSH PRIVILEGES many times as well.

Thanks in advance for any advice you can give.

Answer 1

For future searchers, I found that I get this error when using a combination of MySQL Workbench and phpMyAdmin: if you 'copy SQL' from the tables list, it prepends the schema name on to the table names in the create statement (and in the foreign key commands that may be part of it).

Carefully removing the schema name cured this issue for me.

For example:

CREATE  TABLE IF NOT EXISTS `schema`.`table1` (
...blah blah blah...

CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_id` )
    REFERENCES `schema`.`table1` (`id` )
...blah blah blah...

should be changed to:

CREATE  TABLE IF NOT EXISTS `table1` (
...blah blah blah...

CONSTRAINT `fk_user_id`
    FOREIGN KEY (`user_id` )
    REFERENCES `table1` (`id` )
...blah blah blah...

Answer 2

I would like to suggest the following:

When you login next time run this query:

SELECT USER(),CURRENT_USER();

If you see two different usernames, you have a weird scenario

This you can find in the MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), Chapter 34 or 35

USER() echoes what you attempted to login as

CURRENT_USER() echos what mysql ALLOWED YOU to login as.

Try connecting using as replicator using 127.0.0.1 and run the same query.

You may also want to maker sure all necessary columns are present in mysql.user for the version of mysql you are using. If you did not upgrade the mysql.user table (from a migration from MySQL 4 - MySQL 5, or MySQL 5.0 to 5.1) columns in mysql.user could be out of sync.