errors running pt-table-sync as non-root

[Ryan Brothers]

I am running into an issue that was mentioned at the end of http://code.google.com/p/maatkit/issues/detail?id=95.

I am using pt-table-sync to sync 2 tables in a nightly cronjob and would prefer to run it as a non-root user. The 2 tables/databases are not using replication so I'm not concerned with the binary log in this particular scenario. When I run the following:

pt-table-sync --dry-run --execute h=localhost,P=3306,u=test,p=test,D=db1,t=table1 D=db2

I receive the error:

---
DBD::mysql::db do failed: Access denied; you need (at least one of) the SUPER privilege(s) for this operation at /usr/bin/pt-table-sync line 8676.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
---

To get around it, I commented out line 8676 in /usr/bin/pt-table-sync, but then I get a 2nd error:

---
Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation [for Statement "SHOW SLAVE STATUS"] at line 6165 while doing db2.table1 on localhost
---

I can get around that too by commenting out lines, but is there a better workaround or could something be built into pt-table-sync to support this use case?

[Daniel Nichter (daniel-nichter)]

This is not an issue of running as the root system user but an issue of the MySQL user not having the required privileges. The MySQL user used to run pt-table-sync needs pretty much all privs because it reads and writes (updates) data.

What privs does the MySQL user you're using have?

[Ryan Brothers (ryan-brothers)]

The user running pt-table-sync has full access to the 2 databases:

GRANT ALL ON db1.* to test@localhost;
GRANT ALL ON db2.* to test@localhost;

The user does not have root access to the server, but he shouldn't need it because all he needs to do is run insert/update/delete queries to get the 2 tables in sync.

[Baron Schwartz (baron-xaprb)]

I think ultimately this tool is not flexible enough right now -- it doesn't let you disable things it needs for scenario X when you are trying to do scenario Y, if that makes sense. However, it is not clear to me whether you're doing something that requires SHOW SLAVE STATUS.

[Ryan Brothers (ryan-brothers)]

Yes, in the above scenario, I am just using mk-table-sync to efficiently sync a table between 2 databases. The 2 databases are not replicated and binary logging is not enabled.

[Mark Lehmann (mlehmann-4)]

Work around for getting a table sync issue when super privileges are not available and not syncing between a master and a slave. This code change adds an option called "--no-bin-log-format" to tell pt-table-sync that is is okay to avoid using "SET @@binlog_format=STATEMENT" .

This addresses only one of many possible places where the super privilege is needed

In pt-table-sync there is original code looking like this (I intentionally did not provide diff/patch formatted changes, since these are changes to the distributed perl code):

sub get_cxn {
...
   # Ensure statement-based replication.
   # http://code.google.com/p/maatkit/issues/detail?id=95
   $sql = '/*!50105 SET @@binlog_format="STATEMENT"*/';
   PTDEBUG && _d($dbh, $sql);
   $dbh->do($sql);

The changed code is:

   # Ensure statement-based replication.
   # http://code.google.com/p/maatkit/issues/detail?id=95
   if ( $o->get('bin-log-format') ) {
       $sql = '/*!50105 SET @@binlog_format="STATEMENT"*/';
       PTDEBUG && _d($dbh, $sql);
       $dbh->do($sql);
   }

which requires that the bin-log-format option be added to the POD section, So I added this:

original:

=item --[no]bin-log

default: yes

Log to the binary log (C<SET SQL_LOG_BIN=1>).

Specifying C<--no-bin-log> will C<SET SQL_LOG_BIN=0>.

=item --buffer-in-mysql

Changed code:

=item --[no]bin-log

default: yes
                       ...

[ninguno (soloninguno)]

With the previous comment and this link https://code.google.com/p/maatkit/issues/detail?id=95 (that suggest to commented out the lines between the two MKDEBUGs in get_slave_status method), i manage to synchronize two databases that are not master-slave (no replication used).
I think would be useful to have this option in pt-table-sync, what do you think of supporting it?

[Shahriyar Rzayev (rzayev-sehriyar)]

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