Rollbacked inserts remain permanently in table

To run, please branch the Random Query Generator from and then execute:

$ perl runall.pl \
  --mem \
  --queries=1000000 \
  --duration=300 \
  --basedir=/build/bzr/mysql-6.0 \
  --mysqld=--plugin-dir=/build/bzr/pbxt/src/.libs/\
  --mysqld=--plugin-load=PBXT=libpbxt.so \
  --engine=PBXT \
  --grammar=bug315564.yy \
  --gendata=conf/transactions.zz \
  --validator=DatabaseConsistency \
  --mysqld=--loose-lock-wait-timeout=1 \
  --mysqld=--log-output=file

This will start a random workload containing UPDATE and INSERT statements. Shortly after takeoff, the test will complain that the average of all integers in the table has diverged from the desired. This is because NULL values have appeared in the table, even though the SQL grammar specifies that every INSERT of NULLs should be followed by a ROLLBACK. You can verify in the query log that this is indeed the case.

Philip,

Thanks for the report. However I cannot reproduce the problem on 5.1.30. What I get is:

....
# 23:04:56 Validators: DatabaseConsistency
# 23:04:56 Starting 10 processes, 1000000 queries each, duration 300 seconds.
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: COMMIT
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:56 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: SET AUTOCOMMIT=OFF
# 23:04:57 Bad checksum table: table10_pbxt_int_autoinc; sum: 2400; affected_rows:0E0; query: START TRANSACTION
# 23:04:57 Started periodic reporting process...
# 23:05:02 Killing periodic reporting process with pid 22114...
....

so the tool reports some problem, in the query log I can see the following (an excerpt):

                    5 Query SET AUTOCOMMIT=OFF
                    5 Query START TRANSACTION
                    6 Query SELECT SUM(`int_key`) + SUM(`int`)
                        FROM `table10_pbxt_int_autoinc`
                    5 Query SHOW TABLES
                    5 Query START TRANSACTION
                    6 Query SELECT SUM(`int_key`) + SUM(`int`)
                        FROM `table10_pbxt_int_autoinc`
                    5 Query INSERT INTO `table10_pbxt_int_autoinc` ( `pk` ) VALUES ( NULL ) , ( NULL ) , ( NULL )
                    6 Query SELECT SUM(`int_key`) + SUM(...

Hello,

Please pull from lp:~randgen/randgen/main and try again. I have not tried with 5.1, only with 6.0.

Philip Stoev

Ok, now I can repro it with 5.1.30:

...
# 01:40:49 Bad average for table: table10_pbxt_int_autoinc; average1: 200.0000; average2: 198.9455; count: 569; affected_rows: 0E0; query: ROLLBACK
# 01:40:49 Bad average for table: table10_pbxt_int_autoinc; average1: 200.0000; average2: 198.9455; count: 569; affected_rows: 0E0; query: COMMIT
# 01:40:49 Bad average for table: table10_pbxt_int_autoinc; average1: 200.0000; average2: 198.9455; count: 569; affected_rows: 0E0; query: ROLLBACK
# 01:40:49 Bad average for table: table10_pbxt_int_autoinc; average1: 200.0000; average2: 198.9455; count: 569; affected_rows: 0E0; query: ROLLBACK
# 01:40:49 Bad average for table: table10_pbxt_int_autoinc; average1: 200.0000; average2: 198.9455; count: 569; affected_rows: 0E0; query: COMMIT
#
...

and

mysql> select * from table10_pbxt_int_autoinc where `int` is null;
+---------+------+------+
| int_key | pk | int |
+---------+------+------+
| NULL | 1283 | NULL |
| NULL | 1284 | NULL |
| NULL | 1285 | NULL |
+---------+------+------+
3 rows in set (0.00 sec)

I assume this is what you meant.

Yes this is what I meant. Those NULLs should not be there.

Please accept my apologies for the low quality of the bug reports -- I am freaking out a bit those days :-) I will work with you to move all the bugs to the "Verified" state.

Thank you.

Philip,

you don't have to apologize. Your reports are very valuable for us. Even if you are not sure or cannot permanently reproduce a problem it better to report than not.

Thanks again!

The issue of uncommitted (rolled back) records being visible in separate concurrent REPEATABLE-READ transactions has also been observed with the test suite described in bug 346808, available at http://launchpadlibrarian.net/24210604/stress_tx_rr.tgz, against PBXT 1.0.07-rc and MySQL 6.0.8, using 50 client threads.

Unable to determine if the issue has been fixed, due to crashing bug https://bugs.launchpad.net/bugs/346808