pt-table-checksum says authorization isn't there when it is
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Triaged
|
Undecided
|
Unassigned |
Bug Description
I started with these grants:
mysql> show grants;
+------
| Grants for checksum@localhost |
+------
| GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'checksum'
| GRANT ALL PRIVILEGES ON `percona`.* TO 'checksum'
+------
2 rows in set (0.00 sec)
mysql> exit
Bye
[root@db1 ~]# mysql -u checksum -p -h db2
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 940282
Server version: 5.5.20-55-log Percona Server (GPL), Release rel24.1, Revision 217
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants;
+------
| Grants for checksum@db2 |
+------
| GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'checksum'@'db1' IDENTIFIED BY PASSWORD 'HASH ELIDED' |
| GRANT ALL PRIVILEGES ON `percona`.* TO 'checksum'@'db1' |
+------
2 rows in set (0.00 sec)
mysql> exit
But, when I try to run pt-table-checksum, I get:
[root@db1 ~]# pt-table-checksum --user=checksum --password=ELIDED
05-24T06:07:29 User does not have all privileges on --replicate table `percona`
But I have granted all on percona.*, which surely includes percona.checksums.
I was able to get around it by granting permissions to the table specifically:
mysql> grant all on percona.checksums to checksum@localhost identified by 'checksum'; grant all on percona.checksums to checksum@db1 identified by 'checksum';
tags: | added: privs pt-table-checksum |
Changed in percona-toolkit: | |
status: | New → Triaged |
The problem is that there's no way to win with the way we are currently doing this. Here is the history: we used to not try to check privileges. But then the tool would do a lot of work and "fail late." We were trying to "fail early" for a better user experience. So we tried SHOW FULL COLUMNS, and a bunch of other things like a LIMIT 0 update/delete against the table.
But SHOW FULL COLUMNS actually lies about the privileges. I don't have any bugs handy to reference, but I know I've seen this before. (Maybe someone has even reported it before against this tool, or pt-table-sync.) And a LIMIT 0 change to the table will throw an error about statement-based logging blah blah. This might be fixable by starting a transaction, issuing the change, and then rolling it back... but that won't work for MyISAM blah blah. Oh, so we could do SHOW GRANTS and ... ugh, that's going to be even worse and less reliable.
So we are stuck in a hell of special cases and no-win and so on. We just have to bite the bullet and go back to the following approach, in my opinion: don't check. Just do it. If it fails it fails, and the user will have to fix it.