Pomelo.EntityFrameworkCore.MySql: Auto-increment does not work on Id (primary) column

Hi, When I’m trying to add new entity to database (mariadb 5.5.52). Ef always tries to set Id column (primary key) to 0. I’ve tried to switch to MS SQL and it works fine. Command Update-Database works without any exception.

Pomelo version: 1.1.1 Mariadb: 5.5.52 (centos 7)

dotnet --version
1.0.0

Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    /* ... */
    services.AddDbContext<DataContext>(options => options.UseMySql(Configuration.GetConnectionString("production"))); 
    /* ... */  

}

Connection string: server=localhost;userid=remote;pwd=password;port=3306;database=db;sslmode=none;

Models:

    public class Select : IHasId
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public string Name { get; set; }

        public List<SelectValue> Values { get; set; }
    }

    public class SelectValue : IHasId
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public string Value { get; set; }
        public string Text { get; set; }

        public override string ToString()
        {
            return Value;
        }
    }

    public interface IHasId
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        int Id { get; set; }
    }

Adding entity:

            context.Selects.Add(new Select()
            {
                Name = "type",
                Values = new List<SelectValue>()
            });

            context.Selects.Add(new Select()
            {
                Name = "composition",
                Values = new List<SelectValue>()
                {
                    new SelectValue()
                    {
                        Value = ""
                    },
                    new SelectValue()
                    {
                        Value = "Val1"
                    },
                    new SelectValue()
                    {
                        Value = "Val2"
                    }
                }
            });

            context.SaveChanges();

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry '0' for key 'PRIMARY'
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
         at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
         at MySql.Data.MySqlClient.Results.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__62.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__61.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__50.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.<ExecuteAsync>d__3.MoveNext()
      --- End of stack trace from previous location where exception was thrown ---
         at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
         at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
         at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
         at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Execute(IRelationalConnection connection)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Execute(IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> MySql.Data.MySqlClient.MySqlException: Duplicate entry '0' for key 'PRIMARY'
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySql.Data.MySqlClient.Results.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__62.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__61.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteReaderAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlCommand.<ExecuteReaderAsync>d__50.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.<ExecuteAsync>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Execute(IRelationalConnection connection)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.Internal.MySqlModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)

About this issue

  • Original URL
  • State: closed
  • Created 7 years ago
  • Comments: 30 (5 by maintainers)

Most upvoted comments

I have noticed in my code have this

migrationBuilder.CreateTable(
                name: "Select",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("MySQL:AutoIncrement", true),
                    Name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Select", x => x.Id);
                });

this line .Annotation("MySQL:AutoIncrement", true), causes the script generated without the auto_increment on primary key So I had to change to .Annotation("MySql:ValueGeneratedOnAdd", true), Every thing work like charm

+7
chikien276 on Aug 26, 2017

@Kagamine @Yakkuru @deepak-khopade-isdr this is being fixed in #371 and will be available in the next 2.0.0-rtm release sometime tonight or tomorrow

+3
caleblloyd on Aug 29, 2017

My issue was simple, I had generated the migrations originally using SqlServer connection strings. When I regenerated the migrations everything worked as it should have.

+2
ShawnAbshire on Aug 11, 2018

Have a similar problem, i’ve upgraded to .NET Core 2.0 and Pomelo 2.0.0-rtm-10058, just tried to add a new blank migration just to see if there would be any changes (because I’m using Identity) and it generated a new migration with all my tables in it to basically AlterColumn of the Id field and remove my auto_increment to replace it with .Annotation(“MySql:ValueGenerationStrategy”, MySqlValueGenerationStrategy.IdentityColumn); wonder what it is all about? It’s seems related to this issue #362

+2
Yakkuru on Aug 28, 2017

Sorry no time for repro. I manually rechecked the auto increment box on all tables.

+1
softlion on Feb 18, 2018

Please remove DatabaseGeneratedAttribute and KeyAttribute. The field will be auto increment automatically without that two attributes.

+1
yukozh on May 20, 2017

try add [Key]

for example :

 [Key]
 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
 public int Id { get; set; }

MySQL requires auto increment field to be a key

+1
moemura on Mar 22, 2017
Read more comments on GitHub
← Pomelo.EntityFrameworkCore.MySql: An exception occurred while reading a database value. The expected type was 'System.Byte[]' but the actual value was of type 'System.DateTime'.
Pomelo.EntityFrameworkCore.MySql: "Cannot access a disposed object. Object name: 'MySqlConnection'." when trying to persist 35 MB to MariaDb. →