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

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.

Answer 2

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 3

Oddly enough, simply QUIT-ing the client and reconnecting resolved the issue! Evidently, FLUSH PRIVILEGES is not enough.

I'd like to hear any comments if you have them on why that is. I have always heard that FLUSH PRIVILEGES will do just that. Could it have something to do with me originally logging in as 'replicator'@'%' and then later creating the 'replicator'@'localhost' account?

In any case, if you have weird permissions issues like this, I guess it is worth trying to reconnect the client.

Answer 4

When using MySQL Workbench Migration Wizard to copy a DB from one server to another, I was presented with the same error message because the new DB name did not match the original one. The new server automatically places its domain name in front of all DB names creating a mismatch in names. Therefore when transferring DB xyz to the new server domain, it called the new DB domain_xyz and the above error message occurred for every table creation.

To fix the problem, I manually edited the new DB name after the migration schema was generated, changing it from xyz to doman_xyz.

Tip-of-the-hat to Camwyn's response that got me looking in this direction.