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

Bug #1201443 reported by Kamil Dziedzic
6
This bug affects 1 person
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-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

Related branches

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
Revision history for this message
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)

Revision history for this message
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
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.