Why We’re Debugging MySQL Server and How?

Why Debug MySQL?

Building MySQL from its source code sounds like something you won't normally do, though it can be useful for several scenarios:

1. When you want to contribute to MySQL with a new feature or a bug fix.
2. When you want to explore how MySQL or the underline engines behave, beyond what's documented.
3. When you're considering to fork MySQL for your work and customize it in your own repository.
4. When you want to create a custom MySQL build with custom build parameters or compiler optimizations.

For us at EverSQL, it's reason #2.

To improve our query optimization algorithms, we're constantly exploring the database's source code, to learn how the database operates, beyond what's actually documented, and how can our product complement the database's core functionality.

In this tutorial, you'll learn how to build MySQL from it's latest source code and debug it. For this tutorial, I used Ubuntu 16.04 and built the latest version of MySQL from GitHub, (as of today, MySQL 8.0.19).

Build MySQL 8.x From Source Code

First, clone the latest MySQL server source code from GitHub to your Ubuntu machine:

git clone https://github.com/mysql/mysql-server

Install the following dependencies:

sudo apt install cmake libssl-dev libzstd-dev libncurses5-dev libreadline-dev bison pkg-config

Next, we create the build output directory, and build.
* The cmake parameters will prepare your build for debugging and will also automatically download boost, which is a prerequisite for building MySQL.

cd mysql-server
mkdir build
cd build
cmake .. -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=~/boost_1_69_0
make

Once cmake completes successfully, you should see:

Building with make may take a few (or more) minutes. Once make completes successfully, you should see an output similar to this:

Now you can initialize MySQL and run the server. By default the initialization action will randomize a password for the root account - please copy it from the command's output and use it in the next step.

./build/bin/mysqld --initialize
./build/bin/mysqld --debug

Now in another terminal tab, you can connect to the database using the MySQL client, and change the password:

./build/bin/mysql -u root -p 

ALTER USER 'root'@'localhost' IDENTIFIED BY "<newpassword>";

Run a quick test query to make sure everything is working ok:

show databases;

Now you can shut down the server and get everything set up for debugging the database server:

exit
./build/bin/mysqladmin -u root -p shutdown

Debugging MySQL Server 8.x

To debug MySQL, you can use Visual Studio Code, which is a great (free) IDE from Microsoft. Start with downloading and installing it.

Once installed, we need to install the C++ extension from the extensions repository, as it's not a part of its core functionality. This extension will set up everything you need to efficiently browse and debug C++ code.

Now, please navigate to the Explorer tab on the left, and choose Open Folder. As your source folder, please choose the 'mysql-server' folder you cloned from GitHub:

To set up the debugger to debug the mysqld process, choose from the top menu: Run ==> Start Debugging ==> C++ (gdb). When the loader.json file will open, set the program argument with the path of the mysqld binary file.

You're now ready to start debugging.

Choose again from the menu Run ==> Start Debugging, and you're done, MySQL will start in a debug mode.

If you're looking for an interesting place in the code to assign a breakpoint before you start debugging, one good option will be the Optimize_table_order::greedy_search() function in the file sql_planner.cc, which will allow you to have a glance at how MySQL determines the optimal execution plan for a query.