1MK-DUPLICATE-KEY-CHECKEURs(e1r)Contributed Perl DocumentMaKt-iDoUnPLICATE-KEY-CHECKER(1)
2
6 mk-duplicate-key-checker - Find duplicate indexes and foreign keys on
7 MySQL tables.
8
10 mk-duplicate-key-checker --host host1
11
13 The following section is included to inform users about the potential
14 risks, whether known or unknown, of using this tool. The two main
15 categories of risks are those created by the nature of the tool (e.g.
16 read-only tools vs. read-write tools) and those created by bugs.
17 mk-duplicate-key-checker is a read-only tool that executes SHOW CREATE
19 TABLE and related queries to inspect table structures, and thus is very
20 low-risk.
21 At the time of this release, there is an unconfirmed bug that causes
23 the tool to crash.
24 The authoritative source for updated information is always the online
26 issue tracking system. Issues that affect this tool will be marked as
27 such. You can see a list of such issues at the following URL:
28 http://www.maatkit.org/bugs/mk-duplicate-key-checker
29 <http://www.maatkit.org/bugs/mk-duplicate-key-checker>.
30 See also "BUGS" for more information on filing bugs and getting help.
32
34 This program examines the output of SHOW CREATE TABLE on MySQL tables,
35 and if it finds indexes that cover the same columns as another index in
36 the same order, or cover an exact leftmost prefix of another index, it
37 prints out the suspicious indexes. By default, indexes must be of the
38 same type, so a BTREE index is not a duplicate of a FULLTEXT index,
39 even if they have the same colums. You can override this.
40 It also looks for duplicate foreign keys. A duplicate foreign key
42 covers the same columns as another in the same table, and references
43 the same parent table.
44
46 --all-structs
47 Compare indexes with different structs (BTREE, HASH, etc).
48 By default this is disabled, because a BTREE index that covers the
50 same columns as a FULLTEXT index is not really a duplicate, for
51 example.
52 --ask-pass
54 Prompt for a password when connecting to MySQL.
55 --charset
57 short form: -A; type: string
58 Default character set. If the value is utf8, sets Perl's binmode
60 on STDOUT to utf8, passes the mysql_enable_utf8 option to
61 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
62 other value sets binmode on STDOUT without the utf8 layer, and runs
63 SET NAMES after connecting to MySQL.
64 --[no]clustered
66 default: yes
67 PK columns appended to secondary key is duplicate.
69 Detects when a suffix of a secondary key is a leftmost prefix of
71 the primary key, and treats it as a duplicate key. Only detects
72 this condition on storage engines whose primary keys are clustered
73 (currently InnoDB and solidDB).
74 Clustered storage engines append the primary key columns to the
76 leaf nodes of all secondary keys anyway, so you might consider it
77 redundant to have them appear in the internal nodes as well. Of
78 course, you may also want them in the internal nodes, because just
79 having them at the leaf nodes won't help for some queries. It does
80 help for covering index queries, however.
81 Here's an example of a key that is considered redundant with this
83 option:
84 PRIMARY KEY (`a`)
86 KEY `b` (`b`,`a`)
87 The use of such indexes is rather subtle. For example, suppose you
89 have the following query:
90 SELECT ... WHERE b=1 ORDER BY a;
92 This query will do a filesort if we remove the index on "b,a". But
94 if we shorten the index on "b,a" to just "b" and also remove the
95 ORDER BY, the query should return the same results.
96 The tool suggests shortening duplicate clustered keys by dropping
98 the key and re-adding it without the primary key prefix. The
99 shortened clustered key may still duplicate another key, but the
100 tool cannot currently detect when this happens without being ran a
101 second time to re-check the newly shortened clustered keys.
102 Therefore, if you shorten any duplicate clusterted keys, you should
103 run the tool again.
104 --config
106 type: Array
107 Read this comma-separated list of config files; if specified, this
109 must be the first option on the command line.
110 --databases
112 short form: -d; type: hash
113 Check only this comma-separated list of databases.
115 --defaults-file
117 short form: -F; type: string
118 Only read mysql options from the given file. You must give an
120 absolute pathname.
121 --engines
123 short form: -e; type: hash
124 Check only tables whose storage engine is in this comma-separated
126 list.
127 --help
129 Show help and exit.
130 --host
132 short form: -h; type: string
133 Connect to host.
135 --ignore-databases
137 type: Hash
138 Ignore this comma-separated list of databases.
140 --ignore-engines
142 type: Hash
143 Ignore this comma-separated list of storage engines.
145 --ignore-order
147 Ignore index order so KEY(a,b) duplicates KEY(b,a).
148 --ignore-tables
150 type: Hash
151 Ignore this comma-separated list of tables. Table names may be
153 qualified with the database name.
154 --key-types
156 type: string; default: fk
157 Check for duplicate f=foreign keys, k=keys or fk=both.
159 --password
161 short form: -p; type: string
162 Password to use when connecting.
164 --pid
166 type: string
167 Create the given PID file. The file contains the process ID of the
169 script. The PID file is removed when the script exits. Before
170 starting, the script checks if the PID file already exists. If it
171 does not, then the script creates and writes its own PID to it. If
172 it does, then the script checks the following: if the file contains
173 a PID and a process is running with that PID, then the script dies;
174 or, if there is no process running with that PID, then the script
175 overwrites the file with its own PID and starts; else, if the file
176 contains no PID, then the script dies.
177 --port
179 short form: -P; type: int
180 Port number to use for connection.
182 --set-vars
184 type: string; default: wait_timeout=10000
185 Set these MySQL variables. Immediately after connecting to MySQL,
187 this string will be appended to SET and executed.
188 --socket
190 short form: -S; type: string
191 Socket file to use for connection.
193 --[no]sql
195 default: yes
196 Print DROP KEY statement for each duplicate key. By default an
198 ALTER TABLE DROP KEY statement is printed below each duplicate key
199 so that, if you want to remove the duplicate key, you can copy-
200 paste the statement into MySQL.
201 To disable printing these statements, specify --nosql.
203 --[no]summary
205 default: yes
206 Print summary of indexes at end of output.
208 --tables
210 short form: -t; type: hash
211 Check only this comma-separated list of tables.
213 Table names may be qualified with the database name.
215 --user
217 short form: -u; type: string
218 User for login if not current user.
220 --verbose
222 short form: -v
223 Output all keys and/or foreign keys found, not just redundant ones.
225 --version
227 Show version and exit.
228
230 These DSN options are used to create a DSN. Each option is given like
231 "option=value". The options are case-sensitive, so P and p are not the
232 same option. There cannot be whitespace before or after the "=" and if
233 the value contains whitespace it must be quoted. DSN options are
234 comma-separated. See the maatkit manpage for full details.
235 · A
237 dsn: charset; copy: yes
239 Default character set.
241 · D
243 dsn: database; copy: yes
245 Default database.
247 · F
249 dsn: mysql_read_default_file; copy: yes
251 Only read default options from the given file
253 · h
255 dsn: host; copy: yes
257 Connect to host.
259 · p
261 dsn: password; copy: yes
263 Password to use when connecting.
265 · P
267 dsn: port; copy: yes
269 Port number to use for connection.
271 · S
273 dsn: mysql_socket; copy: yes
275 Socket file to use for connection.
277 · u
279 dsn: user; copy: yes
281 User for login if not current user.
283
285 You can download Maatkit from Google Code at
286 <http://code.google.com/p/maatkit/>, or you can get any of the tools
287 easily with a command like the following:
288 wget http://www.maatkit.org/get/toolname
290 or
291 wget http://www.maatkit.org/trunk/toolname
292 Where "toolname" can be replaced with the name (or fragment of a name)
294 of any of the Maatkit tools. Once downloaded, they're ready to run; no
295 installation is needed. The first URL gets the latest released version
296 of the tool, and the second gets the latest trunk code from Subversion.
297
299 The environment variable "MKDEBUG" enables verbose debugging output in
300 all of the Maatkit tools:
301 MKDEBUG=1 mk-....
303
305 You need the following Perl modules: DBI and DBD::mysql.
306
308 For list of known bugs see
309 http://www.maatkit.org/bugs/mk-duplicate-key-checker
310 <http://www.maatkit.org/bugs/mk-duplicate-key-checker>.
311 Please use Google Code Issues and Groups to report bugs or request
313 support: <http://code.google.com/p/maatkit/>. You can also join
314 #maatkit on Freenode to discuss Maatkit.
315 Please include the complete command-line used to reproduce the problem
317 you are seeing, the version of all MySQL servers involved, the complete
318 output of the tool when run with "--version", and if possible,
319 debugging output produced by running with the "MKDEBUG=1" environment
320 variable.
321
323 This program is copyright 2007-2010 Baron Schwartz. Feedback and
324 improvements are welcome.
325 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
327 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
328 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
329 This program is free software; you can redistribute it and/or modify it
331 under the terms of the GNU General Public License as published by the
332 Free Software Foundation, version 2; OR the Perl Artistic License. On
333 UNIX and similar systems, you can issue `man perlgpl' or `man
334 perlartistic' to read these licenses.
335 You should have received a copy of the GNU General Public License along
337 with this program; if not, write to the Free Software Foundation, Inc.,
338 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
339
341 Baron Schwartz, Daniel Nichter
342
344 This tool is part of Maatkit, a toolkit for power users of MySQL.
345 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
346 primary code contributors. Both are employed by Percona. Financial
347 support for Maatkit development is primarily provided by Percona and
348 its clients.
349
351 This manual page documents Ver 1.2.13 Distrib 6839 $Revision: 6831 $.
352perl v5.12.1 2010-08-01 MK-DUPLICATE-KEY-CHECKER(1)