Bug #91032 InnoDB 5.7 Primary key scan lack data
Submitted: 26 May 2018 8:11 Modified: 15 Jun 2018 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 2018 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 2018 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 2018 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 2018 1:17] Sunny Bains
The suggest fix seems to work.
[31 May 2018 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 2018 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.
[22 Nov 2018 17:24] Yura Sorokin
The same problem should be fixed in 5.6 branch as well.

in 5.6.42 there is still no
'm_prebuilt->m_end_range = false;'
inside 'ha_innobase::reset_template(void)'

See
https://bugs.mysql.com/bug.php?id=91091
and
https://jira.percona.com/browse/PS-4513
for more details.
[23 Nov 2018 12:47] Sinisa Milivojevic
Development is  already informed about your note.
[11 Apr 22:19] Daniel Price
Posted by developer:
 
Patch ported to 5.6 The existing changelog entry was added to the 5.6.45 release notes.
[23 Apr 13:15] Sinisa Milivojevic
Thanks, Daniel.
[26 Jun 7:47] Umesh Shastry
Bug #95991 marked as duplicate of this one
[29 Jul 12:35] Yura Sorokin
Fixed in 5.6.45
InnoDB: A query that scanned the primary key of a table did not return the expected result. (Bug #28104394, Bug #91032)