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