MySQL Bugs: #37501: Connection not closing when CommandBehavior.CloseConnection is used

Bug #37501	Connection not closing when CommandBehavior.CloseConnection is used
Submitted:	18 Jun 2008 20:48	Modified:	21 Jul 2008 20:59
Reporter:	Nathan Preston	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.1.0	OS:	Windows (XP and Server 2003, IIS 6)
Assigned to:		CPU Architecture:	Any
Tags:	CommandBehavior

Description:
Using mysqlConnector/Net driver and Asp.Net 3.5.  Connection is not being closed when creating a MySqlDataReader and calling DataBind() on a DropDownList, even when CommandBehavior.CloseConnection is used to create the reader.  

Looking at open connections verifies on the server that the connection remains open until reader.Close() is called.  I tried pooling=false in the connection string, but get the same behavior.  If you hit the page enough times, you eventually get a "too many connections" error. 

How to repeat:

Very easy to reproduce.
(C#)

 protected void Page_Load(object sender, EventArgs e)
    

MySqlConnection mySqlCon = new MySqlConnection(ConfigurationManager.AppSettings["mySqlDbCon"]);
MySqlCommand sqlcmd = new MySqlCommand("SELECT Model from tblAutos WHERE make = 'Ford'", mySqlCon);
mySqlCon.Open();
MySqlDataReader reader = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
ddlModel.DataSource = reader;
ddlModel.DataTextField = "Model";
ddlModel.DataValueField = "Model";
ddlModel.DataBind();

if (!reader.IsClosed) // It isn't closed, but should be 
    reader.Close();  // Have to manually close the data reader!!!

}

Suggested fix:
I've seen other posts that indicate this is not an issue using the ODBC driver, but it seems like Connector/Net should be consistent.  This is non-standard behavior in .Net.

Current workarounds are manually closing the reader (as in the example above), or using a DataSet.

if (!reader.IsClosed) // It isn't closed, but should be 
    reader.Close();

Hi Nathan and thanks for your report.

I am puzzled as to what the real problem is:
> *Connection is not being closed* ... even when CommandBehavior.CloseConnection is used to create the reader.  

> Looking at open connections verifies on the server that the connection remains open until reader.Close() is called.

Just yesterday I reviewed entire MS specs on this matter and this behavior is correct. You *should* close the reader to signal the framework to close the associated connection as CommandBehavior.CloseConnection is used.

Sync read test case (works on all .NET FW):
MySqlConnection conn = new MySqlConnection();
MySqlDataReader dr;
conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=*****;PORT=****;Allow Zero Datetime=True;logging=True;Connect Timeout=3;Allow Batch=true;Persist Security Info=false;Max Pool Size=100;Pooling=true;Connection Reset=false;";
conn.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandTimeout = 0;
command.CommandType = CommandType.Text;

command.CommandText = "SELECT * FROM boot LIMIT 500";
try
{
int count = 0;
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
Console.Write("{0}\t", dr.GetValue(i));
}
Console.WriteLine();
}
dr.Close();
Console.WriteLine("DONE");
if (conn.State == ConnectionState.Closed) { Console.WriteLine("Conn closed"); }
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
Result:
[18.06.08 10:13:09] - Executing command QUERY with text ='SHOW VARIABLES'
[18.06.08 10:13:09] - Executing command QUERY with text ='SHOW COLLATION'
[18.06.08 10:13:09] - Executing command QUERY with text ='SET NAMES utf8;SET character_set_results=NULL'
--<cut>--
499	1999	IZV	05.10.99	some more data...
500	1999	IZV	05.10.99	some more data...
DONE
Conn closed

Async sample (.NET FW 2+) is equally simple and works as expected.

Can you point me to MS specs that clearly state that connection should be closed (with these settings) before associated reader is closed?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/