Bug #52141 Invalid serialization of a DataTable colums of type MySqlDateTime
Submitted: 17 Mar 2010 14:40 Modified: 28 Apr 2010 16:30
Reporter: Janaka Abeywardhana Email Updates:
Status: Closed Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.1.1 OS:Microsoft Windows (Windows XP SP3)
Assigned to: Tony Bedford CPU Architecture:Any
Tags: ADO.NET, dataset, MysqlDateTime, serialization

[17 Mar 2010 14:40] Janaka Abeywardhana
When a System.Data.DataTable with a column of type MySql.Data.MySqlDateTime is serialized the value of the column seems to be serialized incorrectly. I see 2 problems.

1) the value is serialized with as structured xml, with an xml decleration and then escaped. So an xml parser is unable to parse the actually data out e.g. where the the field name is "LastModified"

<LastModified>&lt;?xml version="1.0" encoding="utf-16"?&gt; &lt;MySqlDateTime xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;   &lt;Year&gt;2007&lt;/Year&gt;   &lt;Month&gt;11&lt;/Month&gt;   &lt;Day&gt;23&lt;/Day&gt;   &lt;Hour&gt;13&lt;/Hour&gt;   &lt;Minute&gt;4&lt;/Minute&gt;   &lt;Second&gt;35&lt;/Second&gt;   &lt;Millisecond&gt;0&lt;/Millisecond&gt; &lt;/MySqlDateTime&gt;</LastModified>

2) the ADO.Net providers for MS SQL Server and Oracle use System.Datatime and this type serializes the datevalue to a simple string. This is easier to work with and allows code to be more cross database compatible.  

E.g. <LastModified>2007-11-23T13:07:19+00:00</LastModified>

How to repeat:
Dim SQL as string = "SELECT SomeDateTimeColumn FROM SomeTableWithDatetimeColumn"             

       Using MySQLConnection1 As New MySqlConnection(ConnectionString)
                        SQL = ConvertToMySQLIdentifierDelimiter(SQL)
                        Using Command1 As New MySqlCommand(SQL, MySQLConnection1)
                            Dim timeout As Int32 = 30

                            Command1.CommandTimeout = timeout
                            Using Adapter As New MySqlDataAdapter(Command1)
                                If QueryParameters IsNot Nothing Then
                                    BuildParameterCollection(Command1.Parameters, QueryParameters)
                                End If

Dim xmldom1 as New XmlDataDocument(DataSet)

dim output as string = xmldom1.innerXml
                            End Using
                        End Using
                    End Using

Suggested fix:
serialize the value as a simple string e.g. where the column name is LastModified <LastModified>2007-11-23T13:07:19+00:00</LastModified>
[18 Mar 2010 3:32] Janaka Abeywardhana
Hi, Have looked into this in greater detail and basically understood that Allow Zero Datetime also controls this behaviour. when set to false the dataset serializes correctly. Details on my blog http://www.janaka.co.uk/2010/03/serialization-issue-with-mysqldatetime.html if interested. If anything better error messages and documentation is probably what is lacking.

Sorry if I wasted anybodies time. Please close the bug at your discretion. 

[7 Apr 2010 8:20] Tonci Grgin
Hi Janaka and thanks for your report. I agree we should try better document this so assigning Tony to fix it.
[28 Apr 2010 16:30] Tony Bedford
The documentation for connection string option Allow Zero Datetime has been clarified. (XML sources have been updated)