Is it possible to remove useless GRANTS in MySQL

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?

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?

StackExchange Database Administrators Q#62960, answer score: 4