Pomelo.entityframeworkcore.mysql: MySqlException: Field 'Id' doesn't have a default value

[SirwanAfifi]

The issue

In one of our projects we are using Pomelo.EntityFrameworkCore.MySql version 2.2.0 but when we try to save/update an entity we get MySqlException: Field 'Id' doesn't have a default value error.

MySqlException: Field 'Id' doesn't have a default value
MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs
MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary<string, CachedProcedure> cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs
MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList<IMySqlCommand> commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs
MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs
System.Data.Common.DbCommand.ExecuteReader()
Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlConverterCommandBuilderFactory+MySqlConverterRelationalCommandBuilder+MySqlConverterRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Here's also how we have defined our base model:

public class BaseModel
{
    [Key]
    [Column(TypeName = "VARCHAR(64)")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    public bool IsDeleted { set; get; }
}

Further technical details

MySQL version:
Operating system: macOS and Windows
Pomelo.EntityFrameworkCore.MySql version: 2.2.0 and 2.2.6
Microsoft.AspNetCore.App version: netcoreapp2.2

[lauxjpn]

@SirwanAfifi We don't actively maintain the 2.2 branch anymore. So my general advice would be to upgrade to either 3.2.4 or 5.0.0-alpha.2.

If you cannot upgrade for some reason, we would need to see more information to find the underlying cause in your particular case. I assume this is an app that is already in production for some time, since you are using an outdated version of Pomelo and .NET Core.

For starters, provide us with the exact entity (including all the values) you are trying to save, with some information whether anything special is happening to the entity before the save operation (e.g. context attachments or detachments), and whether this happens only under certain conditions or if this always happens.

[SirwanAfifi]

@lauxjpn Thanks for your help. We are in the process of migrating from SQL Server to MySQL. The app is in production with SQL Server now we are going to switch to MySQL.

After reading your comment I looked at our DbContext and you're right. There are some operations before/after saving entities:

public override int SaveChanges()
{
    var modifiedEntries = this.ChangeTracker
        .Entries<GolfLink>()
        .Where(x => x.State == EntityState.Modified);

    foreach (var modifiedEntry in modifiedEntries)
    {
        modifiedEntry.Entity.StatusChangesDateTime = DateTime.UtcNow;
    }

    var auditEntries = OnBeforeSaveChanges();
    var result = base.SaveChanges();
    OnAfterSaveChanges(auditEntries);
    return result;
}

private List<AuditEntry> OnBeforeSaveChanges(User userInfo = null)
{
    ChangeTracker.DetectChanges();
    var auditEntries = new List<AuditEntry>();
    foreach (var entry in ChangeTracker.Entries())
    {
        if (entry.Entity is Audit || entry.State == EntityState.Detached || entry.State == EntityState.Unchanged)
            continue;

        var auditEntry = new AuditEntry(entry);
        auditEntry.TableName = entry.Metadata.Relational().TableName;
        auditEntries.Add(auditEntry);

        foreach (var property in entry.Properties)
        {
            if (property.IsTemporary)
            {
                // value will be generated by the database, get the value after saving
                auditEntry.TemporaryProperties.Add(property);
                continue;
            }

            string propertyName = property.Metadata.Name;
            if (property.Metadata.IsPrimaryKey())
            {
                auditEntry.KeyValues[propertyName] = property.CurrentValue;
                continue;
            }

            switch (entry.State)
            {
                case EntityState.Added:
                    auditEntry.NewValues[propertyName] = property.CurrentValue;
                    break;

                case EntityState.Deleted:
                    auditEntry.OldValues[propertyName] = property.OriginalValue;
                    break;

                case EntityState.Modified:
                    if (property.IsModified)
                    {
                        auditEntry.OldValues[propertyName] = property.OriginalValue;
                        auditEntry.NewValues[propertyName] = property.CurrentValue;
                    }
                    break;
            }
        }
    }

    var user = GetUserInfo(userInfo);
    // Save audit entities that have all the modifications
    foreach (var auditEntry in auditEntries.Where(_ => !_.HasTemporaryProperties))
    {
        Audits.Add(auditEntry.ToAudit(user.userId, user.userName));
    }

    // keep a list of entries where the value of some properties are unknown at this step
    return auditEntries.Where(_ => _.HasTemporaryProperties).ToList();
}

private void OnAfterSaveChanges(List<AuditEntry> auditEntries, User userInfo = null)
{
    if (auditEntries == null || auditEntries.Count == 0) return;


    var user = GetUserInfo(userInfo);

    foreach (var auditEntry in auditEntries)
    {
        // Get the final value of the temporary properties
        foreach (var prop in auditEntry.TemporaryProperties)
        {
            if (prop.Metadata.IsPrimaryKey())
            {
                auditEntry.KeyValues[prop.Metadata.Name] = prop.CurrentValue;
            }
            else
            {
                auditEntry.NewValues[prop.Metadata.Name] = prop.CurrentValue;
            }
        }

        // Save the Audit entry
        Audits.Add(auditEntry.ToAudit(user.userId, user.userName));
    }

    SaveChanges();
}

After commenting out these lines of code it worked:

/* 
  public override int SaveChanges() ....
*/

But I am wondering what could be the possible issue with the auditing part, Are we doing something wrong in the code?

[lauxjpn]

@SirwanAfifi If you are currently migrating your app, you should strongly consider to upgrade at least to EF Core 3.1.10 & Pomelo 3.2.4.

That being said, you should try your code just with the OnBeforeSaveChanges() and then just with the OnAfterSaveChanges() call (comment-out the other one).

If one of those variants works with SaveChanges(), investigate the one that does not work further (meaning to comment out parts of the method or to step through it with the debugger and to watch the CurrentValue of the properties very carefully to see when an unexpected change happens.

Feel free to report back to us, once you have done these steps and have more information.

[SirwanAfifi]

Thanks, I appreciate it.

I just tested the code with the steps you mentioned I then figured out that Id in the Audit table wasn't auto incremented. I updated that table and now everything works as expected:

ALTER TABLE `DBNAME`.`audits` 
CHANGE COLUMN `Id` `Id` INT NOT NULL AUTO_INCREMENT ;

Thanks for your help :)

[lauxjpn]

@SirwanAfifi Glad you figured it out and thanks for lettig us know!

[mguinness]

As already noted .NET Core 2.2 EOL was Dec 2019 and .NET Core 2.1 EOL is Aug 2021 (if you want security patches). FWIW, there is also a bug w/ IsTemporary property, see https://github.com/dotnet/efcore/issues/17700 for futher information.