.\" Automatically generated by Pod::Man v1.37, Pod::Parser v1.32
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sh \" Subsection heading
.br
.if t .Sp
.ne 5
.PP
\fB\\$1\fR
.PP
..
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  | will give a
.\" real vertical bar.  \*(C+ will give a nicer C++.  Capital omega is used to
.\" do unbreakable dashes and therefore won't be available.  \*(C` and \*(C'
.\" expand to `' in nroff, nothing in troff, for use with C<>.
.tr \(*W-|\(bv\*(Tr
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
'br\}
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.if \nF \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    nr % 0
.    rr F
.\}
.\"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.hy 0
.if n .na
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "MYSQL-DEADLOCK-LOGGER 1"
.TH MYSQL-DEADLOCK-LOGGER 1 "2007-10-15" "perl v5.8.8" "User Contributed Perl Documentation"
.SH "NAME"
mysql\-deadlock\-logger \- Extract and log MySQL deadlock information.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
The following examples will print deadlocks, store deadlocks in a database
table, and daemonize and check once every 30 seconds for 4 hours,
respectively:
.PP
.Vb 3
\& mysql-deadlock-logger --print
\& mysql-deadlock-logger --source u=user,p=pass,h=server --dest D=test,t=deadlocks
\& mysql-deadlock-logger --dest D=test,t=deadlocks --daemonize -m 4h -i 30s
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
mysql-deadlock-logger extracts deadlock data from a MySQL server (currently only
InnoDB deadlock information is available).  You can print it to standard output
or save it in a database table.  By default it does neither.
.SH "OPTIONS"
.IX Header "OPTIONS"
Some options are negatable with \-\-no.
.IP "\-\-askpass" 4
.IX Item "--askpass"
Prompt for password for connections.
.IP "\-\-collapse" 4
.IX Item "--collapse"
Makes mysql-deadlock-logger collapse all whitespace in queries to a single
space.  This might make it easier to inspect on the command line or in a query.
By default, whitespace is collapsed when printing with \*(L"\-\-print\*(R", but not
modified when storing to \*(L"\-\-dest\*(R".  (That is, the default is different for
each action).
.IP "\-\-columns" 4
.IX Item "--columns"
Makes mysql-deadlock-logger only output and/or save certain columns.  See
\&\*(L"\s-1OUTPUT\s0\*(R" for more details.
.IP "\-\-daemonize" 4
.IX Item "--daemonize"
Fork to the background and detach from the shell.  This probably doesn't work
on Microsoft Windows.
.IP "\-\-dest" 4
.IX Item "--dest"
Specifies a server, database and table in which to store deadlock information,
in the same format as \*(L"\-\-source\*(R".  Missing values are filled in with the same
values as \*(L"\-\-source\*(R", so you can usually omit most parts of this argument if
you're storing deadlocks on the same server on which they happen.
.Sp
By default, whitespace in the query column is left intact; use \*(L"\-\-collapse\*(R"
if you want whitespace collapsed.
.Sp
The following table is suggested if you want to store all the information
mysql-deadlock-logger can extract about deadlocks:
.Sp
.Vb 19
\& CREATE TABLE deadlocks (
\&   server char(20) NOT NULL,
\&   ts datetime NOT NULL,
\&   thread int unsigned NOT NULL,
\&   txn_id bigint unsigned NOT NULL,
\&   txn_time smallint unsigned NOT NULL,
\&   user char(16) NOT NULL,
\&   hostname char(20) NOT NULL,
\&   ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL
\&   db char(64) NOT NULL,
\&   tbl char(64) NOT NULL,
\&   idx char(64) NOT NULL,
\&   lock_type char(16) NOT NULL,
\&   lock_mode char(1) NOT NULL,
\&   wait_hold char(1) NOT NULL,
\&   victim tinyint unsigned NOT NULL,
\&   query text NOT NULL,
\&   PRIMARY KEY  (server,ts,thread)
\& ) ENGINE=InnoDB
.Ve
.Sp
If you use \*(L"\-\-columns\*(R", you can omit whichever columns you don't want to
store.
.IP "\-\-help" 4
.IX Item "--help"
Displays a help message.
.IP "\-\-interval" 4
.IX Item "--interval"
How frequently MySQL Deadlock Logger should check for deadlocks.  The argument
must have a suffix of s, m, h, or d, indicating seconds, minutes, hours, or
days.
.IP "\-\-numip" 4
.IX Item "--numip"
Makes mysql-deadlock-logger express \s-1IP\s0 addresses as integers.
.IP "\-\-print" 4
.IX Item "--print"
Makes mysql-deadlock-logger print results on standard output.  See \*(L"\s-1OUTPUT\s0\*(R"
for more.  By default, enables \*(L"\-\-collapse\*(R" unless you explicitly disable it.
.IP "\-\-source" 4
.IX Item "--source"
Specifies a connection to a server to check for deadlocks.  This argument is
specially formatted as a key=value,key=value string.  Keys are a single letter:
.Sp
.Vb 10
\&   KEY MEANING
\&   === =======
\&   h   Connect to host
\&   P   Port number to use for connection
\&   S   Socket file to use for connection
\&   u   User for login if not current user
\&   p   Password to use when connecting
\&   F   Only read default options from the given file
\&   D   Database to connect to
\&   t   Table in which to store deadlock information
.Ve
.Sp
The 't' part only applies to \*(L"\-\-dest\*(R".  All parts are optional;
mysql-deadlock-logger will try to read MySQL's option files to determine how
to connect.  If you specify a value without an '=' character,
mysql-deadlock-logger will interpret this as a hostname.
.IP "\-\-tab" 4
.IX Item "--tab"
Makes mysql-deadlock-logger output tab-separated columns instead of
whitespace-aligning them.  See \*(L"\s-1OUTPUT\s0\*(R" for more.
.IP "\-\-time" 4
.IX Item "--time"
Causes MySQL Deadlock Logger to stop after the specified time has elapsed.
The argument must have a suffix of s, m, h, or d, indicating seconds, minutes,
hours, or days.
.IP "\-\-version" 4
.IX Item "--version"
Output version information and exit.
.SH "INNODB CAVEATS AND DETAILS"
.IX Header "INNODB CAVEATS AND DETAILS"
InnoDB's output is hard to parse and sometimes there's no way to do it right.
.PP
Sometimes not all information (for example, username or \s-1IP\s0 address) is included
in the deadlock information.  In this case there's nothing for the script to put
in those columns.  It may also be the case that the deadlock output is so long
(because there were a lot of locks) that the whole thing is truncated.
.PP
Though there are usually two transactions involved in a deadlock, there are more
locks than that; at a minimum, one more lock than transactions is necessary to
create a cycle in the waits-for graph.  mysql-deadlock-logger prints the
transactions (always two in the InnoDB output, even when there are more
transactions in the waits-for graph than that) and fills in locks.  It prefers
waited-for over held when choosing lock information to output, but you can
figure out the rest with a moment's thought.  If you see one wait-for and one
held lock, you're looking at the same lock, so of course you'd prefer to see
both wait-for locks and get more information.  If the two waited-for locks are
not on the same table, more than two transactions were involved in the deadlock.
.SH "OUTPUT"
.IX Header "OUTPUT"
You can choose which columns are output and/or saved to \*(L"\-\-dest\*(R" with the
\&\*(L"\-\-columns\*(R" argument.  The default columns are as follows:
.IP "server" 4
.IX Item "server"
The (source) server on which the deadlock occurred.  This might be useful if
you're tracking deadlocks on many servers.
.IP "ts" 4
.IX Item "ts"
The date and time of the last detected deadlock.
.IP "thread" 4
.IX Item "thread"
The MySQL thread number, which is the same as the connection \s-1ID\s0 in \s-1SHOW\s0 \s-1FULL\s0
\&\s-1PROCESSLIST\s0.
.IP "txn_id" 4
.IX Item "txn_id"
The InnoDB transaction \s-1ID\s0, which InnoDB expresses as two unsigned integers.  I
have multiplied them out to be one number.
.IP "txn_time" 4
.IX Item "txn_time"
How long the transaction was active when the deadlock happened.
.IP "user" 4
.IX Item "user"
The connection's database username.
.IP "hostname" 4
.IX Item "hostname"
The connection's host.
.IP "ip" 4
.IX Item "ip"
The connection's \s-1IP\s0 address.  If you specify \*(L"\-\-numip\*(R", this is converted to
an unsigned integer.
.IP "db" 4
.IX Item "db"
The database in which the deadlock occurred.
.IP "tbl" 4
.IX Item "tbl"
The table on which the deadlock occurred.
.IP "idx" 4
.IX Item "idx"
The index on which the deadlock occurred.
.IP "lock_type" 4
.IX Item "lock_type"
The lock type the transaction held on the lock that caused the deadlock.
.IP "lock_mode" 4
.IX Item "lock_mode"
The lock mode of the lock that caused the deadlock.
.IP "wait_hold" 4
.IX Item "wait_hold"
Whether the transaction was waiting for the lock or holding the lock.  Usually
you will see the two waited-for locks.
.IP "victim" 4
.IX Item "victim"
Whether the transaction was selected as the deadlock victim and rolled back.
.IP "query" 4
.IX Item "query"
The query that caused the deadlock.
.SH "SYSTEM REQUIREMENTS"
.IX Header "SYSTEM REQUIREMENTS"
You need Perl, \s-1DBI\s0, DBD::mysql, and some core packages that ought to be
installed in any reasonably new version of Perl.
.SH "BUGS"
.IX Header "BUGS"
Please use the Sourceforge bug tracker, forums, and mailing lists to request
support or report bugs: <http://sourceforge.net/projects/mysqltoolkit/>.
.SH "COPYRIGHT, LICENSE AND WARRANTY"
.IX Header "COPYRIGHT, LICENSE AND WARRANTY"
This program is copyright (c) 2007 Baron Schwartz.
Feedback and improvements are welcome.
.PP
\&\s-1THIS\s0 \s-1PROGRAM\s0 \s-1IS\s0 \s-1PROVIDED\s0 \*(L"\s-1AS\s0 \s-1IS\s0\*(R" \s-1AND\s0 \s-1WITHOUT\s0 \s-1ANY\s0 \s-1EXPRESS\s0 \s-1OR\s0 \s-1IMPLIED\s0
\&\s-1WARRANTIES\s0, \s-1INCLUDING\s0, \s-1WITHOUT\s0 \s-1LIMITATION\s0, \s-1THE\s0 \s-1IMPLIED\s0 \s-1WARRANTIES\s0 \s-1OF\s0
\&\s-1MERCHANTIBILITY\s0 \s-1AND\s0 \s-1FITNESS\s0 \s-1FOR\s0 A \s-1PARTICULAR\s0 \s-1PURPOSE\s0.
.PP
This program is free software; you can redistribute it and/or modify it under
the terms of the \s-1GNU\s0 General Public License as published by the Free Software
Foundation, version 2; \s-1OR\s0 the Perl Artistic License.  On \s-1UNIX\s0 and similar
systems, you can issue `man perlgpl' or `man perlartistic' to read these
licenses.
.PP
You should have received a copy of the \s-1GNU\s0 General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, \s-1MA\s0  02111\-1307  \s-1USA\s0.
.SH "AUTHOR"
.IX Header "AUTHOR"
Baron Schwartz.
.SH "VERSION"
.IX Header "VERSION"
This manual page documents Ver 1.0.4 Distrib 1053 \f(CW$Revision:\fR 855 $.