pt-duplicate-key-checker wants to drop the unique key and does not offer equivalent replacement

[Aleksandar Ivanisevic]

my table:

CREATE TABLE `cat_cki_equipment_dcs_codes` (
  `equipment_code` varchar(8) CHARACTER SET utf8 NOT NULL,
  `carrier` varchar(3) CHARACTER SET utf8 NOT NULL,
  `host` varchar(255) CHARACTER SET utf8 NOT NULL,
  `code` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`host`,`code`),
  UNIQUE KEY `uq_equpment_dcs_codes` (`carrier`,`host`,`code`),
  KEY `fk_equipment_dcs_codes_equipment` (`equipment_code`),
  CONSTRAINT `cat_cki_equipment_dcs_codes_ibfk_1` FOREIGN KEY (`host`) REFERENCES `cat_cki_dcs_hosts` (`name`),
  CONSTRAINT `cat_cki_equipment_dcs_codes_ibfk_2` FOREIGN KEY (`equipment_code`) REFERENCES `cat_equipment` (`equip_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

pt-duplicate-key-checker suggests

# Key uq_equpment_dcs_codes ends with a prefix of the clustered index
# Key definitions:
# UNIQUE KEY `uq_equpment_dcs_codes` (`carrier`,`host`,`code`),
# PRIMARY KEY (`host`,`code`),
# Column types:
# `carrier` varchar(3) character set utf8 not null
# `host` varchar(255) character set utf8 not null
# `code` varchar(255) character set utf8 not null
# To shorten this duplicate clustered index, execute:
ALTER TABLE `edweedptp`.`cat_cki_equipment_dcs_codes` DROP INDEX `uq_equpment_dcs_codes`, ADD INDEX `uq_equpment_dcs_codes` (`carrier`);

surely this can't be right as the uniqueness of the rows by (carrier,host,code) is no longer enforced.

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Verified as described.

You don't need foreign keys for this test, following table is enough:

CREATE TABLE `cat_cki_equipment_dcs_codes` (
  `equipment_code` varchar(8) CHARACTER SET utf8 NOT NULL,
  `carrier` varchar(3) CHARACTER SET utf8 NOT NULL,
  `host` varchar(255) CHARACTER SET utf8 NOT NULL,
  `code` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`host`,`code`),
  UNIQUE KEY `uq_equpment_dcs_codes` (`carrier`,`host`,`code`),
  KEY `fk_equipment_dcs_codes_equipment` (`equipment_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

$ pt-duplicate-key-checker --databases=test -v h=127.0.0.1,P=3371,u=root
# ########################################################################
# test.cat_cki_equipment_dcs_codes
# ########################################################################

# PRIMARY (`host`,`code`)
# fk_equipment_dcs_codes_equipment (`equipment_code`)
# uq_equpment_dcs_codes (`carrier`,`host`,`code`)

# Key uq_equpment_dcs_codes ends with a prefix of the clustered index
# Key definitions:
# UNIQUE KEY `uq_equpment_dcs_codes` (`carrier`,`host`,`code`),
# PRIMARY KEY (`host`,`code`),
# Column types:
# `carrier` varchar(3) character set utf8 not null
# `host` varchar(255) character set utf8 not null
# `code` varchar(255) character set utf8 not null
# To shorten this duplicate clustered index, execute:
ALTER TABLE `test`.`cat_cki_equipment_dcs_codes` DROP INDEX `uq_equpment_dcs_codes`, ADD INDEX `uq_equpment_dcs_codes` (`carrier`);

# ########################################################################
# test.node1
# ########################################################################

# PRIMARY (`id`)

# ########################################################################
# test.node2
# ########################################################################

# PRIMARY (`id`)

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

# Size Duplicate Indexes 1545
# Total Duplicate Indexes 1
# Total Indexes 5

$ pt-duplicate-key-checker --version
pt-duplicate-key-checker 3.0.2

[Shahriyar Rzayev (rzayev-sehriyar)]

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