The information here was adapted from the AskMonty Knowledgebase.
There are two main parts to MariaDB and MySQL: The mysqld server and whatever client you use to interact with the server. The server is absolutely essential and must remain up and running. mysqld is normally very reliable, but there are rare occasions when it will fail. When mysqld fails hard (or core dump) it will, by default, write a stack trace in the 'hostname'.err file in the database directory. However, in some cases this is not enough to find out exactly what happened.
If you ever run into a situation where mysqld crashes and the 'hostname'.err file does not contain enough information for your DBA or support provider to diagnose the problem, you may need to use what is known as a "debug" build to produce a "full stack trace" and a core file. The following instructions describe how to do this on Unix-like systems.
The steps are:
- Download a source tar.gz file (like mariadb-5.2.9.tar.gz).
- Compile it for debugging
- Update your my.cnf file to ensure you get a core and a stack trace.
- Install the debug mysqld instead of your normal one.
- Run with the debug version until you get a new crash.
- Restore your old mysqld version.
Compiling MySQL or MariaDB for debugging
In the following example I use a MariaDB 5.2.9 Source .tar.gz, but the steps apply equally well to a MySQL source .tar.gz file.
Prior to compiling MariaDB or MySQL, your system needs to be set up with all of the correct libraries and developer tools. The Compiling MariaDB from Source section of the AskMonty Knowledgebase has links to several articles to help you get your system set up properly.
Once things are set up, creating a debug version of mysqld is as easy as:
cd ~ mkdir mariadb cd mariadb tar xvf mariadb-5.2.9.tar.gz ln -s mariadb-5.2.9 current cd current ./BUILD/compile-pentium64-debug-max
The last command will produce a debug version of sql/mysqld. If you have a system other than 64 bit Intel/AMD on Linux you can use a different BUILD/...-debug-max file. If this fails, you can try with:
./BUILD/autorun.sh ./configure --with-debug=full -with-extra-charsets=complex \ --with-plugin-aria --with-aria-tmp-tables --without-plugin-innodb_plugin \ --with-plugins=max \ --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static make
Updating your my.cnf
Add the following to the end of your ~/.my.cnf file:
[[mysqld]] --stack-trace --core-file [[mysqld-safe]] --core-file-size=unlimited
These are safe to leave there also in the future.
Note: If you are using safe_mysqld and running mysqld as root, then no core file is created on some systems. The solution is to run mysqld as another user.
Temporarily replacing your standard mysqld with the debug version
This is how to do it on Open SuSE. Other Linux distributions may put ##mysqld## in a different location.
mysqladmin shutdown cd /usr/local/mysql/libexec mv mysqld mysqld-orig cp ~/mariadb/current/sql/mysqld mysqld-debug ln -s mysqld-debug mysqld
The above commands replace the current mysqld with the recently compiled debug version, but do so in a way which makes it trivial to revert back to the original version (you just have to change the sym-link).
Once the debug version is in place, start mysqld again (with something like):
(Use whatever command you normally do. The mysqld binary has been switched to the debug binary, so your startup scripts will automatically use it.)
Restoring your original mysqld version
If you want to restore your original mysqld binary, you can do it with:
cd /usr/local/mysql/libexec rm mysqld ln -s mysqld-orig mysqld
Notice that the debug binary was not deleted, all that was removed was the sym-link (and it was immediately recreated and pointed at the original binary). This way, the debug binary is still there if you need it in the future.
Using the core file
If/when the debug version of mysqld crashes, you will be left with:
- A more precise stack trace in the 'hostname'.err file in the data directory.
- A core or core.#### file in your data directory.
To examine the stack trace and other information in the gdb debugger you can do:
cd ~/mariadb/current cp mariadb-database-directory/core* core gdb ~/mariadb/current/sql/mysqld core backtrace full
Of course, if you're like me. As soon as I have a stack trace and core file, my next step is to turn those, and my debug binary, over to the experts. I'm not going to pretend I know how to effectively use a debugger.
More information on troubleshooting the above steps (if you're still having trouble with your crashing mysqld not generating a core file, and so on...) is available at the Knowledgebase entry linked to at the beginning of this article.