The year of Linux desktop has finally come. It’s Windows + WSL 2. Seriously.

I use a MBP 16 for my daily work and SSH into linux machines for development/testing. While it’s a fantastic machine (and the track pad is second to none), I just hate the Apple trying to lock down the system so much that even setting up gdb to work is a nightmare, and running any simple script it tries to phone home for validation.

So I tried installing Linux on my machines. I do have a personal laptop X1 Carbon Gen7, but it doesn’t work well with Linux: mostly Linux just doesn’t like the 4 Channel Dolby Surround Speakers - they sound something from a tin-can and volume is much lower. While in Windows the sound I get is actually pretty nice (for a laptop, of course). I have spent countless time on it and I’ve seen many people struggling through the same issues. There are also occasionall hipcup with suspend/resume, but I can live with that. I also have a powerful gaming PC which I mostly play games. WSL sounds like a perfect solution for those machines where I can use Windows for their compatiblity / games, while also use it for development / tinkering on Linux. Yes, you can either dual boot or install a linux VM, but the integration between WSL 2 and Windows seems pretty nice to me, so I decided to try it out - and now all my Windows machines have WSL 2 installed.

Setting it up is not too bad - you do need to follow the official instructions to install it, which I’m not going to repeat here. The installation experience was fairly smooth, though it requires multiple steps.

However, to get it work properly requires a bit of extra work. Once you set it up it’s pretty much all I ever needed. Here is what it looks like when I’m done:

Anyone familiar with Python probably knew its history of Unicode support. If you add Python3, Unicode, and SWIG together, imagine what might go wrong?

Python3, Unicode, SWIG, and me

I was debugging a test failure written in Python just now and it is failing with this error:

Many of the end-to-end tests here are written in Python because they are convenient - no one wants to write a C++ code to drive MySql and our infra service to do a series of stuff.

UnicodeEncodeError: 'latin-1' codec can't encode character '\udcfa' in position 293: ordinal not in range(256)

The code looks like this:

sql = get_sql_from_some_magic_place()
decoded_sql = cUnescape(sql.decode("latin-1"))
decoded_sql_str = decoded_sql.encode("latin-1")
execute(decoded_sql_str)

The code seems straight-forward enough. The offending string looks like this: b"SELECT from blah WHERE col='\\372'.

This string was originally escaped by folly::cEscape which does simple thing rather simple - converts the string to be a C representation where ‘' are double escaped and any non-printable characters are escaped with octal. This is convenient as those escaped strings are safe to pass around without worry for encoding as they are, well, ASCII.

folly is Facebook’s open source standard C++ library collection. See https://github.com/facebook/folly for more information.

It is convenient, until you need to call from Python, for which you’ll need to use SWIG:

If you don’t know SWIG - just think it’s a tool that generates Python wrapper for C++ code so that they can be called from Python code. In this case, folly::cUnescape. Go to http://www.swig.org/ to learn more. Many language have equivalent tool/feature built-in, P/invoke in C#, cgo in go, JNI in Java, etc.

std::string cUnescape(const std::string& a) {
  std::string b;
  folly::cUnescape(a, b);
  return b;
}

I was scratching my ahead trying to understand what is happening as there is no way the strings are converted to ‘\udcfa’, until I realize cUnescape might be at fault.

It turns out, SWIG expects UTF-8 string and returns UTF-8 strings back. “\372” can be converted to UTF-8 without any trouble, but once it is unescaped it becomes “\372” which is 0xfa that is going to be interpreted as UTF-8:

b"\372".decode("utf-8", errors="surrogateescape").encode("latin-1")

And you get:

UnicodeEncodeError: 'latin-1' codec can't encode character '\udcfa' in position 0: ordinal not in range(256)

The fix

To fix the problem, you can encode the buffer again with surrogateescape:

>>> b"\372".decode("utf-8", errors="surrogateescape").encode("utf-8", errors="surrogateescape").decode("latin-1")
'ú'

Seems rather backwards, isn’t it? Why not just stop messing with the strings?

That’s exactly what was discussed in SWIG doc here: http://www.swig.org/Doc4.0/Python.html#Python_nn77. There is a magic macro you can use:

%module char_to_bytes
%begin %{
#define SWIG_PYTHON_STRICT_BYTE_CHAR
%}
std::string cUnescape(const std::string& a) {
  std::string b;
  folly::cUnescape(a, b);
  return b;
}

And the original code can be changed to:

sql = get_sql_from_some_magic_place()
decoded_sql = cUnescape(sql).decode("latin-1")
execute(decoded_sql)

Much simpler too.

I’m just happy that I mostly write C++ instead of Python…

Personally I like Python for what it is - a quick prototype language for writing simple utilities/scripts. Unfortunately when you start writing important infrastructure code using Python it quickly falls apart due to its dynamic typing. And all the breaking changes in Python3 didn’t help either.

