S h o r t S t o r i e s

// Tales from software development

‘Too many connections’ with MySQL .NET Connector

leave a comment »

I was Googling for some info on MySQL the other day and stumbled across a few ‘bug’ reports from developers experiencing a ‘Too many connections’ exception when using the MySQL .NET Connector.

This sounded familiar so I checked some code that I wrote about six months ago and confirmed what I thought… It’s simply that calling the MySqlConnection.Close() when you’ve finished with the connection is not enough. You need to call its Dispose() method too.

While calling Dispose() ought to be obvious, Microsoft’s SqlConnection implementation is much more robust and allows developers to get away with some poor programming practices. I’d guess that quite a few developers write code for MySQL that’s very similar to code they’ve previously written for SQL Server that appeared to work well enough but then this problem shows up with MySQL.

If you wrap up database functionality in a class that uses an instance of MySqlConnection then you probably need to implement IDisposable and call Dispose() on the MySqlConnection instance in your class’s Dispose method. For example, I have a base class that’s used for calling stored procedures and manages its own connection to the database:

    public abstract class StoredProcedureExecutor : IDisposable
    {
        private MySqlConnection connection;
        ....
        public void Dispose()
        {
            this.Dispose(true);
            GC.SuppressFinalize(this);
        }
        ...
        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (this.connection != null)
                {
                    this.connection.Dispose();
                    this.connection = null;
                }
            }
        }
    }

 

Advertisements

Written by Sea Monkey

February 1, 2010 at 8:00 am

Posted in Development

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: