Bug #65245 RQG: Query returns different result under REPEATABLE READ
Submitted: 8 May 2012 18:28 Modified: 13 Oct 2012 3:27
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.23 OS:Linux
Assigned to: CPU Architecture:Any
Tags: regression, repeatable read
Triage: Needs Triage: D2 (Serious)

[8 May 2012 18:28] Davi Arnaut
Description:
The RQG test grammar repeatable_read.yy started failing after the patch for Bug#13807811 (btr_pcur_restore_position() can skip a record) was merged into MySQL 5.5.23. The test is designed to test the REPEATABLE READ isolation level by re-executing SELECT using various table access methods within the same transaction.

Sample output from failure:

# 2012-05-08T18:20:36 Query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8 returns different result when executed with additional predicate '/* no additional predicate */' (18 vs. 17 rows).
# 2012-05-08T18:20:36 --- /tmp//randgen5771-1336501236-server0.dump	2012-05-08 18:20:36.000000000 +0000
# 2012-05-08T18:20:36 +++ /tmp//randgen5771-1336501236-server1.dump	2012-05-08 18:20:36.000000000 +0000
# 2012-05-08T18:20:36 @@ -15,4 +15,3 @@
# 2012-05-08T18:20:36  9
# 2012-05-08T18:20:36  9
# 2012-05-08T18:20:36  9
# 2012-05-08T18:20:36 -9
# 2012-05-08T18:20:36 Full result from the original query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9
9
# 2012-05-08T18:20:36 Full result from the follow-up query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8 /* no additional predicate */
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9
# 2012-05-08T18:20:36 Executing the same queries a second time:
# 2012-05-08T18:20:36 Full result from the repeat of the query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9
# 2012-05-08T18:20:36 Full result from the repeat of the query: SELECT `col_int` FROM `table10_innodb_int_autoinc` WHERE `pk` > 8 /* no additional predicate */
0
1
1
2
2
2
2
4
5
5
5
6
7
8
9
9
9

How to repeat:
export RQG_HOME=~/randgen/ MASTER_MYPORT=3306

perl $RQG_HOME/gentest.pl \
 --dsn=dbi:mysql:host=127.0.0.1:port=$MASTER_MYPORT:user=root:database=test \
 --gendata=$RQG_HOME/conf/transactions/transactions.zz \
 --grammar=$RQG_HOME/conf/transactions/repeatable_read.yy \
 --validator=RepeatableRead \
 --reporters=Deadlock,ErrorLog,Backtrace \
 --engine=InnoDB \
 --queries=100000 \
 --duration=60
[8 May 2012 18:52] Sveta Smirnova
Thank you for the report.

Verified as described.
[13 Oct 2012 3:27] Davi Arnaut
Fixed by patch for Bug#14399148 - InnoDB tables under load produce duplicate copies of rows in queries.

Revision-id: marko.makela@oracle.com-20120809065529-utqnp7akvug8zmui
[19 Oct 2012 9:39] Marko Mäkelä
Davi, thanks for verifying the fix. Again, I am sorry for breaking the code.