Merge lp:~percona-toolkit-dev/percona-toolkit/fix-bug-911996 into lp:percona-toolkit/2.0

[Daniel Nichter]

[Daniel Nichter (daniel-nichter)]

review: Approve

[lp:~percona-toolkit-dev/percona-toolkit/fix-bug-911996]

131. By Daniel Nichter on 2012-01-05

Update sample files.

[Baron Schwartz (baron-xaprb)]

review: Approve

[Brian Fraser (fraserbn)]

review: Approve

=== modified file 'Changelog'
--- Changelog	2011-12-30 22:44:29 +0000
+++ Changelog	2012-01-05 18:57:23 +0000
@@ -1,5 +1,7 @@
 Changelog for Percona Toolkit
 
+  * Fixed bug 911996: pt-table-sync --replicate causes "Unknown column" error
+
 v2.0.1 released 2011-12-30
 
   * Completely redesigned pt-table-checksum

=== modified file 'bin/pt-table-sync'
--- bin/pt-table-sync	2011-12-30 22:44:29 +0000
+++ bin/pt-table-sync	2012-01-05 18:57:23 +0000
@@ -8892,13 +8892,13 @@
    my $lb_sql = $asc->{boundaries}->{'>='};
    foreach my $val ( $q->deserialize_list($diff->{lower_boundary}) ) {
       my $quoted_val = $q->quote_val($val);
-      $lb_sql =~ s/\?/$val/;
+      $lb_sql =~ s/\?/$quoted_val/;
    }
 
    my $ub_sql = $asc->{boundaries}->{'<='};
    foreach my $val ( $q->deserialize_list($diff->{upper_boundary}) ) {
       my $quoted_val = $q->quote_val($val);
-      $ub_sql =~ s/\?/$val/;
+      $ub_sql =~ s/\?/$quoted_val/;
    }
 
    return "$lb_sql AND $ub_sql";

=== modified file 't/pt-table-sync/basics.t'
--- t/pt-table-sync/basics.t	2011-12-30 16:23:41 +0000
+++ t/pt-table-sync/basics.t	2012-01-05 18:57:23 +0000
@@ -28,7 +28,7 @@
    plan skip_all => 'Cannot connect to sandbox slave';
 }
 else {
-   plan tests => 17;
+   plan tests => 19;
 }
 
 $sb->wipe_clean($master_dbh);
@@ -168,6 +168,35 @@
 # Restore PTDEBUG env.
 $ENV{PTDEBUG} = $dbg || 0;
 
+# ###########################################################################
+# Fix bug 911996.
+# ###########################################################################
+`$trunk/bin/pt-table-checksum h=127.1,P=12345,u=msandbox,p=msandbox --max-load '' --lock-wait 3 --chunk-size 50 --chunk-index idx_actor_last_name -t sakila.actor --quiet`;
+
+PerconaTest::wait_for_table($slave_dbh, "percona.checksums", "db='sakila' and tbl='actor' and chunk=7");
+$slave_dbh->do("update percona.checksums set this_crc='' where db='sakila' and tbl='actor' and chunk=3");
+$slave_dbh->do("update sakila.actor set last_name='' where actor_id=30");
+
+$output = output(
+   sub {
+      pt_table_sync::main('h=127.1,P=12345,u=msandbox,p=msandbox',
+         qw(--print --execute --replicate percona.checksums),
+         qw(--no-foreign-key-checks))
+   }
+);
+
+like(
+   $output,
+   qr/^REPLACE INTO `sakila`.`actor`\(`actor_id`, `first_name`, `last_name`, `last_update`\) VALUES \('30', 'SANDRA', 'PECK', '2006-02-15 04:34:33'\)/,
+   "--replicate with char index col (bug 911996)"
+);
+
+$output = `$trunk/bin/pt-table-checksum h=127.1,P=12345,u=msandbox,p=msandbox --max-load '' --lock-wait 3 --chunk-size 50 --chunk-index idx_actor_last_name -t sakila.actor`;
+is(
+   PerconaTest::count_checksum_results($output, 'diffs'),
+   0,
+   "Synced diff (bug 911996)"
+);
 
 # #############################################################################
 # Done.

