GNU Info

(mysql.info)Crashing

---

Next: Resetting permissions Prev: Administration Issues Up: Administration Issues

---

Enter node , (file) or (file)node

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'.
Note: Error log.

Many crashes of MySQL are caused by corrupted index / data files.
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 `delayed_key_writes',
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 file system locks (normally
     handled by the `lockd' daemon ) or if you are running multiple
     servers with `--skip-locking'

   * You have a crashed index/data file 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 database handler 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 or not 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.  Note: MySQL Database Administration.

   * Use `mysqld --log' and try to determine from the information in
     the log whether or not 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. Note: Query log.  If you can repeatadly 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!  Note: Bug reports.

   * Try to make a test case that we can use to reproduce the problem.
     Note: Reproduceable test case.

   * Try running the included mysql-test test and the MySQL benchmarks.
     Note: MySQL test suite.  They should test MySQL rather well.
     You can also add code that to the benchmarks to 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.  Note:
     Debugging 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-locking' option to `mysqld'.  On some systems, the
     `lockd' lock manager does not work properly; the `--skip-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).  Note:
          Using gdb on mysqld.

       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. Note: 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 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!