MySQL Bugs: #87911: Wrong Query Result immediately after a large delete operation

Bug #87911	Wrong Query Result immediately after a large delete operation
Submitted:	29 Sep 2017 10:50	Modified:	22 Jun 2018 4:55
Reporter:	Mark Schuart	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.18, 5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After a large delete operation using PKs a query returns only one row instead of over 5000. If we delay the query we get the correct result.
5.7.10 and 5.7.17 seams to be not effected.

How to repeat:
It only happens on our upgraded database. So we can't give instruction how to reproduce it.

Hello Mark,

Thank you for the report.
In order to confirm this issue at our end, may I request you to please provide complete logical dump of the data, exact SQL queries(along with explain plan for the SELECT) and configuration details from your environment? This would help us to reproduce the reported issue at our end.   
If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-87911.zip) and upload one to sftp.oracle.com. Your Oracle Web account (m.schuart@bachmann.info) and a client that supports SFTP are required in order to access the SFTP server.

More upload specific details are in the "Files" section of this bug report.

Thanks,
Umesh

The problem does not occur when I recreate the tables, so a logical dump of affected tables wont help and I can't give you the whole DB.
It seams to be a timing problem depending on the current internal structure of pages, mysqlcheck nor innochecksum doesn't find any error.
Is there any other possibility to verify it, could we arrange a WebEX or TeamViewer session?

Hello Umesh,

I removed all unnecessary Tables from the database and uploaded it including a test case.

Hope this helps
Mark

Thank you Mark, for providing requested data(looks like you have provided entire datadir). Could you please also post conf details(if not defaults),exact delete, and select statement which showing the problem? Please mark it as private when you post here.

Regards,
Umesh

The uploaded zip-file contains
- my.ini with my configuration it is almost the default cfg
- README-file with detailed explanation
- sql-files with all the statements
- bat-file to run the test
- out-files with the results from my run of the bat-file

If the zip-file is already deleted please let me know.

The Symptom:
If I run the statements from test.sql I get different results from selectOnly.sql depending on the time between both calls.

I'm not seeing any zip file with name mysql-bug-data-87911.zip etc Please re-upload and let me know once you have uploaded.

I've tried with dummy schema/data but not seeing the issue even with millions of rows. Also, as you mentioned in original post "If we delay the query we get the correct result." sounds like some other issue.

Thanks,
Umesh

I uploaded it again.
Please look for a file called mysql-bug-data-87911-2.zip

Thank you, let me download and test at my end.

Regards,
Umesh

Could you find a solution?
Is it already fixed in current version?

Imho - Let us call this as a duplicate of Bug #91362 since it is fully verified.

Thanks,
Umesh