LP #919499: pt-table-checksum fails with binary log error in mysql >= 5.5.18

Description

**Reported in Launchpad by Bryan Aldridge last update 20-07-2012 14:00:42

When trying to run pt-table-checksum (percona-toolkit-2.0.2-1) against a master (with one slave) both running MySQL-server-5.5.19, the tool generates the following message for all tables/databases:

$ pt-table-checksum --ask-pass --user root

01-20T16:16:23 Error checksumming table mysql.user: Error executing checksum query: Checksum query for table mysql.user caused MySQL error 1592:
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave.
Query: REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `host`, `user`, `password`, `select_priv`, `insert_priv`, `update_priv`, `delete_priv`, `create_priv`, `drop_priv`, `reload_priv`, `shutdown_priv`, `process_priv`, `file_priv`, `grant_priv`, `references_priv`, `index_priv`, `alter_priv`, `show_db_priv`, `super_priv`, `create_tmp_table_priv`, `lock_tables_priv`, `execute_priv`, `repl_slave_priv`, `repl_client_priv`, `create_view_priv`, `show_view_priv`, `create_routine_priv`, `alter_routine_priv`, `create_user_priv`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, CONCAT(ISNULL(`plugin`), ISNULL(`authentication_string`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `mysql`.`user` /checksum table/

I suspect it is related to the first "Incompatible change: replication" under "Bug Fixed" here: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-18.html

Environment

None

Smart Checklist

Activity

lpjirasync January 24, 2018 at 1:52 PM

**Comment from Launchpad by: Daniel Nichter on: 18-05-2012 17:16:07

Might be the reason for the 5.8/5.10 behavior difference: http://perldoc.perl.org/perl590delta.html#Closures%2c-eval-and-lexicals

lpjirasync January 24, 2018 at 1:52 PM

**Comment from Launchpad by: Daniel Nichter on: 18-05-2012 16:04:33

This is tested and fixed in the 2.0 and 2.1 branches. You can download it now directly from those branches, or this fix will be in the 2.0.5 and 2.1.2 releases.

lpjirasync January 24, 2018 at 1:52 PM

**Comment from Launchpad by: Daniel Nichter on: 18-05-2012 15:00:13

Failing test on CentOS 5 with Perl 5.8:

not ok 6 - Bug 987393 (Perl 5.8 scoping): no errors

  1. Failed test 'Bug 987393 (Perl 5.8 scoping): no errors'

  2. in bugs.t at line 2277.

  3. got: '1'

  4. expected: '0'
    not ok 7 - Bug 987393 (Perl 5.8 scoping): checksummed table

  5. Failed test 'Bug 987393 (Perl 5.8 scoping): checksummed table'

  6. in bugs.t at line 2283.

  7. got: '0'

  8. expected: '109'

  9. Looks like you failed 2 tests of 7.

lpjirasync January 24, 2018 at 1:52 PM

**Comment from Launchpad by: Daniel Nichter on: 17-05-2012 22:25:44

Further evidence that this is probably a Perl 5.8 closure bug is that if you use that snippet as a module, i.e. "require test.pl", then call main(), then it works (the OK message is printed). This came up in testing because that's normally how tests call tools (require "pt-table-checksum" then pt_table_checksum::main()), but that wasn't reproducing the problem even though the problem occurred when I ran the tool from the command line. No problem, the test can just call pt-table-checksum as if from the command line.

lpjirasync January 24, 2018 at 1:52 PM

**Comment from Launchpad by: Daniel Nichter on: 17-05-2012 22:16:07

Indeed, closures in Perl 5.8 either work differently or there is a bug, as demonstrated by:

#!/usr/bin/perl

sub main {
exec_nibble();
return 0;
}

{
my %foo = ( 1 => 2 );

sub exec_nibble {
my $callback = sub {
print "OK in callback in exec_nibble()\n" if $foo{1};
};
$callback->();
}
}

if ( !caller ) { exit main(@ARGV); }
1;

On 5.8 that prints nothing, but on 5.10 it prints the OK message. I would argue that this is more a bug than how closures work in 5.8 because what's interesting is if you add something like print Dumper(%foo); either before or after the callback, then %foo becomes visible in the callback and the OK message is printed. Somehow touching %foo in the sub's scope brings it to life in the callback's/closure's scope, so sometimes it's in scope and sometimes it's not even though the scopes don't change.

Odd, but oh well; no need to file a Perl 5.8 bug. We'll just remove the outer { } and make the hashes global vars.

Done

Details

Assignee

Reporter

Priority

Smart Checklist

Created January 24, 2018 at 1:51 PM
Updated January 24, 2018 at 1:52 PM
Resolved January 24, 2018 at 1:52 PM