MySQL Bugs: #28958: Connection unexpectedly terminated

Bug #28958	Connection unexpectedly terminated
Submitted:	7 Jun 2007 20:18	Modified:	25 Jul 2007 20:58
Reporter:	Pushparaj Silva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.2	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Connection, MySql.Data, MySqlConnection, MySqlConnection.Open, Terminated

Description:
Under some occasions I get the following error on my ASP.NET 2.0/C# e-commerce solution with MySQL Connector/NET:

Connection unexpectedly terminated. at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
at MySql.Data.MySqlClient.NativeDriver.ReadOk(Boolean read) 
at MySql.Data.MySqlClient.NativeDriver.SetDatabase(String dbName) 
at MySql.Data.MySqlClient.MySqlConnection.ChangeDatabase(String database) 
at MySql.Data.MySqlClient.MySqlConnection.Open() 
at DatabaseHandler.getSQLSingleResult(String sql)

code for DatabaseHandler:

public class DatabaseHandler
{

 private static string getConnectionString()
 {
        return ConfigurationManager.ConnectionString
   ["connectionString"].ConnectionString;
 }

 public static MySqlDataReader getSQLDataReader(String sql)
 {
   MySqlConnection con = new MySqlConnection(getConnectionString());
   con.Open();
   MySqlCommand cmd = new MySqlCommand(sql, con);
   return cmd.ExecuteReader(CommandBehavior.CloseConnection); 
        // The connection is closed when the reader is closed 
 }

 public static object getSQLSingleResult(String sql)
 {
   object ret = "";
   MySqlDataReader dataReader = getSQLDataReader(sql);
        
   dataReader.Read(); // go to the next row
   if (dataReader.HasRows && !dataReader.IsDBNull(0))
   {
     ret = dataReader.GetValue(0);
   }
   dataReader.Close();
   return ret;
 }

}

How to repeat:
Have not found out under which circumstances this happens. I'm not having a heavy load on my page (maximum 10 simultaneous users).

Each connection is closed only when its reader is closed (see the above code).

Suggested fix:
-

Get's the same error for the an asp:GridView, but with a different stacktrace:

[MySqlException (0x80004005): Connection unexpectedly terminated.]
   MySql.Data.MySqlClient.MySqlStream.LoadPacket() +173
   MySql.Data.MySqlClient.MySqlStream.OpenPacket() +55
   MySql.Data.MySqlClient.NativeDriver.ReadOk(Boolean read) +244
   MySql.Data.MySqlClient.NativeDriver.SetDatabase(String dbName) +41
   MySql.Data.MySqlClient.MySqlConnection.ChangeDatabase(String database) +78
   MySql.Data.MySqlClient.MySqlConnection.Open() +335
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +41
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

After an update of the site I get the following stacktrace:

[NullReferenceException: Object reference not set to an instance of an object.]
   MySql.Data.MySqlClient.NativeDriver.Configure(MySqlConnection connection) +20
   MySql.Data.MySqlClient.MySqlConnection.Open() +276
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +41
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Control.PreRenderRecursiveInternal() +161
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

I noticed that when I turned the pooling off by setting "pooling=false;", it seems to work.

So the first question that arise is; does the pooling handling work as it should? Or does it close connections even though they are in use?

Hi Pushparaj and thanks for your report.

You are using very old version of c/NET please upgrade and retest. It is virtually impossible to tell what happened just by looking into stack traces so I would have to guess...If you are not disabling connection pooling and including "connection reset=false" on  your connection string, then that will cause the problem. We have committed a fix for this to our source repo. The work around until you upgrade is to either disable pooling or set 'connection reset=true' on your connection string.

Next time, please provide as much info as possible, including:
 - MySQL server version, host OS info
 - .NET fw used
 - small but complete test case exhibiting the error every time (doesn't matter if it needs to be run for several hours or several times)

Hi Tonci.

Thanks for the answer.

I'm now trying with the Connector/Net v.5.1.2.2. I will try this for one week, with the pooling flag set to true, to see if it works better. (I'll write a post in a week with the result.)

And the information you asked for.
* MySQL Server: MySQL 5.0.18
* OS: Windows (as I'm having this site on a hosted environment, I'm not sure  of exactly which version of Windows they are running)
* The .Net fw version I'm using was stated in my first post (ASP.Net 2.0)

I also sent you a code snippet for where the problem occurs. However, I don't think the root cause for this problem lies there, but in the handling of all the connections in the connection pool. I'm not handling the connections in my app in an unusual way (this was merely this I wanted to show with the code snippet.)

For what it goes for the ASP:GridView and ASP:SqlDataSource, I'm not sure of how they handles the connections.

And it is only in the stated code snippet, ASP:GridView and ASP:SqlDataSource that I have all the entry points to the database.

However, let's see if this is solved with Connector/Net v.5.1.2.2.

Pushparaj, waiting on results from your test.

Hi again,
It seems to be working fine now.
If this problem appears again later, I will contact you again.
Thank you for your help :)

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/

Pushparaj, glad your problem is solved. Setting this to Closed in 5.1.2.2

Thanks for your interest in MySQL.

Hi again.

This problem has appeared again.
(And as I wrote earlier I have upgraded to version 5.1.2)

You wrote earlier that:
"The work around until you upgrade is to either disable pooling or
set 'connection reset=true' on your connection string."

I now have set "pooling=false", but which work-around is the best to prefer?

Do you know if you are working on a solution for this problem? (Once again, I'm using the latest version)

--
Regards,
Pushparaj Silva

Hi Pushparaj.

> This problem has appeared again.(And as I wrote earlier I have upgraded to version 5.1.2)

Noted.

> You wrote earlier that: "The work around until you upgrade is to either disable pooling or set 'connection reset=true' on your connection string." I now have set "pooling=false", but which work-around is the best to prefer?

It is not a matter of 2 workarounds, it's just one mechanism: set pooling to false and connection reset to true.

> Do you know if you are working on a solution for this problem? (Once again, I'm using the latest version)

I am unable to work on any "problem" that's just described! So far I haven't even seen you connection string, let alone the test case... Please refer to my prior post:
Next time, please provide as much info as possible, including:
 - MySQL server version, host OS info
 - .NET fw used
 - small but complete test case exhibiting the error every time (doesn't matter if it needs to be run for several hours or several times)
 - my.ini file used for starting MySQL server

Without repeatable test case we are unable to do anything. Reading stack trace is very painful and usually leads nowhere. If you provide information asked I'm more than willing to reopen this report.