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 Email Updates:
Status: Verified Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.22, 8.0.23 OS:Linux (Gentoo)
Assigned to: CPU Architecture:Other (x64)

[23 Oct 2020 16:54] Foo Bar
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;
FROM `Invoices`
 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.

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 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) {
        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();
[24 Oct 2020 14:39] MySQL Verification Team

Thank you for the report and feedback.

[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());