MySQL Bugs: #86402: Insert returns incorrect result, unknown corruption

Bug #86402	Insert returns incorrect result, unknown corruption
Submitted:	22 May 2017 10:52	Modified:	24 May 2017 13:27
Reporter:	Shravan Rajinikanth	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	7.0.7-m61	OS:	Windows (Build 1703)
Assigned to:		CPU Architecture:	Any

Description:
Please note that the description here may appear vague since I am unsure of the source of the issue.

I am using MySQL Server 5.7.17 on Windows 10 x64 Build 1703, with all updates installed. I am developing my application on Visual Studio 2017 and ASP.NET Core. I use MySQL Workbench 6.3.9 for debugging.

The issue presented multiple different problems:

1. Database sometimes gets locked on its own and refuses INSERTs, DROP SCHEMAs, etc. I need to manually identify the offending transaction and kill it.

2. INSERTing rows (the database is initially empty) using command.ExecuteNonQuery() returns 1, indicating that the operation was successful, but SELECTing * in MySQL Workbench returns 0 rows. Any future SELECTs using the same ASP.NET instance return half the previous inserts (Interestingly, these appear to be _alternate_ entries of the original INSERTs). Re-INSERTing the failed rows results in the following exception:

MySql.Data.MySqlClient.MySqlException: Duplicate entry '9989744461' for key 'PRIMARY'
[The column is a BIGINT(20), UNIQUE, PRIMARY KEY]

So it seems aware that this is a duplicate entry even though a SELECT does not return any rows.

3. Re-creating the database and re-installing MySQL Server have no effect on the issue, however it only occurs on one development machine. It worked fine until one day a month or so ago and then just occurs continuously. I waited to report it since I was unsure if I was missing something trivial.

This is one exception that is thrown:

MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. ---> MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. ---> System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---

4. INSERTing records via MySQL Workbench's interface seems to work correctly. They are returned in subsequent SELECTs and also show up in SELECTs made from ASP.NET Core.

5. Repeating failed operations on MySQL Workbench complete successfully while those from ASP.NET Core do not, suggesting that the issue lies with the connector. However, everything works correctly on another system, using the same connector, suggesting that the issue lies with MySQL Server. As such, I am unsure of the cause of the problem.

How to repeat:
Unknown, but I will gladly run any experiments needed to identify the cause of the issue.

Suggested fix:
None.

I'll add that the "other machine" I refer to has similar specs as the first. Both run on Windows 10 x64 with all updates installed. MySQL Server and MySQL Workbench are the latest versions.

Hello Shravan,

Thank you for the bug report.
This is most likely duplicate of Bug #75917, please see Bug #75917.

Thanks,
Chiranjeevi.

Dear Chiranjeevi,

I can confirm that is not a duplicate of the bug you mentioned. My issue is with corruption and inconsistency in the database, not with the connection itself. The exception concerning the timeout is only a by-product of that. 

Shravan

I will add that unlike the other issue where the timeouts occurred randomly, my issue is deterministic. I have ensured that all timeouts are accurate.

Hello Shravan,

Thank you for the feedback.
Could you please provide repeatable test case (exact steps/sample project, create tables statements/database etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Dear Chiranjeevi,

I am unsure how to reproduce this issue. I do not have this problem on my second computer.

However, I will be able to provide the code I used to reproduce the issue on my end (even while it only occurs on that PC), any dumps like SHOW ENGINE STATUS or such.

Please let me know what code/dumps I can provide.

Shravan

Hello Shravan,

Thank you for the feedback.
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.