Description:
I used my fuzzing tool to test MySQL and found a transaction-related bug that make the server strange results.
How to repeat:
*MySQL installation (debug mode, enable ASAN)**
tar -zxf mysql-8.0.30.tar.gz mv mysql-server-mysql-8.0.30 mysql_source/
cd /home/mysql/mysql_source/build
cmake -DWITH_ASAN=ON -DWITH_ASAN_SCOPE=ON -DWITH_DEBUG=ON ../ -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/mysql
make -j12
sudo make install
sudo chown -R mysql:mysql /usr/local/mysql/
/usr/local/mysql//bin/mysqld --initialize-insecure --thread_stack=512k
*Setup the environment*
1) /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql &
2) /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql # set up the database, mysql_bk.sql is attached.
*Reproduce bug*
/usr/local/mysql/bin/mysql -uroot -Dtestdb # set up for the transaction T0
/usr/local/mysql/bin/mysql -uroot -Dtestdb # set up for the transaction T1
T0> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T1> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T1> START TRANSACTION;
T1> update t_g6ckkb set wkey = 162;
T0> START TRANSACTION;
T0> select * from t_g6ckkb;
T1> COMMIT;
T0> select * from t_rpjlsd where t_rpjlsd.c_pfd8ab <= (select min(wkey) from t_g6ckkb);
+------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
| wkey | pkey | c_trycnb | c_5b3h_c | c_pfd8ab | c_mvgo1c | c_2twe2d | c_nmcpzc | c_loj6e | c_veoe1 |
+------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
| 43 | 243000 | 30 | NULL | 8 | NULL | 70 | NULL | awnrab | 39.83 |
+------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
1 rows in set (0.006 sec)
T0> update t_rpjlsd set wkey = 63 where t_rpjlsd.c_pfd8ab <= (select min(wkey) from t_g6ckkb);
---
--- Query OK, 2 rows affected (0.003 sec)
--- Rows matched: 2 Changed: 2 Warnings: 0
---
T0> select * from t_rpjlsd where wkey = 63;
+------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
| wkey | pkey | c_trycnb | c_5b3h_c | c_pfd8ab | c_mvgo1c | c_2twe2d | c_nmcpzc | c_loj6e | c_veoe1 |
+------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
| 63 | 243000 | 30 | NULL | 8 | NULL | 70 | NULL | awnrab | 39.83 |
| 63 | 332000 | 68 | _pqr1c | 53 | 9g7bt | NULL | 75 | tb1ugc | 7.62 |
+------+--------+----------+----------+----------+----------+----------+----------+---------+---------+
2 rows in set (0.002 sec)
T0> COMMIT;
*Analysis*
The first SELECT in T0 and the UPDATE in T0 should handle the same rows because they use the same predicate (WHERE clause) and execute adjacently. However, the first SELECT in T0 outputs only 1 row while the UPDATE in T0 changes 2 rows. To make sure what the UPDATE in T0 changes, we use the second SELECT in T0, and it outputs the changed 2 rows.
Based on the analysis, I think it might be a bug triggering inconsistent read (first SELECT in T0) and write (UPDATE in T0).
