How to debug MySQL and MariaDB

Some general tips how to debug the daemon itself (some info valid for Oracle's MySQL only):

• http://dev.mysql.com/doc/refman/5.5/en/using-gdb-on-mysqld.html
• http://dev.mysql.com/doc/refman/5.5/en/debugging-server.html
• http://dev.mysql.com/doc/refman/5.5/en/porting.html

For MariaDB specific info, see:

• https://mariadb.com/kb/en/mariadb/development/debugging-mariadb/compiling-mariadb-for-debugging/
• https://mariadb.com/kb/en/mariadb/development/debugging-mariadb/how-to-produce-a-full-stack-trace-for-mysqld/
• https://mariadb.com/kb/en/mariadb/development/debugging-mariadb/creating-a-trace-file/

Where to report bugs for MariaDB and MySQL
Bugs for MariaDB (except those that are caused by packaging issue) should be reported to MariaDB upstream. Oracle does not pay that much attention to bugs reported to their bug tracker, but ideally the bugs should be reported there as well.

How to build MariaDB with debugging enabled

First you need to build MariaDB with debug mode enabled. To do so, use the following cmake option during build:

-DCMAKE_BUILD_TYPE=Debug

Do not forget to install -debuginfo package.
You may either install it directly by yum install mariadb-debuginfo or install it with all dependencies using debuginfo-install mariadb

How to configure mysqld daemon

Some useful arguments to /usr/libexec/mysqld:

--skip-stack-trace --gdb --core-file --general-log --general-log-file --verbose

If compiled with debug option, then run with:

--debug 

Doc for those and others is available at http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html.

To set some debugging options for mysqld, create a new configuration file that is read in the end of /etc/my.cnf:

# cat /etc/my.cnf.d/debug.cnf 
[mysqld]
debug=d,info,error,query:o,/tmp/mysqld.trace
stack-trace
core-file

[mysqld_safe]
core-file-size=unlimited

The configuration above instructs mysqld daemon to store full trace file, which may include important information for debugging the daemon. Providing that trace file to upstream may help a lot.

Where the trace file is actually stored
Please, mind, that path /tmp/mysqld.trace is set from the POV of the mysqld process. Service mariadb uses PrivateTmp feature of the systemd, so the log can be found at /tmp/systemd-mariadb.service-XXXXXX/tmp/mysqld.trace actually.

In order to change core file limit for the service started by systemd, create a drop-in configuration file for the service:

# cat /etc/systemd/system/mariadb.service.d/debug.conf
[Service]
LimitCORE=infinity

Where is the core dump stored
After crashing, a core dump is usually located in the datadir, which is /var/lib/mysql in default configuration.

Running mysqld without systemd

It is not very handy to debug daemon run by systemd, which runs mysqld_safe bash script and this script runs mysqld daemon itself. It might be better to run the mysqld daemon directly with the same arguments and under mysql user (so it can work with the data as usually).

#> systemctl start mariadb
#> systemctl status mariadb -l | grep /usr/libexec/mysqld
           └─29233 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
#> systemctl stop mariadb
#> ulimit -c unlimited
#> su -s /bin/bash mysql -c "/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock --skip-stack-trace --gdb --core-file --general-log-file=/var/log/mariadb/mariadb_query.log --verbose --general-log=1 &"
#> # play with mysql
#> echo "create table ..." | mysql test
#> # observe the log files /var/log/mariadb/mariadb.log /var/log/mariadb/mariadb_query.log
#> killall mysqld

Also systemtap can be used to debug mysqld.
If somebody is familiar with systemtap and is able to use it to debug MariaDB/MySQL, be so kind and describe it shortly here.