MySqlDataReader stops reading after about 4 minutes and returns

Question

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) 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.)

Answer 1

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! :)

Answer 2

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.