3

I'm using pt-show-grants to create all my GRANTS on a new server but there's lots of old GRANTS that refer to tables that doesn't exist anymore so I get errors when I try to apply the SQL file on the new server.

Is there a way to remove GRANTS that refer to tables that doesn't exist anymore?

Improve this question

2 Answers 2

Reset to default
4

I don't see any option in pt-show-grants to do this kind of restriction. There are options for --only and --ignore specific users, but not tables. Nor is there an automatic option to ignore grants on nonexistant tables.

I also glanced in the code, and it doesn't seem to break out the tables to scan through them for any reason.

For what it's worth, I don't see any error when I try to grant to a non-existant table. I tested on Percona Server 5.6.16:

mysql> grant all on test.nonexist to 'user'@'%';
Query OK, 0 rows affected (0.00 sec)

$ pt-show-grants
. . .
GRANT ALL PRIVILEGES ON `test`.`nonexist` TO 'user'@'%';

Can you tell me anything else about your version of MySQL and the specific error that it produces when you try to run that grant script?

Re your comment:

The difference between ALL PRIVILEGES and specific privileges seems to be deliberate, according to Bug #10406 Grant all command does not give error though table does not exist.

If you use GRANT CREATE, SELECT ... ON test.notexist TO 'user'@'%' then this works. As long as you include CREATE privilege, then it permits the grant to the non-existant table.

Anyway, that's interesting trivia, but it doesn't address your original question.

This has been requested before, but never implemented. It was recorded as a "blueprint" (like a feature request) here: https://blueprints.launchpad.net/percona-toolkit/+spec/pt-show-grants-for-nonexistent-tables

Perhaps someone would like to implement a patch and contribute it to Percona Toolkit?

Improve this answer
2
  • Your GRANT command works but not mine. Try : GRANT INSERT, SELECT, UPDATE ON test.notexist TO 'user'@'%';
    – bbigras
    Commented Apr 10, 2014 at 22:48
  • Thanks. I'll take a look at it and try to implement a patch. If my perl skills are good enough.
    – bbigras
    Commented Apr 11, 2014 at 0:40
0

I have the same problem. Dumped data from CloudSQL and loading it into an AWS Aurora instance. Data appears to be correct but includes tons of legacy grants for tables that no longer exist. Apparently this is now an error. My solution was to iterate through the output and comment out the legacy grants. Horrible manual approach, but it got me unblocked. :(

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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