pt-table-checksum --chunk-index better use case insensitive comparison

[Aleksandr Kuzminsky]

An index that is specified with --chunk-index is ignored.

pt-table-checksum --no-check-binlog-format --ignore-tables mysql.inventory --chunk-index metric8f60732102034046720ab81022c12e7c6cbf_Idx --tables <db>.<table> <master ip>

Some relevant PTDEBUG output:

# NibbleIterator:6444 7459 User wants to use index metric8f60732102034046720ab81022c12e7c6cbf_Idx
# NibbleIterator:6446 7459 Cannot use user index because it does not exist
# NibbleIterator:6469 7459 Auto-selecting best index
# TableParser:4399 7459 Indexes sorted best-first: metric42294eae0e93c043fb083b1049e4fcbed9d7_idx, metric8a56a17b007640471f0856e099e06401589f_idx, metric8f60732102034046720ab81022c12e7c6cbf_idx, metric9214757d0b1cb04d7c0877b0e1905565056a_idx, metricc920303d064b5041920bc2c06fcb125163ef_idx, metricc97f4db70478c0465109e2c0f42a212bc587_idx, metriccb6b187c05cb1040670b1960da411de188b5_idx

As you can see pt-tc rejects index metric8f60732102034046720ab81022c12e7c6cbf_Idx but few lines below it is listed.

$ pt-table-checksum --version
pt-table-checksum 2.2.5

MySQL version on the master and a slave - 5.5.33-31.1-log.

12-09T09:13:08 Skipping chunk 1 of <db>.<table> because it is oversized. The current chunk size limit is 2000 rows (chunk size=1000 * chunk size limit=2.0), but MySQL estimates that there are 69172 rows in the chunk.
12-09T09:13:08 Error checksumming table <db>.<table>: Possible infinite loop detected! The lower boundary for chunk 1 is <1, 1> and the lower boundary for chunk 2 is also <1, 1>. This usually happens when using a non-unique single column index. The current chunk index for table <db>.<table> is metric42294eae0e93c043fb083b1049e4fcbed9d7_idx which is not unique and covers 1 column.

[Peiran Song (peiran-song)]

I have verified that the error was due to case sensitive comparison on the index name. When specify --chunk-index with index name that is of the same case as defined in the database, the tool does use it. Same behavior in 2.2.5 and latest 2.2.11. Since index name is not case sensitive on any platform, I think pt-table-checksum should use case-insensitive comparison to check index name.

Below is the detail of the test:
-------------------------------------------------
create table ptc_i_test
(col1 int,
col2 varchar(10),
col3 int,
key metric42294eae0e93c043fb083b1049e4fcbed9d7_idx (col1),
key metric8a56a17b007640471f0856e099e06401589f_idx (col3),
key metric8f60732102034046720ab81022c12e7c6cbf_idx (col2)
) engine = innodb;

PTDEBUG=1 ./pt-table-checksum --no-check-binlog-format --chunk-index metric8f60732102034046720ab81022c12e7c6cbf_idx --tables test.ptc_i_test --recursion-method dsn=D=percona,t=dsns h=127.0.0.1,P=13000,u=msandbox,p=msandbox >ptc.out 2>ptc.err

# NibbleIterator:6444 28172 User wants to use index metric8f60732102034046720ab81022c12e7c6cbf_idx
# NibbleIterator:6464 28172 Wanted index is a possible index
# NibbleIterator:6482 28172 No PRIMARY or unique indexes; will use index with highest cardinality
# NibbleIterator:6513 28172 SHOW INDEXES FROM `test`.`ptc_i_test` WHERE Key_name = 'metric8f60732102034046720ab81022c12e7c6cbf_idx'
# NibbleIterator:6523 28172 Index metric8f60732102034046720ab81022c12e7c6cbf_idx cardinality: 4
# NibbleIterator:6502 28172 Best index: metric8f60732102034046720ab81022c12e7c6cbf_idx

[Jericho Rivera (jericho-rivera)]

Version 2.2.19 still affected.

*************************** 1. row ***************************
       Table: x
Create Table: CREATE TABLE `x` (
  `id` int(11) NOT NULL,
  `s_id` varchar(10) NOT NULL,
  `s_date` date NOT NULL,
  `uuid` varchar(32) NOT NULL,
  PRIMARY KEY (`s_id`),
  UNIQUE KEY `uuid` (`uuid`),
  UNIQUE KEY `UPPERCASE_IDX` (`id`),
  KEY `idx_some_index` (`s_id`,`s_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

PTDEBUG=1 /data/perc/tmp/pt-online-schema-change --user=root --password=msandbox --host=127.0.01 --port=5522 --chunk-index='UPPERCASE_IDX' --no-check-alter --no-drop-old-table --no-drop-new-table --alter "drop primary key, add primary key(s_id), add index idx_some_index(s_id, s_date)" D=test,t=x --execute > debug.txt 2>&1

# NibbleIterator:5710 32714 One nibble: yes
# NibbleIterator:5741 32714 User wants to use index UPPERCASE_IDX
# NibbleIterator:5743 32714 Cannot use user index because it does not exist
# NibbleIterator:5769 32714 Auto-selecting best index
# TableParser:3308 32714 Indexes sorted best-first: PRIMARY, uppercase_idx, uuid
# NibbleIterator:5802 32714 Best index: PRIMARY

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1186