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...