Description:
I used my fuzzer to test the MySQL server and found a transaction-related issue. This issue exposes that the MySQL server may return incorrect results of SELECT statements. The isolation level is READ COMMITTED.
How to repeat:
** Setup the environment **
/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot
mysql> create database testdb;
mysql> \q
Then, we use test case 1 and test case 2 to test MySQL separately under the environment we set up. They should return the same results but they did not.
---------- Test case 1 ----------
conn_0> /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached)
conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb
conn_0> SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --- set to READ COMMITTED isolation level
conn_1> mysql -h "127.0.0.1" -u root -P 4000 -D testdb
conn_1> SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --- set to READ COMMITTED isolation level
conn_0> START TRANSACTION;
conn_1> START TRANSACTION;
delete from t_8fhx8c;
ROLLBACK;
#Note: copy these three statements (START, DELETE, and ROLLBACK) at once, and then paste them to the terminal directly.
conn_0> select *
from
t_8fhx8c as ref_1
where ref_1.c_0byzvd not in (
select
nullif(19, 19) as c0
from
(select
ref_2.c_zov5kd as c0
from
t_8fhx8c as ref_2
) as subq_0
window w_u6cwrd as (partition by subq_0.c0));
#Note: this SELECT should be inputted to connection 1 as soon as possible after conn_1 has rollbacked.
conn_0> commit;
** Output of SELECT statement in conn_0 (Test case 1) **
+------+-------+----------+----------+----------+----------+----------+
| wkey | pkey | c_0byzvd | c_iskisc | c_zov5kd | c_gcqkxd | c_qhs9nb |
+------+-------+----------+----------+----------+----------+----------+
| 2 | 14000 | 97 | 84.74 | wa2sbb | 95 | 89.41 |
| 2 | 15000 | 86 | 21.11 | zronkb | 55 | 45.8 |
| 2 | 16000 | 14 | 19.5 | c2gzzd | 32 | 85.98 |
| ... | ... | ... | ... | ... | ... | ... |
| 12 | 75000 | 94 | 57.76 | l1ei4c | NULL | 58.27 |
+------+-------+----------+----------+----------+----------+----------+
34 rows in set (0.00 sec)
---------- Test case 2 ----------
conn_0> /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached)
conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb
conn_0> START TRANSACTION;
conn_0> select *
from
t_8fhx8c as ref_1
where ref_1.c_0byzvd not in (
select
nullif(19, 19) as c0
from
(select
ref_2.c_zov5kd as c0
from
t_8fhx8c as ref_2
) as subq_0
window w_u6cwrd as (partition by subq_0.c0));
conn_0> COMMIT;
** Output of SELECT statement in conn_0 (Test case 2) **
Empty set (0.00 sec)
--------------------
The SELECT statement print different results in test case 1 and test case 2, while they should be the same ideally.
Suggested fix:
The SELECT statement in test case 2 may return wrong results. Because nullif(19, 19) returns NULL, the SELECT statement should print the rows whose t_8fhx8c.c_0byzvd are not equal to NULL.
Description: I used my fuzzer to test the MySQL server and found a transaction-related issue. This issue exposes that the MySQL server may return incorrect results of SELECT statements. The isolation level is READ COMMITTED. How to repeat: ** Setup the environment ** /usr/local/mysql/bin/mysqld_safe & /usr/local/mysql/bin/mysql -uroot mysql> create database testdb; mysql> \q Then, we use test case 1 and test case 2 to test MySQL separately under the environment we set up. They should return the same results but they did not. ---------- Test case 1 ---------- conn_0> /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached) conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb conn_0> SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --- set to READ COMMITTED isolation level conn_1> mysql -h "127.0.0.1" -u root -P 4000 -D testdb conn_1> SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --- set to READ COMMITTED isolation level conn_0> START TRANSACTION; conn_1> START TRANSACTION; delete from t_8fhx8c; ROLLBACK; #Note: copy these three statements (START, DELETE, and ROLLBACK) at once, and then paste them to the terminal directly. conn_0> select * from t_8fhx8c as ref_1 where ref_1.c_0byzvd not in ( select nullif(19, 19) as c0 from (select ref_2.c_zov5kd as c0 from t_8fhx8c as ref_2 ) as subq_0 window w_u6cwrd as (partition by subq_0.c0)); #Note: this SELECT should be inputted to connection 1 as soon as possible after conn_1 has rollbacked. conn_0> commit; ** Output of SELECT statement in conn_0 (Test case 1) ** +------+-------+----------+----------+----------+----------+----------+ | wkey | pkey | c_0byzvd | c_iskisc | c_zov5kd | c_gcqkxd | c_qhs9nb | +------+-------+----------+----------+----------+----------+----------+ | 2 | 14000 | 97 | 84.74 | wa2sbb | 95 | 89.41 | | 2 | 15000 | 86 | 21.11 | zronkb | 55 | 45.8 | | 2 | 16000 | 14 | 19.5 | c2gzzd | 32 | 85.98 | | ... | ... | ... | ... | ... | ... | ... | | 12 | 75000 | 94 | 57.76 | l1ei4c | NULL | 58.27 | +------+-------+----------+----------+----------+----------+----------+ 34 rows in set (0.00 sec) ---------- Test case 2 ---------- conn_0> /usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached) conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb conn_0> START TRANSACTION; conn_0> select * from t_8fhx8c as ref_1 where ref_1.c_0byzvd not in ( select nullif(19, 19) as c0 from (select ref_2.c_zov5kd as c0 from t_8fhx8c as ref_2 ) as subq_0 window w_u6cwrd as (partition by subq_0.c0)); conn_0> COMMIT; ** Output of SELECT statement in conn_0 (Test case 2) ** Empty set (0.00 sec) -------------------- The SELECT statement print different results in test case 1 and test case 2, while they should be the same ideally. Suggested fix: The SELECT statement in test case 2 may return wrong results. Because nullif(19, 19) returns NULL, the SELECT statement should print the rows whose t_8fhx8c.c_0byzvd are not equal to NULL.