MySQL Bugs: #40091: mysql driver 5.2.3.0 connection pooling issue

Bug #40091	mysql driver 5.2.3.0 connection pooling issue
Submitted:	16 Oct 2008 21:02	Modified:	24 Oct 2008 16:14
Reporter:	David Nelson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.3.0	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	connection pooling

Description:
There is an issue with the mysql 5.2.3.0 driver regarding connection pooling, which effectively disables connection pooling completely.  There does not appear to be a workaround to the issue.

If you don’t set explicitly set connection pooling in the connection string, the mysql driver will add “;Pooling=False” to the end of your connection string when you call MySqlCommand.ExecuteReader().

If you  DO explicitly set connection pooling in the connection string, when you call MySqlConnection.Open(), it lowercases the “Pooling=True” to “pooling=True”.

Then, when you call MySqlCommand.ExecuteReader(), it concatenates “;Pooling=False” to the end of the connection string.  The resulting connection string then looks like this at the end: “pooling=True;Pooling=False”.  

At this point the connection was already opened, so we can’t try and munge the connection string at this point (no workaround).

How to repeat:
Write c# code to create a connection, open it and call a stored procedure on it passing 1 parameter.

Suggested fix:
Don't concatenate ";Pooling=False" to the end of all connection strings when ExecuteReader() (and all it's similar variant methods).

Hi David and thanks for your report.

So far I am able to confirm "Pooling=True" being converted to "pooling=True" part but nothing else. Please attach small but self sufficient test case proving your point.

C# test case:

        private void button1_Click(object sender, EventArgs e)
        {
            string result = "";
            MySqlConnection con = null;
            try
            {
                con = new MySqlConnection();
                con.ConnectionString = "server=localhost;database=test;user id=***;pwd=***;Pooling=True";
                con.Open();

                MessageBox.Show("CONNECTION OPENED!\r\nConnection string: " + con.ConnectionString);

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandTimeout = 5;

                string query = "SELECT SLEEP(10);";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = query;
                MySqlDataReader dr = cmd.ExecuteReader();
                MessageBox.Show("READER OPENED!\r\nConnection string: " + con.ConnectionString);

                while (dr.Read())
                    result += dr[0].ToString() + "\r\n";
                    dr.Close();

                    MessageBox.Show("RESULTS:\r\n"+result);

            }
            catch (Exception ex)
            {
                string error = ex.Message.ToString();
                MessageBox.Show(error);
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

    }

fix is coming soon

--- command.cs.old      2008-10-20 09:31:37.000000000 +0200
+++ command.cs  2008-10-20 09:32:18.000000000 +0200
@@ -254,7 +254,11 @@
                        if (!connection.driver.Version.isAtLeast(5, 0, 0))
                                throw new NotSupportedException(Resources.CancelNotSupported);
 
-                       using(MySqlConnection c = new MySqlConnection(connection.Settings.GetConnectionString(true)))
+            string cs = connection.ConnectionString;
+            // formatted and parsed connection string shoud contain pooling 
+            // option exactly as "pooling=True" or "pooling=False"
+            cs = cs.Replace("pooling=True", "pooling=False");
+            using(MySqlConnection c = new MySqlConnection(cs))
                        {
                 c.Settings.Pooling = false;
                 c.Open();

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56628

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56629

- fixed problem that caused in use connection strings to be modified when a pooled connection timed out and was cancelled.  (bug #40091)  

The problem was that we were creating our connection object before setting the pooling property to false.  This caused a connection to be made in an existing connection string cache which caused all the connection strings in that cache to change when we set our pooling property.  The fix was to create a connection string builder object, set the pooling property, and then create the connection object.

Fixed in 5.1.8 and 5.2.4

An entry was added to the 5.1.8 and 5.2.4 changelogs:

If connection pooling was not set explicitly in the connection string, Connector/NET added “;Pooling=False” to the end of the connection string when MySqlCommand.ExecuteReader() was called. 

If connection pooling was explicitly set in the connection string, when MySqlConnection.Open() was called it converted “Pooling=True” to “pooling=True”. 

If MySqlCommand.ExecuteReader() was subsequently called, it concatenated “;Pooling=False” to the end of the connection string. The resulting connection string was thus terminated with “pooling=True;Pooling=False”. This disabled connection pooling completely.

I uninstalled 5.2.3 and installed 5.2.5 and I am still seeing my connections grow and grow.  Upon examination of the ConnectionString, I do see that "pooling=True" is showing up in the string.  Whether the lowercase "p" in "Pooling" matters or not, I don't know.  But it does not appear to be pooling the connections at all.

I'm using Windows XP SP2 with VS 2008 on an ASP.NET 2.0 web application.  I'm talking to an MySql server on a Windows 2000 server.

I've had to configure the server to drop any connections that have been "sleeping" for over 30 seconds just so that my users don't flood the box with connections and then they all start getting "maximum # of connections reached".

I have my configuration string set to :

    <add name="ConnectionString" connectionString="Database=mydb;Data Source=myserver;User Id=myuserid;Password=mypassword;Pooling=true;
Max Pool Size=200;Connection Lifetime=0;" providerName="MySql.Data.MySqlClient"/>

Is there some setting on the server that must be set to enable connection pooling?  I thought this was more of a client level issue.

Please disregard previous comment.  I found a spot in one of our custom controls where we weren't closing the connection after we had used them.  This was causing the connection build up each time the control was drawn on the page.

Once we fixed that control, the pooling worked as expected.