pt-duplicate-key-checker seems useless with MySQL 5.6

Bug #1402730 reported by Valerii Kravchuk on 2014-12-15
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
Frank Cizmich

Bug Description

Old bug report, https://bugs.launchpad.net/percona-toolkit/+bug/1217013, is claimed to be fixed in version 2.2.6, but the test case from it still produces no hints about duplicate keys while working with Percona Server 5.6.21-71.0:

[openxs@centos ~]$ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `domains` (
  `id` bigint(20) NOT NULL,
  `domain` varchar(175) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`),
  UNIQUE KEY `unique_key_domain` (`domain`)
    -> `id` bigint(20) NOT NULL,
    -> `domain` varchar(175) COLLATE utf8_bin NOT NULL,
    -> PRIMARY KEY (`id`),
    -> UNIQUE KEY `domain` (`domain`),
    -> UNIQUE KEY `unique_key_domain` (`domain`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected, 1 warning (0.37 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1831
Message: Duplicate index 'unique_key_domain' defined on the table 'test.domains'. This is deprecated and will be disallowed in a future release.
1 row in set (0.00 sec)

mysql> exit
Bye
[openxs@centos ~]$ pt-duplicate-key-checker --verbose --user=root --tables=test.domains
# ########################################################################
# test.domains
# ########################################################################

# unique_key_domain (`domain`)
# domain (`domain`)
# PRIMARY (`id`)

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

# Total Indexes 3

Related branches

As you can see, server itself is able to detect duplicate keys, but not the tool. Moreover, even with simple single column duplicated indexes we get nothing useful from the tool:

[openxs@centos ~]$ mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.21-70.1-log Percona Server (GPL), Release 70.1, Revision 698

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table ai5(id int primary key, c1 int, c2 int, key (c1), key (c1));
Query OK, 0 rows affected, 1 warning (0.18 sec)

mysql> show warnings\G *************************** 1. row ***************************
  Level: Note
   Code: 1831
Message: Duplicate index 'c1_2' defined on the table 'test.ai5'. This is deprecated and will be disallowed in a future release.
1 row in set (0.01 sec)

mysql> show create table ai5\G
*************************** 1. row ***************************
       Table: ai5
Create Table: CREATE TABLE `ai5` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`),
  KEY `c1_2` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> exit
Bye
[openxs@centos ~]$ pt-duplicate-key-checker --verbose --user=root --tables=test.ai5
# ########################################################################
# test.ai5
# ########################################################################

# PRIMARY (`id`)
# c1_2 (`c1`)
# c1 (`c1`)

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

# Total Indexes 3
[openxs@centos ~]$ pt-duplicate-key-checker --version
pt-duplicate-key-checker 2.2.12

So, currently it seems just plain useless...

tags: added: i49100
Changed in percona-toolkit:
status: New → Confirmed
Changed in percona-toolkit:
status: Confirmed → In Progress
importance: Undecided → Medium
assignee: nobody → Frank Cizmich (frank-cizmich)
Frank Cizmich (frank-cizmich) wrote :

If you remove the --verbose option, it works.

Changed in percona-toolkit:
milestone: none → 2.2.14
Frank Cizmich (frank-cizmich) wrote :

Uploading a patch for 2.2.13

The issue was that using --verbose simply skipped duplicate key checks :-)

tags: added: pt-duplicate-key-checker
Changed in percona-toolkit:
status: In Progress → Fix Committed
Frank Cizmich (frank-cizmich) wrote :

Fixed according to patch.

Changed in percona-toolkit:
status: Fix Committed → Fix Released

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

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

Other bug subscribers