MySQL Bugs: #52141: Invalid serialization of a DataTable colums of type MySqlDateTime

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:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.1.1	OS:	Windows (Windows XP SP3)
Assigned to:	Tony Bedford	CPU Architecture:	Any
Tags:	ADO.NET, dataset, MysqlDateTime, serialization

Description:
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
                            MySQLConnection1.Open()
                            Using Adapter As New MySqlDataAdapter(Command1)
                                If QueryParameters IsNot Nothing Then
                                    BuildParameterCollection(Command1.Parameters, QueryParameters)
                                End If
                                Adapter.Fill(DataSet,"MyTableName")

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>

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. 

Thanks

Hi Janaka and thanks for your report. I agree we should try better document this so assigning Tony to fix it.

The documentation for connection string option Allow Zero Datetime has been clarified. (XML sources have been updated)