MySQL Bugs: #32595: Connector/NET Mis-Formats Dates Stored as VARCHAR

Bug #32595	Connector/NET Mis-Formats Dates Stored as VARCHAR
Submitted:	21 Nov 2007 20:01	Modified:	22 Nov 2007 14:36
Reporter:	Jeffrey McManus	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.8.1	OS:	Windows (Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	.net

Description:
A field defined as a VARCHAR type that is assigned a value of type MySqlDbType.Datetime is formatted incorrectly when the row is updated. Specifically, the MySqlDbType.Datetime value is saved with an additional space between the date and time parts of the field (for example, '2007-01-01 15:56:45'). This only happens when the date/time value is assigned to a field defined as a VARCHAR type.

This becomes a problem when you go to read the data, because it means that the field can't be read (the assignment fails with the error "Input string not in correct format.") This problem is even more challenging to diagnose because the erroneous data (the extra space) is invisible.

Although assigning a Date/Time value to a VARCHAR is something that falls into the category of "stupid," it's still a mistake that people will make (and it could conceivably be done on purpose in some situations).

We've seen this behavior on both our MySQL 4.1/InnoDB and 5.0/InnoDB databases. We did not notice this problem before we recently upgraded to Connector/NET 5.0.8.1, so this may represent a regression.

How to repeat:
Create a table with a VARCHAR(50) field called LastModified.

Insert a row, assigning a value of DateTime.Now to the field. This should succeed.

Update that row, assigning a different value to DateTime.Now. This should also succeed, although the data you inserted will be in an invalid format.

Create a DataReader and attempt to assign the value of the field to a variable of type DateTime. This will fail with the "Input string not in correct format" error.

Test case VS2005

Attachment: bug32595.test (application/octet-stream, text), 2.51 KiB.

Hi Jeffrey and thanks for your report. As there is no test case attached I wrote one and found no problems:
[22.11.07 15:29:21] - Executing command QUERY with text ='SHOW VARIABLES'
[22.11.07 15:29:21] - Executing command QUERY with text ='SHOW COLLATION'
[22.11.07 15:29:21] - Executing command QUERY with text ='SET NAMES utf8;SET character_set_results=NULL'
[22.11.07 15:29:21] - Executing command QUERY with text ='DROP TABLE IF EXISTS `test`.`bug32595`'
[22.11.07 15:29:21] - Executing command QUERY with text ='CREATE TABLE `test`.`bug32595` (`ID` int unsigned NOT NULL AUTO_INCREMENT, `LastModified` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`ID`) ) DEFAULT CHARSET=latin1'
[22.11.07 15:32:04] - Executing command QUERY with text ='INSERT INTO bug32595 VALUES (NULL,'2007-11-22 15:32:04')'
dt is now: 2007-11-22 15:32:04
dt is now: 2007-11-22 15:32:12
VARCHAR
System.String
System.String
dt is finally: 2007-11-22 15:32:12
[22.11.07 15:32:12] - Executing command QUERY with text ='UPDATE bug32595 SET LastModified = '2007-11-22 15:32:12''
[22.11.07 15:29:21] - Executing command QUERY with text ='SELECT * FROM bug32595'

Environment:
 MySQL server 5.0.54BK on WinXP Pro SP2 localhost
 Net FW 2, c/NET 5.0.8.1

Test case is attached.