pt-duplicate-key-checker error when EXPLAIN key_len=0

Reported by Kamil Dziedzic on 2013-07-15
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit
Medium
Daniel Nichter

Bug Description

Hi,

I got some error messages when I ran pt-duplicate-key-checker against our database:
Error checking database_name.table_name: Use of uninitialized value $chosen_key in concatenation (.) or string at /usr/bin/pt-duplicate-key-checker line 5042.

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://dev.mysql.com/doc/refman/5.5/en/explain-output.html#jointype_range
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
*************************** 1. row ***************************
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
*************************** 1. row ***************************
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-key-checker you will see $chosen_key variable which at this point is undef. This variable is returned by get_key_size function and the problem is in condition on line 2221:
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-key-checker
# ########################################################################
# 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-key-checker line 5042.
# ########################################################################
# Summary of indexes
# ########################################################################

# Total Duplicate Indexes 1
# Total Indexes 1

Results after fix:
./pt-duplicate-key-checker
# ########################################################################
# 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
*************************** 1. row ***************************
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

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
Daniel Nichter (daniel-nichter) wrote :

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)

Daniel Nichter (daniel-nichter) wrote :

It's a MySQL bug or mystery why the first query in my previous comment returns key_len=0 but the second query returns key_len=4. If anything, the 2nd should (maybe) return zero because without "Using where" it's doing a full index scan, so it's not using any part of the parent_id index to match rows.

In any case, I fixed this in KeySize but checking for key=<key> && key_len=0. If this happen, get_key_size() calls itself again (only once) with a special arg that affects the EXPLAIN query in an attempt to get MySQL to actually use the index. It works in tests.

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
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers