Bug #64593 Connector fails when parsing mysql DATETIME values that include microseconds
Submitted: 8 Mar 2012 15:31 Modified: 1 Jun 2012 13:39
Reporter: Dan Berger Email Updates:
Status: Can't repeat Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.4.5 OS:Windows
Assigned to: Gabriela Martinez Sanchez CPU Architecture:Any

[8 Mar 2012 15:31] Dan Berger
When using a query returns a DATETIME value that include microseconds, the parsing code throws a FormatExection.

Here's the stack trace:
	System.FormatException: Input string was not in a correct format.
	at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
	at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
	at MySql.Data.Types.MySqlDateTime.ParseMySql(String s, Boolean is41)
	at MySql.Data.Types.MySqlDateTime.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
	at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
	at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
	at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
	at MySql.Data.MySqlClient.MySqlDataReader.Read()
	at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()
	ExecutableTest.cs(3568,0): at DuncanTest.ExecutableTest.ShowMicrosecondError()

How to repeat:
public void ShowMicrosecondError()
    string ConnectionString = "server=fr-dev1;user=dev;port=3306;password=test";
    using (var Conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString))

        using (var Cmd = Conn.CreateCommand())
            Cmd.CommandText = "SELECT NOW() + INTERVAL 123456 MICROSECOND";

            var r = Cmd.ExecuteScalar(); // throws FormatException

Suggested fix:
MySql.Data.Types.MySqlDateTime.ParseMySql(string s) function reads splits the string and then does int.Parse on each part. The seconds part could be parsed as a double - this would handle all fractional seconds.
[2 May 2012 20:44] Gabriela Martinez Sanchez
Hi Dan, I tried to reproduce the bug but looks like it is already fixed. We're about to release a new version for this 6.4 branch which includes this fix, so I would like to ask you to upgrade to get the fix. Please check the http://forums.mysql.com/list.php?38 for the release announcement.
[1 Jun 2012 13:39] Dan Berger
This does not seem to be fixed in 6.4.5 - I still get the FormatException.

I can narrow the problem down even more if it helps:

MySqlDateTime Success = new MySqlDateTime("2012-01-01 12:00:00");

MySqlDateTime Failure = new MySqlDateTime("2012-01-01 12:00:00.123456"); // throws FormatException

My server is "MySQL 5.1.41-3ubuntu12.10", if that makes a difference.