(mysql.info.gz) Crashing
Info Catalog
(mysql.info.gz) Resetting permissions
(mysql.info.gz) Administration Issues
(mysql.info.gz) 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 will always help if you try to find out exactly what
crashes your system, because you will 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.
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. 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 will update 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 will ensure 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 the `--skip-external-locking' option.
* 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 table type to another storage 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 will ensure
that you are running from a clean state. MySQL 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 will be one of the last queries
in the log file just before the server restarts. 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. Bug reports.
* Try to make a test case that we can use to repeat the problem.
Reproduceable test case.
* Try running the tests in the `mysql-test' directory and the MySQL
benchmarks. 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 will be 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. 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 will be
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). Using
`gdb' on `mysqld' 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
#', where `#' 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. 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 will force 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.gz) Resetting permissions
(mysql.info.gz) Administration Issues
(mysql.info.gz) Full disk
automatically generated byinfo2html