Bug #19481 Where clause with datetime throws exception [any warning causes the exception]
Submitted: 2 May 2006 15:03 Modified: 4 Jun 2006 4:45
Reporter: Kenneth Witt Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:1.0.7 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[2 May 2006 15:03] Kenneth Witt
Description:
When using the .NET connector (1.0.7) to a MySQL 5 database, if the WHERE clause involves a DATETIME comparison operation, the query results in a "There is already an open DataReader associated with this Connection which must be closed first." exception.  The problem is repeatable.  I am using Visual Studio 2003, with .NET 1.1, on a up-to-date patched Windows XP Pro box.  I have used both MySQL 5.0.18 and the current 20a release to repeat the problem.  The problem does not exist using a 4.0.20a database.

How to repeat:
The demonstrator for the repeatability has be extricated from a much larger and complicated system, though this is sufficient to demonstate the issue.

The following tables and data are used:

-- out with the old
DROP TABLE IF EXISTS CONTACT_ACTIVITY;
DROP TABLE IF EXISTS GROUND_STATION_ANTENNA;

-- in with the new
CREATE TABLE GROUND_STATION_ANTENNA
(
    ANTENNAID   INT NOT NULL,
    LOCATION     VARCHAR(16) NOT NULL,
    NETWORK     ENUM('DSN','GN') NOT NULL,
    PRIMARY KEY ( ANTENNAID )
);

CREATE TABLE CONTACT_ACTIVITY
(
    ID                      INT NOT NULL AUTO_INCREMENT,
    SATELLITEID        VARCHAR(3) NOT NULL,
    ANTENNAID          INT,
    AOS_TIMESTAMP  DATETIME NOT NULL,
    TEL_TIMESTAMP   DATETIME,
    LOS_TIMESTAMP   DATETIME,
    FOREIGN KEY (ANTENNAID) REFERENCES GROUND_STATION_ANTENNA( ANTENNAID ),
    PRIMARY KEY ( ID )
);

