1MK-DUPLICATE-KEY-CHECKEURs(e1r)Contributed Perl DocumentMaKt-iDoUnPLICATE-KEY-CHECKER(1)
2
3
4

NAME

6       mk-duplicate-key-checker - Find duplicate indexes and foreign keys on
7       MySQL tables.
8

SYNOPSIS

10       Usage: mk-duplicate-key-checker [OPTION...] [DSN]
11
12       mk-duplicate-key-checker examines MySQL tables for duplicate or
13       redundant indexes and foreign keys.  Connection options are read from
14       MySQL option files.
15
16          mk-duplicate-key-checker --host host1
17

RISKS

19       The following section is included to inform users about the potential
20       risks, whether known or unknown, of using this tool.  The two main
21       categories of risks are those created by the nature of the tool (e.g.
22       read-only tools vs. read-write tools) and those created by bugs.
23
24       mk-duplicate-key-checker is a read-only tool that executes SHOW CREATE
25       TABLE and related queries to inspect table structures, and thus is very
26       low-risk.
27
28       At the time of this release, there is an unconfirmed bug that causes
29       the tool to crash.
30
31       The authoritative source for updated information is always the online
32       issue tracking system.  Issues that affect this tool will be marked as
33       such.  You can see a list of such issues at the following URL:
34       <http://www.maatkit.org/bugs/mk-duplicate-key-checker>.
35
36       See also "BUGS" for more information on filing bugs and getting help.
37

DESCRIPTION

39       This program examines the output of SHOW CREATE TABLE on MySQL tables,
40       and if it finds indexes that cover the same columns as another index in
41       the same order, or cover an exact leftmost prefix of another index, it
42       prints out the suspicious indexes.  By default, indexes must be of the
43       same type, so a BTREE index is not a duplicate of a FULLTEXT index,
44       even if they have the same columns.  You can override this.
45
46       It also looks for duplicate foreign keys.  A duplicate foreign key
47       covers the same columns as another in the same table, and references
48       the same parent table.
49

OPTIONS

51       This tool accepts additional command-line arguments.  Refer to the
52       "SYNOPSIS" and usage information for details.
53
54       --all-structs
55           Compare indexes with different structs (BTREE, HASH, etc).
56
57           By default this is disabled, because a BTREE index that covers the
58           same columns as a FULLTEXT index is not really a duplicate, for
59           example.
60
61       --ask-pass
62           Prompt for a password when connecting to MySQL.
63
64       --charset
65           short form: -A; type: string
66
67           Default character set.  If the value is utf8, sets Perl's binmode
68           on STDOUT to utf8, passes the mysql_enable_utf8 option to
69           DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL.  Any
70           other value sets binmode on STDOUT without the utf8 layer, and runs
71           SET NAMES after connecting to MySQL.
72
73       --[no]clustered
74           default: yes
75
76           PK columns appended to secondary key is duplicate.
77
78           Detects when a suffix of a secondary key is a leftmost prefix of
79           the primary key, and treats it as a duplicate key.  Only detects
80           this condition on storage engines whose primary keys are clustered
81           (currently InnoDB and solidDB).
82
83           Clustered storage engines append the primary key columns to the
84           leaf nodes of all secondary keys anyway, so you might consider it
85           redundant to have them appear in the internal nodes as well.  Of
86           course, you may also want them in the internal nodes, because just
87           having them at the leaf nodes won't help for some queries.  It does
88           help for covering index queries, however.
89
90           Here's an example of a key that is considered redundant with this
91           option:
92
93             PRIMARY KEY  (`a`)
94             KEY `b` (`b`,`a`)
95
96           The use of such indexes is rather subtle.  For example, suppose you
97           have the following query:
98
99             SELECT ... WHERE b=1 ORDER BY a;
100
101           This query will do a filesort if we remove the index on "b,a".  But
102           if we shorten the index on "b,a" to just "b" and also remove the
103           ORDER BY, the query should return the same results.
104
105           The tool suggests shortening duplicate clustered keys by dropping
106           the key and re-adding it without the primary key prefix.  The
107           shortened clustered key may still duplicate another key, but the
108           tool cannot currently detect when this happens without being ran a
109           second time to re-check the newly shortened clustered keys.
110           Therefore, if you shorten any duplicate clustered keys, you should
111           run the tool again.
112
113       --config
114           type: Array
115
116           Read this comma-separated list of config files; if specified, this
117           must be the first option on the command line.
118
119       --databases
120           short form: -d; type: hash
121
122           Check only this comma-separated list of databases.
123
124       --defaults-file
125           short form: -F; type: string
126
127           Only read mysql options from the given file.  You must give an
128           absolute pathname.
129
130       --engines
131           short form: -e; type: hash
132
133           Check only tables whose storage engine is in this comma-separated
134           list.
135
136       --help
137           Show help and exit.
138
139       --host
140           short form: -h; type: string
141
142           Connect to host.
143
144       --ignore-databases
145           type: Hash
146
147           Ignore this comma-separated list of databases.
148
149       --ignore-engines
150           type: Hash
151
152           Ignore this comma-separated list of storage engines.
153
154       --ignore-order
155           Ignore index order so KEY(a,b) duplicates KEY(b,a).
156
157       --ignore-tables
158           type: Hash
159
160           Ignore this comma-separated list of tables.  Table names may be
161           qualified with the database name.
162
163       --key-types
164           type: string; default: fk
165
166           Check for duplicate f=foreign keys, k=keys or fk=both.
167
168       --password
169           short form: -p; type: string
170
171           Password to use when connecting.
172
173       --pid
174           type: string
175
176           Create the given PID file.  The file contains the process ID of the
177           script.  The PID file is removed when the script exits.  Before
178           starting, the script checks if the PID file already exists.  If it
179           does not, then the script creates and writes its own PID to it.  If
180           it does, then the script checks the following: if the file contains
181           a PID and a process is running with that PID, then the script dies;
182           or, if there is no process running with that PID, then the script
183           overwrites the file with its own PID and starts; else, if the file
184           contains no PID, then the script dies.
185
186       --port
187           short form: -P; type: int
188
189           Port number to use for connection.
190
191       --set-vars
192           type: string; default: wait_timeout=10000
193
194           Set these MySQL variables.  Immediately after connecting to MySQL,
195           this string will be appended to SET and executed.
196
197       --socket
198           short form: -S; type: string
199
200           Socket file to use for connection.
201
202       --[no]sql
203           default: yes
204
205           Print DROP KEY statement for each duplicate key.  By default an
206           ALTER TABLE DROP KEY statement is printed below each duplicate key
207           so that, if you want to remove the duplicate key, you can copy-
208           paste the statement into MySQL.
209
210           To disable printing these statements, specify --nosql.
211
212       --[no]summary
213           default: yes
214
215           Print summary of indexes at end of output.
216
217       --tables
218           short form: -t; type: hash
219
220           Check only this comma-separated list of tables.
221
222           Table names may be qualified with the database name.
223
224       --user
225           short form: -u; type: string
226
227           User for login if not current user.
228
229       --verbose
230           short form: -v
231
232           Output all keys and/or foreign keys found, not just redundant ones.
233
234       --version
235           Show version and exit.
236

