pt-duplicate-key-checker error when EXPLAIN key_len=0
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
Medium
|
Daniel Nichter |
Bug Description
Hi,
I got some error messages when I ran pt-duplicate-
Error checking database_
After some investigation I found out where the problem is.
id: 1
select_type: SIMPLE
table: table_name
type: range
possible_keys: some_id
key: some_id
key_len: 0
ref: NULL
rows: 221683
Extra: Using where; Using index
Explain shows that it will use key some_id but instead of using join type "type: index" it uses "type: range" and key_len equals 0.
Following MySql documentation for "range" join type:
"The key_len contains the longest key part that was used."
http://
However I still have no idea why it chooses "range" join type instead of "index", and why "the longest key part that was used" equals to 0.
I was trying to reproduce problem with clean tables but I failed.
CREATE DATABASE `test`;
USE `test`;
CREATE TABLE parent (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT NOT NULL AUTO_INCREMENT,
parent_id INT NOT NULL,
KEY id (id),
KEY parent_id (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
) ENGINE=INNODB;
EXPLAIN SELECT parent_id FROM `test`.`child` WHERE parent_id=1 OR parent_id<>1 \G
*******
id: 1
select_type: SIMPLE
table: child
type: index
possible_keys: parent_id
key: parent_id
key_len: 4
ref: NULL
rows: 1
Extra: Using where; Using index
However, later I've started to populate tables with test data:
INSERT INTO `parent` (`id`) VALUES(1);
DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 50000 DO
INSERT INTO child (parent_id) VALUES (1);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
call prepare_data();
and from time to time I've got:
EXPLAIN SELECT parent_id FROM `test`.`child` WHERE parent_id=1 OR parent_id<>1 \G
*******
id: 1
select_type: SIMPLE
table: child
type: range
possible_keys: parent_id
key: parent_id
key_len: 0
ref: NULL
rows: 7119
Extra: Using where; Using index
The more rows I've inserted then the more often mysql optimizer chooses to use "type: range" instead of "type: index".
While I still don't get why sometimes it uses "type: range" and sometimes "type: index", and also I still don't get why for "type: range" the length of used key is 0 ("key_len: 0"), then I know now such situation may happen. So going back to error message - on line 5042 in pt-duplicate-
if ( $key_len && $rows )
Which equaly treats null values and 0 for $key_len - which I think is a bug. The easiest way to fix this is to change this line to:
if ( defined $key_len && $rows )
However I think there really should be checked only if $chosen_key is defined. $key_len can be null only if $key is null and $rows is never null. I've made merge request with fix for this.
Results before fix:
./pt-duplicate-
# #######
# test.child
# #######
# FOREIGN KEY child_ibfk_2 (`parent_id`) REFERENCES `test`.`parent` (`id`) is a duplicate of FOREIGN KEY child_ibfk_1 (`parent_id`) REFERENCES `test`.`parent` (`id`)
# Key definitions:
# CONSTRAINT `child_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
# CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
# Column types:
# `parent_id` int(11) not null
# To remove this duplicate foreign key, execute:
ALTER TABLE `test`.`child` DROP FOREIGN KEY `child_ibfk_2`;
Error checking test.child: Use of uninitialized value $chosen_key in concatenation (.) or string at ./pt-duplicate-
# #######
# Summary of indexes
# #######
# Total Duplicate Indexes 1
# Total Indexes 1
Results after fix:
./pt-duplicate-
# #######
# test.child
# #######
# FOREIGN KEY child_ibfk_2 (`parent_id`) REFERENCES `test`.`parent` (`id`) is a duplicate of FOREIGN KEY child_ibfk_1 (`parent_id`) REFERENCES `test`.`parent` (`id`)
# Key definitions:
# CONSTRAINT `child_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
# CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
# Column types:
# `parent_id` int(11) not null
# To remove this duplicate foreign key, execute:
ALTER TABLE `test`.`child` DROP FOREIGN KEY `child_ibfk_2`;
# MySQL uses the parent_id index for this foreign key constraint
# #######
# Summary of indexes
# #######
# Total Duplicate Indexes 1
# Total Indexes 5
Also I wonder why EXPLAIN on line 2188 is created like this:
EXPLAIN SELECT parent_id FROM `test`.`child` WHERE parent_id=1 OR parent_id<>1 \G
Instead of simply:
EXPLAIN SELECT parent_id FROM `test`.`child` WHERE parent_id=1 \G
?
This probably would also fix this issue as mysql doesn't choose "type: range" in such case. However I've didn't changed that because this might be done intentionally.
EXPLAIN SELECT parent_id FROM `test`.`child` WHERE parent_id=1 \G
*******
id: 1
select_type: SIMPLE
table: child
type: ref
possible_keys: parent_id
key: parent_id
key_len: 4
ref: const
rows: 1
Extra: Using index
Related branches
- Daniel Nichter: Disapprove
-
Diff: 21 lines (+2/-2)1 file modifiedbin/pt-duplicate-key-checker (+2/-2)
- Daniel Nichter: Approve
-
Diff: 1776 lines (+873/-519)13 files modifiedbin/pt-duplicate-key-checker (+571/-490)
lib/DuplicateKeyFinder.pm (+26/-12)
lib/KeySize.pm (+17/-1)
t/lib/DuplicateKeyFinder.t (+61/-2)
t/lib/KeySize.t (+102/-4)
t/lib/samples/dupekeys/prefix_bug_1214114.sql (+7/-0)
t/lib/samples/dupekeys/simple_dupe_bug_1217013.sql (+7/-0)
t/pt-duplicate-key-checker/basics.t (+15/-4)
t/pt-duplicate-key-checker/clustered_keys.t (+15/-2)
t/pt-duplicate-key-checker/samples/bug-894140.txt (+1/-0)
t/pt-duplicate-key-checker/samples/fk_chosen_index_bug_1201443.sql (+21/-0)
t/pt-duplicate-key-checker/samples/simple_dupe_bug_1217013.txt (+21/-0)
t/pt-duplicate-key-checker/standard_options.t (+9/-4)
Changed in percona-toolkit: | |
status: | New → Confirmed |
Changed in percona-toolkit: | |
importance: | Undecided → Medium |
assignee: | nobody → Daniel Nichter (daniel-nichter) |
Changed in percona-toolkit: | |
milestone: | none → 2.2.6 |
Changed in percona-toolkit: | |
status: | Confirmed → In Progress |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
summary: |
- pt-duplicate-key-checker raises error when explain shows key_len = 0 + pt-duplicate-key-checker error when EXPLAIN key_len=0 |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
mysql> EXPLAIN SELECT parent_id FROM `fk_chosen_ index_bug_ 1201443` .`child` FORCE INDEX (parent_id) WHERE parent_id=1 OR parent_id<>1\G ******* ******* ****** 1. row ******* ******* ******* ******
*******
id: 1
select_type: SIMPLE
table: child
type: range
possible_keys: parent_id
key: parent_id
key_len: 0
ref: NULL
rows: 5233
Extra: Using where; Using index
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT parent_id FROM `fk_chosen_ index_bug_ 1201443` .`child` FORCE INDEX (parent_id) WHERE parent_id=1\G ******* ******* ****** 1. row ******* ******* ******* ******
*******
id: 1
select_type: SIMPLE
table: child
type: ref
possible_keys: parent_id
key: parent_id
key_len: 4
ref: const
rows: 5233
Extra: Using index
1 row in set (0.00 sec)