Bug #115109 Incorrect result when using secondary index.
Submitted: 24 May 2:38 Modified: 24 May 9:57
Reporter: Ju Brain Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.30 OS:CentOS
Assigned to: CPU Architecture:ARM

[24 May 2:38] Ju Brain
For some reason, we cannot offer you the detailed record or the ibd file.
But we did encouter this situation.

create table tbtmp(
  f1 char(64),
  f2 char(3),
  f3 char(17),
  f4 char(12),
  f5 char(18),
  ...(all 50 columns)
  primary key(f2,f3,f4,f5),
  key idx01(f2,f4)
  key idx02 (...)
  key idx03 (...)

After 10 days since the insert, we found that in one slave node that:
"select count(*) from tbtmp" returns 869745
"select count(*) from tbtmp force index(primary)" returns 869746
"select count(*) from tbtmp force index(idx02)" returns 869746
"select count(*) from tbtmp force index(idx03)" returns 869746

One specific record cannot be found when using the secondary index.

Then we look into the ibd file, we fould that the leaf node in idx01 doesn't contain the missing data.
We compose the leaf node data with f2f4f3f5, and all the other data can be found in the ibd file.

The system has been running for 4 years, and this is the first time we meet this   bug.
There is no rebooting or shutting down or master-slave delay or error message in log, everything looks like normal.
We have run out of way to slove this mystery and we are eager to receive your opinion.

How to repeat:
Saddly, we have tried for a long time. Cannot repeat.
[24 May 9:57] MySQL Verification Team
Hi MR. Brain,

Thank you for your bug report.

However, we must inform you that version 5.7 is deprecated for over a year.

If you can repeat your problem on the latest 8.0 release, please file a new bug report, but with a fully repeatable test case in the form of the set of SQL statements.