Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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

Description

**Reported in Launchpad by Aleksandar Ivanisevic last update 07-04-2017 18:01:45

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

  1. Key uq_equpment_dcs_codes ends with a prefix of the clustered index

  2. Key definitions:

  3. UNIQUE KEY `uq_equpment_dcs_codes` (`carrier`,`host`,`code`),

  4. PRIMARY KEY (`host`,`code`),

  5. Column types:

  6. `carrier` varchar(3) character set utf8 not null

  7. `host` varchar(255) character set utf8 not null

  8. `code` varchar(255) character set utf8 not null

  9. 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.

Environment

None

Smart Checklist

Activity

Sveta Smirnova April 7, 2024 at 10:32 AM

See also https://forums.percona.com/t/pt-duplicate-key-checker/29380 Slightly different case but reason is the same.

lpjirasync January 24, 2018 at 9:31 PM

**Comment from Launchpad by: Sveta Smirnova on: 06-04-2017 19:24:11

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

  1. ########################################################################

  2. test.cat_cki_equipment_dcs_codes

  3. ########################################################################

  1. PRIMARY (`host`,`code`)

  2. fk_equipment_dcs_codes_equipment (`equipment_code`)

  3. uq_equpment_dcs_codes (`carrier`,`host`,`code`)

  1. Key uq_equpment_dcs_codes ends with a prefix of the clustered index

  2. Key definitions:

  3. UNIQUE KEY `uq_equpment_dcs_codes` (`carrier`,`host`,`code`),

  4. PRIMARY KEY (`host`,`code`),

  5. 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

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

  1. ########################################################################

  2. test.node1

  3. ########################################################################

  1. PRIMARY (`id`)

  1. ########################################################################

  2. test.node2

  3. ########################################################################

  1. PRIMARY (`id`)

  1. ########################################################################

  2. Summary of indexes

  3. ########################################################################

  1. Size Duplicate Indexes 1545

  2. Total Duplicate Indexes 1

  3. Total Indexes 5

$ pt-duplicate-key-checker --version

pt-duplicate-key-checker 3.0.2

Details

Assignee

Reporter

Priority

Fix versions

Labels

Smart Checklist

Created January 24, 2018 at 9:31 PM
Updated April 7, 2024 at 10:32 AM

Flag notifications