pt-table-checksum dies if creating the --replicate table fails

[Baron Schwartz]

It's nice that pt-table-checksum automatically creates the database or table, but if I want it to run as a restricted user, currently I have to use a workaround:

   08-21T10:44:34 Error executing CREATE DATABASE IF NOT EXISTS `percona` /... DBD::mysql::db do failed: Access denied for user 'checksum'@'%'...

I don't want it to have this privilege because I don't want it to accidentally create any database or table in case I mess something up. It should ideally catch this error, determine that the database exists, and go on. (If it's going to do that, though, it should just check if-exists before trying to create the database/table).

The workaround right now is to use --no-create-* options.

[Brian Fraser (fraserbn)]

I think we could catch this in particular by looking at the error message (iirc there's code in the branch history to deal with exactly that), but as of late we've been moving away from permission checks, since they didn't work on 5.5. Since there's a workaround, I'm not sure if adding those back would be a step forward?

[Baron Schwartz (baron-xaprb)]

Right, I'm only suggesting an existence check, not a permission check.

[Brian Fraser (fraserbn)]

Yeah. We actually aren't doing the existence check on purpose, to avoid replication failing -- at that point of the code we only have a the master's dbh, and that the master has the table doesn't mean that the slaves do. Of course, the real solution would be to check that the table exists on all servers (which is https://bugs.launchpad.net/percona-toolkit/+bug/1009510), which I was about to push, but this bug report made me realize that the checks out to be elsewhere. Thanks!

[Brian Fraser (fraserbn)]

I was somewhat confused when I wrote my previous message; https://bugs.launchpad.net/percona-toolkit/+bug/1009510 is only tangentially related to this bug. I've thought things through a bit more and wrote down this behavior:

if --create-replicate-table
   try creating the table
   if that failed
      if the table exists
         check the table for existence in every slave, like in 1009510
      else
         die
else
   if the table doesn't exist
      die

(similar behavior for the database creation)

So for this case, the CREATE TABLE would fail because the user doesn't have enough permissions, but if the database and table exist in all the slaves (and the table has at least the columns of the master) then the tool will DTRT and carry on. This somewhat breaks down if the slaves are delayed and, while the db/table don't currently exist, thet eventually will; but for that case you can use --no-create-replicate-table.

[Daniel Nichter (daniel-nichter)]

See bug 1009510 for my changes and comments on this branch.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-571