A classical computer science problem is a worker queue - imagine you have N WorkItems to be divided between M workers (of course this implies N » M). This pattern has many application such as generating lots of data and insert into database, processing CPU-intensive work, etc. This can be easily achieved with go routine and channels. I recently spend a bit of time working on a similar problem with go and I’d like to share the pattern that I came up with.

Overview

MySQL is one of the most widely used OpenSource relational databases and is used by many companies such as Amazon, Facebook, Google, Alibaba, etc. In my current job we deploy MySQL widely within the company, we had our MySQL 5.6 own fork and moving towards MySQL 8.0 currently in a branch. We also have an “new” storage engine built on top of RocksDB, not surprisingly called MyRocks, which lives under storage/rocksdb folder in the MySQL 5.6 fork.

On a 10000-feet view, the architecture of MySQL server looks like this:

1. Connection Management / Authentication
2. Table/Schema Management/Caching
3. SQL Parser
4. SQL Optimizer and Query Executioner
5. Execution Engine
6. Replication and logging

If you dive deeper, an execution engine itself could include following pieces:

1. Transaction / MVCC / Locking / Snapshot support
2. Schema/Metadata
3. In-memory core database data structure (B+ tree / LSM tree / etc) and operation (insert/delete/update) for records
4. Indexing data structures, and searching/updating
5. Logging, Checkpointing & Recovery
6. Database storage persistence
7. Caching (disk blocks/pages, etc)

One of the most amazing features in MySQL is to swap the underlying storage engine while keeping the same upper layers - this way you can have different in-memory and on-disk representation of databases for different workloads, while keeping the same SQL execution functionality so the client wouldn’t even know the underlying storage engine have changed. The default storage engine is InnoDB - a B+ tree based database storage engine, and the one that we have is MyRocks which is built on top of RocksDB, a LSM-tree based database storage engine. There is an API layer called handler that storage engine need to implement/override. You can go to Comparison of MySQL database engines to see a list of common storage engines in MySQL.

Of course, the statement that they wouldn’t know the storage engine has changed is not entirely accurate. There are specific configurations you might need to tune / config the storage engine to your needs, and different storage engine has different performance / behavior / features / capabilities, so it’s not completely transparent.

Building

You can obtain source code from MySQL website, but most folks probably would prefer a github mirror:

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

This contains the latest MySQL 8.0.16.

In a typical Ubuntu system, you need to install following dependencies:

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

All my instructions below are tested on a Azure Linux Ubuntu 18.04 VM and on a MacBook Pro 2018. They may vary slightly due to your configuration/distribution if you are on a unix/linux system. Getting it to work on Windows requires installing OpenSSL binaries and GNU Bison. If you are using latest Visual Studio 2019, you may also need to apply a fix to boost 1.69.0 for a outdated VC workaround (a workaround for a workaround, essentially). Fortunately in most cases MySQL is pretty good about telling you exactly what is missing and where to download them.

Now let’s create a debug directory to store all our build files, and start the debug build:

mkdir debug
cd debug
cmake .. -DWITH_DEBUG=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=~/boost_1_69_0
make

1. WITH_DEBUG=1 requests a debug build, which makes debugger easier
2. DOWNLOAD_BOOST=1 WITH_BOOST=~/boost_1_69_0 downloads the boost at ~/boost_1_69_0 (that’s the version MySQL is asking for), and will skip the downloading if it is already there

One the build is done, you can find everything under debug/bin.

Don’t change the build directory after the fact once you done the build. The directory name is remembered and changing that naming requires a rebuild.

Running a test

To validate that we indeed have a working MySQL build, let’s try running a quick test called select_all.

To run any test, there is a script mysql-test-run.pl located under the mysql-test directory from the build directory, and it takes a test name in the form of <testname> or <testsuite>.<testname>:

cd debug/mysql-test
./mysql-test-run.pl select_all

This runs the test under mysql-test/t/select_all.test with baseline mysql-test/r/select_all.result. It runs a simple test language containing test directives/commands and SQL commands, and compare the output with the baseline. If the output diverges from the baseline the test would fail, otherwise it would pass. Simple enough, right?

Actually, not quite. The testing of MySQL can get quite complicated when it involves multiple connections / servers communicating with each other. And stablizing the results so that they are not affected by external environment / code changes can be also an headache.

Here is what you should see:

[~/local/github/mysql-server/debug/mysql-test, 8.0, 51s, SUDO]: ./mysql-test-run.pl select_all 
Logging: /home/yzha/local/github/mysql-server/mysql-test/mysql-test-run.pl  select_all       
MySQL Version 8.0.16                                                                         
Checking supported features                                                                  
 - Binaries are debug compiled                                                               
