Percona Toolkit moved to https://jira.percona.com/projects/PT

Merge lp:~percona-toolkit-dev/percona-toolkit/fix-bug-821673 into lp:percona-toolkit/1.0

  1. fix-bug-821673
  2. Merge into 1.0
Proposed by Daniel Nichter
Status: Merged
Approved by: Daniel Nichter
Approved revision: 100
Merged at revision: 95
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/fix-bug-821673
Merge into: lp:percona-toolkit/1.0
Diff against target: 391 lines (+187/-33)
10 files modified
bin/pt-table-checksum (+7/-8)
lib/TableChunker.pm (+9/-10)
t/lib/TableChunker.t (+72/-8)
t/pt-table-checksum/basics.t (+36/-1)
t/pt-table-checksum/chunk_index.t (+2/-6)
t/pt-table-checksum/samples/where01.out (+3/-0)
t/pt-table-checksum/samples/where01.sql (+26/-0)
t/pt-table-checksum/samples/where02.out (+4/-0)
t/pt-table-checksum/samples/where02.sql (+26/-0)
t/pt-table-checksum/throttle.t (+2/-0)
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/fix-bug-821673
Reviewer Review Type Date Requested Status
Daniel Nichter Approve
Review via email: mp+73144@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Daniel Nichter (daniel-nichter) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'bin/pt-table-checksum'
--- bin/pt-table-checksum 2011-08-23 14:31:04 +0000
+++ bin/pt-table-checksum 2011-08-27 18:33:24 +0000
@@ -2863,7 +2863,7 @@
28632863
2864sub _chunk_char {2864sub _chunk_char {
2865 my ( $self, %args ) = @_;2865 my ( $self, %args ) = @_;
2866 my @required_args = qw(dbh db tbl tbl_struct chunk_col rows_in_range chunk_size);2866 my @required_args = qw(dbh db tbl tbl_struct chunk_col min max rows_in_range chunk_size);
2867 foreach my $arg ( @required_args ) {2867 foreach my $arg ( @required_args ) {
2868 die "I need a $arg argument" unless defined $args{$arg};2868 die "I need a $arg argument" unless defined $args{$arg};
2869 }2869 }
@@ -2874,12 +2874,7 @@
2874 my $row;2874 my $row;
2875 my $sql;2875 my $sql;
28762876
2877 $sql = "SELECT MIN($chunk_col), MAX($chunk_col) FROM $db_tbl "2877 my ($min_col, $max_col) = @{args}{qw(min max)};
2878 . "ORDER BY `$chunk_col`";
2879 MKDEBUG && _d($dbh, $sql);
2880 $row = $dbh->selectrow_arrayref($sql);
2881 my ($min_col, $max_col) = ($row->[0], $row->[1]);
2882
2883 $sql = "SELECT ORD(?) AS min_col_ord, ORD(?) AS max_col_ord";2878 $sql = "SELECT ORD(?) AS min_col_ord, ORD(?) AS max_col_ord";
2884 MKDEBUG && _d($dbh, $sql);2879 MKDEBUG && _d($dbh, $sql);
2885 my $ord_sth = $dbh->prepare($sql); # avoid quoting issues2880 my $ord_sth = $dbh->prepare($sql); # avoid quoting issues
@@ -2950,7 +2945,9 @@
2950 MKDEBUG && _d("Base", $base, "chars:", @chars);2945 MKDEBUG && _d("Base", $base, "chars:", @chars);
29512946
29522947
2953 $sql = "SELECT MAX(LENGTH($chunk_col)) FROM $db_tbl ORDER BY `$chunk_col`";2948 $sql = "SELECT MAX(LENGTH($chunk_col)) FROM $db_tbl "
2949 . ($args{where} ? "WHERE $args{where} " : "")
2950 . "ORDER BY `$chunk_col`";
2954 MKDEBUG && _d($dbh, $sql);2951 MKDEBUG && _d($dbh, $sql);
2955 $row = $dbh->selectrow_arrayref($sql);2952 $row = $dbh->selectrow_arrayref($sql);
2956 my $max_col_len = $row->[0];2953 my $max_col_len = $row->[0];
@@ -5560,6 +5557,7 @@
5560 tbl => $tbl,5557 tbl => $tbl,
5561 chunk_col => $chunk_col,5558 chunk_col => $chunk_col,
5562 tbl_struct => $struct,5559 tbl_struct => $struct,
5560 where => $final_o->get('where'),
5563 );5561 );
5564 if ( !grep { !defined $params{$_} } qw(min max rows_in_range) ) {5562 if ( !grep { !defined $params{$_} } qw(min max rows_in_range) ) {
5565 @chunks = $ch->calculate_chunks(5563 @chunks = $ch->calculate_chunks(
@@ -5571,6 +5569,7 @@
5571 chunk_size => $rows_per_chunk,5569 chunk_size => $rows_per_chunk,
5572 zero_chunk => $final_o->get('zero-chunk'),5570 zero_chunk => $final_o->get('zero-chunk'),
5573 chunk_range => $final_o->get('chunk-range'),5571 chunk_range => $final_o->get('chunk-range'),
5572 where => $final_o->get('where'),
5574 %params,5573 %params,
5575 );5574 );
5576 $maxval = $params{max};5575 $maxval = $params{max};
55775576
=== modified file 'lib/TableChunker.pm'
--- lib/TableChunker.pm 2011-07-12 21:47:07 +0000
+++ lib/TableChunker.pm 2011-08-27 18:33:24 +0000
@@ -204,6 +204,7 @@
204# exact - Use exact chunk_size? Use approximates is not.204# exact - Use exact chunk_size? Use approximates is not.
205# tries - Fetch up to this many rows to find a non-zero value205# tries - Fetch up to this many rows to find a non-zero value
206# chunk_range - Make chunk range open (default) or openclosed206# chunk_range - Make chunk range open (default) or openclosed
207# where - WHERE clause.
207#208#
208# Returns:209# Returns:
209# Array of WHERE predicates like "`col` >= '10' AND `col` < '20'",210# Array of WHERE predicates like "`col` >= '10' AND `col` < '20'",
@@ -510,6 +511,9 @@
510# <TableChunker::get_range_statistics()>511# <TableChunker::get_range_statistics()>
511# chunk_size - requested size of each chunk512# chunk_size - requested size of each chunk
512#513#
514# Optional Arguments:
515# where - WHERE clause.
516#
513# Returns:517# Returns:
514# Array of chunker info that <calculate_chunks()> uses to create518# Array of chunker info that <calculate_chunks()> uses to create
515# chunks, like:519# chunks, like:
@@ -522,7 +526,7 @@
522# (end code)526# (end code)
523sub _chunk_char {527sub _chunk_char {
524 my ( $self, %args ) = @_;528 my ( $self, %args ) = @_;
525 my @required_args = qw(dbh db tbl tbl_struct chunk_col rows_in_range chunk_size);529 my @required_args = qw(dbh db tbl tbl_struct chunk_col min max rows_in_range chunk_size);
526 foreach my $arg ( @required_args ) {530 foreach my $arg ( @required_args ) {
527 die "I need a $arg argument" unless defined $args{$arg};531 die "I need a $arg argument" unless defined $args{$arg};
528 }532 }
@@ -533,15 +537,8 @@
533 my $row;537 my $row;
534 my $sql;538 my $sql;
535539
536 # Get what MySQL says are the min and max column values.
537 # For example, is 'a' or 'A' the min according to MySQL?
538 $sql = "SELECT MIN($chunk_col), MAX($chunk_col) FROM $db_tbl "
539 . "ORDER BY `$chunk_col`";
540 MKDEBUG && _d($dbh, $sql);
541 $row = $dbh->selectrow_arrayref($sql);
542 my ($min_col, $max_col) = ($row->[0], $row->[1]);
543
544 # Get the character codes between the min and max column values.540 # Get the character codes between the min and max column values.
541 my ($min_col, $max_col) = @{args}{qw(min max)};
545 $sql = "SELECT ORD(?) AS min_col_ord, ORD(?) AS max_col_ord";542 $sql = "SELECT ORD(?) AS min_col_ord, ORD(?) AS max_col_ord";
546 MKDEBUG && _d($dbh, $sql);543 MKDEBUG && _d($dbh, $sql);
547 my $ord_sth = $dbh->prepare($sql); # avoid quoting issues544 my $ord_sth = $dbh->prepare($sql); # avoid quoting issues
@@ -642,7 +639,9 @@
642 # [ant, apple, azur, boy]. We assume data is more evenly distributed639 # [ant, apple, azur, boy]. We assume data is more evenly distributed
643 # than not so we use the minimum number of characters to express a chunk640 # than not so we use the minimum number of characters to express a chunk
644 # size.641 # size.
645 $sql = "SELECT MAX(LENGTH($chunk_col)) FROM $db_tbl ORDER BY `$chunk_col`";642 $sql = "SELECT MAX(LENGTH($chunk_col)) FROM $db_tbl "
643 . ($args{where} ? "WHERE $args{where} " : "")
644 . "ORDER BY `$chunk_col`";
646 MKDEBUG && _d($dbh, $sql);645 MKDEBUG && _d($dbh, $sql);
647 $row = $dbh->selectrow_arrayref($sql);646 $row = $dbh->selectrow_arrayref($sql);
648 my $max_col_len = $row->[0];647 my $max_col_len = $row->[0];
649648
=== modified file 't/lib/TableChunker.t'
--- t/lib/TableChunker.t 2011-07-12 21:47:07 +0000
+++ t/lib/TableChunker.t 2011-08-27 18:33:24 +0000
@@ -27,7 +27,7 @@
27 plan skip_all => 'Cannot connect to sandbox master';27 plan skip_all => 'Cannot connect to sandbox master';
28}28}
29else {29else {
30 plan tests => 86;30 plan tests => 90;
31}31}
3232
33$sb->create_dbs($dbh, ['test']);33$sb->create_dbs($dbh, ['test']);
@@ -1174,21 +1174,27 @@
11741174
1175$sb->load_file('master', "t/lib/samples/char-chunking/world-city.sql", 'test');1175$sb->load_file('master', "t/lib/samples/char-chunking/world-city.sql", 'test');
1176$t = $p->parse( $du->get_create_table($dbh, $q, 'test', 'world_city') );1176$t = $p->parse( $du->get_create_table($dbh, $q, 'test', 'world_city') );
1177%params = $c->get_range_statistics(
1178 dbh => $dbh,
1179 db => 'test',
1180 tbl => 'world_city',
1181 chunk_col => 'name',
1182 tbl_struct => $t,
1183 chunk_size => '500',
1184);
1177@chunks = $c->calculate_chunks(1185@chunks = $c->calculate_chunks(
1186 dbh => $dbh,
1187 db => 'test',
1188 tbl => 'world_city',
1178 tbl_struct => $t,1189 tbl_struct => $t,
1179 chunk_col => 'name',1190 chunk_col => 'name',
1180 min => 'A Coruña (La Coruña)',
1181 max => '´s-Hertogenbosch',
1182 rows_in_range => 4079,
1183 chunk_size => 500,1191 chunk_size => 500,
1184 dbh => $dbh,1192 %params,
1185 db => 'test',
1186 tbl => 'world_city',
1187);1193);
1188ok(1194ok(
1189 @chunks >= 9,1195 @chunks >= 9,
1190 "At least 9 char chunks on test.world_city.name"1196 "At least 9 char chunks on test.world_city.name"
1191);1197) or print STDERR Dumper(\@chunks);
11921198
1193my $n_rows = count_rows("test.world_city", "name", @chunks);1199my $n_rows = count_rows("test.world_city", "name", @chunks);
1194is(1200is(
@@ -1231,6 +1237,64 @@
1231 );1237 );
1232};1238};
12331239
1240# ############################################################################
1241# Bug 821673: pt-table-checksum doesn't included --where in min max queries
1242# ############################################################################
1243$sb->load_file('master', "t/pt-table-checksum/samples/where01.sql");
1244$t = $p->parse( $du->get_create_table($dbh, $q, 'test', 'checksum_test') );
1245%params = $c->get_range_statistics(
1246 dbh => $dbh,
1247 db => 'test',
1248 tbl => 'checksum_test',
1249 chunk_col => 'id',
1250 tbl_struct => $t,
1251 where => "date = '2011-03-03'",
1252);
1253is(
1254 $params{min},
1255 11,
1256 'MIN int range stats with --where (bug 821673)'
1257);
1258is(
1259 $params{max},
1260 15,
1261 'MAX int range stats with --where (bug 821673)'
1262);
1263
1264# char chunking
1265$sb->load_file('master', "t/pt-table-checksum/samples/where02.sql");
1266$t = $p->parse( $du->get_create_table($dbh, $q, 'test', 'checksum_test') );
1267%params = $c->get_range_statistics(
1268 dbh => $dbh,
1269 db => 'test',
1270 tbl => 'checksum_test',
1271 chunk_col => 'id',
1272 tbl_struct => $t,
1273 where => "date = '2011-03-03'",
1274);
1275is(
1276 $params{min},
1277 'Apple',
1278 'MIN char range stats with --where (bug 821673)'
1279);
1280is(
1281 $params{max},
1282 'raspberry',
1283 'MAX char range stats with --where (bug 821673)'
1284);
1285
1286# It's difficult to construct a char chunk test where WHERE will matter.
1287#@chunks = $c->calculate_chunks(
1288# dbh => $dbh,
1289# db => 'test',
1290# tbl => 'checksum_test',
1291# tbl_struct => $t,
1292# chunk_col => 'id',
1293# chunk_size => 5,
1294# where => "date = '2011-03-03'",
1295# %params,
1296#);
1297
1234# #############################################################################1298# #############################################################################
1235# Done.1299# Done.
1236# #############################################################################1300# #############################################################################
12371301
=== modified file 't/pt-table-checksum/basics.t'
--- t/pt-table-checksum/basics.t 2011-07-12 22:56:55 +0000
+++ t/pt-table-checksum/basics.t 2011-08-27 18:33:24 +0000
@@ -24,7 +24,7 @@
24 plan skip_all => 'Cannot connect to sandbox master';24 plan skip_all => 'Cannot connect to sandbox master';
25}25}
26else {26else {
27 plan tests => 12;27 plan tests => 14;
28}28}
2929
30my ($output, $output2);30my ($output, $output2);
@@ -120,6 +120,41 @@
120 "--sleep doesn't sleep unless table is chunked"120 "--sleep doesn't sleep unless table is chunked"
121);121);
122122
123
124# ############################################################################
125# Bug 821673: pt-table-checksum doesn't included --where in min max queries
126# ############################################################################
127$sb->load_file('master', "t/pt-table-checksum/samples/where01.sql");
128
129ok(
130 no_diff(
131 sub { pt_table_checksum::main(@args,
132 qw(--no-zero-chunk --chunk-size 5), '--where', "date = '2011-03-03'");
133 },
134 "t/pt-table-checksum/samples/where01.out",
135 trf => "awk '{print \$1 \" \" \$2 \" \" \$3 \" \" \$6}'",
136 ),
137 "--where affects int range stats (bug 821673)"
138);
139
140# Test it again with a varchar primary key. The resulting 5 rows are:
141# | Apple | 2011-03-03 |
142# | lemon | 2011-03-03 |
143# | lime | 2011-03-03 |
144# | pineapple | 2011-03-03 |
145# | raspberry | 2011-03-03 |
146$sb->load_file('master', "t/pt-table-checksum/samples/where02.sql");
147ok(
148 no_diff(
149 sub { pt_table_checksum::main(@args,
150 qw(--no-zero-chunk --chunk-size 5), '--where', "date = '2011-03-03'");
151 },
152 "t/pt-table-checksum/samples/where02.out",
153 trf => "awk '{print \$1 \" \" \$2 \" \" \$3 \" \" \$6}'",
154 ),
155 "--where affects char range stats (bug 821673)"
156);
157
123# #############################################################################158# #############################################################################
124# Done.159# Done.
125# #############################################################################160# #############################################################################
126161
=== modified file 't/pt-table-checksum/chunk_index.t'
--- t/pt-table-checksum/chunk_index.t 2011-07-12 22:56:55 +0000
+++ t/pt-table-checksum/chunk_index.t 2011-08-27 18:33:24 +0000
@@ -127,12 +127,8 @@
127127
128is(128is(
129 $output,129 $output,
130"issue_519 t SELECT /*issue_519.t:1/5*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `i`, `y`, `t`, CONCAT(ISNULL(`t`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_519`.`t` FORCE INDEX (`y`) WHERE (`y` = 0) AND ((y > 2009))130"issue_519 t SELECT /*issue_519.t:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `i`, `y`, `t`, CONCAT(ISNULL(`t`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `issue_519`.`t` FORCE INDEX (`y`) WHERE (1=1) AND ((y > 2009))
131issue_519 t `y` = 0131issue_519 t 1=1
132issue_519 t `y` > 0 AND `y` < '2003'
133issue_519 t `y` >= '2003' AND `y` < '2006'
134issue_519 t `y` >= '2006' AND `y` < '2009'
135issue_519 t `y` >= '2009'
136",132",
137 "Auto-chosen --chunk-index for --where (issue 378)"133 "Auto-chosen --chunk-index for --where (issue 378)"
138);134);
139135
=== added file 't/pt-table-checksum/samples/where01.out'
--- t/pt-table-checksum/samples/where01.out 1970-01-01 00:00:00 +0000
+++ t/pt-table-checksum/samples/where01.out 2011-08-27 18:33:24 +0000
@@ -0,0 +1,3 @@
1DATABASE TABLE CHUNK COUNT
2test checksum_test 0 2
3test checksum_test 1 3
04
=== added file 't/pt-table-checksum/samples/where01.sql'
--- t/pt-table-checksum/samples/where01.sql 1970-01-01 00:00:00 +0000
+++ t/pt-table-checksum/samples/where01.sql 2011-08-27 18:33:24 +0000
@@ -0,0 +1,26 @@
1drop database if exists test;
2create database test;
3use test;
4
5CREATE TABLE `checksum_test` (
6 `id` int(11) NOT NULL DEFAULT '0',
7 `date` date DEFAULT NULL,
8 PRIMARY KEY (`id`)
9) ENGINE=InnoDB;
10
11INSERT INTO `checksum_test` VALUES
12 (1, '2011-03-01'),
13 (2, '2011-03-01'),
14 (3, '2011-03-01'),
15 (4, '2011-03-01'),
16 (5, '2011-03-01'),
17 (6, '2011-03-02'),
18 (7, '2011-03-02'),
19 (8, '2011-03-02'),
20 (9, '2011-03-02'),
21 (10, '2011-03-02'),
22 (11, '2011-03-03'),
23 (12, '2011-03-03'),
24 (13, '2011-03-03'),
25 (14, '2011-03-03'),
26 (15, '2011-03-03');
027
=== added file 't/pt-table-checksum/samples/where02.out'
--- t/pt-table-checksum/samples/where02.out 1970-01-01 00:00:00 +0000
+++ t/pt-table-checksum/samples/where02.out 2011-08-27 18:33:24 +0000
@@ -0,0 +1,4 @@
1DATABASE TABLE CHUNK COUNT
2test checksum_test 0 1
3test checksum_test 1 4
4test checksum_test 2 0
05
=== added file 't/pt-table-checksum/samples/where02.sql'
--- t/pt-table-checksum/samples/where02.sql 1970-01-01 00:00:00 +0000
+++ t/pt-table-checksum/samples/where02.sql 2011-08-27 18:33:24 +0000
@@ -0,0 +1,26 @@
1drop database if exists test;
2create database test;
3use test;
4
5CREATE TABLE `checksum_test` (
6 `id` varchar(255) NOT NULL,
7 `date` date DEFAULT NULL,
8 PRIMARY KEY (`id`)
9) ENGINE=InnoDB;
10
11INSERT INTO `checksum_test` VALUES
12 ('Apple', '2011-03-03'),
13 ('banana', '2011-03-01'),
14 ('orange', '2011-03-01'),
15 ('grape', '2011-03-01'),
16 ('kiwi', '2011-03-01'),
17 ('strawberry', '2011-03-02'),
18 ('peach', '2011-03-02'),
19 ('mango', '2011-03-02'),
20 ('tomato', '2011-03-02'),
21 ('nectarine', '2011-03-02'),
22 ('pear', '2011-03-01'),
23 ('lemon', '2011-03-03'),
24 ('lime', '2011-03-03'),
25 ('pineapple', '2011-03-03'),
26 ('raspberry', '2011-03-03');
027
=== modified file 't/pt-table-checksum/throttle.t'
--- t/pt-table-checksum/throttle.t 2011-08-23 15:11:00 +0000
+++ t/pt-table-checksum/throttle.t 2011-08-27 18:33:24 +0000
@@ -281,6 +281,8 @@
281# Done.281# Done.
282# #############################################################################282# #############################################################################
283diag(`$trunk/sandbox/stop-sandbox 12347 >/dev/null`);283diag(`$trunk/sandbox/stop-sandbox 12347 >/dev/null`);
284diag(`/tmp/12346/stop >/dev/null`); # Start/stop clears SHOW SLAVE HOSTS.
285diag(`/tmp/12346/start >/dev/null`);
284$sb->wipe_clean($master_dbh);286$sb->wipe_clean($master_dbh);
285diag(`$trunk/sandbox/test-env reset >/dev/null`);287diag(`$trunk/sandbox/test-env reset >/dev/null`);
286exit;288exit;

Subscribers

People subscribed via source and target branches

to all changes: