How to debug MySQL and MariaDB

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

For MariaDB specific info, see:

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.

Generating random data

In case you need to populate your tables to run some tests, there is a small handy command line tool from Percona:
Download the tool (GitHub)
Article from Percona on how to use it

Generating Core Dump files

1) Update server configuration

For MariaDB add:

[mariadb] 
core-file

For MySQL add:

[mysqld] 
core-file

2) For MySQL >= 8.0, update the systemc core dump configuration

Change proc/sys/fs/suid_dumpable:

# cat /proc/sys/fs/suid_dumpable
0
# echo 2 > /proc/sys/fs/suid_dumpable
# cat /proc/sys/fs/suid_dumpable
2

3) Start the server, apply the new configuration

IMPORTANT:
Configuration from previous steps won't be applied until next start of the service!

If the server wasn't running, just start it. Otherwise restart the server.

4) Get the core dump

Either wait for one in buggy server, or provoke one by sending SIG 11 to the daemon.

5) Where to find the generated coredump file

coredumpctl utility takes care of coredumps on Fedora by default. It looks something like this:

# coredumpctl
    TIME                            PID   UID   GID SIG COREFILE  EXE
    Tue 2019-02-26 16:08:13 EST    2916    27    27  11 present   /usr/libexec/mysqld

In order to get the coredump file, you need to call

# coredumpctl -o <path> dump <pid>

Where -o <path> is the location where should be the file saved, dump is telling what to do and <pid> is a pattern to match the specific coredump if more are available. In such cases, PID is usually a nice unique indentificator.

The default location for coredumps for both MariaDB and MySQL are in their datadirs (/usr/lib/mysql). If you have coredumpctl disabled for example, you'll find them there.

IMPORTANT:
Please note the size of the core dump. Freshly installed, empty database will produce ~0,5GB coredump. DB with real load can easily produce coredumps of dozens or even hundreds of GB!

Search