Bug #80011 MySQL.Data.dll won't serialize .NET DateTime objects to the new versions correct
Submitted: 15 Jan 2016 22:10 Modified: 18 Jan 2016 8:07
Reporter: Tuomas Hietanen Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.8, 8.0.11 OS:Microsoft Windows (Windows Server 2012 R2 Datacenter, Windows 10)
Assigned to: CPU Architecture:Any
Tags: datetime, incorrect datetime value, MySql.Data.dll

[15 Jan 2016 22:10] Tuomas Hietanen
Description:
MySQL.Data.dll won't serialize .NET DateTime objects to the new versions correctly.

----

This example is using UK localizations. If I make a table having DateTime type column called DateAdded and try to add .NET DateTime.Now to it, it will fail:

"MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect datetime value: '15/01/2016 17:56:40' for column 'DateAdded' at row 1\r\n   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()\r\n   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)\r\n   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)\r\n   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()\r\n   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)\r\n   at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

How ever, this is perfect .NET datetime value. It is not in ISO 8601 format though, which can be the reason.

----

Also comparison won't work. If I try to execute this SQL:

 SELECT `c`.`Id` as `Id`,`c`.`Name` as `Name`,
        `c`.`DatabaseDateTime` as `DatabaseDateTime`
 FROM MyDatabase.Items as `c` 
 WHERE ((`c`.`DatabaseDateTime`< @param1 AND `c`.`DatabaseDateTime`> @param2))

and give parmeters 
  // param2      = 1/1/1970 12:00:00 AM
  // c.DatabaseDateTime = 4/4/1975 12:00:00 AM
  // param1     = 1/11/2016 4:35:17 PM

Then the param2 condition is always true and param1 condition is never true.

How to repeat:
You can reproduce this in many ways. But here is one, not maybe the simplest one though.

Use some new Windows. Have UK localization settings and a new operating system.

Install some MySQL server, e.g. MariaDB
Add a database and a data table containing datetime field.

Create new F# project and take into use SQLPrvider Nuget package.
Try to insert some data to your table, or filter selection with dates.

Suggested fix:
Serialization should be always done in ISO 8601 format: yyyy-mm-dd hh:mm:ss
[18 Jan 2016 8:07] Chiranjeevi Battula
Hello Tuomas Hietanen,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi.
[18 Jan 2016 8:07] Chiranjeevi Battula
Error Message:
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
[18 Jan 2016 8:09] Chiranjeevi Battula
Screenshot.

Attachment: 80011.png (image/png, text), 141.41 KiB.

[11 Jun 2018 6:46] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=91199 marked as duplicate of this one.