A.4.1 What To Do If MySQL Keeps Crashing

All MySQL versions are tested on many platforms before they are released. This doesn't mean that there aren't any bugs in MySQL, but it means if there are bugs, they are 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, as you will have a much better chance of getting this fixed quickly.

First, you should try to find out whether the problem is that the mysqld daemon 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, you may find the reason for this in the file `mysql-data-directory/`hostname`.err'. See section 4.9.1 The Error Log.

On some systems you can find in this file a stack trace of where mysqld died that you can resolve with resolve_back_stack. See section E.1.4 Using a Stack Trace. Note that the variable values written in the .err file may not always be 100 percent correct.

Many crashes of MySQL are caused by corrupted index files or datafiles. MySQL will update the data 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 only the data is written.) This means that the data is safe even if mysqld crashes, as the OS will ensure that the not flushed data is written to disk. You can force MySQL to sync everything to disk after every SQL command by starting mysqld with --flush.

The above means that normally you shouldn't get corrupted tables unless:

• Someone/something killed mysqld or the machine in the middle of an update.
• You have found a bug in mysqld that caused it to die in the middle of an update.
• Someone is manipulating the data/index files outside of mysqld without locking the table properly.
• If you are running many mysqld servers on the same data on a system that doesn't support good filesystem locks (normally handled by the lockd daemon ) or if you are running multiple servers with --skip-external-locking
• You have a crashed index/datafile that contains very wrong data that got mysqld confused.
• You have found a bug in the data storage code. This isn't that likely, but it's at least possible. In this case you can try to change the file 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:

Take down the mysqld daemon with mysqladmin shutdown, run myisamchk --silent --force */*.MYI on all tables, and restart the mysqld daemon. This will ensure that you are running from a clean state. See section 4 Database Administration.
• Use mysqld --log and try to determine from the information in 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 MySQL restarted. See section 4.9.2 The General Query Log. If you can repeatedly kill MySQL with one of the queries, even when you have checked all tables just before doing the query, then you have been able to locate the bug and should do a bug report for this! See section 1.7.1.3 How to Report Bugs or Problems.
• Try to make a test case that we can use to reproduce the problem. See section E.1.6 Making a Test Case If You Experience Table Corruption.
• Try running the included mysql-test test and the MySQL benchmarks. See section 12.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 `bench' directory in the source distribution or, for a binary distribution, in the `sql-bench' directory under your MySQL installation directory.
• Try fork_test.pl and fork2_test.pl.
• If you configure MySQL for debugging, it will be much easier to gather information about possible errors if something goes wrong. Reconfigure MySQL with the --with-debug option or --with-debug=full to configure and then recompile. See section E.1 Debugging a MySQL server.
• 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.
• Have you 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 2 mysqld servers on the same data and that you must be careful if you use myisamchk, but 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 processlist will usually 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 or in a separate window to produce statistics while you run your other queries.
• Try the following:
  1. Start mysqld from gdb (or in another debugger). See section E.1.3 Debugging mysqld under gdb.
  2. Run your test scripts.
  3. Print the backtrace and the local variables at the 3 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. See section 1.7.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 with dynamic-length rows and you are not using BLOB/TEXT columns (but only VARCHAR 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 at least 3 years without any problems, but by nature dynamic-length rows are more prone to errors, so it may be a good idea to try the above to see if it helps!

User Comments

Any time your database is acting strangely, you should suspect, and subsequently prove the operation of, your hardware. A single bad block on a hard drive or single bad bit in memory can cause all kinds of problems.

http://www.memtest86.com/ is an excellent memory tester for x86 based hardware. For SCSI hard drives a low level (controller initiated) format can often map out all bad blocks and restore reliability. IDE drives can also be reformatted low level, but I've found a few that still had bad blocks after a remapping. In linux you can use the badblocks program or call it from mke2fs with the -c switch. It's not comprehensive, but it will find blocks that have hard errors in them.

Add your own comment.