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

[29 Sep 2017 10:50] Mark Schuart
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.
[29 Sep 2017 12:40] Umesh Shastry
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
[29 Sep 2017 14:10] Mark Schuart
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?
[2 Oct 2017 13:28] Mark Schuart
Hello Umesh,

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

Hope this helps
Mark
[7 Oct 2017 12:06] Umesh Shastry
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
[16 Oct 2017 10:15] Mark Schuart
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.
[17 Oct 2017 8:11] Umesh Shastry
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
[17 Oct 2017 11:23] Mark Schuart
I uploaded it again.
Please look for a file called mysql-bug-data-87911-2.zip
[17 Oct 2017 12:19] Umesh Shastry
Thank you, let me download and test at my end.

Regards,
Umesh
[19 Dec 2017 9:17] Mark Schuart
Could you find a solution?
Is it already fixed in current version?
[22 Jun 2018 4:55] Umesh Shastry
Imho - Let us call this as a duplicate of Bug #91362 since it is fully verified.

Thanks,
Umesh