At 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:
1 2 3 4 | git diff mysql-8.0.19..mysql-8.0.20 | wc -l 737454 git diff mysql-8.0.19..mysql-8.0.20 --name-only | wc -l 4495 |
737K lines in 4495 files have changed from one minor version to another.
1 2 | git log mysql-8.0.19..mysql-8.0.20 --pretty=oneline | wc -l 1966 |
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:
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).
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).
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.
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | mysql> CREATE TABLE t1 (t1_id INT PRIMARY KEY AUTO_INCREMENT, t2_id INT DEFAULT NULL); Query OK, 0 rows affected (0,04 sec) mysql> CREATE TABLE t2 (t2_id INT PRIMARY KEY AUTO_INCREMENT, is_active TINYINT NOT NULL DEFAULT '1'); Query OK, 0 rows affected (0,01 sec) mysql> INSERT INTO t2 VALUES (2,1),(3,0),(1000,1); Query OK, 3 rows affected (0,01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t1 VALUES (1,1000),(2,5); Query OK, 2 rows affected (0,00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql 8.0.19> SELECT t1.t1_id,t1.t2_id,t2.t2_id FROM t1 LEFT JOIN t2 ON (t1.t2_id = t2.t2_id AND t2.is_active=1) GROUP BY t1_id ORDER BY t1_id LIMIT 2; +-------+-------+-------+ | t1_id | t2_id | t2_id | +-------+-------+-------+ | 1 | 1000 | 1000 | | 2 | 5 | NULL | +-------+-------+-------+ 2 rows in set (0,00 sec) mysql 8.0.20> SELECT t1.t1_id,t1.t2_id,t2.t2_id FROM t1 LEFT JOIN t2 ON (t1.t2_id = t2.t2_id AND t2.is_active=1) GROUP BY t1_id ORDER BY t1_id LIMIT 2; +-------+-------+-------+ | t1_id | t2_id | t2_id | +-------+-------+-------+ | 1 | 1000 | NULL | | 2 | 5 | NULL | +-------+-------+-------+ 2 rows in set (0,00 sec) |
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:
1 2 | /work/mysql/src - source code / git repository /work/mysql/bld - build directory, where cmake/make and mtr will run |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | cd mysql-test cat <<EOF > t/bisect.test --echo # --echo BUG #99398 / PS-7019: Wrong query results for LEFT JOIN with GROUP BY since 8.0.20 --echo # CREATE TABLE t1 (t1_id INT PRIMARY KEY AUTO_INCREMENT, t2_id INT DEFAULT NULL); CREATE TABLE t2 (t2_id INT PRIMARY KEY AUTO_INCREMENT, is_active TINYINT NOT NULL DEFAULT '1'); INSERT INTO t2 VALUES (2,1),(3,0),(1000,1); INSERT INTO t1 VALUES (1,1000),(2,5); SELECT t1.t1_id,t1.t2_id,t2.t2_id FROM t1 LEFT JOIN t2 ON (t1.t2_id = t2.t2_id AND t2.is_active=1) GROUP BY t1_id ORDER BY t1_id LIMIT 2; DROP TABLE t1,t2; EOF ./mtr bisect --record |
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:
1 2 3 | marcelo@marce-bld:/work/mysql/src$ git bisect start mysql-8.0.20 mysql-8.0.19 Bisecting: 983 revisions left to test after this (roughly 10 steps) [780a3f8418b87a8ac7d754050c5303b2658c3dcb] NULL Merge branch 'mysql-8.0' into mysql-trunk |
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:
- Enter the build directory
- Run cmake & make to recompile the server with the changes from each commit
- Start mtr testing
- 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.
1 2 3 4 5 6 7 8 9 10 | marcelo@marce-bld:/work/mysql/src$ git bisect run sh -c ' cd /work/mysql/bld/ cmake /work/mysql/src -DWITH_BOOST=/work/boost make -j 32 /work/mysql/bld/mysql-test/mtr bisect if [ "$?" -eq "0" ]; then exit 0 else exit 1 fi' |
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.
1 | 3039fac3969f7c1521863bfe1513631986d2b6bd is the first bad 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | marcelo@marce-bld:/work/mysql/src$ git bisect log # bad: [7d10c82196c8e45554f27c00681474a9fb86d137] Bug #31077699: NORMAL USER CAN CREATE VIEW/PROC/TRIG WITH DEFINER=USER_WITH_SYSTEM_USER_PRIV RB#24132 # good: [ea7d2e2d16ac03afdd9cb72a972a95981107bf51] Bug #30417719 ASSERTION FAILURE: FSP0FSP.CC:2371:N_USED_NOT_FULL > 0 || N_TOTAL_NOT_FULL == 0 git bisect start 'mysql-8.0.20' 'mysql-8.0.19' # bad: [780a3f8418b87a8ac7d754050c5303b2658c3dcb] NULL Merge branch 'mysql-8.0' into mysql-trunk git bisect bad 780a3f8418b87a8ac7d754050c5303b2658c3dcb # bad: [626c2f351ac9ecca7ef06b509a95e1cf44688fee] Bug #30573446: VARIOUS OPTIMIZER CLEANUPS [2/4, ref_by] git bisect bad 626c2f351ac9ecca7ef06b509a95e1cf44688fee # good: [df0d0968b82715b756ee6c6e8d691e184cd7061a] Merge branch 'mysql-8.0' into mysql-trunk git bisect good df0d0968b82715b756ee6c6e8d691e184cd7061a # bad: [160ff50b7b810cc1c610f2a7136c6e84cf441f37] Merge branch 'mysql-8.0' into mysql-trunk git bisect bad 160ff50b7b810cc1c610f2a7136c6e84cf441f37 # bad: [892ed3e4d32e0b6bfd13c72d7a9850a0bfbc4088] Merge remote-tracking branch '80bug/bug' into bug git bisect bad 892ed3e4d32e0b6bfd13c72d7a9850a0bfbc4088 # good: [f6fde14429d2dc23c85522ab432a5927fae36387] Null-merge from 8.0. git bisect good f6fde14429d2dc23c85522ab432a5927fae36387 # good: [f64d999b7febcc54f4151c1032cd0d38cdfda222] Merge branch 'mysql-8.0-downmix' into mysql-trunk-downmix git bisect good f64d999b7febcc54f4151c1032cd0d38cdfda222 # bad: [e9149f1fb8cc10bff89df708ce5ff5597e94929f] git bisect bad e9149f1fb8cc10bff89df708ce5ff5597e94929f # bad: [c5f8a626480925b544988ccac77878c83654d157] Bug#30460528: RENAME FIELD::REAL_MAYBE_NULL() TO FIELD::IS_NULLABLE() git bisect bad c5f8a626480925b544988ccac77878c83654d157 # good: [551692a91b5a006b901d006c4af5e68c964e318d] Merge branch 'mysql-8.0' into mysql-trunk git bisect good 551692a91b5a006b901d006c4af5e68c964e318d # bad: [3039fac3969f7c1521863bfe1513631986d2b6bd] Bug#30460528: RENAME FIELD::REAL_MAYBE_NULL() TO FIELD::IS_NULLABLE() git bisect bad 3039fac3969f7c1521863bfe1513631986d2b6bd # first bad commit: [3039fac3969f7c1521863bfe1513631986d2b6bd] Bug#30460528: RENAME FIELD::REAL_MAYBE_NULL() TO FIELD::IS_NULLABLE() |
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.