-- Populate the Ground Station Antenna table
INSERT INTO GROUND_STATION_ANTENNA VALUES (0,'McMurdo','GN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (24,'Goldstone','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (25,'Goldstone','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (26,'Goldstone','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (34,'Canberra','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (54,'Madrid','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (55,'Madrid','DSN');

-- Populate the Contact Activity table
-- DAY 24
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','0','2005-07-24 00:00:00','2005-07-24 00:02:00','2005-07-24 00:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','24','2005-07-24 03:00:00','2005-07-24 03:02:30','2005-07-24 03:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','34','2005-07-24 09:00:00','2005-07-24 09:00:30','2005-07-24 09:15:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','54','2005-07-24 12:00:00','2005-07-24 12:01:00','2005-07-24 12:33:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','25','2005-07-24 15:00:00','2005-07-24 15:02:00','2005-07-24 15:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','0','2005-07-24 17:00:00','2005-07-24 17:02:12','2005-07-24 17:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','24','2005-07-24 19:00:00','2005-07-24 19:02:00','2005-07-24 19:27:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','34','2005-07-24 21:00:00','2005-07-24 21:02:33','2005-07-24 21:22:55');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','55','2005-07-24 23:00:00','2005-07-24 23:00:45','2005-07-24 23:22:23');

-- DAY 25
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','0','2005-07-25 00:00:00','2005-07-25 00:02:00','2005-07-25 00:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','24','2005-07-25 03:00:00','2005-07-25 03:02:30','2005-07-25 03:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','55','2005-07-25 09:00:00','2005-07-25 09:00:30','2005-07-25 09:15:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','0','2005-07-25 12:00:00','2005-07-25 12:01:00','2005-07-25 12:33:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','0','2005-07-25 15:00:00','2005-07-25 15:02:00','2005-07-25 15:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','0','2005-07-25 17:00:00','2005-07-25 17:02:12','2005-07-25 17:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','55','2005-07-25 19:00:00','2005-07-25 19:02:00','2005-07-25 19:27:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','34','2005-07-25 21:00:00','2005-07-25 21:02:33','2005-07-25 21:22:55');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','0','2005-07-25 23:00:00','2005-07-25 23:00:45','2005-07-25 23:22:23');

This is the C# class, run as a console application:

using System;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;

namespace ConsoleApplication2
{
	class Class1
	{
		public void GetContacts( DateTime startRange, DateTime endRange )
		{
			string conString = "Server=localhost;Database=TestDB;User ID=Dev;pooling=false";
			MySqlConnection con = null;
			try
			{
				con = new MySqlConnection( conString );
				con.Open();

				StringBuilder sql = new StringBuilder();
				sql.Append( "SELECT ID, ANTENNAID, TEL_TIMESTAMP, LOS_TIMESTAMP " );
				sql.Append( "FROM CONTACT_ACTIVITY " );

				// this part of the query causes a failure/exception
				sql.AppendFormat( "WHERE TEL_TIMESTAMP >= '{0}' ", startRange.ToString( "u" ) );

				// eliminate the prior and use this and things are okay
				//sql.AppendFormat( "WHERE ANTENNAID >= {0} ", 24 );

				Console.WriteLine( sql.ToString() ); // for sanity

				MySqlDataAdapter da = new MySqlDataAdapter( sql.ToString(), con );
				DataSet data = new DataSet();
				da.Fill( data );

				foreach (DataRow row in data.Tables[0].Rows)
				{
					Console.WriteLine( "AntennaID:: {0}", row["ANTENNAID"].ToString() );
				}

				da.Dispose();
			}
			catch( MySqlException ex )
			{
				Console.WriteLine( "GetContacts:: {0}", ex.Message );
			}
			catch( Exception ex )
			{
				Console.WriteLine( "GetContacts:: {0}", ex.Message );
			}
			finally
			{
				if (con.State == ConnectionState.Open)
				{
					con.Close();
				}
			}

			return;
		}

		public void test()
		{
			DateTime start = DateTime.Parse( "7/24/2005" );
			DateTime end = DateTime.Parse( "7/25/2005" );

			GetContacts( start, end );

			return;
		}

		[STAThread]
		static void Main(string[] args)
		{
			Class1 c = new Class1();
			c.test();

			return;
		}
	}
}

The above code should produce a list of AntennaIDs that were used on or after the start date (7/24/2005), which is basically all the data in the example DDL given.
[2 May 2006 15:12] Kenneth Witt
C# source code to reproduce problem with DATETIME query

Attachment: Class1.cs (text/plain), 1.78 KiB.

[2 May 2006 15:13] Kenneth Witt
The DDL to create the tables and stuff example data

Attachment: Database.sql (text/plain), 3.35 KiB.

[2 May 2006 15:15] Kenneth Witt
I forgot to mention that running the query manually through the MySQL command line interface works as expected, for all tested versions of the database engine.
[2 May 2006 20:24] Kenneth Witt
In further exploring the problem and a possible solution, I walked through the .NET Connector source code.  Turns out that the problem occurs anytime that MySQL reports a warning to the driver.  [[In my case, using the universal Z character at the end of the string that results from the DateTime.ToString( "u" ) conversion causes MySQL to balk in versions 5.0 and up.]]  In trying to report the warning, the CommandResult calls Driver.ReportWarnings() which generates a new query to the database.  Lo and behold, this is a second reader on the connection, and we end up with an exception.

Thus commenting out the call to driver.ReportWarnings() in function RedNextResult(bool isFirst) in the file CommandResult.cs effectively eliminates the exception being thrown.  It also removes the possibility of recovering warnings from the server programmatically through the Connector.

While I have 2 workarounds-- 1) don't use the 'u' formatter, or 2) use my 'tailored' driver-- these are not really resolutions for the long term.  The driver needs to close the reader on the user's query before generating its own query and reusing the connection or ????.  Is there a way to allow multiple readers on a connection through the connection string or a configuration setting?
[4 May 2006 12:19] Kenneth Witt
Problem resides in Connector, not DB engine
[16 May 2006 20:38] Tonci Grgin
Hi Kenneth.
Great test case! My compliments. 
For now it seems you're right but I intend to analyse more. 
Let's focus on "... if the WHERE clause
involves a DATETIME comparison operation .." sentence. It is not completely correct. You can produce result by *not* using "u" format constant or by issuing query directly as you mentioned later. As for final conclusion, I'll have to check more.
[17 May 2006 7:25] Tonci Grgin
Verified as described by reporter:
Driver.cs, L:251, function public void ReportWarnings()  opens additional reader to check for warnings causing code to fail with error reported. Since SHOW WARNINGS() was introduced in 4.1 all versions from 4.1 fail in same manner.
[18 May 2006 19:53] Bugs System
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/6594
[1 Jun 2006 18:40] Reggie Burnett
Fixed in 1.0.8
[4 Jun 2006 4:45] MC Brown
Documented in the changelog.