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 23:02	Modified:	24 Oct 2008 18:14
Reporter:	David Nelson
Status:	Closed
Category:	Connector/Net	Severity:	S1 (Critical)
Version:	5.2.3.0	OS:	Microsoft Windows
Assigned to:	Bogdan Degtyariov	Target Version:
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.