MySQL Bugs: #49563: Mysql Client wrongly communications with server when using pooled connections

Bug #49563	Mysql Client wrongly communications with server when using pooled connections
Submitted:	9 Dec 2009 14:44	Modified:	30 Mar 2011 21:27
Reporter:	Conrad Micallef	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	trunk	OS:	Any
Assigned to:	Iggy Galarza	CPU Architecture:	Any
Tags:	"UNKNOWN COMMAND"

Description:
Mysql c# client running 6.1.3.0
Linux Redhat 64bit 5.1.40 server (Community Server)

Running the code below gives an exception when i=1 seemingly indicating that the connection reset mechanism is not working properly or not leaving the connection in a usable state.

How to repeat:
static void mysqlconnpoolbug()
        {
            MySqlConnectionStringBuilder msb = new MySqlConnectionStringBuilder();
            msb.ConnectionLifeTime = 60;
            msb.ConnectionReset = true;
            msb.ConvertZeroDateTime = true;
            msb.UserID = "user";
            msb.Password = "password";
            msb.Server = "serverip";
            msb.Pooling = true;
            for (int i = 0; i < 5; i++)
            {
                using (MySqlConnection c = new MySqlConnection(msb.ToString()))
                {
                    c.Open();
                    c.ChangeDatabase("test");
                    using (MySqlCommand cmd = new MySqlCommand("select now()", c))
                    {
                        cmd.ExecuteScalar();
                    }
                }
            }
        }

Hi Conrad and thanks for your report.

Verified just as described using test case provided.

The problem is in connection.ChangeDatabase thus workaround is really simple:
msb.Database = "mysql";
or
String connStr="..." + using (MySqlConnection c = new MySqlConnection(connStr))

This is in no way S2 so please lower the severity.

Please note that I cannot use msb.database="test" as msb.database is READ ONLY.

Also if I comment out the ChangeDatabase command problem remains.

Only solution is to disable pooling but then I have degraded performance - hence the notion of the Serious Severity.

Please advise

Conrad, I must test against latest sources so there might be differences.

For me, assigning msb.database just works... Also, pre-setting database via connection string (as I already described) works without a problem too.
So the only remaining problem I see, running your test case against c/NET trunk, is that connection.ChangeDatabase throws reported error in case when database is not pre-set in any way.

Please note error occurs NOT ON CHANGE DATABASE, but on the OPEN command. THis error occurs when using server 5.1.40. Same code on server 5.0.77 DOES NOT have any problem.

I have the same problem, but as Conrad said it happens during the open command. But Reggie are also right it happens because the database has not been preset in anyway. The following fails:
string connectionString = "Server=ip;Uid=user;Pwd=password;Connection Reset=True;Pooling=True";
using (MySqlConnection conn = new MySqlConnection(connectionString)) {
  conn.Open();
  conn.Close();
}
using (MySqlConnection conn = new MySqlConnection(connectionString)) {
  conn.Open();
  conn.Close();
}

But if the connection string defined the database like this:
"Server=ip;Uid=user;Pwd=password;Connection Reset=True;Pooling=True;Database=test";
It works

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/111165

816 Iggy Galarza	2010-06-15
      Bug #49563  Mysql Client wrongly communications with server when using pooled connections
      - Make sure packet string is properly terminated.