MySQL Bugs: #80011: MySQL.Data.dll won't serialize .NET DateTime objects to the new versions correct

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

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

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.

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()

Screenshot.

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

http://bugs.mysql.com/bug.php?id=91199 marked as duplicate of this one.

Posted by developer:
 
Verified using the latest release, v8.0.31.