MySQL Bugs: #31247: SqlDataSource not closing connection to MySql.

Bug #31247	SqlDataSource not closing connection to MySql.
Submitted:	27 Sep 2007 15:28	Modified:	23 Oct 2007 19:49
Reporter:	Brent Martin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.0.8.1	OS:	Windows (XP Service Pack 2)
Assigned to:		CPU Architecture:	Any
Tags:	connector, Process, SqlDataSource

Description:
When using a SqlDataSource control on an ASP.NET page, MySql will show connections staying open on the web server (viewing current Processes through phpMyAdmin).  If the DataSourceMode for the datasource is set to "DataSet", then it seems that the same process is used each time (when the process is started from the same web server/process); when the datasource is set to "DataReader", a new process is spawned each time the page is hit.  If pooling is turned off in the connection-string, the "DataSet" connections are seemingly closed correctly, but "DataReader" connections still leave open processes running on the database server.

We have tried this on mutliple IIS web servers (all running XP), with multiple MySql.Data.MySqlClient connectors (versions 5.0.3.0, 5.0.8.1, and 5.1.3.0), and tried it on two different MySql DB versions (5.0.44 and  5.0.38)...all with the same results.

How to repeat:
Created the following page:

<%@ Page Language="C#" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <h1>Test Page</h1>
    <form id="form1" runat="server">
	<asp:SqlDataSource ID="DsMain" runat="server"
	    DataSourceMode="DataReader"
	    ProviderName="MySql.Data.MySqlClient"
	    ConnectionString="server=serverName; database=dbName; uid=userId; password=pswd;"
	    SelectCommand="SELECT DISTINCT someColumn FROM TblName"
	/>
    <asp:DropDownList runat="server" ID="DropDown" DataSourceID="DsMain" />
    </form>
</body>
</html>

Tried the following variations of DataSourceMode:  "DataReader" and "DataSet".

Also tried the following variation of the ConnectionString:  "Pooling=false; server=serverName; database=dbName; uid=userId; password=pswd;"

Suggested fix:
Is this a connector problem where the connection is not being closed properly?

Hi Brent and thanks for your report. I believe this is a duplicate of Bug#19178 but would like to hear your opinion.

Brent ?

I apologize for the delay responding.  I don't know that this is a duplicate of Bug#19178...although there may be some similarities (in the fact that threads appear to not be getting released.?.?).  By reading the commments from Bug#19178, I'm having trouble understanding whether anything was actually ever resolved or not.  Were any changes ever implemented because of Bug#19178?  I didn't see anything in those comments that would seem to help with the problem that I've run into.

Please note that my original post stated that: "We have tried this on mutliple IIS web servers (all running XP)".  As an FYI, we've tried this on both XP and Windows Server 2003...

I have same problem but not with web based application. My application run at win based. The problem is when creating dataset.xsd, I have configure the connection correctly, by set datasource type into MySQL, set user name, password, database name. But when pass into next scene (selecting database object), the tree seems empty (no tables, no views, no stored procedures, no functions). How can I create dataset.xsd at design mode (not runtime using source code).

Brent

I've analyzed this one to death and I've become convinced that this is not a bug.    The reason I say that is because Sql Server seems to retrieve connections from the database differently than we do for MySQL.  This is even in the case of pooling=false.  

If we look at what is happening here, when you choose the data reader mode you are asking the SqlDataSource to return an IDataReader object (which it does).  The SqlDataSource can't really be sure when you are done with this object.  In fact, the reader is opened with CommandBehavior.CloseConnection so the corresponding conection will be closed when the reader is closed.

Now, during data binding the reader is asked for it's enumerator.  The interface requested here is IEnumerator which has not facility for diposing.  What this means is that whatever control is bound to the enumerator has no way of disposing of the enumerator when done.  

The gist of this is that I don't see a way for the SqlDataSource to know when the binding is done to close the reader.  Heck, you could have saved the reader into a private variable so the last thing the SqlDataSource wants to do is close it out from under you.

I would think you would retrieve the reader object manually and close it when binding is done.  This is awkward and I would like to think I'm wrong but I can find no better way.  perhaps you are better off using dataset mode.

Reggie,

Thanks for the info.  I was wondering if you could weigh in on the following:  If I change the provider to ODBC, I do not see the same problem (processes staying open on the MySQL server).  Here's how I'm setting up the SqlDataSource in this case:

	<asp:SqlDataSource ID="DsMain" runat="server"
	    DataSourceMode="DataReader"
	    ProviderName="System.Data.Odbc"
	    ConnectionString="DRIVER={MySQL ODBC 3.51 Driver}; server=serverName; database=dbName; uid=userId; password=pswd;"
	    SelectCommand="SELECT DISTINCT someColumn FROM TblName"
	/>

Is there an obvious difference between ODBC and the native connector that would allow an ODBC connection to close/kill all processes, while the MySQL .NET connector wouldn't?

Thanks,
Brent M.

Brent, I think that feature was introduced into MyODBC but I can't speculate what managed code can and can not do...

I don't think this should be closed. I'm using connector/net 5.2.1.0 and get the same behavior - the connection is not closed when DataBind() is called, even when the DataReader is opened using CommandBehavior.CloseConnection.  This is non-standard behavior.   The only way around it is explicitly checking if the connection has been closed, i.e.
if (!dr.IsClosed)
            dr.Close();