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:03]
Kenneth Witt
[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.