| 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: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 1.0.7 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[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.
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.