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
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.7.22
• OS: Any
• 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] MySQL Verification Team

Development is  already informed about your note.

[11 Apr 2019 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 2019 13:15] MySQL Verification Team

Thanks, Daniel.

[26 Jun 2019 7:47] MySQL Verification Team

Bug #95991 marked as duplicate of this one

[29 Jul 2019 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)