MySqlDataReader stops reading after about 4 minutes and returns

Question

I have a simple query that returns 25,026 rows:

MySqlCommand cmd = new MySqlCommand("SELECT ID FROM People", DB); MySqlDataReader reader = cmd.ExecuteReader(); 

( ID is int .) If I just do this:

 int i = 0; while (reader.Read()) i++; 

i will be equal to 25026. However, I need to do some processing for each ID in my loop; each iteration ends up happening somewhere in the hundreds of milliseconds.

 int i = 0; MySqlCommand updater = new MySqlCommand("INSERT INTO OtherTable (...)", anotherConnection); updater.Prepare(); while (reader.Read()) { int id = reader.getInt32(0); // do stuff, then updater.ExecuteNonQuery(); i++; } 

However, after approximately 4:15 of processing, reader.Read() simply returns false. In most of my test runs, i was 14896, but it also sometimes stops at 11920. A DataReader crash after the same number of records is suspicious, and the times it stops after a different number of rows seem even weirder.

Why reader.Read() return false when there are definitely more lines? Throws no exceptions - not even the first random exceptions.

---

Update: I mentioned in Shaun's answer that I made sure MySqlDataReader.Read() swallowing an exception, so I downloaded the Connector Source Code / Net ( bzr branch lp:connectornet/6.2 C:/local/path ) and added the project to my solution . Of course, after 6:15 a processing exception!

Calling resultSet.NextRow() throws a MySqlException message with the message "Read from stream failed." InnerException is a SocketException :

 { Message: "An existing connection was forcibly closed by the remote host", ErrorCode: 10054, SocketErrorCode: ConnectionReset } 

10054 means that the TCP socket was interrupted with RST instead of the usual disconnect ( FIN , FIN ACK , ACK ), which tells me something is happening with the network connection.

In my.ini, I clicked interactive_timeout and wait_timeout at 1814400 (seconds) to no avail.

So ... why is my connection broken after reading for 6:15 (375 seconds)?

(Also, why is this exception swallowed when I use the official binary? It looks like it should bubble up to my application code.)

Answer 1

Since I just read int s, I ended up just reading the entire result set in the List<int> , closing the reader, and then doing my processing. This is normal for int , since even a million takes 100 MB of RAM, but I'm still disappointed that the root problem is not resolved - if I read more than one int for each line, memory would become a very big problem with a large data set .

Answer 2

Perhaps you have a damaged table - this problem with the guy is very similar to yours: http://forums.asp.net/t/1507319.aspx?PageIndex=2 - repair the table and see what happens.

If this does not work, read:

I assume that you are pushing some kind of dead end, especially considering that you are reading and writing. This explains why it works with a simple loop, but does not work when you do updates. This also explains why this happens around the same series / time each time.

A strange error occurred in SqlDataReader that threw exceptions ( http://support.microsoft.com/kb/316667 ). MySqlDatareader might be something like this. After your last call to .Read (), try calling .NextResult (). Even if it’s not a dead end, it can help you diagnose the problem. In such situations, you want to focus more on "trust but verify" - yes, the documentation says that an exception will be thrown for a while, but sometimes (very rarely) that the documentation lies :) This is especially true for third-party vendor developers - for example see http://bugs.mysql.com/bug.php?id=53439 - there were several problems in the mysql.net library, similar to the ones you have in the past.

Another idea - to see what happens in your database - make sure that the data is saved up to the line in which your code is located.

Otherwise, I would just read all the data, cache it, and then execute your changes. By making changes to the modifications, the code would be less chatty and would execute faster.

Alternatively, reset the reader every 1000 lines (and keep track of which line identifier you had)

Hope something here helps you with disappointment! :)

Answer 3

Try setting a longer connection timeout.

Answer 4

There are 2 problems that make things more confusing than it should be:

The first, as mentioned in another post, is that older versions of the MySQL.NET connector swallowed a timeout exception. I used mysql.data.dll version 6.1.x and after upgrading to 6.3.6 the exception was correctly selected.

Secondly, these are the default MySQL server timeouts, in particular net_read_timeout and net_write_timeout (which by default is 30 and 60 seconds, respectively).

With older versions of mysql.data.dll, when you perform an action on data in a datareader loop and you exceed the default 60 second timeout, it just sits and does nothing. With newer versions, it correctly throws a timeout exception that helps diagnose the problem.

Hope this helps someone when I stumbled upon this, but the solution was to use a different approach rather than the actual reason / correction.

TL; DR . Fixed increase in net_read_timeout and net_write_timeout on mysql server in my.ini, although updating mysql.data.dll is a good idea.

Answer 5

Maybe this is a server side timeout?

Answer 6

Try setting a timeout on MySqlCommand rather than MySqlConnection on "cmd" and "updater". What happens if you do: SELECT TOP 100 ID FROM PEOPLE ?

Answer 7

Try this 1. Add the System.Transactions link;

 using(TransactionScope scope = new TransactionScope()) { //Initialize connection // execute command : : scope.Complete(); } 

Record all insert / update logic within the scope. It will definitely help you.

Answer 8

Add the following after creating your team.

 cmd.CommandTimeout = 0; 

This will cause the CommandTimeout to be indefinite. The reason you get a timeout is probably because the connection, although it is completed, is still in the “command” phase due to reading.

Try setting CommandTimeout = 0 or reading everything first and then performing the following functions based on the results. Otherwise, the only problem I can see is that the Sql server deletes the result set with the specified process identifier due to a timeout on the server itself.

Answer 9

I found an article here http://corengen.wordpress.com/2010/06/09/mysql-connectornet-hangs-on-mysqldatareader-read/

What this guy experienced was something similar: hang the Read method at exactly the same moment while reading the same record (which is the same as what you seem to think). In his case, he called another web service during the Read () loop, and this timeout made Read () hang without exception.

Could it be the same on your computer that the update in the read () loop expires (I think the update uses the default timeout of 30 seconds) and has the same effect?

Perhaps a long shot, but reading two stories, a lot of familiair sounded.