Bug #107066 ROLLBACK transaction affects the result of COMMIT transaction
Submitted: 20 Apr 8:18 Modified: 20 Apr 11:53
Reporter: Zuming Jiang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:8.0.28, 5.7.37 OS:Ubuntu (20.04)
Assigned to: CPU Architecture:Any

[20 Apr 8:18] Zuming Jiang
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)
[20 Apr 8:20] Zuming Jiang
mysql_bk.sql

Attachment: mysql_bk.sql (application/octet-stream, text), 7.42 KiB.

[20 Apr 8:20] Zuming Jiang
mysql_bk.sql

Attachment: mysql_bk.sql (application/octet-stream, text), 7.42 KiB.

[20 Apr 11:53] MySQL Verification Team
Hello Zuming Jiang,

Thank you for the report and test case.

regards,
Umesh