CODEWITHSUNDEEP
c#mysqlentity-framework-6mysql-workbenchconnection-timeout
Prashant Kankhara
Prashant Kankhara

Reputation: 1588

MySQl Command timeout during read operation from table having 40+ million records

We have a strange situation where we are getting below error when using mysql with EF6.

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

If, we take the generated query from EF6 to MySql workbench, it execute without any error in 75 sec. But, when running in application it throws error within 30 seconds.

The table has huge records, which will take more than 70 sec to return with rows. But thats fine only thing is "it should not throw error due to read timeout failure".

Tried solution:

We have changed the Global as well as mysql settings for read timeout to 99999 but, still it shows the same error.

SET GLOBAL net_read_timeout=999999;
SET net_read_timeout = 999999

enter image description here

Upvotes: 1

Views: 1282

Answers (1)

Chris Danna
Chris Danna

Reputation: 1264

You can increase the timeout (default is 30) in either the connection string or the DbContext Database object.

C#

DbContext.Database.CommandTimeout = 999;

Connection string

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
default command timeout=999;

Upvotes: 1

Related Questions