MySQL Bugs: #113380: MySql.Data Client creates trailing space in datetime parameter causing app crash

Bug #113380	MySql.Data Client creates trailing space in datetime parameter causing app crash
Submitted:	8 Dec 2023 16:36	Modified:	15 Dec 2023 16:58
Reporter:	Alex Kundzich	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0.32	OS:	Windows (2019 Server)
Assigned to:		CPU Architecture:	Any
Tags:	MySqlCommand MySqlParameter datetime

Description:
MySql.Data.MySqlClient creates an Extra trailing space in datetime query parameter which causes C# app to crash. It presents the date as '2022-11-28 00:00:00 '.
This bug was not noticeable before ver. 8.0.29. The server still returns a correct recordset, but this and later versions also sends a WARNING message after the recordset, which can't be deserialized and thus causing a crash.

How to repeat:
Create a table with a datetime column.
Run this code in C# app:
  MySqlCommand cmd = new MySqlCommand("SELECT * FROM Activity WHERE ActivityDate >= @cDate", connection);
  cmd.Parameters.AddWithValue("cDate", new DateTime(2022,10,20));
  MySqlDataReader reader = cmd.ExecuteReader();
  < Loop through reader... >

Put a breakpoint in the loop and view in debugger that recordset returns the correct data until the Warning comes from the server:
Delimiter ' ' in position 19 in datetime value '2022-11-28 00:00:00 ' at row 1 is superfluous and is deprecated. Please remove.

Checking the General-Query log shows that the query was:
SELECT * FROM Activity WHERE ActivityDate >= '2022-11-28 00:00:00 '
with incorrect trailing space

Suggested fix:
Fix the Data Client or make an option to disable warnings Globally

Hello Alex,

Thank you for the bug report.
Could you please provide complete repeatable test case along with table structure for "Activity"?

Regards,
Ashwini Patil

-- It can be any table with a datatime.
Create table Activity (id int, ActivityDate datetime);
INSERT INTO Activity () VALUES (1,'2022-01-01'), (2,'2023-01-01');

This is modified sample from your tutorial
    https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html
public class Tutorial2
{
    public static void Main()
    {
        MySqlConnection conn = new MySqlConnection(ConnectionString);
        try
        {
            conn.Open();
            MySqlCommand cmd = new MySqlCommand(SELECT id FROM Activity WHERE ActivityDate >= @cDate", conn);
	    cmd.Parameters.AddWithValue("cDate", new DateTime(2022, 10, 20));
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                Console.WriteLine(reader.GetInt32(0).ToString());
            }
            rdr.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
			// At this point you will see the exception
			// "Fatal error encountered attempting to read the resultset."
        }
        conn.Close();
    }
}

In MySQL General_Log (should be enabled) you should see the actual Query was
SELECT id FROM Activity WHERE ActivityDate >= '2022-10-20 00:00:00 '
- Extra Trailing space causes the error.

Hello Alex,

Thank you for the details.
I tried to reproduce your issue on Windows 11 with Connector/NET 8.0.32 and provided test case but I am not seeing any issues at my end. If you can provide more information, feel free to add it to this bug. Thanks.

Regards,
Ashwini Patil

Was your MySQL Server version also 8.0.32? 
If this is the case, then likely the issue was resolved in latest MySQL.Data versions, so you can close this issue.
I was using MySQL Server 8.0.32 and Connector MySQL.Data version 6.9.9.0.

This bug was fixed in MySql.Data over six years ago: https://github.com/mysql/mysql-connector-net/commit/da35d906125d684e105ced866c438cf3ae36d8....

You should update to a more recent, supported version.