MySQL .NET Connector 5.2.3 - Win 2k3 - Random Error - Unable to connect to any hosts - Restart website and it's fixed

I have a strange error on a Win 2k3 box running the MySQL connector 5.2.3. It's happened 3 times in the last 24 hours but only 4 times total in the last 6 months +.

[Exception: Exception of type 'System.Exception' was thrown.] MySql.Data.MySqlClient.NativeDriver.Open() +259

[MySqlException (0x80004005): Unable to connect to any of the specified MySQL hosts.]

开发者_运维知识库If I restart the app pool and website in IIS, the problem is resolved temporarily, but as stated, it's happened 3 times now in the last 24 hours. No server changes during that time either.

Any ideas?

---

Here's a guess based on limited information.

I don't think you are properly disposing of your database connections. Make sure you have the connections wrapped in using clauses. Calling .Close() isn't good enough. That closes the connection, but it doesn't dispose of it.

Second, the reason why now instead of months ago is probably related to a combination of the amount of traffic you are now seeing versus before and the amount of database connections you are instantiating now versus before.

I just got called to help with an app that exhibited the exact same behavior. Everything was fine until one day it started crashing. A lot. They had the sql command objects wrapped in using clauses, but not the connection. As traffic increased the connection pool was filling up and bursting. Once we wrapped the connections in using clauses (forcing a call to dispose()), everything was smooth sailing.

UPDATE
I just wanted to clarify one thing. SqlConnection is a wrapper to an unmanaged resource; which is why it implements IDisposable. When your connection object goes out of scope, then it is available to be garbage collected. From a coding perspective, you really have no idea of when this might happen. It might be a few seconds, it might be considerably longer.

However the connection pool, which SqlConnection talks to, is a completely separate entity. It doesn't know the connection is no longer going to be used. The Dispose() method basically tells the connection pool that this particular piece of code is done with the connection. Which means that the connection pool can immediately reallocate those resources. Note that some of the MS documentation states that Close() is equivalent to Dispose() but my testing has shown that this simply isn't true.

If your site creates more connections than it is explicitly disposing of then the connection pool is potentially going to fill up based on when garbage collection takes place. It really depends on the number of connections created and the amount of traffic received. Higher traffic = more connections and longer periods of time between GC runs.

Now, a default IIS configuration gives you 100 executing threads per worker process. And the default configuration of the application pool is 100 connections. If you are disposing of your connections immediately after using them then you will never exceed the pool size. Even if you make a lot of db calls, the request thread is doing them one at a time.

However, if you add more worker processes, increase the number of threads per process, or fail to dispose of your connections, then you run into the very real possibility of exceeding the connection pool size. Note that you can control the pool size in your sql connection string.

Don't worry about performance of calling dispose then reopening the connection several times while processing a single page. The pool exists to keep connections alive with the server in order to make this a very fast process.