Bug #91032 InnoDB 5.7 Primary key scan lack data
Submitted: 26 May 8:11 Modified: 15 Jun 17:25
Reporter: raolh rao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.22 OS:Any
Assigned to: CPU Architecture:Any

[26 May 8:11] raolh rao
Description:
when do a pk range scan operation on a table, the results may lack lots of data in a race condition ,  return only one record  no matter how many actual records tt should been.

How to repeat:
1. prepare data

create table t1(id int auto_increment primary key);
insert into t1 values(1),(2),(3),(4),(5),(6),(121);

2. start testing

session 1.
1)start a transaction
begin;
 
sesssion 2.
1) start a transaction
begin;

2)insert more than 100 records
insert into t1 values(7),(8),(9) ....(110);
 
session 1.
2)pk range first
mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*)    |
+----------+
|        6        |
+----------+
 
3) pk range second
mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*)   |
+----------+
|        1        |
+----------+

Suggested fix:
set m_prebuilt->m_end_range=0 in ha_innobase::index_end()
[31 May 1:02] Sunny Bains
Session 1:
mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

Session 2:
...
mysql> insert into t1 values(110);
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|       99 |
+----------+
1 row in set (0.00 sec)
[31 May 1:07] Sunny Bains
The how to reproduce is missing a COMMIT in session 2. If I commit session 2 and then rerun the query in session 1. I get very strange behaviour.

mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1 where id>0 and id<100;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
[31 May 1:17] Sunny Bains
The suggest fix seems to work.
[31 May 4:13] Jimmy Yang
5.7 specific bug, the resetting line is missing in 5.7 when patch is patched from 8.0.
[15 Jun 17:25] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.24, 8.0.13  release, and here's the changelog entry:

A query that scanned the primary key of a table did not return the
expected results.