G.1 Debugging a MySQL server

If you are using some functionality that is very new in MySQL, you can try to run mysqld with the --skip-new (which will disable all new, potentially unsafe functionality) or with --safe-mode which disables a lot of optimization that may cause problems. 18.1 What to do if MySQL keeps crashing.

If mysqld doesn't want to start, you should check that you don't have any my.cnf file that interferes with your setup! You can check your my.cnf arguments with mysqld --print-defaults and avoid using them by starting with mysqld --no-defaults ....

If you have some very specific problem, you can always try to debug MySQL. To do this you must configure MySQL with the option --with-debug. You can check whether or not MySQL was compiled with debugging by doing: mysqld --help. If the --debug flag is listed with the options then you have debugging enabled. mysqladmin ver also lists the mysqld version as mysql ... -debug in this case.

If you are using gcc or egcs, the recommended configure line is: 

CC=gcc CFLAGS="-O6" CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-debug

This will avoid problems with the libstdc++ library and with C++ exceptions.

If you can cause the mysqld server to crash quickly, you can try to create a trace file of this:

Start the mysqld server with a trace log in `/tmp/mysql.trace'. The log file will get very BIG.

mysqld --debug --log

or you can start it with

mysqld --debug=d,info,error,query,general,where:O,/tmp/mysql.trace

which only prints information with the most interesting tags.

When you configure MySQL for debugging you automatically enable a lot of extra safety check functions that monitor the health of mysqld. If they find something ``unexpected,'' an entry will be written to stderr, which safe_mysqld directs to the error log! This also means that if you are having some unexpected problems with MySQL and are using a source distribution, the first thing you should do is to configure MySQL for debugging! (The second thing, of course, is to send mail to mysql@lists.mysql.com and ask for help. Please use the mysqlbug script for all bug reports or questions regarding the MySQL version you are using!

On most system you can also start mysqld from gdb to get more information if mysqld crashes.

With some gdb versions on Linux you must use run --one-thread if you want to be able to debug mysqld threads. In this case you can only have one thread active at a time.

If you are using gdb 4.17.x on Linux, you should install a `.gdb' file, with the following information, in your current directory: 

set print sevenbit off
handle SIGUSR1 nostop noprint
handle SIGUSR2 nostop noprint
handle SIGWAITING nostop noprint
handle SIGLWP nostop noprint
handle SIGPIPE nostop
handle SIGALRM nostop
handle SIGHUP nostop
handle SIGTERM nostop noprint

Here follows an example how to debug mysqld: 

shell> gdb /usr/local/libexec/mysqld
gdb> run
...
back   # Do this when mysqld crashes
info locals
up
info locals
up
...
(until you get some information about local variables)

quit

Include the above output in a mail generated with mysqlbug and mail this to mysql@lists.mysql.com.

If mysqld hangs you can try to use some system tools like strace or /usr/proc/bin/pstack to examine where mysqld has hanged.

If mysqld starts to eat up CPU or memory or if it ``hangs'', you can use mysqladmin processlist status to find out if someone is executing some query that takes a long time. It may be a good idea to run mysqladmin -i10 processlist status in some window if you are experiencing performance problems or problems when new clients can't connect.

If mysqld dies or hangs, you should start mysqld with --log. When mysqld dies again, you can check in the log file for the query that killed mysqld. Note that before starting mysqld with --log you should check all your tables with isamchk. 13 Maintaining a MySQL installation.

If you are using a log file, mysqld --log, you should check the 'hostname' log files, that you can find in the database directory, for any queries that could cause a problem. Try the command EXPLAIN on all SELECT statements that takes a long time to ensure that mysqld are using indexes properly. EXPLAIN. You should also test complicated queries that didn't complete within the mysql command line tool.

If you find the text mysqld restarted in the error log file (normally named `hostname.err') you have probably found a query that causes mysqld to fail. If this happens you should check all your tables with isamchk ( 13 Maintaining a MySQL installation), and test the queries in the MySQL log files if someone doesn't work. If you find such a query, try first upgrading to the newest MySQL version. If this doesn't help and you can't find anything in the mysql mail archive, you should report the bug to mysql@lists.mysql.com. Links to mail archives are available at the online MySQL documentation page.

If you get corrupted tables or if mysqld always fails after some update commands, you can test if this bug is reproducible by doing the following:

  • Stop the mysqld daemon (with mysqladmin shutdown)
  • Check all tables with isamchk -s database/*.ISM. Repair any wrong tables with isamchk -r database/table.ISM.
  • Start mysqld with --log-update
  • When you have got a crashed table, stop the mysqld server.
  • Restore the backup.
  • Restart the mysqld server without --log-update
  • Re-execute the commands with mysql < update-log. The update log is saved in the MySQL database directory with the name your-hostname.#.
  • If the tables are now again corrupted, you have found reproducible bug in the ISAM code! FTP the tables and the update log to ftp://www.mysql.com/pub/mysql/secret and we will fix this as soon as possible!

The command mysqladmin debug will dump some information about locks in use, used memory and query usage to the mysql log file. This may help solve some problems. This command also provides some useful information even if you haven't compiled MySQL for debugging!

If the problem is that some tables are getting slower and slower you should try to repair the tables with isamchk to optimize the table layout. You should also check the slow queries with EXPLAIN. 13 Maintaining a MySQL installation.

You should also read the OS-specific section in this manual for problems that may be unique to your environment. 4.11 System-specific issues

If you are using the Perl DBI interface, you can turn on debugging information by using the trace method or by setting the DBI_TRACE environment variable. Perl DBI Class.