How to know number of connections which are still open using mysql and .Net?

Question

I'm using Asp.Net together with MySql and I'm trying to solve the error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I'm trying to make sure that I'm not leaking any connections. I need to know the status of the connection pool (how many connections are open right now)? In this thread I learned that I can use the Performance Monitor to know the pool count. But how can I do that when using MySql instead of Sql Server?

Answer 1

Original Reference for the above code discussed below is here:

I've done the following with Oracle, I think it can work with MySQL with some small modifications (e.g. change OracleConnection, etc.). You can create a class called ConnectionSpy (see below). This is in VB.Net, you could convert the code to C# if you choose.

Imports System
Imports System.Data
Imports Oracle.DataAccess.Client
Imports System.Diagnostics
Imports System.IO

Public Class ConnectionSpy
    Dim con As OracleConnection
    Dim st As StackTrace
    Dim handler As StateChangeEventHandler
    Dim logfileName As String

    Public Sub New(ByVal con As OracleConnection, ByVal st As StackTrace, ByVal logfileName As String)
        If (logfileName Is Nothing Or logfileName.Trim().Length() = 0) Then
            Throw New ArgumentException("The logfileName cannot be null or empty", logfileName)
        End If

        Me.logfileName = logfileName
        Me.st = st
        'latch on to the connection
        Me.con = con
        handler = AddressOf StateChange
        AddHandler con.StateChange, handler
        'substitute the spy's finalizer for the 
        'connection's
        GC.SuppressFinalize(con)
    End Sub
    Public Sub StateChange(ByVal sender As Object, ByVal args As System.Data.StateChangeEventArgs)
        If args.CurrentState = ConnectionState.Closed Then
            'detach the spy object and let it float away into space
            'if the connection and the spy are already in the FReachable queue
            'GC.SuppressFinalize doesn't do anyting.
            GC.SuppressFinalize(Me)
            RemoveHandler con.StateChange, handler
            con = Nothing
            st = Nothing
        End If
    End Sub
    Protected Overrides Sub Finalize()
        'if we got here then the connection was not closed.
        Using stream As FileStream = New FileStream( _
                    logfileName, _
                    FileMode.Append, _
                    FileAccess.Write, _
                    FileShare.Write)

            Using sw As StreamWriter = New StreamWriter(stream)
                Dim text As String = _
                    DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") & _
                    ": WARNING: Open Connection is being Garbage Collected" & _
                    Environment.NewLine & "The connection was initially opened " & st.ToString()
                sw.WriteLine(text)
                sw.Flush()
                RemoveHandler con.StateChange, handler
                'clean up the connection
                con.Dispose()
            End Using

        End Using
    End Sub
End Class

Then, in your web.config/app.config, you can add a section like this:

<configuration>
    <appSettings>
        <!--
          if you need to track connections that are not being closed, set
          UseConnectionSpy to "true". If you use the connection spy, a log
          will be created (the logfile can be specified with the ConnectionSpyLog
          parameter) which will give you a stack trace of each code location
          where a connection was not closed        
        -->
    <add key="UseConnectionSpy" value="true"/>
    <add key="ConnectionSpyLog" value="c:\\log\\connectionspy.log"/>
    </appSettings>
</configuration>

Then, wherever in your code you create a DB connection, you can do this (see below).The mConnection variable would represent your MySQL connection, which you would have already created when you get to this point.

    If ConfigurationManager.AppSettings("UseConnectionSpy") = "true" Then
        Dim st As StackTrace = New StackTrace(True)
        Dim sl As ConnectionSpy = New ConnectionSpy(mConnection, st, _
                                                    ConfigurationManager.AppSettings("ConnectionSpyLog"))
    End If

So then, if you have any connections that are being garbage collected before they're closed, it will be reported in the log file. I've used this technique in some of my applications and it works well.

What you would need to do is create a wrapper class for your MySQL DB Connections, and you can put the above code in place to track the connections. Or, you could just add the above calls to wherever you create your connections. The wrapper class is a bit cleaner though (that's what we did)