(mysql.info) crashing
Info Catalog
(mysql.info) resetting-permissions
(mysql.info) administration-issues
(mysql.info) full-disk
A.4.2 What to Do If MySQL Keeps Crashing
----------------------------------------
Each MySQL version is tested on many platforms before it is released.
This doesn't mean that there are no bugs in MySQL, but if there are
bugs, they should be very few and can be hard to find. If you have a
problem, it always helps if you try to find out exactly what crashes
your system, because you have a much better chance of getting the
problem fixed quickly.
First, you should try to find out whether the problem is that the
`mysqld' server dies or whether your problem has to do with your
client. You can check how long your `mysqld' server has been up by
executing `mysqladmin version'. If `mysqld' has died and restarted, you
may find the reason by looking in the server's error log. See
error-log.
On some systems, you can find in the error log a stack trace of where
`mysqld' died that you can resolve with the `resolve_stack_dump'
program. See using-stack-trace. Note that the variable values
written in the error log may not always be 100% correct.
Many server crashes are caused by corrupted data files or index files.
MySQL updates the files on disk with the `write()' system call after
every SQL statement and before the client is notified about the result.
(This is not true if you are running with -delay-key-write, in which
case data files are written but not index files.) This means that data
file contents are safe even if `mysqld' crashes, because the operating
system ensures that the unflushed data is written to disk. You can
force MySQL to flush everything to disk after every SQL statement by
starting `mysqld' with the -flush option.
The preceding means that normally you should not get corrupted tables
unless one of the following happens:
* The MySQL server or the server host was killed in the middle of an
update.
* You have found a bug in `mysqld' that caused it to die in the
middle of an update.
* Some external program is manipulating data files or index files at
the same time as `mysqld' without locking the table properly.
* You are running many `mysqld' servers using the same data
directory on a system that doesn't support good filesystem locks
(normally handled by the `lockd' lock manager), or you are running
multiple servers with external locking disabled.
* You have a crashed data file or index file that contains very
corrupt data that confused `mysqld'.
* You have found a bug in the data storage code. This isn't likely,
but it's at least possible. In this case, you can try to change
the storage engine to another engine by using `ALTER TABLE' on a
repaired copy of the table.
Because it is very difficult to know why something is crashing, first
try to check whether things that work for others crash for you. Please
try the following things:
* Stop the `mysqld' server with `mysqladmin shutdown', run
`myisamchk --silent --force */*.MYI' from the data directory to
check all `MyISAM' tables, and restart `mysqld'. This ensures that
you are running from a clean state. See
database-administration.
* Start `mysqld' with the -log option and try to determine from the
information written to the log whether some specific query kills
the server. About 95% of all bugs are related to a particular
query. Normally, this is one of the last queries in the log file
just before the server restarts. See query-log. If you can
repeatedly kill MySQL with a specific query, even when you have
checked all tables just before issuing it, then you have been able
to locate the bug and should submit a bug report for it. See
bug-reports.
* Try to make a test case that we can use to repeat the problem. See
reproducible-test-case.
* Try running the tests in the `mysql-test' directory and the MySQL
benchmarks. See mysql-test-suite. They should test MySQL
rather well. You can also add code to the benchmarks that
simulates your application. The benchmarks can be found in the
`sql-bench' directory in a source distribution or, for a binary
distribution, in the `sql-bench' directory under your MySQL
installation directory.
* Try the `fork_big.pl' script. (It is located in the `tests'
directory of source distributions.)
* If you configure MySQL for debugging, it is much easier to gather
information about possible errors if something goes wrong.
Configuring MySQL for debugging causes a safe memory allocator to
be included that can find some errors. It also provides a lot of
output about what is happening. Reconfigure MySQL with the
-with-debug or -with-debug=full option to `configure' and then
recompile. See debugging-server.
* Make sure that you have applied the latest patches for your
operating system.
* Use the -skip-external-locking option to `mysqld'. On some
systems, the `lockd' lock manager does not work properly; the
-skip-external-locking option tells `mysqld' not to use external
locking. (This means that you cannot run two `mysqld' servers on
the same data directory and that you must be careful if you use
`myisamchk'. Nevertheless, it may be instructive to try the option
as a test.)
* Have you tried `mysqladmin -u root processlist' when `mysqld'
appears to be running but not responding? Sometimes `mysqld' is
not comatose even though you might think so. The problem may be
that all connections are in use, or there may be some internal
lock problem. `mysqladmin -u root processlist' usually is able to
make a connection even in these cases, and can provide useful
information about the current number of connections and their
status.
* Run the command `mysqladmin -i 5 status' or `mysqladmin -i 5 -r
status' in a separate window to produce statistics while you run
your other queries.
* Try the following:
1. Start `mysqld' from `gdb' (or another debugger). See
using-gdb-on-mysqld.
2. Run your test scripts.
3. Print the backtrace and the local variables at the three
lowest levels. In `gdb', you can do this with the following
commands when `mysqld' has crashed inside `gdb':
backtrace
info local
up
info local
up
info local
With `gdb', you can also examine which threads exist with
`info threads' and switch to a specific thread with `thread
N', where N is the thread ID.
* Try to simulate your application with a Perl script to force MySQL
to crash or misbehave.
* Send a normal bug report. See bug-reports. Be even more
detailed than usual. Because MySQL works for many people, it may
be that the crash results from something that exists only on your
computer (for example, an error that is related to your particular
system libraries).
* If you have a problem with tables containing dynamic-length rows
and you are using only `VARCHAR' columns (not `BLOB' or `TEXT'
columns), you can try to change all `VARCHAR' to `CHAR' with
`ALTER TABLE'. This forces MySQL to use fixed-size rows.
Fixed-size rows take a little extra space, but are much more
tolerant to corruption.
The current dynamic row code has been in use at MySQL AB for
several years with very few problems, but dynamic-length rows are
by nature more prone to errors, so it may be a good idea to try
this strategy to see whether it helps.
* Do not rule out your server hardware when diagnosing problems.
Defective hardware can be the cause of data corruption. Particular
attention should be paid to both RAMS and hard-drives when
troubleshooting hardware.
Info Catalog
(mysql.info) resetting-permissions
(mysql.info) administration-issues
(mysql.info) full-disk
automatically generated byinfo2html