pt-table-checksum says authorization isn't there when it is

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.

Could we compromise and have a --no-check-auth option, so that in
situations like these we can overcome it? or is the workaround of
"just grant duplicate meaningless permissions" workaround enough?

-Sheeri

On Thu, May 24, 2012 at 1:00 PM, Baron Schwartz <email address hidden> wrote:
> 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.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1003939
>
> Title:
>  pt-table-checksum says authorization isn't there when it is
>
> Status in Percona Toolkit:
>  New
>
> Bug description:
>  I started with these grants:
>
>  mysql> show grants;
>  +------------------------------------------------------------------------------------------------------------------------------------------------------+
>  | Grants for checksum@localhost                                                                                                                        |
>  +------------------------------------------------------------------------------------------------------------------------------------------------------+
>  | GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'checksum'@'localhost' IDENTIFIED BY PASSWORD 'HASH ELIDED' |
>  | GRANT ALL PRIVILEGES ON `percona`.* TO 'checksum'@'localhost'                                                                                        |
>  +------------------------------------------------------------------------------------------------------------------------------------------------------+
>  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 trademark...

I don't think that option not to check authorization would actually be a win. I think we just need to rip it all out and let failures be the test of success. The tool does very little upfront work these days anyway, so it should fail much sooner.

This is a duplicate of bug 916168.

MySQL 5.5 currently has a bug that affects this: http://bugs.mysql.com/bug.php?id=61846

For the record:

mysql> SHOW GRANTS FOR CURRENT_USER\G

5.1
===

*************************** 1. row ***************************
Grants for test_user@%: GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
*************************** 2. row ***************************
Grants for test_user@%: GRANT ALL PRIVILEGES ON `percona`.* TO 'test_user'@'%'

5.5.21
======

*************************** 1. row ***************************
Grants for test_user@%: GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test_user'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF'
*************************** 2. row ***************************
Grants for test_user@%: GRANT ALL PRIVILEGES ON `percona`.* TO 'test_user'@'%'

Identical grants, but:

mysql> USE percona; SHOW FULL COLUMNS FROM checksums\G

5.1
===

*************************** 1. row ***************************
     Field: db
      Type: char(64)
 Collation: latin1_swedish_ci
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:

5.5.21
======

*************************** 1. row ***************************
     Field: db
      Type: char(64)
 Collation: latin1_swedish_ci
      Null: NO
       Key: PRI
   Default: NULL
     Extra:
Privileges: select
   Comment:

So as Baron said in bug 916168, I'm just going to remove the privs check and let the tool either work or not, and if it doesn't, then the user will know without a doubt that they need to fix the user's privs.

Correction: "So as Baron said in _this bug_ ..."