errors running pt-table-sync as non-root

Reported by Ryan Brothers on 2012-04-15
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Undecided
Unassigned

Bug Description

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) wrote :

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?

tags: added: pt-table-sync
tags: added: privs
Changed in percona-toolkit:
status: New → Triaged
Ryan Brothers (ryan-brothers) wrote :

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) wrote :

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) wrote :

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) wrote :
Download full text (5.5 KiB)

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
                       ...

Read more...

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.