pt-table-sync should always set REPEATABLE READ

Bug #869005 reported by Hartmut Holzgraefe
20
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Medium
Daniel Nichter

Bug Description

On servers with default isolation level set to READ COMMITTED or READ UNCOMMITTED and log-bin enabled the permission check in pt-table-sync will fail as it sets replication mode to statement based replication and then checks whether the user has sufficient permissions to execute a "DELETE FROM table LIMIT 0".

This fails on InnoDB tables as DELETE in READ [UN]COMMITTED is not safe for statement based replication.

Current workaround would be to call pt-table-sync with --transaction which will explicitly set isolation level REPEATABLE READ,
IMHO this should always be set though as it also has an effect in auto commit mode outside of transactions as single statements by themselves will still be transactions of their own, and so be affected by the "DELETE in READ [UN]COMMITTED is not safe for statement based replication" limitation.

The error message raised by pt-table-sync is also very confusing in this context as it only reports that the users permissions are insufficient (even for users with ALL PRIVILEGES WITH GRANT OPTION) and does not reveal the actual error message and so giving no hints towards the actual problem.

Revision history for this message
Hartmut Holzgraefe (hartmut-php) wrote :

See also last two entries (comment #8 and #9) on http://code.google.com/p/maatkit/issues/detail?id=1029

tags: added: pt-table-sync
tags: added: tx-isolation
Changed in percona-toolkit:
status: New → Triaged
Revision history for this message
Frederic Descamps (lefred) wrote :

For information this is the error returned when delete permissions is checked,

eval { $dbh->do($sql); };
   my $can_delete = $EVAL_ERROR ? 0 : 1;

this returns:

DBD::mysql::db do failed: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. [for Statement "DELETE FROM `database_name`.`table_name` LIMIT 0"] at ./bin/pt-table-sync line 5577.

tags: added: i24538
tags: added: percona-24538
removed: i24538
Changed in percona-toolkit:
milestone: none → 2.1.3
Changed in percona-toolkit:
importance: Undecided → Medium
Changed in percona-toolkit:
status: Triaged → In Progress
assignee: nobody → Daniel Nichter (daniel-nichter)
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

The tool now always does SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ. The privs check was removed for bug 916168 "pt-table-checksum privilege check fails on MySQL 5.5", so this bug should definitely no longer be an issue.

Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

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

Other bug subscribers

Remote bug watches

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