MySQL Bugs: #70686: Wrong milliseconds serialization of time(3) and time(6)

Bug #70686	Wrong milliseconds serialization of time(3) and time(6)
Submitted:	22 Oct 2013 10:23	Modified:	10 Aug 2022 17:16
Reporter:	Jerzy Jaskiewicz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	milliseconds

Description:
When Connector.NET updates TIME(6) fields with milliseconds value greater than zero, but less than 0.1 seconds, it treats highest significant digit of milliseconds part as hundreds of milliseconds, e.g.
2 ms -> 200 ms
10 ms -> 100 ms
0.009 ms -> 900 ms (!)
because it omits trailing zeros while serialization milliseconds part.

How to repeat:
Write seconds TimeSpan(0, 0, 0, 10) (10 ms) value using Connector.NET to database field of TIME(6) type. The stored value is 0:0:0.100000, which is incorrect.

Suggested fix:
In MySqlTime.cs file, lines 107-108:

String s = String.Format("'{0}{1} {2:00}:{3:00}:{4:00}.{5:000000}'",
       negative ? "-" : "", ts.Days, ts.Hours, ts.Minutes, ts.Seconds, (ts.Ticks/10)%1000000);

Hello Jerzy.

After some test and analysis, I was not able to reproduce the issue.
Please, can you share the code or the project where you found the issue? That will be very helpful.

This is the code that I'm using:
...
    try
    {
      var command = new MySqlCommand();
      var value = new TimeSpan(0, 0, 0, 0, 10);
      command.Connection = _connTarget;
      _OpenTargetConn();
      command.CommandText = string.Format("INSERT INTO Test(timeCol) VALUES('{0}');", value);
      command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
      Console.WriteLine(string.Format("Error: {0} | StackTrace:{1}", ex.Message, ex.StackTrace));
      throw ex;
    }
    finally
    {
      _CloseTargetConn();
    }
...

Thanks for your time.

Hello Francisco,
in code you have posted, time value serialization is done with string.Format(). Problem appears when MySQLConnector serializes the value itself:

var command = new MySqlCommand();
var value = new TimeSpan(0, 0, 0, 0, 10);
command.Connection = _connTarget;
_OpenTargetConn();
command.CommandText = "INSERT INTO Test(timeCol) VALUES(@timeCol);";
command.Parameters.AddWithValue("@timeCol", value); 
command.ExecuteNonQuery();

Hope it helps,
Regards, Jurek

Added the following entry to the Connector/Net 6.8.2 changelog:

"Millisecond values written to a TIME(6)-typed field of a database were wrongly serialized by Connector/Net."

The error returned in 6.9.6 (maybe earlier).

Hello Jerzy Jaskiewicz,

Thank you for your feedback.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net), MySQL Connector/Net 6.9.8 version but not seeing any issues in milliseconds.
please try with latest version.

Thanks,
Chiranjeevi.

Hi Chiranjeevi Battula,
I've also tested, and noticed, that it occurs with 6.9.8, but not with timestamp fields, but - for sure - with time(3) and time(6) fields. When I write 0.04s, the written value is 0.4s (as MySQLWokbench shows).
Kind regards,
Jurek

updated version

Hello Jerzy Jaskiewicz,

Thank you for your feedback.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net) and Connector/Net 6.9.8 but not seeing any issues in milliseconds serialization.
Could you please provide repeatable test case (create table statements, sample code etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

Screenshot.

Attachment: 70686.JPG (image/jpeg, text), 87.94 KiB.

The test code:

namespace BugTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new MySqlConnection("SERVER = localhost; DATABASE = test; UID = root; Keepalive = 300; charset = utf8;"))
            {
                conn.Open();
                if (conn.State == System.Data.ConnectionState.Open)
                {
                    MySqlCommand command = new MySqlCommand("INSERT INTO test_table (time3, time6) VALUES (@time3, @time6);", conn);
                    command.Parameters.AddWithValue("@time3", new TimeSpan(0, 0, 0, 0, 10));  //10 ms
                    command.Parameters.AddWithValue("@time6", new TimeSpan(0, 0, 0, 0, 10)); // 10 ms
                    var id = command.ExecuteNonQuery();

                    command = new MySqlCommand("SELECT time3, time6 FROM test_table where id=@id;", conn);
                    command.Parameters.AddWithValue("@id", id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine("time3 field value {0}", reader.GetTimeSpan("time3"));
                            Console.WriteLine("time6 field value {0}", reader.GetTimeSpan("time6"));
                        }
                    }
                    Console.ReadLine();
                }
            }                
        }
    }
}

Output:
time3 field value 00:00:00.1000000
time6 field value 00:00:00.1000000

Output shows the same values as MySql Workbench.

Hello Jerzy Jaskiewicz,

Thank you for your feedback and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.8 version.

Thanks,
Chiranjeevi.

I have developed an open source MySQL ADO.NET library that doesn't have this bug. It's MySqlConnector on NuGet and should be a drop-in replacement for MySql.Data: https://www.nuget.org/packages/MySqlConnector/

Full source code is here: https://github.com/mysql-net/MySqlConnector

This bug has been fixed in commit 305211d and is backed by test "MySql.Data.Tests.DateTimeTests.CanUpdateMillisecondsUsingTimeType".

This bug report should be closed.

This bug has been fixed in commit 305211d and is backed by test
"MySql.Data.Tests.DateTimeTests.CanUpdateMillisecondsUsingTimeType".