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)
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)