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

Bug #894140 reported by Rob Smith
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Medium
Daniel Nichter
2.0
Fix Released
Medium
Daniel Nichter
2.1
Fix Released
Medium
Daniel Nichter

Bug Description

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`);

Related branches

Rob Smith (kormoc)
tags: added: pt-duplicate-key-checker
tags: added: wrong-output
Changed in percona-toolkit:
status: New → Confirmed
Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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.

Revision history for this message
Rob Smith (kormoc) wrote :

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

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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`;

Revision history for this message
Olivier Doucet (odoucet) wrote :
Revision history for this message
Marc Castrovinci (marc-castrovinci) wrote :

I'm still seeing this problem

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

Revision history for this message
Daniel Nichter (daniel-nichter) wrote :

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

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-436

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

Other bug subscribers

Bug attachments