Description:
I used my fuzzer to test MySQL server and found a transaction bug.
The ROLLBACK transaction can affect the result of COMMIT transaction by changing the value of "auto_increment" columns (primary key in our test case).
How to repeat:
** Setup the environment **
/usr/local/mysql/bin/mysqld_safe &
/usr/local/mysql/bin/mysql -uroot
mysql> create database testdb;
mysql> \q
/usr/local/mysql/bin/mysql -uroot -Dtestdb < mysql_bk.sql (mysql_bk.sql is in the attached)
By this way, MySQL imports a table defined as followed:
CREATE TABLE `t_zcfqb` (
`wkey` int(11) DEFAULT NULL,
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`c_rvm_p` text DEFAULT NULL,
`c_dl_pmd` int(11) DEFAULT NULL,
`c_avevqc` text DEFAULT NULL,
`c_ywxlqb` double DEFAULT NULL,
`c_sqqbbd` int(11) DEFAULT NULL,
`c_2wz8nc` text DEFAULT NULL,
`c_qyxu0` double DEFAULT NULL,
`c_slu2bd` int(11) DEFAULT NULL,
`c_ph8nh` text DEFAULT NULL,
PRIMARY KEY (`pkey`)
);
Note that pkey is an auto-increment primary key.
Then, we use test case 1 and test case 2 to test MySQL under the environment we set up. They should return the same results but they did not.
** Test case 1 **
// setup two connection, conn_0 and conn_1
conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb
conn_1> mysql -h "127.0.0.1" -u root -P 4000 -D testdb
conn_1> BEGIN OPTIMISTIC;
conn_0> BEGIN OPTIMISTIC;
conn_1> insert into t_zcfqb (wkey, pkey, c_dl_pmd, c_avevqc, c_sqqbbd, c_2wz8nc, c_qyxu0, c_slu2bd) values
(182, 264000, null, 'biiumc', null, 'dwzl6d', 93.90, null);
conn_0> insert into t_zcfqb (wkey, c_rvm_p, c_avevqc, c_ywxlqb, c_sqqbbd, c_2wz8nc, c_qyxu0, c_ph8nh)
select
121 as c0,
ref_0.c_h1m_zb as c1,
ref_0.c_gsbzrd as c2,
ref_0.c_hdnifc as c3,
(select wkey from t_nvues order by wkey limit 1 offset 5)
as c4,
ref_0.c_h1m_zb as c5,
ref_0.c_hdnifc as c6,
ref_0.c_h1m_zb as c7
from
t_nvues as ref_0
where (0 <> 0)
or (ref_0.wkey is not NULL);
conn_0> select pkey from t_zcfqb where wkey = 121;
conn_1> ROLLBACK;
conn_0> COMMIT;
** Output of SELECT statement in conn_0 (Test case 1) **
+--------+
| pkey |
+--------+
| 264001 |
| 264002 |
| 264003 |
| ... |
| 264049 |
+--------+
49 rows in set (0.00 sec)
** Test case 2 **
conn_0> mysql -h "127.0.0.1" -u root -P 4000 -D testdb
conn_0> BEGIN OPTIMISTIC;
conn_0> insert into t_zcfqb (wkey, c_rvm_p, c_avevqc, c_ywxlqb, c_sqqbbd, c_2wz8nc, c_qyxu0, c_ph8nh)
select
121 as c0,
ref_0.c_h1m_zb as c1,
ref_0.c_gsbzrd as c2,
ref_0.c_hdnifc as c3,
(select wkey from t_nvues order by wkey limit 1 offset 5)
as c4,
ref_0.c_h1m_zb as c5,
ref_0.c_hdnifc as c6,
ref_0.c_h1m_zb as c7
from
t_nvues as ref_0
where (0 <> 0)
or (ref_0.wkey is not NULL);
conn_0> select pkey from t_zcfqb where wkey = 121;
conn_0> COMMIT;
** Output of SELECT statement in conn_0 (Test case 2) **
+-------+
| pkey |
+-------+
| 77001 |
| 77002 |
| 77003 |
| ... |
| 77049 |
+-------+
49 rows in set (0.01 sec)