2
########################
3
pt-duplicate-key-checker
4
########################
14
pt-duplicate-key-checker - Find duplicate indexes and foreign keys on MySQL tables.
22
Usage: pt-duplicate-key-checker [OPTION...] [DSN]
24
pt-duplicate-key-checker examines MySQL tables for duplicate or redundant
25
indexes and foreign keys. Connection options are read from MySQL option files.
30
pt-duplicate-key-checker --host host1
39
The following section is included to inform users about the potential risks,
40
whether known or unknown, of using this tool. The two main categories of risks
41
are those created by the nature of the tool (e.g. read-only tools vs. read-write
42
tools) and those created by bugs.
44
pt-duplicate-key-checker is a read-only tool that executes SHOW CREATE TABLE and
45
related queries to inspect table structures, and thus is very low-risk.
47
At the time of this release, there is an unconfirmed bug that causes the tool
50
The authoritative source for updated information is always the online issue
51
tracking system. Issues that affect this tool will be marked as such. You can
52
see a list of such issues at the following URL:
53
`http://www.percona.com/bugs/pt-duplicate-key-checker <http://www.percona.com/bugs/pt-duplicate-key-checker>`_.
55
See also "BUGS" for more information on filing bugs and getting help.
63
This program examines the output of SHOW CREATE TABLE on MySQL tables, and if
64
it finds indexes that cover the same columns as another index in the same
65
order, or cover an exact leftmost prefix of another index, it prints out
66
the suspicious indexes. By default, indexes must be of the same type, so a
67
BTREE index is not a duplicate of a FULLTEXT index, even if they have the same
68
columns. You can override this.
70
It also looks for duplicate foreign keys. A duplicate foreign key covers the
71
same columns as another in the same table, and references the same parent
80
This tool accepts additional command-line arguments. Refer to the
81
"SYNOPSIS" and usage information for details.
86
Compare indexes with different structs (BTREE, HASH, etc).
88
By default this is disabled, because a BTREE index that covers the same columns
89
as a FULLTEXT index is not really a duplicate, for example.
95
Prompt for a password when connecting to MySQL.
101
short form: -A; type: string
103
Default character set. If the value is utf8, sets Perl's binmode on
104
STDOUT to utf8, passes the mysql_enable_utf8 option to DBD::mysql, and runs SET
105
NAMES UTF8 after connecting to MySQL. Any other value sets binmode on STDOUT
106
without the utf8 layer, and runs SET NAMES after connecting to MySQL.
114
PK columns appended to secondary key is duplicate.
116
Detects when a suffix of a secondary key is a leftmost prefix of the primary
117
key, and treats it as a duplicate key. Only detects this condition on storage
118
engines whose primary keys are clustered (currently InnoDB and solidDB).
120
Clustered storage engines append the primary key columns to the leaf nodes of
121
all secondary keys anyway, so you might consider it redundant to have them
122
appear in the internal nodes as well. Of course, you may also want them in the
123
internal nodes, because just having them at the leaf nodes won't help for some
124
queries. It does help for covering index queries, however.
126
Here's an example of a key that is considered redundant with this option:
135
The use of such indexes is rather subtle. For example, suppose you have the
141
SELECT ... WHERE b=1 ORDER BY a;
144
This query will do a filesort if we remove the index on \ ``b,a``\ . But if we
145
shorten the index on \ ``b,a``\ to just \ ``b``\ and also remove the ORDER BY, the query
146
should return the same results.
148
The tool suggests shortening duplicate clustered keys by dropping the key
149
and re-adding it without the primary key prefix. The shortened clustered
150
key may still duplicate another key, but the tool cannot currently detect
151
when this happens without being ran a second time to re-check the newly
152
shortened clustered keys. Therefore, if you shorten any duplicate clustered
153
keys, you should run the tool again.
161
Read this comma-separated list of config files; if specified, this must be the
162
first option on the command line.
168
short form: -d; type: hash
170
Check only this comma-separated list of databases.
176
short form: -F; type: string
178
Only read mysql options from the given file. You must give an absolute pathname.
184
short form: -e; type: hash
186
Check only tables whose storage engine is in this comma-separated list.
198
short form: -h; type: string
208
Ignore this comma-separated list of databases.
216
Ignore this comma-separated list of storage engines.
222
Ignore index order so KEY(a,b) duplicates KEY(b,a).
230
Ignore this comma-separated list of tables. Table names may be qualified with
237
type: string; default: fk
239
Check for duplicate f=foreign keys, k=keys or fk=both.
245
short form: -p; type: string
247
Password to use when connecting.
255
Create the given PID file. The file contains the process ID of the script.
256
The PID file is removed when the script exits. Before starting, the script
257
checks if the PID file already exists. If it does not, then the script creates
258
and writes its own PID to it. If it does, then the script checks the following:
259
if the file contains a PID and a process is running with that PID, then
260
the script dies; or, if there is no process running with that PID, then the
261
script overwrites the file with its own PID and starts; else, if the file
262
contains no PID, then the script dies.
268
short form: -P; type: int
270
Port number to use for connection.
276
type: string; default: wait_timeout=10000
278
Set these MySQL variables. Immediately after connecting to MySQL, this string
279
will be appended to SET and executed.
285
short form: -S; type: string
287
Socket file to use for connection.
295
Print DROP KEY statement for each duplicate key. By default an ALTER TABLE
296
DROP KEY statement is printed below each duplicate key so that, if you want to
297
remove the duplicate key, you can copy-paste the statement into MySQL.
299
To disable printing these statements, specify --nosql.
307
Print summary of indexes at end of output.
313
short form: -t; type: hash
315
Check only this comma-separated list of tables.
317
Table names may be qualified with the database name.
323
short form: -u; type: string
325
User for login if not current user.
333
Output all keys and/or foreign keys found, not just redundant ones.
339
Show version and exit.
349
These DSN options are used to create a DSN. Each option is given like
350
\ ``option=value``\ . The options are case-sensitive, so P and p are not the
351
same option. There cannot be whitespace before or after the \ ``=``\ and
352
if the value contains whitespace it must be quoted. DSN options are
353
comma-separated. See the percona-toolkit manpage for full details.
358
dsn: charset; copy: yes
360
Default character set.
366
dsn: database; copy: yes
374
dsn: mysql_read_default_file; copy: yes
376
Only read default options from the given file
390
dsn: password; copy: yes
392
Password to use when connecting.
400
Port number to use for connection.
406
dsn: mysql_socket; copy: yes
408
Socket file to use for connection.
416
User for login if not current user.
426
Visit `http://www.percona.com/software/ <http://www.percona.com/software/>`_ to download the latest release of
427
Percona Toolkit. Or, to get the latest release from the command line:
432
wget percona.com/latest/percona-toolkit/PKG
435
Replace \ ``PKG``\ with \ ``tar``\ , \ ``rpm``\ , or \ ``deb``\ to download the package in that
436
format. You can also get individual tools from the latest release:
441
wget percona.com/latest/percona-toolkit/TOOL
444
Replace \ ``TOOL``\ with the name of any tool.
452
The environment variable \ ``PTDEBUG``\ enables verbose debugging output to STDERR.
453
To enable debugging and capture all output to a file, run the tool like:
458
PTDEBUG=1 pt-duplicate-key-checker ... > FILE 2>&1
461
Be careful: debugging output is voluminous and can generate several megabytes
470
You need Perl, DBI, DBD::mysql, and some core packages that ought to be
471
installed in any reasonably new version of Perl.
479
For a list of known bugs, see `http://www.percona.com/bugs/pt-duplicate-key-checker <http://www.percona.com/bugs/pt-duplicate-key-checker>`_.
481
Please report bugs at `https://bugs.launchpad.net/percona-toolkit <https://bugs.launchpad.net/percona-toolkit>`_.
482
Include the following information in your bug report:
485
\* Complete command-line used to run the tool
493
\* MySQL version of all servers involved
497
\* Output from the tool including STDERR
501
\* Input files (log/dump/config files, etc.)
505
If possible, include debugging output by running the tool with \ ``PTDEBUG``\ ;
514
Baron Schwartz and Daniel Nichter
517
*********************
518
ABOUT PERCONA TOOLKIT
519
*********************
522
This tool is part of Percona Toolkit, a collection of advanced command-line
523
tools developed by Percona for MySQL support and consulting. Percona Toolkit
524
was forked from two projects in June, 2011: Maatkit and Aspersa. Those
525
projects were created by Baron Schwartz and developed primarily by him and
526
Daniel Nichter, both of whom are employed by Percona. Visit
527
`http://www.percona.com/software/ <http://www.percona.com/software/>`_ for more software developed by Percona.
530
********************************
531
COPYRIGHT, LICENSE, AND WARRANTY
532
********************************
535
This program is copyright 2007-2011 Baron Schwartz, 2011 Percona Inc.
536
Feedback and improvements are welcome.
538
THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
539
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
540
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
542
This program is free software; you can redistribute it and/or modify it under
543
the terms of the GNU General Public License as published by the Free Software
544
Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
545
systems, you can issue \`man perlgpl' or \`man perlartistic' to read these
548
You should have received a copy of the GNU General Public License along with
549
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
550
Place, Suite 330, Boston, MA 02111-1307 USA.
558
Percona Toolkit v1.0.0 released 2011-08-01
added
removed
docs/user/pt-duplicate-key-checker.rst