DSN OPTIONS

238       These DSN options are used to create a DSN.  Each option is given like
239       "option=value".  The options are case-sensitive, so P and p are not the
240       same option.  There cannot be whitespace before or after the "=" and if
241       the value contains whitespace it must be quoted.  DSN options are
242       comma-separated.  See the maatkit manpage for full details.
243
244       ·   A
245
246           dsn: charset; copy: yes
247
248           Default character set.
249
250       ·   D
251
252           dsn: database; copy: yes
253
254           Default database.
255
256       ·   F
257
258           dsn: mysql_read_default_file; copy: yes
259
260           Only read default options from the given file
261
262       ·   h
263
264           dsn: host; copy: yes
265
266           Connect to host.
267
268       ·   p
269
270           dsn: password; copy: yes
271
272           Password to use when connecting.
273
274       ·   P
275
276           dsn: port; copy: yes
277
278           Port number to use for connection.
279
280       ·   S
281
282           dsn: mysql_socket; copy: yes
283
284           Socket file to use for connection.
285
286       ·   u
287
288           dsn: user; copy: yes
289
290           User for login if not current user.
291

DOWNLOADING

293       You can download Maatkit from Google Code at
294       <http://code.google.com/p/maatkit/>, or you can get any of the tools
295       easily with a command like the following:
296
297          wget http://www.maatkit.org/get/toolname
298          or
299          wget http://www.maatkit.org/trunk/toolname
300
301       Where "toolname" can be replaced with the name (or fragment of a name)
302       of any of the Maatkit tools.  Once downloaded, they're ready to run; no
303       installation is needed.  The first URL gets the latest released version
304       of the tool, and the second gets the latest trunk code from Subversion.
305

ENVIRONMENT

307       The environment variable "MKDEBUG" enables verbose debugging output in
308       all of the Maatkit tools:
309
310          MKDEBUG=1 mk-....
311

SYSTEM REQUIREMENTS

313       You need the following Perl modules: DBI and DBD::mysql.
314

BUGS

316       For a list of known bugs see
317       <http://www.maatkit.org/bugs/mk-duplicate-key-checker>.
318
319       Please use Google Code Issues and Groups to report bugs or request
320       support: <http://code.google.com/p/maatkit/>.  You can also join
321       #maatkit on Freenode to discuss Maatkit.
322
323       Please include the complete command-line used to reproduce the problem
324       you are seeing, the version of all MySQL servers involved, the complete
325       output of the tool when run with "--version", and if possible,
326       debugging output produced by running with the "MKDEBUG=1" environment
327       variable.
328

COPYRIGHT, LICENSE AND WARRANTY

330       This program is copyright 2007-2011 Baron Schwartz.  Feedback and
331       improvements are welcome.
332
333       THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
334       WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
335       MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
336
337       This program is free software; you can redistribute it and/or modify it
338       under the terms of the GNU General Public License as published by the
339       Free Software Foundation, version 2; OR the Perl Artistic License.  On
340       UNIX and similar systems, you can issue `man perlgpl' or `man
341       perlartistic' to read these licenses.
342
343       You should have received a copy of the GNU General Public License along
344       with this program; if not, write to the Free Software Foundation, Inc.,
345       59 Temple Place, Suite 330, Boston, MA  02111-1307  USA.
346

AUTHOR

348       Baron Schwartz, Daniel Nichter
349

ABOUT MAATKIT

351       This tool is part of Maatkit, a toolkit for power users of MySQL.
352       Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
353       primary code contributors.  Both are employed by Percona.  Financial
354       support for Maatkit development is primarily provided by Percona and
355       its clients.
356

VERSION

358       This manual page documents Ver 1.2.15 Distrib 7540 $Revision: 7477 $.
359
360
361
362perl v5.30.1                      2020-01-29       MK-DUPLICATE-KEY-CHECKER(1)
Impressum