2

When running a c# web service hosted inside IIS it runs perfectly when I use MySQL driver 5.1.10 but when I update to the latest driver 5.2 I get the following error

ERROR [HY000] [MySQL]ODBC 5.2(w) Driver][mysqld-5.5.28-log]Incorrect datetime value:      '2012-12-14 14:01:13.553000000' for column 'Date' at row 1

I'm using a system DSN.

I'm running MySQL server version 5.5 x64 on Windows 7 and the data type it's trying to save the value into is a DateTime as indicated by the error message. Has anyone else run into this problem?

user1267357
  • 63
  • 4

1 Answers1

1

I've had the same issue, and it seems that previous versions of the ODBC connector (5.1.10 and earlier) have very different handling of C# DateTime fields. It may be that the earlier versions of the connector were just truncating enough of the DateTime to make it work in MySQL while the current version is keeping more of the fractional data.

I've also noticed some odd behavior with Decimal variables completely crashing the connector with no descriptive error message. My solution for that was to use Double types instead.

EDIT I found the changelog that I had seen a while back for ODBC/Connector v5.1.11 and it confirms the different handling of DateTime values in the Bugs Fixed list:

Fractional seconds part of timestamp was ignored in prepared statements that use SQLBindParameter and SQL_C_TIMESTAMP type. For example, a prepared query comparing two timestamp values that only differed in the fractional part would consider the values identical. (Bug #12767761, Bug #60648)

What's funny about it is that up until MySQL 5.6.4 DATETIME and TIMESTAMP fields only had precision down to seconds (no milliseconds or microseconds), so the "incorrect" behavior of previous versions of the connector ignored the fractional seconds and MySQL was happy. With the connector now not ignoring the fractional data an error is generated if this data is passed in via a stored procedure or prepared statement. So the solution for the moment is either:

1) Update to MySQL 5.6 (which is now GA)

2) Use a neat technique to strip the fractional data from a DateTime while still maintaining the type and all its other attributes:

DateTime dt = DateTime.Now //Or any existing DateTime value
dt = dt.AddTicks( - (dt.Ticks % TimeSpan.TicksPerSecond));

3) If you don't want to deal with the nuances of C# and DateTime types at all, and are aware of the potential security ramifications of using string data when working with databases, you could always build your own date string or use the ToString method of a DateTime and pass the string to MySQL:

// convert DateTime to string with formatting for MySQL
string dateformysql = mydatetime.ToString("yyyy-MM-dd HH:mm:ss");

References:
How to truncate milliseconds off of a .NET DateTime
Why doesn't MySQL support millisecond / microsecond precision?
ODBC Connector 5.1.11 Changelog

Community
  • 1
  • 1
nextgentech
  • 3,561
  • 1
  • 24
  • 19