NHibernate + MySql Intermittent TimeoutException

Question

I have just migrated my NHibernate app from SQL Server to MySql. All seems well except I am getting intermittent TimeoutExceptions. I can find a lot of info about this timeouts with MySql and even one blog post about timeouts with Hibernate + MySql + C3PO (whatever that is). But nothing about how to tweak NHib to stop this problem. Do you know?

Here is the stack trace:

NHibernate.TransactionException: Begin failed with SQL exception ---> System.TimeoutException: 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.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
   at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   --- End of inner exception stack trace ---
   at MyNetworkStream.HandleOrRethrowException(Exception e)
   at MyNetworkStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.ReadOk(Boolean read)
   at MySql.Data.MySqlClient.NativeDriver.SetDatabase(String dbName)
   at MySql.Data.MySqlClient.Driver.SetDatabase(String dbName)
   at MySql.Data.MySqlClient.MySqlConnection.ChangeDatabase(String databaseName)
   at MySql.Data.MySqlClient.MySqlConnection.Open()
   at NHibernate.Connection.DriverConnectionProvider.GetConnection()
   at NHibernate.AdoNet.ConnectionManager.GetConnection()
   at NHibernate.Impl.SessionImpl.get_Connection()
   at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)
   --- End of inner exception stack trace ---
   at NHibernate.Transaction.AdoTransaction.Begin(IsolationLevel isolationLevel)
   at NHibernate.Transaction.AdoTransaction.Begin()
   at NHibernate.AdoNet.ConnectionManager.BeginTransaction()
   at NHibernate.Impl.SessionImpl.BeginTransaction()

for now you could put a Timer to do some dummy SELECT, to avoid the timeout? — Tony, Apr 02 '11 at 01:38
Tim did you solve this as I wondering about this more and more... — Rippo, Apr 12 '11 at 15:06

score 0 · Answer 1 · answered Apr 04 '11 at 07:11

0

I run NHibernate on Rackspace cloud with a MYSQL database. I found I was getting timeouts due to the fact that connections were being left semi open due to sleeping. Eventually I was running out of connections and my web site timed out.

In the end I added Pooling=false; into my connection string. I am not sure if this is the ultimate fix but it has stopped my problem.

You can check for sleeping connections by using show processlist in a MySQL editor...

answered Apr 04 '11 at 07:11

Rippo

22,117
14
78
117

Thanks. Yikes! Pooling=false? Are you worried about the performance impact of that? – Tim Scott Apr 06 '11 at 04:15
Most likely yes, it does depend on use case; however I think there is a bug in the pooling code that marks connections as `sleeping` BUT does not allow them to be woken up thus eventually you run out of connections. Like I said this is not the ultimate fix but it is a workaround for me at least for now. – Rippo Apr 06 '11 at 06:46
Tim have a look at this http://stackoverflow.com/questions/5567097/using-mysqlconnection-in-c-does-not-close-properly – Rippo Apr 12 '11 at 15:08

NHibernate + MySql Intermittent TimeoutException

1 Answers1