pt-duplicate-key-checker sometimes recreates a key it shouldn't

[Rob Smith]

v1.0.1

# ########################################################################
# database.table
# ########################################################################

# Key row_id ends with a prefix of the clustered index
# Key definitions:
# UNIQUE KEY `row_id` (`row_id`),
# PRIMARY KEY (`row_id`),
# Column types:
# `row_id` bigint(20) not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `database`.`table` DROP INDEX `row_id`, ADD INDEX `row_id` (`row_id`);

[Daniel Nichter (daniel-nichter)]

I cannot reproduce this in 1.0 or 2.1:

# row_id is a duplicate of PRIMARY
# Key definitions:
# UNIQUE KEY `row_id` (`row_id`)
# PRIMARY KEY (`row_id`),
# Column types:
# `row_id` bigint(20) not null auto_increment
# To remove this duplicate index, execute:
ALTER TABLE `sakila`.`t` DROP INDEX `row_id`;

Rob, please send PTDEBUG output, the command line you're using, and SHOW CREATE TABLE for the table.

[Rob Smith (kormoc)]

Here's the info you requested:

Command line:
pt-duplicate-key-checker h=localhost,u=root --database=test

Create Statement:
CREATE TABLE `bug_894140` (
  `row_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `player_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`row_id`),
  UNIQUE KEY `row_id` (`row_id`),
  UNIQUE KEY `player_id` (`player_id`),
  KEY `player_id_2` (`player_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Normal output:
# ########################################################################
# test.bug_894140
# ########################################################################

# player_id_2 is a duplicate of player_id
# Key definitions:
# KEY `player_id_2` (`player_id`)
# UNIQUE KEY `player_id` (`player_id`),
# Column types:
# `player_id` bigint(20) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`bug_894140` DROP INDEX `player_id_2`;

# Key row_id ends with a prefix of the clustered index
# Key definitions:
# UNIQUE KEY `row_id` (`row_id`),
# PRIMARY KEY (`row_id`),
# Column types:
# `row_id` bigint(20) not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `test`.`bug_894140` DROP INDEX `row_id`, ADD INDEX `row_id` (`row_id`);

# ########################################################################
# Summary of indexes
# ########################################################################

# Size Duplicate Indexes 17
# Total Duplicate Indexes 2
# Total Indexes 4

PTDEBUG output attached

[Daniel Nichter (daniel-nichter)]

Thanks Rob. I can reproduce this now and I'll fix it...

[Daniel Nichter (daniel-nichter)]

Fixed in the linked branch; will be in the next release:

# row_id is a duplicate of PRIMARY
# Key definitions:
# UNIQUE KEY `row_id` (`row_id`),
# PRIMARY KEY (`row_id`),
# Column types:
# `row_id` bigint(20) not null auto_increment
# To remove this duplicate index, execute:
ALTER TABLE `test`.`bug_894140` DROP INDEX `row_id`;

# player_id_2 is a duplicate of player_id
# Key definitions:
# KEY `player_id_2` (`player_id`)
# UNIQUE KEY `player_id` (`player_id`),
# Column types:
# `player_id` bigint(20) default null
# To remove this duplicate index, execute:
ALTER TABLE `test`.`bug_894140` DROP INDEX `player_id_2`;

[Olivier Doucet (odoucet)]

I also have this bug.
I tested latest file (http://bazaar.launchpad.net/~percona-toolkit-dev/percona-toolkit/2.1/download/daniel%40percona.com-20120524172520-d8ra1wqf49oavle2/ptduplicatekeychecke-20110624220137-or26tn4expb9ul2a-5/pt-duplicate-key-checker)
and I can confirm that this bug is fixed in this version.

[Marc Castrovinci (marc-castrovinci)]

I'm still seeing this problem

pt-duplicate-key-checker --version
pt-duplicate-key-checker 2.1.2

[Daniel Nichter (daniel-nichter)]

Marc, can you please create a new bug and provide any info necessary to reproduce the bug? Thanks.

[Shahriyar Rzayev (rzayev-sehriyar)]

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