pt-duplicate-key-checker seems useless with MySQL 5.6
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:/
[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
*******
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-
# #######
# test.domains
# #######
# unique_key_domain (`domain`)
# domain (`domain`)
# PRIMARY (`id`)
# #######
# Summary of indexes
# #######
# Total Indexes 3
Related branches
- Daniel Nichter: Approve
- David Bennett (community): Needs Fixing
-
Diff: 285 lines (+89/-62)5 files modifiedbin/pt-duplicate-key-checker (+61/-51)
t/pt-duplicate-key-checker/basics.t (+15/-2)
t/pt-duplicate-key-checker/issue_331.t (+2/-2)
t/pt-duplicate-key-checker/samples/issue_331.txt (+3/-3)
t/pt-duplicate-key-checker/samples/simple_dupe_bug_1217013.txt (+8/-4)
Changed in percona-toolkit: | |
status: | Confirmed → In Progress |
importance: | Undecided → Medium |
assignee: | nobody → Frank Cizmich (frank-cizmich) |
tags: | added: pt-duplicate-key-checker |
Changed in percona-toolkit: | |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
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 key-checker --verbose --user=root --tables=test.ai5 ####### ####### ####### ####### ####### ####### ####### ####### ####### ## ####### ####### ####### ####### ####### ####### ####### ####### ####### ##
Bye
[openxs@centos ~]$ pt-duplicate-
# #######
# test.ai5
# #######
# PRIMARY (`id`)
# c1_2 (`c1`)
# c1 (`c1`)
# ####### ####### ####### ####### ####### ####### ####### ####### ####### ####### ## ####### ####### ####### ####### ####### ####### ####### ####### ####### ##
# Summary of indexes
# #######
# Total Indexes 3 key-checker --version key-checker 2.2.12
[openxs@centos ~]$ pt-duplicate-
pt-duplicate-
So, currently it seems just plain useless...