Finding a Regression in MySQL Source Code: A Case Study

finding regression in mysql sourceAt the Percona engineering team, we often receive requests to analyze changes in MySQL/Percona Server for MySQL behavior from one version to another, either due to regression or a bug fix (when having to point out to a customer that commit X has fixed their issue and upgrading to a version including that fix will solve their problem).

In this blog post, we will analyze the approach used to fix PS-7019 – Correct query results for LEFT JOIN with GROUP BY.

Each release comes with a lot of changes. For example, the difference between MySQL 8.0.19 to 8.0.20:

737K lines in 4495 files have changed from one minor version to another.

8.0.20 alone has 1966 commits. Analyzing each one of the files or either commits isn’t practical. Let’s have a look at how we can narrow down the issue to a particular commit.

Git Bisect

Git bisect is a powerful command in git. It navigates through commit history using the well-known binary search algorithm to efficiently find the offending commit. We basically need to specify a point that we know is bad and ideally, (if known) a starting point where we know the issue is not present. With that information, it will sort the commit from bad and good and start in the middle point. We will then have to evaluate if that point is good or bad. Based on that evaluation, it will decide to move forward or backward to the next half of commits following the binary search algorithm. The below flow helps to demonstrate it in action:

git bisect step 1

We start with a range of 20 commits, and we instruct git bisect that commit 1 is a good commit (does not have the issue) and commit 20 is a bad commit. With this information, it will checkout commit 10. Then we test this particular commit. On the above example, commit 10 is still bad, so we can infer that commits between 10 and 20 are all bad, move the upper mark (bad commit) to 10 and we move the working range to the bottom half of commits (10 to 1/ 8 commits to test).

git bisect step 2

At this point, git will stop a commit 5, and we check that 5 is still a good commit. We no longer need commits from 1 to 4 since we know they are all good. This time we move the lower mark (good commit) to 5, cutting down the working range to commits between 5 to 10 (4 commits to test).

git bisect step 3

Stopping at commit 7, we have validated that it is a bad commit. As you can imagine by now, git will change the upper mark to commit 7. This leaves us to a single commit to test.

git bisect step 4

As a final step in our example, we validated commit 6 and it is still a bad commit. At this point, we don’t have any further commit to test between upper and lower marks. This means we have found the first commit introducing a regression.

MySQL MTR

MySQL has a powerful test framework – MySQL Test Run, a.k.a. MTR. For brevity, I will not enter too deep into it during this post. Readers can find more information at online documentation and in this webinar.

In the scope of git bisect, we will be using MTR as validation to test each commit to verify if it is a good or bad commit.

Case Study

PS-7019 describes an issue with GROUP BY queries starting to happen on MySQL 8.0.20, where queries are returning different results from MySQL 8.0.19:

As you can see above, t2_id for the first line returns 1000 on 8.0.19 while on 8.0.20 it returns NULL. We will use the above example as a test case.

To get started, let me explain how my directories are organized:

We also need to set up the test case to validate each commit and the expected result of the test case. We can create the result file manually or we can instruct MTR to record it. MTR is sensitive to white-spaces, uppercase, and so on. As some advice, always get MTR to record the result file for you.

Assuming you have a MySQL version without the issue, set up the test case by running:

With all the setup done, move t/bisect.test and r/bisect.result files into your source tree and let’s start git bisect showing bad and good commit. Here we will be passing the release tags instead of commit hash:

At this point, git placed my src tree to 780a3f8418b87a8ac7d754050c5303b2658c3dcb commit. Now we will use git bisect run passing a shell script that will run a few steps to validate each commit:

  1. Enter the build directory
  2. Run cmake & make to recompile the server with the changes from each commit
  3. Start mtr testing
  4. Based on the exit code of MTR we will exit our script

Git bisect will consider exit code 0 as a good commit and everything else as a bad commit.

The above command will take a while to run depending on your hardware. It will have to recompile MySQL a few times (roughly 10 steps as shown at git bisect start) until it finds the offending commit.

With that information, we have limited the scope of the research to just a single commit. Then we can focus the analysis into what has changed there. For this particular case, the fix was simple, just a missing test condition as you can see at this pull request.

The bisect history, showing all steps taken, is available via the log argument:

Summary

“The key is not to turn the screw,  it’s to know which screw to turn”. You probably have heard this phrase before. This was exactly the case here. Finding the single line that introduced the issue was 99% of the effort here. The fix was simple, once we identified it.

Git bisect is a really powerful tool that can save us a lot of time when troubleshooting code related regressions/fixes. The same approach demonstrated here can be used the other way around; to find a commit that fixed an issue. Also, you can extend it to any type of project that versioned under git. Manual validation via git bisect good/bad can be used instead of git bisect run to decide to which direction it should go next. In cases where a merge with a big list of commits land into your range, you may need to specify a list of commits to test. Also when a commit breaks compilation of the software, manual validation will be mandatory in both cases.

Share this post

Leave a Reply