MySqlDataReader stops reading after about 4 minutes and returns

Answer 1

Try to set longer connection timeout.

Answer 2

May be this is timeout on server-side?

Answer 3

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 ?

Answer 4

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.

Answer 5

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.

Answer 6

Hi,

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.

Answer 7

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