Bug #87120 EntityFrameworkCore MySQLDataReader throws exception with 0000-00-00 date

• Submitted: 19 Jul 2017 14:33
• Modified: 6 Oct 2017 20:48
• Reporter: Thomas Wendt
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 8.0.8-dmr
• OS: Any
• Assigned to: Roberto Ezequiel Garcia Ballesteros
• CPU Architecture: Any
• Tags: ConvertZeroDateTime, datetime, entityframework

[19 Jul 2017 14:33] Thomas Wendt

Description:
Even with Convert Zero Datetime=True a MySqlConversionException is thrown:

Unable to convert MySQL date/time value to System.DateTime
   at MySql.Data.Types.MySqlDateTime.GetDateTime()
   at MySql.Data.EntityFrameworkCore.Storage.Internal.MySQLDataReader.GetDateTime(Int32 ordinal)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at MySQLTest.Program.Main(String[] args) in c:\users\twendt\documents\visual studio 2017\Projects\MySQLTest\MySQLTest\Program.cs:line 55

How to repeat:
1. Create an empty database with a user allowed to create tables
2. Download the attached project
3. Adjust the connection with the correct database and user
4. Execute

Suggested fix:
In EntityFrameworkCore/src/MySql.Data.EntityFrameworkCore/Storage/Internal/MySQLDataReader.cs replace line 63
public override DateTime GetDateTime(int ordinal) => GetReader().GetMySqlDateTime(ordinal).GetDateTime();

with this

public override DateTime GetDateTime(int ordinal) => GetReader().GetDateTime(ordinal);

[19 Jul 2017 14:35] Thomas Wendt

.NetCore Project to reproduce the issue

Attachment: MySQLTest.zip (application/zip, text), 2.14 KiB.

[29 Jul 2017 0:18] Jonatan Bouillon

I had the same problem and the cause is because in the  MySql.Data.EntityFrameworkCore.Storage.Internal MySQLDataReader the function look not to be the rigth one call. 

Right now it is using the GetMySqlDateTime(ordinal).GetDateTime() but this is not working with date format '0000-00-00'. You must use the following function instead.

public override DateTime GetDateTime(int i)
    {
      IMySqlValue val = GetFieldValue(i, true);
      MySqlDateTime dt;

      if (val is MySqlDateTime)
        dt = (MySqlDateTime)val;
      else
      {
        // we need to do this because functions like date_add return string
        string s = GetString(i);
        dt = MySqlDateTime.Parse(s);
      }

      dt.TimezoneOffset = driver.timeZoneOffset;
      if (_connection.Settings.ConvertZeroDateTime && !dt.IsValidDateTime)
        return DateTime.MinValue;
      else
        return dt.GetDateTime();
    }

I have change it for the following and it works.

Before : 
public override DateTime GetDateTime(int ordinal) => GetReader().GetMySqlDateTime(ordinal).GetDateTime();

After :
public override DateTime GetDateTime(int ordinal) => GetReader().GetDateTime(ordinal);

You must also add the ConvertZeroDateTime in the connexionstring.

[31 Jul 2017 15:49] Chiranjeevi Battula

Hello  Thomas,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.

[6 Oct 2017 20:48] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.10.4 release, and here's the changelog entry:

Inserting a date of '0000-00-00' into a column when also setting Convert
Zero Datetime=True in the connection string of a class that derives from
DbContext produced an exception, instead of performing the expected
conversion.

Thank you for the bug report.