MySQL Connection Pool Count

Question

I have started to receive an error with my VB.NET application:

Timeout Expired. The timeout 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 did have a few methods that were not correctly disposing of the connection. These have been fixed using Try and Finally.

However, this morning I received the error again whilst trying to open a simple report. I had MySQL Workbench open monitoring client connections. At the time I had 4 threads connected.

The MySQL DB kills connections that have been asleep for more than 15 seconds.

I am at a loss as to how I could have reached the max pool size, if indeed that is what the error is referring to.

Does the application read the max pool size setting and hold its own count of connections and throw the error when that number is reached, or does it get the number from the MySQL DB directly every time a new connection is opened?

Or could the error be due to something else?

EDIT 1

Some stats from MySQL Workbench
Threads Connected:3
Threads Running: 1
Threads Created: 250
Threads Cached: 5
Rejected (over limit): 0
Total Connections: 2822
Connection limit: 151
Aborted Clients: 2694
Aborted Connections: 84
Errors: 0

EDIT 2

Sample code calling and disposing of connection:

Public Shared Function GetCoursePaperCertificate(ByVal CourseTypeID As Integer) As String
Dim connx As New MySqlConnection(My.Settings.BMConnString)
Try
    Dim cmdTextx = "Select `Cert` From `Courses` WHERE `ID`=@ID"
    Dim cmdx As New MySqlCommand(cmdTextx, connx)
    cmdx.Parameters.AddWithValue("@ID", CourseTypeID)
    connx.Open()
    Dim result = cmdx.ExecuteScalar
    If result Is Nothing OrElse result Is DBNull.Value Then
        Return String.Empty
    Else
        Return result
    End If
Catch ex As Exception
    Return String.Empty
Finally
    connx.Dispose()
    connx = Nothing
End Try
End Function

Use the Using-statement wherever you use a connection. That means, use them only in a method scope and never make them shared. — Tim Schmelter
As @TimSchmelter said before, try to use using when working with db connections to avoid problems with any connection that you might leave open. By the way, the problem could be caused by any proccess that takes too long and is using a transaction to lock up any table. Did you checked if there is any proccess that might be locking up the table that you are trying to use? — Abner
Thanks. I haven't explicitly implement any transaction methods anywhere in the project. I'll convert the code to using There does appear to be one thread that is open and sleeping longer than it should. But, I would have thought that with 151 possible connections, this wouldn't be the culprit itself? — Dave B
It very such sounds like you are not disposing of your connections after use. The server killing dormant threads doesnt impact the connection poll (Workbench wont be able to see that) — Ňɏssa Pøngjǣrdenlarp
@Plutonix Is there any way to see the actual number of connections in the poll? — Dave B

Ňɏssa Pøngjǣrdenlarp Ňɏssa Pøngjǣrdenlarp · Accepted Answer · 2016-11-22T15:34:41

There are several things in your code.

First turn on Option Strict. The function is declared to return a string, but you are trying to return Object with Return result
Everything which implements a Dispose method ought to be used inside a Using block. This allows you to declare and initialize an object, use it and dispose of it at the end.
Parameters.Add is better than AddWithValue. The later forces the DB Provider to guess the datatype based on the data.
Depending on the load and whether that method is used a lot, you could load the data to a DataTable and do lookups on that rather than query the DB over and over.

The core issue is (probably) that you do not dispose of the DBCommand object. Look at the constructor you use:

Dim cmdx As New MySqlCommand(cmdTextx, connx)

The DBCommand object is passed a reference to the connection. Even though you explicitly dispose of the connection, cmdx still has a reference to it, and it was not disposed. Using blocks make it simple to be sure things are disposed:

Dim sql = "Select `Cert` From `Courses` WHERE `ID`=@ID"

Using dbCon As New MySqlConnection(MySQLConnStr)
    Using cmd As New MySqlCommand(sql, dbCon)
        cmd.Parameters.Add("@Id", MySqlDbType.Int32).Value = CourseTypeID
        dbCon.Open()
        Dim result = cmd.ExecuteScalar

        If result Is Nothing OrElse result Is DBNull.Value Then
            Return String.Empty
        Else
            Return result.ToString()
        End If
    End Using           ' close, dispose of conn
End Using               ' dispose of DBCommand

To reduce indentation, you can "stack" items into one Using block:

Using connx As New MySqlConnection(MySQLConnStr),
    cmd As New MySqlCommand(sql, connx)
    ...
End Using

Note the comma at the end of the first line.

I'd be surprised if this was not the cause of your leak (of course all the code would need to be changed).

MySQL Connection Pool Count

1 Answers