Bug #101294 Invalid query generated for new entity without any values results in exception

• Submitted: 23 Oct 2020 16:54
• Modified: 24 Oct 2020 14:39
• Reporter: Foo Bar
• Status: Verified
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 8.0.22, 8.0.23
• OS: Linux (Gentoo)
• CPU Architecture: Other (x64)

[23 Oct 2020 16:54] Foo Bar

Description:
When an EntityFramework Core entity without values is added via a SaveChanges() call on the context, the generated query will contain the "DEFAULT VALUES" expression. The MySQL server does not support this syntax, it must be "VALUES()" instead.

The "problem" is that after the "INSERT INTO" query has been send, the EntityFramework will automatically send a "SELECT" query afterwards to get the generated values for the new added entity. There is/was a fix in <https://github.com/mysql/mysql-connector-net/blob/6.9/Source/MySql.Data/command.cs#L138> which handle such a case. However, this "DEFAULT VALUES" part is not at the end of the query. As you see in the following exception, the "DEFAULT VALUES" part was in the middle of the query, so the fix in the MySql.Data.MySqlClient.MySqlCommand class doesn't work anymore:

MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES;
SELECT `Id`
FROM `Invoices`
WHERE ROW_COUNT() = 1
 AND `Id`=LAST' at line 2
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

When you use the [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute on the properties of the entity, it will not generate the SELECT query. However, when using this attribute the value will be missing in the tracked entity of EntityFramework.

Versions:
MySql.Data.EntityFrameworkCore: 8.0.22
Microsoft.EntityFrameworkCore: 3.1.9
MySQL Server version: 8.0.21 Gentoo Linux mysql-8.0.21-r1
C# target framework: netcoreapp3.1

How to repeat:
Create the following "Invoices" table:

CREATE TABLE Invoices
(
    Id int AUTO_INCREMENT PRIMARY KEY
);

Create the following "Invoice" C# class for the EntityFramework:

public class Invoice
{
   public int Id { get; set; }
}

Create the following DbContext class for the EntityFramework:

public class MyContext : DbContext {
 
    public DbSet<Invoice> Invoices {get; set;}

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseMySQL("server=localhost;database=test;"); // or any other valid connection string
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
    }
}

Create a class with the following Main method:

public static void Main()
{   
    using (MyContext context = new MyContext())
    {
        Invoice invoice = new Invoice();
        context.Invoices.Add(invoice);
        context.SaveChanges();                
    }
}

[24 Oct 2020 14:39] MySQL Verification Team

Hello!

Thank you for the report and feedback.

regards,
Umesh

[25 Mar 2021 13:18] MySQL Verification Team

Bug #103049 marked as duplicate of this one.

[19 May 2023 20:28] Søren Nielsen

Can be worked around by using a DbCommandInterceptor:

--------------------------------------------------------

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;

public class MySqlDefaultValuesInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        if (command.CommandText.Contains("DEFAULT VALUES()"))
        {
            command.CommandText = command.CommandText.Replace("DEFAULT VALUES()", "VALUES()");
        }

        return result;
    }
}

--------------------------------------------------------

And then in your DbContext:

--------------------------------------------------------

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new MySqlDefaultValuesInterceptor());
    base.OnConfiguring(optionsBuilder);
}   

--------------------------------------------------------