Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.8.3, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
-
Ubuntu 20.04
Description
I used my fuzzing tool to test MariaDB and found a transaction-related bug that make server produce incorrect results.
Mariadb installation
1) cd mariadb-10.8.3
2) mkdir build; cd build
3) cmake .. -DCMAKE_BUILD_TYPE=Debug
4) make -j12 && sudo make install
Setup the environment
1) export ASAN_OPTIONS=detect_leaks=0
2) /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql &
3) /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql # set up the database
Reproduce bug
/usr/local/mysql/bin/mysql -uroot -Dtestdb # set up 3 transactions T0, T1 and T2
Txn 0> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Txn 0> START TRANSACTION;
Txn 2> START TRANSACTION;
Txn 0> update t_j_eqsc set wkey = 37, c_fm792b = PI();
Txn 0> COMMIT;
Txn 1> START TRANSACTION;
Txn 2> insert into t_j_eqsc (wkey, pkey) values (79, 162000);
Txn 2> ROLLBACK;
Txn 1> select * from t_j_eqsc
where t_j_eqsc.c_fm792b not in (
select PI() as c0 from t_xqlwp as ref_0); --- output 10 rows
Txn 1> COMMIT;
/usr/local/mysql/bin/mysql -uroot -Dtestdb
Txn 0> update t_j_eqsc set wkey = 37, c_fm792b = PI();
Txn 0> select * from t_j_eqsc
where t_j_eqsc.c_fm792b not in (
select PI() as c0 from t_xqlwp as ref_0); --- output empty
Testcase 1 and Testcase 2 should produce the same results. However, the SELECT statement in Testcase 1 output 10 rows while the SELECT statement in Testcase 2 output empty.
Analyzing the test case, I think the SELECT statement in Testcase 1 produces incorrect results. Because the UPDATE in txn 0 has changed t_j_eqsc.c_fm792b to PI(), the WHERE clause in SELECT in txn 1 will be violated and thus the SELECT should output empty.
Attachments
Issue Links
- relates to
-
MDEV-29083 MariaDB produce different results for INSERT statement when using transaction
- Open
-
MDEV-29123 Incorrect results of SELECT statement found by transaction comparison
- Confirmed
-
MDEV-29398 undermined result of SELECT statements
- Confirmed