1#!/usr/bin/env perl 2 3BEGIN { 4 die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n" 5 unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH}; 6 unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib"; 7}; 8 9use strict; 10use warnings FATAL => 'all'; 11use English qw(-no_match_vars); 12use Test::More; 13 14# Hostnames make testing less accurate. Tests need to see 15# that such-and-such happened on specific slave hosts, but 16# the sandbox servers are all on one host so all slaves have 17# the same hostname. 18$ENV{PERCONA_TOOLKIT_TEST_USE_DSN_NAMES} = 1; 19 20use Data::Dumper; 21use PerconaTest; 22use Sandbox; 23 24require "$trunk/bin/pt-table-checksum"; 25 26my $dp = new DSNParser(opts=>$dsn_opts); 27my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp); 28my $master_dbh = $sb->get_dbh_for('master'); 29my $slave1_dbh = $sb->get_dbh_for('slave1'); 30my $slave2_dbh = $sb->get_dbh_for('slave2'); 31 32if ( !$master_dbh ) { 33 plan skip_all => 'Cannot connect to sandbox master'; 34} 35elsif ( !$slave1_dbh ) { 36 plan skip_all => 'Cannot connect to sandbox slave1'; 37} 38elsif ( !@{$master_dbh->selectall_arrayref("show databases like 'sakila'")} ) { 39 plan skip_all => 'sakila database is not loaded'; 40} else { 41 plan tests => 40; 42} 43 44# The sandbox servers run with lock_wait_timeout=3 and it's not dynamic 45# so we need to specify --set-vars innodb_lock_wait_timeout=3 else the tool will die. 46my $master_dsn = 'h=127.1,P=12345,u=msandbox,p=msandbox'; 47my $slave2_dsn = 'h=127.1,P=12347,u=msandbox,p=msandbox'; 48my @args = ($master_dsn, qw(--set-vars innodb_lock_wait_timeout=3 --ignore-tables load_data)); 49my $row; 50my $output; 51my $exit_status; 52my $sample = "t/pt-table-checksum/samples/"; 53my $outfile = '/tmp/pt-table-checksum-results'; 54my $repl_db = 'percona'; 55 56sub reset_repl_db { 57 $master_dbh->do("drop database if exists $repl_db"); 58 $master_dbh->do("create database $repl_db"); 59 $master_dbh->do("use $repl_db"); 60} 61 62# ############################################################################ 63# Default checksum and results. The tool does not technically require any 64# options on well-configured systems (which the test env cannot be). With 65# nothing but defaults, it should create the repl table, checksum and check 66# all tables, dynamically adjust the chunk size, and throttle itself and based 67# on all slaves' lag. We don't explicitly test throttling here; that's done 68# in throttle.t. 69# ############################################################################ 70 71# 1 72ok( 73 no_diff( 74 sub { pt_table_checksum::main(@args) }, 75 "$sample/default-results-$sandbox_version.txt", 76 post_pipe => 'awk \'{print $2 " " $3 " " $4 " " $7 " " $9}\'', 77 ), 78 "Default checksum" 79); 80 81# On fast machines, the chunk size will probably be be auto-adjusted so 82# large that all tables will be done in a single chunk without an index. 83# Since this varies by default, there's no use checking the checksums 84# other than to ensure that there's at least one for each table. 85# 2 86$row = $master_dbh->selectrow_arrayref("select count(*) from percona.checksums"); 87my $max_chunks = $sandbox_version < '5.7' ? 60 : 100; 88 89ok( 90 $row->[0] > 25 && $row->[0] < $max_chunks, 91 'Between 25 and 60 chunks' 92) or diag($row->[0]); 93 94# ############################################################################ 95# Static chunk size (disable --chunk-time) 96# ############################################################################ 97# 3 98ok( 99 no_diff( 100 sub { pt_table_checksum::main(@args, qw(--chunk-time 0 --ignore-databases mysql)) }, 101 "$sample/static-chunk-size-results-$sandbox_version.txt", 102 post_pipe => 'awk \'{print $2 " " $3 " " $4 " " $6 " " $7 " " $9}\'', 103 ), 104 "Static chunk size (--chunk-time 0)" 105); 106 107$row = $master_dbh->selectrow_arrayref("select count(*) from percona.checksums"); 108 109my $max_rows = $sandbox_version >= '8.0' ? 102 : $sandbox_version < '5.7' ? 90 : 100; 110ok( 111 $row->[0] >= 75 && $row->[0] <= $max_rows, 112 'Between 75 and 90 chunks on master' 113) or diag($row->[0]); 114 115 116my $row2 = $slave1_dbh->selectrow_arrayref("select count(*) from percona.checksums"); 117is( 118 $row2->[0], 119 $row->[0], 120 '... same number of chunks on slave' 121) or diag($row->[0], ' ', $row2->[0]); 122 123 124# ############################################################################ 125# --[no]replicate-check and, implicitly, the tool's exit status. 126# ############################################################################ 127 128# Make one row on the slave differ. 129$row = $slave1_dbh->selectrow_arrayref("select city, last_update from sakila.city where city_id=1"); 130$slave1_dbh->do("update sakila.city set city='test' where city_id=1"); 131 132$exit_status = pt_table_checksum::main(@args, 133 qw(--quiet -t sakila.city --chunk-size 1)); 134 135is( 136 $exit_status, 137 16, # = TABLE_DIFF but nothing else; https://bugs.launchpad.net/percona-toolkit/+bug/944051 138 "--replicate-check on by default, detects diff" 139) or diag("exit status: $exit_status"); 140 141$exit_status = pt_table_checksum::main(@args, 142 qw(--quiet --quiet -t sakila.city --no-replicate-check)); 143 144is( 145 $exit_status, 146 0, 147 "--no-replicate-check, no diff detected" 148); 149 150# Restore the row on the slave, else other tests will fail. 151$slave1_dbh->do("update sakila.city set city='$row->[0]', last_update='$row->[1]' where city_id=1"); 152 153# ############################################################################# 154# --[no]empty-replicate-table 155# Issue 21: --empty-replicate-table doesn't empty if previous runs leave info 156# ############################################################################# 157 158$sb->wipe_clean($master_dbh); 159$sb->load_file('master', 't/pt-table-checksum/samples/issue_21.sql'); 160 161# Run once to populate the repl table. 162pt_table_checksum::main(@args, qw(--quiet --quiet -t test.issue_21), 163 qw(--chunk-time 0 --chunk-size 2)); 164 165# Insert two fake rows into the repl table. The first row tests that 166# --empty-replicate-table deletes all rows for each checksummed table, 167# and the second row tests that if a table isn't checksummed, then its 168# rows aren't deleted. 169$master_dbh->do("INSERT INTO percona.checksums VALUES ('test', 'issue_21', 999, 0.00, 'idx', '0', '0', '0', 0, '0', 0, NOW())"); 170$master_dbh->do("INSERT INTO percona.checksums VALUES ('test', 'other_tbl', 1, 0.00, 'idx', '0', '0', '0', 0, '0', 0, NOW())"); 171 172pt_table_checksum::main(@args, qw(--quiet --quiet -t test.issue_21), 173 qw(--chunk-time 0 --chunk-size 2)); 174 175$row = $master_dbh->selectall_arrayref("SELECT tbl, chunk FROM percona.checksums WHERE db='test' ORDER BY tbl, chunk"); 176is_deeply( 177 $row, 178 [ 179 [qw(issue_21 1)], 180 [qw(issue_21 2)], 181 [qw(issue_21 3)], 182 [qw(issue_21 4)], # lower oob 183 [qw(issue_21 5)], # upper oob 184 # fake row for chunk 999 is gone 185 [qw(other_tbl 1)], # this row is still here 186 ], 187 "--emptry-replicate-table on by default" 188) or print STDERR Dumper($row); 189 190# ############################################################################ 191# --[no]recheck 192# ############################################################################ 193 194$exit_status = pt_table_checksum::main(@args, 195 qw(--quiet --quiet --chunk-time 0 --chunk-size 100 -t sakila.city)); 196 197$slave1_dbh->do("update percona.checksums set this_crc='' where db='sakila' and tbl='city' and (chunk=1 or chunk=6)"); 198PerconaTest::wait_for_table($slave2_dbh, "percona.checksums", "db='sakila' and tbl='city' and (chunk=1 or chunk=6) and thic_crc=''"); 199 200# 9 201ok( 202 no_diff( 203 sub { pt_table_checksum::main(@args, qw(--replicate-check-only)) }, 204 "$sample/no-recheck.txt", 205 ), 206 "--no-recheck (just --replicate-check)" 207); 208 209# ############################################################################ 210# Detect infinite loop. 211# ############################################################################ 212$sb->load_file('master', "t/pt-table-checksum/samples/oversize-chunks.sql"); 213 214$output = output( 215 sub { pt_table_checksum::main(@args, qw(-t osc.t --chunk-size 10)) }, 216 stderr => 1, 217); 218 219# 10 220like( 221 $output, 222 qr/infinite loop detected/, 223 "Detects infinite loop" 224); 225 226# ############################################################################ 227# Oversize chunk. 228# ############################################################################ 229# 11 230ok( 231 no_diff( 232 sub { pt_table_checksum::main(@args, 233 qw(-t osc.t2 --chunk-size 8 --explain --explain)) }, 234 "$sample/oversize-chunks.txt", 235 ), 236 "Upper boundary same as next lower boundary", 237); 238 239$output = output( 240 sub { pt_table_checksum::main(@args, 241 qw(-t osc.t2 --chunk-time 0 --chunk-size 8 --chunk-size-limit 1)) }, 242 stderr => 1, 243); 244 245is( 246 PerconaTest::count_checksum_results($output, 'skipped'), 247 2, 248 "Skipped oversize chunks" 249); 250 251is( 252 PerconaTest::count_checksum_results($output, 'errors'), 253 0, 254 "Oversize chunks are not errors" 255); 256 257# SKIPPED should be accurate if the first skipped chunk # > 1. 258# https://bugs.launchpad.net/percona-toolkit/+bug/1011738 259$output = output( 260 sub { pt_table_checksum::main(@args, 261 qw(-t osc.t --chunk-size 6 --chunk-size-limit 1)) }, 262 stderr => 1, 263); 264 265like( 266 $output, 267 qr/Skipping chunk 2/i, 268 "Skipped chunk 2" 269); 270 271is( 272 PerconaTest::count_checksum_results($output, 'skipped'), 273 1, 274 "Skipped 1 chunk (bug 1011738)" 275) or diag($output); 276 277# ############################################################################ 278# Check slave table row est. if doing doing 1=1 on master table. 279# ############################################################################ 280$master_dbh->do('truncate table percona.checksums'); 281$sb->load_file('master', "t/pt-table-checksum/samples/3tbl-resume.sql"); 282 283$master_dbh->do('set sql_log_bin=0'); 284$master_dbh->do('truncate table test.t1'); 285$master_dbh->do('set sql_log_bin=1'); 286 287$output = output( 288 sub { 289 $exit_status = pt_table_checksum::main(@args, qw(-d test --chunk-size 2)) 290 }, 291 stderr => 1, 292); 293 294like( 295 $output, 296 qr/Skipping table test.t1/, 297 "Warns about skipping large slave table" 298); 299 300is_deeply( 301 $master_dbh->selectall_arrayref("select distinct tbl from percona.checksums where db='test'"), 302 [ ['t2'], ['t3'] ], 303 "Does not checksum large slave table on master" 304); 305 306is_deeply( 307 $slave1_dbh->selectall_arrayref("select distinct tbl from percona.checksums where db='test'"), 308 [ ['t2'], ['t3'] ], 309 "Does not checksum large slave table on slave" 310); 311 312is( 313 $exit_status, 314 64, # SKIP_TABLE 315 "Non-zero exit status" 316); 317 318is( 319 PerconaTest::count_checksum_results($output, 'skipped'), 320 0, 321 "0 skipped" 322); 323 324is( 325 PerconaTest::count_checksum_results($output, 'errors'), 326 0, 327 "0 errors" 328); 329 330is( 331 PerconaTest::count_checksum_results($output, 'rows'), 332 52, 333 "52 rows checksummed" 334); 335 336# ############################################################################# 337# pt-table-checksum chunk-size-limit of 0 does not disable chunk size limit 338# checking 339# https://bugs.launchpad.net/percona-toolkit/+bug/938660 340# ############################################################################# 341 342# Decided _not_ to do this; we want to always check slave table size when 343# single-chunking a table on the master. 344 345$output = output( 346 sub { 347 $exit_status = pt_table_checksum::main(@args, 348 qw(-d test --chunk-size 2 --chunk-size-limit 0)) 349 }, 350 stderr => 1, 351); 352 353like( 354 $output, 355 qr/Skipping table test.t1/, 356 "--chunk-size-limit=0 does not disable #-of-rows checks on slaves" 357); 358 359# ############################################################################# 360# Crash if no host in DSN. 361# https://bugs.launchpad.net/percona-toolkit/+bug/819450 362# http://code.google.com/p/maatkit/issues/detail?id=1332 363# ############################################################################# 364 365$output = output( 366 sub { $exit_status = pt_table_checksum::main( 367 qw(--user msandbox --pass msandbox), 368 qw(-S /tmp/12345/mysql_sandbox12345.sock --set-vars innodb_lock_wait_timeout=3 --run-time 8)) }, 369 stderr => 1, 370); 371 372# This test no longer works because of 373# https://bugs.launchpad.net/percona-toolkit/+bug/1087804 374# So comment out this test... 375#is( 376# $exit_status, 377# 0, 378# "No host in DSN, zero exit status" 379#) or diag($output); 380 381# ... and use this one instead: 382 383# Aaaaand this one also no longer works because of 384# https://bugs.launchpad.net/percona-toolkit/+bug/1042727 385# pt-table-checksum will keep trying to find a slave ... forever. 386# (notice the --runtime in the original command otherwise it loops forever) 387# So we comment out these other 2 tests 388 389#like( 390# $output, 391# qr/sakila.store/, 392# "No host in DSN, checksums happened" 393#) or diag($output); 394 395#is( 396# PerconaTest::count_checksum_results($output, 'errors'), 397# 0, 398# "No host in DSN, 0 errors" 399#) or diag($output); 400 401 402# and instead check if it waits for slaves 403 404# This test is no longer working 405# TODO: double check messages 406# like( 407# $output, 408# qr/replica.*stopped.*waiting/i, 409# "Warns when waiting for replicas." 410# ) or diag($output); 411# 412 413# Check if no slaves were found. Bug 1087804: 414# Notice we simply execute the command but on 12347, the slaveless slave. 415$output = output( 416 sub { $exit_status = pt_table_checksum::main( 417 qw(--user msandbox --pass msandbox), 418 ('--set-vars', 'innodb_lock_wait_timeout=3', '--run-time', '5', $slave2_dsn )) }, 419 stderr => 1, 420); 421 422like( 423 $output, 424 qr/no slaves were found/, 425 "Warns when no slave are found (bug 1087804)" 426) or diag($output); 427 428is( 429 $exit_status, 430 8, # https://bugs.launchpad.net/percona-toolkit/+bug/944051 431 "Exit status 8 when no slaves are found (bug 1087804)" 432) or diag($output); 433 434# ############################################################################# 435# Test --where. 436# ############################################################################# 437$sb->load_file('master', 't/pt-table-checksum/samples/600cities.sql'); 438$master_dbh->do("LOAD DATA INFILE '$trunk/t/pt-table-checksum/samples/600cities.data' INTO TABLE test.t"); 439 440$output = output( 441 sub { $exit_status = pt_table_checksum::main(@args, 442 qw(-t test.t --chunk-size 20 --explain --explain), 443 "--where", "id>=100 AND id<=200"); }, 444 stderr => 1, 445); 446 447like( 448 $output, 449 qr/^REPLACE INTO.+?id>=100 AND id<=200.+?checksum chunk/m, 450 "--where in checksum chunk query" 451); 452 453like( 454 $output, 455 qr/^REPLACE INTO.+?id>=100 AND id<=200.+?past lower chunk/m, 456 "--where in past lower chunk query" 457); 458 459like( 460 $output, 461 qr/^REPLACE INTO.+?id>=100 AND id<=200.+?past upper chunk/m, 462 "--where in past upper chunk query" 463); 464 465like( 466 $output, 467 qr/^SELECT.+?id>=100 AND id<=200.+?next chunk boundary/m, 468 "--where in next chunk boundary query" 469); 470 471like( 472 $output, 473 qr/^1\s+100\s+119/m, 474 "--where for first chunk" 475); 476 477like( 478 $output, 479 qr/^6\s+200\s+200/m, 480 "--where for last chunk" 481); 482 483like( 484 $output, 485 qr/^7\s+100$/m, 486 "--where for lower oob chunk" 487); 488 489like( 490 $output, 491 qr/^8\s+200\s+$/m, 492 "--where for upper oob chunk" 493); 494 495# ############################################################################# 496# Bug 932442: column with 2 spaces 497# ############################################################################# 498$sb->load_file('master', "t/pt-table-checksum/samples/2-space-col.sql"); 499 500$output = output( 501 sub { $exit_status = pt_table_checksum::main(@args, 502 qw(-t test.t --chunk-size 3)) }, 503 stderr => 1, 504); 505 506is( 507 $exit_status, 508 0, 509 "Bug 932442: 0 exit" 510); 511 512is( 513 PerconaTest::count_checksum_results($output, 'errors'), 514 0, 515 "Bug 932442: 0 errors" 516); 517 518# ############################################################################# 519# Bug 821675: can't parse column names containing periods 520# ############################################################################# 521$sb->load_file('master', "t/pt-table-checksum/samples/dot.sql"); 522 523ok( 524 no_diff( 525 sub { pt_table_checksum::main(@args, 526 qw(-t test.t --chunk-size 3 --explain --explain)) 527 }, 528 "t/pt-table-checksum/samples/dot.out", 529 ), 530 "Bug 821675 (dot): queries" 531); 532 533$output = output( 534 sub { $exit_status = pt_table_checksum::main(@args, 535 qw(-t test.t --chunk-size 3 --explain --explain)) }, 536 stderr => 1, 537); 538 539is( 540 $exit_status, 541 0, 542 "Bug 821675 (dot): 0 exit" 543); 544 545is( 546 PerconaTest::count_checksum_results($output, 'errors'), 547 0, 548 "Bug 821675 (dot): 0 errors" 549); 550 551# ############################################################################# 552# Bug 1019479: does not work with sql_mode ONLY_FULL_GROUP_BY 553# ############################################################################# 554 555# add a couple more modes to test that commas don't affect setting 556$master_dbh->do("SET sql_mode = 'NO_ZERO_DATE,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES'"); 557 558# force chunk-size because bug doesn't show up if table done in one chunk 559$exit_status = pt_table_checksum::main(@args, 560 qw(--quiet --quiet -t sakila.actor --chunk-size=50)); 561 562is( 563 $exit_status, 564 0, 565 "sql_mode ONLY_FULL_GROUP_BY is overidden" 566); 567 568DONE: 569# ############################################################################# 570# Done. 571# ############################################################################# 572$sb->wipe_clean($master_dbh); 573ok($sb->ok(), "Sandbox servers") or BAIL_OUT(__FILE__ . " broke the sandbox"); 574done_testing; 575