Using 'all' suites                                                                           
Collecting tests                                                                             
Checking leftover processes                                                                  
Removing old var directory                                                                   
Creating var directory '/home/yzha/local/github/mysql-server/debug/mysql-test/var'             
Installing system database                                                                   
Using parallel: 1                                                                            
                                                                                             
==============================================================================               
                  TEST NAME                       RESULT  TIME (ms) COMMENT                  
------------------------------------------------------------------------------               
[100%] main.select_all                           [ pass ]  36259                             
------------------------------------------------------------------------------               
The servers were restarted 0 times                                                           
The servers were reinitialized 0 times                                                       
Spent 36.259 of 70 seconds executing testcases                                               

Launching and connecting

Running a test seems straight-forward enough. If you want to launch mysql server and run some SQL commands against it, it takes a bit of work.

First we need to have mysqld initializes a blank data directory:

cd debug/bin
./mysqld --initialize

In Windows you’ll need to add --console to write error output to screen. Otherwise it’s only available in the .err log file.

You should see:

2019-06-05T05:16:31.376510Z 0 [System] [MY-013169] [Server] /datadrive/github/mysql-server/debug/runtime_output_directory/mysqld (mysqld 8.0.16-debug) initializing of server in progress as process 70030
2019-06-05T05:16:44.066787Z 5 [Note] [MY-010454] [Server] A temporary password is generated for [email protected]: <.....>
2019-06-05T05:16:53.317610Z 0 [System] [MY-013170] [Server] /datadrive/github/mysql-server/debug/runtime_output_directory/mysqld (mysqld 8.0.16-debug) initializing of server has completed

Note the temporary password generated in the second line. You’ll need it later.

This means that mysqld has successfully initialized at debug/data directory:

'#innodb_temp'   auto.cnf   ca-key.pem   ca.pem   client-cert.pem   client-key.pem   ib_buffer_pool   ib_logfile0   ib_logfile1   ibdata1   mysql   mysql.ibd   performance_schema   private_key.pem   public_key.pem   server-cert.pem   server-key.pem   sys   undo_001   undo_002

Now we can finally start the server:

cd debug/bin
./mysqld --debug

In Windows you’ll need to add --console to write error output to screen. Otherwise it’s only available in the .err log file.

--debug switch means we start the mysql server in debug mode.

Now launch another terminal / TMUX window / whatever, and connect to the mysql server:

cd debug/bin
./mysql -uroot --socket=/tmp/mysql.sock -p

You should see:

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.16-debug Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

However we are not quite done yet. Mysql will ask us to change the password - you can do it by using the following:

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

Now any future login can be done using this new password you just gave.

Finally we can run some SQL command!

mysql> SELECT @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.16-debug |
+--------------+
1 row in set (0.00 sec)

Before I leave, let me address a question that is absolutely going to be asked - how do I terminate the server gracefully? CTRL+C doesn’t work anymore.

The right way is to use mysqladmin:

cd debug/bin
./mysqladmin -uroot -p shutdown

You’ll see the server waving goodbye:

2019-06-05T05:54:09.028071Z 30 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.16-debug).
2019-06-05T05:54:10.670124Z 0 [System] [MY-010910] [Server] /datadrive/github/mysql-server/debug/runtime_output_directory/mysqld: Shutdown complete (mysqld 8.0.16-debug)  Source distribution

Debugging

In Linux/Mac debugging is relatively straightforward and isn’t any different from other applications. My personal recommendation is to use Visual Studio Code and setup lldb/gdb debugging there.

For Windows, the obvious choice is Visual Studio (2019 Community Edition is the one I’ve tested on). However it looks like mysqld is launching another mysqld instance that does the real work, so F5 debugging that mysqld from Visual Studio requires Child Process Debugging Power Tool in order for your breakpoints to hit since they need to be set in the child mysqld process, not the parent. Of course attaching to the correct mysqld would always work regardless without the help of the Child Process Debugging Power Tool.

What’s next

I’m planning a series of articles that will go through many interesting aspects of MySQL:

1. A quick tour of the source code and important concepts in MySQL source
2. How is the parsing and AST tree generation done for complex statements
3. How are statement being executed in MySQL
4. How to add your own command
5. How does MySQL optimizer / query execution work
6. How does plugin / storage engine work
7. How does system variables work
8. How does replication work
9. How does SHOW command work
10. How does binlog work

I’m also planning to write about MyRocks, as well as RocksDB / LevelDB / InnoDB, but I’ll priorize MySQL articles first as they lay down a nice foundation for rest of the stuff and this also serves as documentation when people get lost in the vast amount of MySQL source code.

Let me know what do you think about the article and/or if you are running into issues. Feel free to suggest topics as well. But I probably can’t help much with your DBA questions…