MySQL Bugs: #15564: "Connection unexpectedly terminated" using DateTime column

Bug #15564	"Connection unexpectedly terminated" using DateTime column
Submitted:	7 Dec 2005 22:51	Modified:	14 Jan 2006 18:57
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Linux (Gentoo Linux)
Assigned to:		CPU Architecture:	Any

Description:
Using the code in the "How to Repeat" section, a call to the GetAssessmentDetails function results in a MySqlDataReader exception when the AssessmentDateStart or AssessmentDateEnd fields are set to the DATETIME data type in MySQL (version 4.1.14). The call fails upon attempt to perform the Read method on the reader, even when data is present. Changing the field type to varchar(25) causes the method to succeed as expected. The stack trace for this exception is:

MySql.Data.MySqlClient.MySqlException: Connection unexpectedly terminated ---> MySql.Data.MySqlClient.MySqlException: Connection unexpectedly terminated
in <0x001ed> MySql.Data.MySqlClient.PacketReader:Read (System.Byte[] buffer, Int64 pos, Int64 len)--- End of inner exception stack trace ---

in <0x002d2> MySql.Data.MySqlClient.PacketReader:Read (System.Byte[] buffer, Int64 pos, Int64 len)
in <0x00098> MySql.Data.MySqlClient.PacketReader:ReadString (Int64 length)
in <0x00040> MySql.Data.MySqlClient.PacketReader:ReadLenString ()
in <0x00028> MySql.Data.Types.MySqlString:ReadValue (MySql.Data.MySqlClient.PacketReader reader, Int64 length)
in <0x00104> MySql.Data.MySqlClient.NativeDriver:ReadFieldValue (Int32 index, MySql.Data.MySqlClient.MySqlField field, MySql.Data.Types.MySqlValue valObject)
in <0x000be> MySql.Data.MySqlClient.CommandResult:ReadDataRow (Boolean loadFields)
in <0x000bd> MySql.Data.MySqlClient.MySqlDataReader:Read ()

How to repeat:
The following C# code throws an exception when the AssessmentDate* fields are of the MySQL type DATETIME, but successful if they are varchar(25):

		public bool GetAssessmentDetails(int nAssessmentID, ref String strAssessmentType, ref String strCustomer, ref String strDateStart,  ref String strDateEnd, ref String strEngineer, ref double dblTargetAmount, ref String strDescription,  ref String strError)
		{
			bool bResult = false;
			MySqlCommand cmd = new MySqlCommand();
			MySqlDataReader rdr;
			
			try
			{
				string strConnError = OpenConnection();
				if (strConnError != "")
					throw new ApplicationException(strConnError);
				
				strAssessmentType = "";
				strCustomer = "";
				strDateStart = "";
				strDateEnd = "";
				strEngineer = "";
				dblTargetAmount = 0.0;
				strDescription = "";
				strError = "";
				
				cmd.Connection = conn;
				cmd.CommandText = "SELECT AssessmentTypeName, CustomerName, AssessmentDateStart, AssessmentDateEnd, EngineerName, TargetAmount, Description " +
								  "FROM tblAssessments INNER JOIN tblAssessmentTypes ON tblAssessments.AssessmentTypeID = tblAssessmentTypes.AssessmentTypeID " +
								  "INNER JOIN tblEngineers ON tblAssessments.LeadEngineerID = tblEngineers.EngineerID " +
								  "INNER JOIN tblCustomers ON tblAssessments.CustomerID = tblCustomers.CustomerID WHERE AssessmentID = ?aid LIMIT 1";
				cmd.Prepare();
				cmd.Parameters.Add("?aid", nAssessmentID);
				rdr = cmd.ExecuteReader();
				
				if (rdr.Read()) // MySQL connector throws exception on this line!!
				{
					// Only returning the first value, which should be no problem since there is a LIMIT 1 clause
					strAssessmentType = (string)rdr["AssessmentTypeName"];
					strCustomer = (string)rdr["CustomerName"];
					strDateStart = (string)rdr["AssessmentDateStart"];
					strDateEnd = (string)rdr["AssessmentDateEnd"];
					strEngineer = (string)rdr["EngineerName"];
					dblTargetAmount = (double)rdr["TargetAmount"];
					strDescription = (string)rdr["Description"];
				}
				
				bResult = true;
				rdr.Close();
				rdr = null;
			}
			catch(Exception ex)
			{
				strError = ex.Message;
				log.Error("An exception occurred in " + System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, ex);
				return false;
			}
			finally
			{
				cmd = null;
			}
			
			return bResult;
		}
	}

Suggested fix:
Reader should properly return columns of the type DATETIME.

I was not able to reproduce the bug. As far as I can understand you use Mono, am I right ? I created simple table with DateTime column and fetch it without problem. Could you please write here table definitions ?

Correct, sorry, I should have been more explicit about the development
platform. It is running on Mono 1.1.10.

Here's the description for the table as it stands currently (hopefully
it's readable):

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra
|
+---------------------+--------------+------+-----+---------+----------------+
| AssessmentID        | int(11)      |      | PRI | NULL    |
auto_increment |
| AssessmentTypeID    | int(11)      |      |     | 0       |
|
| CustomerID          | int(11)      |      |     | 0       |
|
| AssessmentDateStart | datetime     | YES  |     | NULL    |
|
| AssessmentDateEnd   | datetime     | YES  |     | NULL    |
|
| LeadEngineerID      | int(11)      |      |     | 0       |
|
| TargetAmount        | double(10,0) | YES  |     | NULL    |
|
| Description         | varchar(255) | YES  |     | NULL    |
|
+---------------------+--------------+------+-----+---------+----------------+

If there's anything more I can do in the mean time to help reproduce or
resolve this issue, let me know, I'm more than happy to help.

More information about this bug...

If I do not use a prepared statement, it appears to work ok. I am currently debugging through the MySql connector code trying to track this down. It is very reproducible on my machine.

Probably you need to create and attach full text of your test case and maybe project files. Could you please do it ?

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".

Hi,

I have spent ages trying to figure out this problem. After lots of investigation I have concluded the following.

After performing a large SELECT query, the results are then sent to the client which requested it. This can take some time (especially if you are not connecting from localhost).

This error occurs (often at random places depending on network traffic), because the net_write_timeout limit was reached before all the rows had been returned.

I don't beleive changing this timeout limit is the best way to resolve the problem, instead change your SELECT query from:

SELECT * FROM YourTableName

To:

SELECT SQL_BUFFER_RESULT * FROM YourTableName

The use of SQL_BUFFER_RESULT will make sure your MySQL server doesn't lock out or timeout while sending the results of your query to the client.

Hope this helps.

Chris