MySQL Reference Manual :: A.4.2 What to Do If MySQL Keeps Crashing

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.

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 Chapter 5, 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 Section 5.10.2, “The General 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 Section 1.6.1.3, “How to Report Bugs or Problems”.

Try to make a test case that we can use to repeat the problem. See Section E.1.6, “Making a Test Case If You Experience Table Corruption”.

Try running the tests in the mysql-test directory and the MySQL benchmarks. See Section 27.1.2, “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 Section E.1, “Debugging a MySQL 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:

Try to simulate your application with a Perl script to force MySQL to crash or misbehave.

Send a normal bug report. See Section 1.6.1.3, “How to Report Bugs or Problems”. 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.