MySqlDataReader stops reading after about 4 minutes and returns

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

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

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

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

i will equal 25026. However, I need to do some processing on each ID in my loop; each iteration ends up taking 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 about 4:15 of processing, reader.Read() simply returns false. In most of my test runs, i equaled 14896, but it also sometimes stops at 11920. The DataReader quitting after the same number of records is suspicious, and the times it stops after a different number of rows seems even stranger.

Why is reader.Read() returning false when there's definitely more rows? There are no exceptions being thrown – not even first chance exceptions.

Update: I mentioned in my response to Shaun's answer that I was becoming convinced that MySqlDataReader.Read() is swallowing an exception, so I downloaded Connector/Net's source code (bzr branch lp:connectornet/6.2 C:/local/path) and added the project to my solution. Sure enough, after 6:15 of processing, an exception!

The call to resultSet.NextRow() throws a MySqlException with a message of "Reading from the stream has failed." The InnerException is a SocketException:

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

10054 means the TCP socket was aborted with a RST instead of the normal disconnection handshake (FIN, FIN ACK, ACK), which tells me something screwy is happening to the network connection.

In my.ini, I cranked interactive_timeout and wait_timeout to 1814400 (seconds)开发者_StackOverflow to no avail.

So... why is my connection getting torn down after reading for 6:15 (375 sec)?

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

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

If that doesn't work, read on:

My guess is that you are hitting some type of Deadlock, especially considering you are reading and writing. This would explaing why it works with the simple loop, but doesn't work when you do updates. It would also explain why it happens around the same row / time each time.

There was a weird bug in SqlDataReader that squelched exceptions (http://support.microsoft.com/kb/316667). There might be something similar in MySqlDatareader - After your final .Read() call, try calling .NextResult(). Even if it's not a deadlock, it might help you diagnose the problem. In these type of situations, you want to lean more towards "trust but verify" - yes, the documentation says that and exception will be thrown on timeone, but sometimes (very rarely) that documentation lies :) This is especially true for 3rd party vendors - e.g. see http://bugs.mysql.com/bug.php?id=53439 - the mysql .net library has had a couple of problems like the one you are having in the past.

Another idea would be to watch what's happening in your database - make sure data is contantly being fetched up till the row that your code exits on.

Failing that, I would just read all the data in, cache it, and then do your modifications. By batching the modifications, the code would be less chatty and execute faster.

Alternatively, reset the reader every 1000 or so rows (and keep track of what row ID you were up to)

Hope something here helps you with your frustration! :)

Since I'm just reading ints, I ultimately just read the entire resultset into a List<int>, closed the reader, and then did my processing. This is fine for ints since a even a million take up < 100 MB of RAM, but I'm still disappointed that the root issue isn't resolved – if I were reading more than a single int per row, memory would become a very large problem with a large dataset.

Try to set longer connection timeout.

There are 2 issues that make things a bit more confusing than it should be:

The first, as has been mentioned in another post, is that older versions of the MySQL .NET connector were swallowing a timeout exception. I was using mysql.data.dll version 6.1.x and after upgrading to 6.3.6 the exception was being properly thrown.

The second is the default MySQL server timeouts, specifically net_read_timeout and net_write_timeout (which default to 30 and 60 seconds respectively).

With older versions of mysql.data.dll, when you are performing an action with the data in the datareader loop and you exceed the 60 second default timeout it would just sit there and not do anything. With newer versions it properly throws a timeout exception which helps diagnose the problem.

Hope this helps someone as I stumbled upon this but the solution was to use a different approach, not an actual cause/fix.

TLDR: The fix is increase net_read_timeout and net_write_timeout on the mysql server in my.ini although upgrading mysql.data.dll is a good idea.

May be this is timeout on server-side?

Try this 1. Add reference System.Transactions;

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

Write your entire insert/update logic inside Scope's using. This will definetly help you.

Add the following after creating your Command.

cmd.CommandTimeout = 0;

This will set the CommandTimeout to indefinitly. The reason your getting a timeout is probably because the connection though executed, is still in the 'command' phase because of the Reader.

Either try setting the CommandTimeout = 0 or reading everything first, then doing subsequent functions on the results. Otherwise the only other issue i could possibley see is that the Sql Server is dropping the result set against the specified process id due to a timeout on the server itself.

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

What this guy experienced was something similar: a hang on the Read method at exactly the same moment, during reading of the same record (which is the same thing you experience i guess). In his case he called another webservice during the Read() loop, and that one timed out causing the Read() to hang without an exception.

Can it be the same at your machine, that an update in the Read() loop times out (i think that update uses the default 30 secs timeout) and causes the same effect?

Maybe a longshot, but reading the two stories the sounded a lot familiair.

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