Pomelo.entityframeworkcore.mysql: Auto-increment does not work on Id (primary) column

Created on 21 Mar 2017 · 30Comments · Source: PomeloFoundation/Pomelo.EntityFrameworkCore.MySql

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:
```c#
public void ConfigureServices(IServiceCollection services)
{
/* ... /
services.AddDbContext(options => options.UseMySql(Configuration.GetConnectionString("production")));
/ ... */

}


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

Models:
```c#
    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)

closed-question

Source

AlfonzAlfonz

Most helpful comment

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

chikien276 on 20 May 2017

👍7

All 30 comments

try add [Key]

for example :

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

MySQL requires auto increment field to be a key

moemura on 22 Mar 2017

👍1

I've tried to add [Key] attributte and EF generated an empty migration and nothing have happened. It seems that EF sets set Id column as a key but doesn't set it as auto-increment column.

AlfonzAlfonz on 22 Mar 2017

When I add AI manually it works fine.

AlfonzAlfonz on 22 Mar 2017

Sir. Is there any workaround on this issue ?
The problem is Pomelo generates create table script without auto_incement on Id column

chikien276 on 20 May 2017

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

yukozh on 20 May 2017

👍1

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);
                });

chikien276 on 20 May 2017

👍7

still, throws this exception MySqlException: Duplicate entry '1' for key 'PRIMARY' at the latest version of the library. For newly created table where no data present.

masums on 23 Aug 2017

@masums which version do you use?
I use latest 2.0-rtm and 1.1.2 and there are fine.
try remove migration or try @chikien276 method

moemura on 26 Aug 2017

When upgraded to .NET Core 2.0 and Pomelo 2.0.0-rtm-10058, started getting this error for a table which has no data yet. Please advise.

deepak-khopade-isdr on 27 Aug 2017

Complete stack trace below:
fail: Microsoft.EntityFrameworkCore.Update[100000] An exception occurred in the database while saving changes for context type 'app.common.db.AppDb'. 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 MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) at MySql.Data.MySqlClient.MySqlDataReader.<NextResultAsync>d__5.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.NextResult() at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries) 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 'PRIMAR Y' at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) at MySql.Data.MySqlClient.MySqlDataReader.<NextResultAsync>d__5.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.NextResult() at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.MySqlBatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries) 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)

deepak-khopade-isdr on 27 Aug 2017

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

Yakkuru on 28 Aug 2017

👍2

This was a breaking change when upgrading your old 1.1 project to 2.0. Now we are using MySqlValueGenerationStrategy.IdentityColumn instead of the old MySqlValueGeneratedOnAdd. So for each users, you must modify the old one into MySqlValueGenerationStrategy.IdentityColumn.

This was just align to the official ef core.

yukozh on 28 Aug 2017

@Kagamine, @Yakkuru: thanks for your responses here; I found that almost all our models need to be changed to accommodate this.

deepak-khopade-isdr on 28 Aug 2017

@Kagamine But executing the migration removes the auto_increment from the Id.
I tested creating a new model the migration will correctly add the auto_increment.

Yakkuru on 28 Aug 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

caleblloyd on 29 Aug 2017

👍3

Thanks, @caleblloyd; appreciated.

deepak-khopade-isdr on 29 Aug 2017

@caleblloyd, @Kagamine : added the migration to make sure to adopt the new strategy for aut-increment key generation like below:
migrationBuilder.AlterColumn<int>( name: "NamespaceId", table: "xbrl_Namespace", type: "int", nullable: false, oldClrType: typeof(int)) .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);
This migration was suucessfull fo the entities wherever it was applicable; however, when trying to insert data to these tables, still getting an error like previous:
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'

Any recommendations further?

Currently using: <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.0.0-rtm-10058" />

deepak-khopade-isdr on 4 Sep 2017

Please upgrade to 10062

yukozh on 5 Sep 2017

@Kagamine: yes tried that too, but no luck. I will create a small sample possible and try on it and then can share that repo if I reproduce the issue. Thanks.

deepak-khopade-isdr on 5 Sep 2017

The DB connection has no issues, however, found that wherever I have AUTO_INCREMENT int as a PK, and when new migration is created to use a new strategy, it's just simply removing an existing Extras on that column. Please see the screenshot below:

deepak-khopade-isdr on 9 Sep 2017

Original before .NET 2.0 changes:

migrationBuilder.CreateTable(
    name: "Employee",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("MySql:ValueGeneratedOnAdd", true)
    }
);

And when new migration was created to align with new strategy, this is the code it creates which is simply taking off the auto_increment.
However, the auto_increment is already added on FluentAPI of model.

migrationBuilder.AlterColumn<int>(
    name: "Id",
    table: "tax_TaxElPreNode",
    type: "int",
    nullable: false,
    oldClrType: typeof(int))
    .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);

FluentAPI:

modelBuilder.Entity<Employee>()
    .HasKey(c => c.Id);

modelBuilder.Entity<Employee>()
    .Property(p => p.Id)
    .HasAnnotation("MySql:ValueGeneratedOnAdd", true)
    .ValueGeneratedOnAdd();

deepak-khopade-isdr on 9 Sep 2017

Just FYI - I have a workaround to manually add extras for now within the migration so good to go now. Thanks.

deepak-khopade-isdr on 9 Sep 2017

Hi @deepak-khopade-isdr Could you share your workaround? I'm facing this error trying to update my API to 2.0

abarazarte on 27 Oct 2017

Hello @abarazarte: I just had to run few manual SQL statements through the migrations and continued further.

deepak-khopade-isdr on 27 Oct 2017

@deepak-khopade-isdr I am having the same issue after upgrading to netCore 2 as well. When I add a migration, the migration created drops the Auto Increment property on all of my tables.

kellyrmartin on 4 Nov 2017

+1 same issue

softlion on 16 Feb 2018

Tryed to fix this manually by adding these lines around the generated migration code. This is clearly a bug in Pomelo 2.

            migrationBuilder.Sql("SET foreign_key_checks = 0");
            var pomeloMigrationNoteCore2Bug = "ALTER TABLE {0} MODIFY COLUMN {1} int auto_increment";

            migrationBuilder.AlterColumn<int>(
                name: "VehiculeInfoId",
                table: "VehiculeInfo",
                nullable: false,
                oldClrType: typeof(int))
                .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn);
            migrationBuilder.Sql(String.Format(pomeloMigrationNoteCore2Bug, "VehiculeInfo", "VehiculeInfoId"));

            migrationBuilder.Sql("SET foreign_key_checks = 1");

softlion on 16 Feb 2018

This issue is closed. Please open a new issue and preferably include a full recreate in a Git repository to help us debug quicker

caleblloyd on 18 Feb 2018

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

softlion on 18 Feb 2018

👍1

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.