How to know number of connections which are still open using mysql and .Net?
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?
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)