=== modified file 't/pt-table-sync/diff_where.t'
--- t/pt-table-sync/diff_where.t	2011-12-22 22:43:15 +0000
+++ t/pt-table-sync/diff_where.t	2012-01-05 18:57:23 +0000
@@ -54,7 +54,7 @@
       this_crc       => '4a57d814',
       upper_boundary => '9'
    },
-   where => "((`id` >= 7)) AND ((`id` <= 9))",
+   where => "((`id` >= '7')) AND ((`id` <= '9'))",
 );
 
 # #############################################################################

=== modified file 't/pt-table-sync/samples/issue_560_output_2.txt'
--- t/pt-table-sync/samples/issue_560_output_2.txt	2011-12-22 22:50:52 +0000
+++ t/pt-table-sync/samples/issue_560_output_2.txt	2012-01-05 18:57:23 +0000
@@ -1,22 +1,22 @@
 # Syncing via replication P=12346,h=127.1,p=...,u=msandbox
 # DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND ((((`player_id` > 301) OR (`player_id` = 301 AND `buddy_id` >= 887)) AND ((`player_id` < 350) OR (`player_id` = 350 AND `buddy_id` <= 2454)))) FOR UPDATE
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND ((((`player_id` > 301) OR (`player_id` = 301 AND `buddy_id` >= 887)) AND ((`player_id` < 350) OR (`player_id` = 350 AND `buddy_id` <= 2454)))) LOCK IN SHARE MODE
-# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND (((`player_id` > 301) OR (`player_id` = 301 AND `buddy_id` >= 887)) AND ((`player_id` < 350) OR (`player_id` = 350 AND `buddy_id` <= 2454))) ORDER BY `player_id`, `buddy_id` FOR UPDATE
-# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND (((`player_id` > 301) OR (`player_id` = 301 AND `buddy_id` >= 887)) AND ((`player_id` < 350) OR (`player_id` = 350 AND `buddy_id` <= 2454))) ORDER BY `player_id`, `buddy_id` LOCK IN SHARE MODE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND ((((`player_id` > '301') OR (`player_id` = '301' AND `buddy_id` >= '887')) AND ((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454')))) FOR UPDATE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND ((((`player_id` > '301') OR (`player_id` = '301' AND `buddy_id` >= '887')) AND ((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454')))) LOCK IN SHARE MODE
+# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND (((`player_id` > '301') OR (`player_id` = '301' AND `buddy_id` >= '887')) AND ((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) ORDER BY `player_id`, `buddy_id` FOR UPDATE
+# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) AND (((`player_id` > '301') OR (`player_id` = '301' AND `buddy_id` >= '887')) AND ((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454'))) ORDER BY `player_id`, `buddy_id` LOCK IN SHARE MODE
 DELETE FROM `issue_560`.`buddy_list` WHERE `player_id`='333' AND `buddy_id`='0' LIMIT 1;
 DELETE FROM `issue_560`.`buddy_list` WHERE `player_id`='334' AND `buddy_id`='0' LIMIT 1;
 REPLACE INTO `issue_560`.`buddy_list`(`player_id`, `buddy_id`) VALUES ('333', '3414');
 REPLACE INTO `issue_560`.`buddy_list`(`player_id`, `buddy_id`) VALUES ('334', '6626');
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '350') OR (`player_id` = '350' AND `buddy_id` > '2454')) AND 1=1)) AND ((((`player_id` > 301) OR (`player_id` = 301 AND `buddy_id` >= 887)) AND ((`player_id` < 350) OR (`player_id` = 350 AND `buddy_id` <= 2454)))) FOR UPDATE
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '350') OR (`player_id` = '350' AND `buddy_id` > '2454')) AND 1=1)) AND ((((`player_id` > 301) OR (`player_id` = 301 AND `buddy_id` >= 887)) AND ((`player_id` < 350) OR (`player_id` = 350 AND `buddy_id` <= 2454)))) LOCK IN SHARE MODE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '350') OR (`player_id` = '350' AND `buddy_id` > '2454')) AND 1=1)) AND ((((`player_id` > '301') OR (`player_id` = '301' AND `buddy_id` >= '887')) AND ((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454')))) FOR UPDATE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '350') OR (`player_id` = '350' AND `buddy_id` > '2454')) AND 1=1)) AND ((((`player_id` > '301') OR (`player_id` = '301' AND `buddy_id` >= '887')) AND ((`player_id` < '350') OR (`player_id` = '350' AND `buddy_id` <= '2454')))) LOCK IN SHARE MODE
 #      2       2      0      0 Nibble    00:00:00 00:00:00 2    issue_560.buddy_list
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND ((((`player_id` > 451) OR (`player_id` = 451 AND `buddy_id` >= 9245)) AND ((`player_id` < 500) OR (`player_id` = 500 AND `buddy_id` <= 4272)))) FOR UPDATE
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND ((((`player_id` > 451) OR (`player_id` = 451 AND `buddy_id` >= 9245)) AND ((`player_id` < 500) OR (`player_id` = 500 AND `buddy_id` <= 4272)))) LOCK IN SHARE MODE
-# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND (((`player_id` > 451) OR (`player_id` = 451 AND `buddy_id` >= 9245)) AND ((`player_id` < 500) OR (`player_id` = 500 AND `buddy_id` <= 4272))) ORDER BY `player_id`, `buddy_id` FOR UPDATE
-# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND (((`player_id` > 451) OR (`player_id` = 451 AND `buddy_id` >= 9245)) AND ((`player_id` < 500) OR (`player_id` = 500 AND `buddy_id` <= 4272))) ORDER BY `player_id`, `buddy_id` LOCK IN SHARE MODE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND ((((`player_id` > '451') OR (`player_id` = '451' AND `buddy_id` >= '9245')) AND ((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272')))) FOR UPDATE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND ((((`player_id` > '451') OR (`player_id` = '451' AND `buddy_id` >= '9245')) AND ((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272')))) LOCK IN SHARE MODE
+# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND (((`player_id` > '451') OR (`player_id` = '451' AND `buddy_id` >= '9245')) AND ((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) ORDER BY `player_id`, `buddy_id` FOR UPDATE
+# SELECT /*rows in nibble*/ `player_id`, `buddy_id`, CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS __crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE (((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) AND (((`player_id` > '451') OR (`player_id` = '451' AND `buddy_id` >= '9245')) AND ((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272'))) ORDER BY `player_id`, `buddy_id` LOCK IN SHARE MODE
 DELETE FROM `issue_560`.`buddy_list` WHERE `player_id`='486' AND `buddy_id`='0' LIMIT 1;
 REPLACE INTO `issue_560`.`buddy_list`(`player_id`, `buddy_id`) VALUES ('486', '1660');
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '500') OR (`player_id` = '500' AND `buddy_id` > '4272')) AND 1=1)) AND ((((`player_id` > 451) OR (`player_id` = 451 AND `buddy_id` >= 9245)) AND ((`player_id` < 500) OR (`player_id` = 500 AND `buddy_id` <= 4272)))) FOR UPDATE
-# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '500') OR (`player_id` = '500' AND `buddy_id` > '4272')) AND 1=1)) AND ((((`player_id` > 451) OR (`player_id` = 451 AND `buddy_id` >= 9245)) AND ((`player_id` < 500) OR (`player_id` = 500 AND `buddy_id` <= 4272)))) LOCK IN SHARE MODE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '500') OR (`player_id` = '500' AND `buddy_id` > '4272')) AND 1=1)) AND ((((`player_id` > '451') OR (`player_id` = '451' AND `buddy_id` >= '9245')) AND ((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272')))) FOR UPDATE
+# SELECT /*issue_560.buddy_list:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `player_id`, `buddy_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_560`.`buddy_list` FORCE INDEX (`PRIMARY`) WHERE ((((`player_id` > '500') OR (`player_id` = '500' AND `buddy_id` > '4272')) AND 1=1)) AND ((((`player_id` > '451') OR (`player_id` = '451' AND `buddy_id` >= '9245')) AND ((`player_id` < '500') OR (`player_id` = '500' AND `buddy_id` <= '4272')))) LOCK IN SHARE MODE
 #      1       1      0      0 Nibble    00:00:00 00:00:00 2    issue_560.buddy_list