O'Reilly logo

MySQL Reference Manual by Kaj Arno, David Axmark, Michael Widenius

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Appendix D. Porting to Other Systems

This appendix will help you port MySQL to other operationg systems. Do check the list of currently supported operating systems first. See Section 2.2.2. If you have created a new port of MySQL, please let us know so that we can list it here and on our web site (http://www.mysql.com/), recommending it to other users.

Note: If you create a new port of MySQL, you are free to copy and distribute it under the GPL license, but it does not make you a copyright holder of MySQL.

A working Posix thread library is needed for the server. On Solaris 2.5 we use Sun PThreads (the native thread support in 2.4 and earlier versions is not good enough) and on Linux we use LinuxThreads by Xavier Leroy, .

The hard part of porting to a new Unix variant without good native thread support is probably to port MIT-pthreads. See mit-pthreads/README and Programming POSIX Threads (http://www.humanfactor.com/pthreads/).

The MySQL distribution includes a patched version of Provenzano’s Pthreads from MIT (see the MIT Pthreads web page at http://www.mit.edu:8001/people/proven/pthreads.html). This can be used for some operating systems that do not have POSIX threads.

It is also possible to use another user-level thread package named FSU Pthreads (see http://www.informatik.hu-berlin.de/~mueller/pthreads.htmlFSU, the Pthreads home page). This implementation is being used for the SCO port.

See the thr_lock.c and thr_alarm.c programs in the mysys directory for some tests/examples of these problems.

Both the server and the client need a working C++ compiler (we use gcc and have tried SPARCworks). Another compiler that is known to work is the Irix cc.

To compile only the client use ./configure --without-server.

There is currently no support for only compiling the server, nor is it likely to be added unless someone has a good reason for it.

If you want/need to change any Makefile or the configure script you must get Automake and Autoconf. We have used the automake-1.2 and autoconf-2.12 distributions.

All steps needed to remake everything from the most basic files.

/bin/rm */.deps/*.P
/bin/rm -f config.cache
aclocal
autoheader
aclocal
automake
autoconf
./configure --with-debug=full --prefix='your installation directory'

# The makefiles generated above need GNU make 3.75 or newer.
# (called gmake below)
gmake clean all install init-db

If you run into problems with a new port, you may have to do some debugging of MySQL! See Section D.1.

Note: Before you start debugging mysqld, first get the test programs mysys/thr_alarm and mysys/thr_lock to work. This will ensure that your thread installation has even a remote chance of working!

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 optimisation that may cause problems. See Section A.4.1.

If mysqld doesn’t want to start, you should check that you don’t have any my.cnf files that interfere 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 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 a 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.

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 optimise the table with OPTIMIZE TABLE or myisamchk. See Chapter 4. You should also check the slow queries with EXPLAIN.

You should also read the OS-specific section in this manual for problems that may be unique to your environment. See Section 2.6.

Compiling MySQL for Debugging

If you have some very specific problem, you can always try to debug MySQL. To do this you must configure MySQL with the --with-debug or the --with-debug=full option. You can check whether MySQL was compiled with debugging by doing mysqld --help. If the --debug flag is listed with the options, 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="-O2" CXX=gcc CXXFLAGS="-O2 -felide-constructors \
   -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \
   --with-debug --with-extra-charsets=complex

This will avoid problems with the libstdc++ library and with C++ exceptions (many compilers have problems with C++ exceptions in threaded code) and compile a MySQL version with support for all character sets.

If you suspect a memory overrun error, you can configure MySQL with --with-debug=full, which will install a memory allocation (SAFEMALLOC) checker. Running with SAFEMALLOC is, however, quite slow, so if you get performance problems you should start mysqld with the --skip-safemalloc option. This will disable the memory overrun checks for each call to malloc and free.

If mysqld stops crashing when you compile it with --with-debug, you have probably found a compiler bug or a timing bug within MySQL. In this case you can try to add -g to the CFLAGS and CXXFLAGS variables and not use --with-debug. If mysqld now dies, you can at least attach to it with gdb or use gdb on the core file to find out what happened.

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 configure MySQL for debugging! (The second thing, of course, is to send mail to and ask for help. Please use the mysqlbug script for all bug reports or questions regarding the MySQL version you are using!)

In the Windows MySQL distribution, mysqld.exe is, by default, compiled with support for trace files.

Creating Trace Files

If the mysqld server doesn’t start or if you can cause the mysqld server to crash quickly, you can try to create a trace file to find the problem.

To do this you have to have a mysqld that is compiled for debugging. You can check this by executing mysqld -V. If the version number ends with -debug, it’s compiled with support for trace files.

Start the mysqld server with a trace log in /tmp/mysqld.trace (or C:\mysqld.trace on Windows):

mysqld --debug

On Windows you should also use the --standalone flag to not start mysqld as a service:

In a DOS window do:

mysqld --debug --standalone

After this you can use the mysql.exe command-line tool in a second DOS window to reproduce the problem. You can take down the mysqld server with mysqladmin shutdown.

Note that the trace file will get very big! If you want to have a smaller trace file, you can use something like:

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

which only prints information with the most interesting tags in /tmp/mysqld.trace.

If you make a bug report about this, please only send the lines from the trace file to the appropriate mailing list where something seems to go wrong! If you can’t locate the wrong place, you can ftp the trace file, together with a full bug report, to ftp://support.mysql.com/pub/mysql/secret/ so that a MySQL developer can take a look at it.

The trace file is made with the DBUG package by Fred Fish. See Section D.3.

Debugging mysqld under gdb

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

With some older 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. We recommend that you upgrade to gdb 5.1 ASAP, as thread debugging works much better with this version!

When running mysqld under gdb, you should disable the stack trace with --skip-stack-trace to be able to catch segfaults within gdb.

It’s very hard to debug MySQL under gdb if you do a lot of new connections the whole time, as gdb doesn’t free the memory for old threads. You can avoid this problem by starting mysqld with -O thread_cache_size= 'max_connections +1'. In most cases just using -O thread_cache_size=5' will help a lot!

If you want to get a core dump on Linux if mysqld dies with a SIGSEGV signal, you can start mysqld with the --core-file option. This core file can be used to make a backtrace that may help you find out why mysqld died:

shell> gdb mysqld core
gdb>   backtrace full
gdb>   exit

See Section A.4.1.

If you are using gdb 4.17.x or above 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

If you have problems debugging threads with gdb, you should download gdb 5.x and try this instead. The new gdb version has very improved thread handling!

Here is an example how to debug mysqld:

shell> gdb /usr/local/libexec/mysqld
gdb> run
...
backtrace full # Do this when mysqld crashes

Include this 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 hung.

strace /tmp/log libexec/mysqld

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. See Section 8.2.2.

Using a Stack Trace

On some operating systems, the error log will contain a stack trace if mysqld dies unexpectedly. You can use this to find out where (and maybe why) mysqld died. See Section 4.9.1. To get a stack trace, you must not compile mysqld with the -fomit-frame-pointer option to gcc. See Section D.1.1.

If the error file contains something like the following:

mysqld got signal 11;
The manual section 'Debugging a MySQL server' tells you how to use a
stack trace and/or the core file to produce a readable backtrace that may
help in finding out why mysqld died
Attemping backtrace. You can use the following information to find out
where mysqld died.  If you see no messages after this, something went
terribly wrong
stack range sanity check, ok, backtrace follows
0x40077552
0x81281a0
0x8128f47
0x8127be0
0x8127995
0x8104947
0x80ff28f
0x810131b
0x80ee4bc
0x80c3c91
0x80c6b43
0x80c1fd9
0x80c1686

you can find where mysqld died by doing the following:

  1. Copy the preceding numbers to a file—for example, mysqld.stack.

  2. Make a symbol file for the mysqld server:

    nm -n libexec/mysqld > /tmp/mysqld.sym

    Note that many MySQL binary distributions come with this file, named mysqld.sym.gz. In this case you must unpack this by doing:

    gunzip < bin/mysqld.sym.gz > /tmp/mysqld.sym
  3. Execute resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack.

    This will print out where mysqld died. If this doesn’t help you find out why mysqld died, you should make a bug report and include the output from the preceding command with the bug report.

    Note, however, that in most cases it will not help us to just have a stack trace to find the reason for the problem. To be able to locate the bug or provide a workaround, we would, in most cases, need to know the query that killed mysqld, and we would prefer to have a test case so that we can repeat the problem! See Section 1.6.2.3.

Using Log Files to Find Cause of Errors in mysqld

Note that before starting mysqld with --log you should check all your tables with myisamchk. See Chapter 4.

If mysqld dies or hangs, you should start mysqld with --log. When mysqld dies again, you can examine the end of the log file for the query that killed mysqld.

If you are using --log without a file name, the log is stored in the database directory as ‘hostname’.log. In most cases it’s the last query in the log file that killed mysqld, but if possible you should verify this by restarting mysqld and executing the found query from the mysql command-line tools. If this works, you should also test all complicated queries that didn’t complete.

You can also try the command EXPLAIN on all SELECT statements that take a long time to ensure that mysqld is using indexes properly. See Section 5.2.1.

You can find the queries that take a long time to execute by starting mysqld with --log-slow-queries. See Section 4.9.5.

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 myisamchk (see Chapter 4), and test the queries in the MySQL log files to see if one doesn’t work. If you find such a query, first try 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 . Links to mail archives are available online at http://lists.mysql.com/.

If you have started mysqld with myisam-recover, MySQL will automatically check and try to repair MyISAM tables if they are marked as ‘not closed properly’ or ‘crashed’. If this happens, MySQL will write an entry in the hostname.err file 'Warning: Checking table ...' which is followed by Warning: Repairing table if the table needs to be repaired. If you get a lot of these errors, without mysqld having died unexpectedly just before, something is wrong and needs to be investigated further. See Section 4.1.1.

It’s, of course, not a good sign if mysqld died unexpectedly, but in this case one shouldn’t investigate the Checking table... messages. Instead, one should try to find out why mysqld died.

Making a Test Case When You Experience Table Corruption

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:

  1. Take down the MySQL daemon (with mysqladmin shutdown).

  2. Make a backup of the tables (to guard against the very unlikely case that the repair will do something bad).

  3. Check all tables with myisamchk -s database/*.MYI. Repair any wrong tables with myisamchk -r database/table.MYI.

  4. Make a second backup of the tables.

  5. Remove (or move away) any old log files from the MySQL data directory if you need more space.

  6. Start mysqld with --log-bin. See Section 4.9.4. If you want to find a query that crashes mysqld, you should use --log --log-bin.

  7. When you have gotten a crashed table, stop the mysqld server.

  8. Restore the backup.

  9. Restart the mysqld server without --log-bin.

  10. Re-execute the commands with mysqlbinlog update-log-file | mysql. The update log is saved in the MySQL database directory with the name hostname-bin.#.

  11. If the tables are corrupted again or you can get mysqld to die with the preceding command, you have found a reproducible bug that should be easy to fix! Ftp the tables and the binary log to ftp://support.mysql.com/pub/mysql/secret/ and send a mail to or (if you are a support customer) to about the problem and the MySQL team will fix it as soon as possible.

You can also use the script mysql_find_rows to just execute some of the update statements if you want to narrow down the problem.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required