Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
3.0.11
-
None
-
bm-support01 in ~/sandboxes/rsandbox_5_7_22
○ → ./m -e "SELECT @@version, @@version_comment"
----------------------------------------+@@version @@version_comment -----------
-----------------------------+
| 5.7.22-log | MySQL Community Server (GPL) |
----------------------------------------+bm-support01 in ~/sandboxes/rsandbox_5_7_22
○ → ./m -e "SHOW VARIABLES LIKE '%char%'"
-----------------------------------------------------------------------------+Variable_name Value -------------------------
----------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |character_sets_dir /home/sandbox_packages/mysql/5.7.22/share/charsets/ -------------------------
----------------------------------------------------+bm-support01 in ~/sandboxes/rsandbox_5_7_22
○ → perl --versionThis is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi...(snip...)
bm-support01 in ~/sandboxes/rsandbox_5_7_22
○ → pt-table-checksum --version
pt-table-checksum 3.0.11bm-support01 in ~/sandboxes/rsandbox_5_7_22
○ → perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, " dbd versions\n";'
4.023 dbd versionsbm-support01 in ~/sandboxes/rsandbox_5_7_22 ○ → ./m -e "SELECT @@version, @@version_comment" ----------- -----------------------------+ @@version @@version_comment ----------- -----------------------------+ | 5.7.22-log | MySQL Community Server (GPL) | ----------- -----------------------------+ bm-support01 in ~/sandboxes/rsandbox_5_7_22 ○ → ./m -e "SHOW VARIABLES LIKE '%char%'" ------------------------- ----------------------------------------------------+ Variable_name Value ------------------------- ----------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | character_sets_dir /home/sandbox_packages/mysql/5.7.22/share/charsets/ ------------------------- ----------------------------------------------------+ bm-support01 in ~/sandboxes/rsandbox_5_7_22 ○ → perl --version This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi...(snip...) bm-support01 in ~/sandboxes/rsandbox_5_7_22 ○ → pt-table-checksum --version pt-table-checksum 3.0.11 bm-support01 in ~/sandboxes/rsandbox_5_7_22 ○ → perl -MDBD::mysql -e 'print $DBD::mysql::VERSION, " dbd versions\n";' 4.023 dbd versions
Description
Create a table we will checksum like this -
CREATE TABLE junk.pt_test_100 (id1 binary(16), id2 binary(16), PRIMARY KEY(id1, id2))
Now insert tons of data on it so that pt-table-checksum does not process it in one chunk. Inserted 100000 rows of random data.
#!/bin/bash
function s16() {
dd if=/dev/urandom bs=1 count=16 2>/dev/null|tr -d '[:space:]'|tr -d "'";
}
for i in {1..100000}; do { # will run for a while
mysql -ve "INSERT INTO junk.pt_test_100 VALUES ('$(s16)', '$(s16)')";
} done;
Insert row with invalid utf8 char
INSERT INTO junk.pt_test_100 (id1, id2) VALUES(unhex('F96DD7'), unhex('F96DD7'))
Make checksums table support binary strings
ALTER TABLE percona.checksums MODIFY upper_boundary BLOB, MODIFY lower_boundary BLOB;
Run the checksum (timeout must be adjusted according to performance of testbed such that only part of the table gets checksummed):
timeout 1.5 pt-table-checksum --defaults-file=master/my.sandbox.cnf --recursion-method=dsn=D=percona,t=dsns --no-check-binlog-format --chunk-time=0.002 --chunk-size-limit=1 --empty-replicate-table
Once checksum stops, insert an entry in percona.checksums table to make it resume from bad entry
SELECT MAX(chunk) INTO @chunk FROM percona.checksums WHERE tbl='pt_test_100'; INSERT INTO percona.checksums (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_crc, this_cnt, master_crc, master_cnt) VALUES ('junk', 'pt_test_100', @chunk, 'PRIMARY', unhex('F96DD72CF96DD7'), unhex('F96DD72CF96DD7'), 'crc', 1, 'crc', 1)
Then restart checksum with --resume
PTDEBUG=1 pt-table-checksum --defaults-file=master/my.sandbox.cnf --recursion-method=dsn=D=percona,t=dsns --no-check-binlog-format --chunk-time=0.002 --chunk-size-limit=1 --resume > pt.dbg 2>&1
