Identity and Mysql in Code First - Specified key was too long; max key length is 1000 bytes

5

I am trying to create a CodeFirst Database with IentityCore using MySQL driver.

My User DbModel is like this-

[Table("User")]
public class User : IdentityUser<int>
{
    public string Name { get; set; }
    public string Password { get; set; }
}

and Role DBModel is like this-

[Table("Role")]
public class Role : IdentityRole<int>
{
    public string Description { get; set; }
}

My DbContext is like this-

public class InventoryManagementDbContext : IdentityDbContext<User, Role, int>
{
    //Table List
    public DbSet<Employee> Employies { get; set; }
    public DbSet<ProductCategory> ProductCategorys { get; set; }
    public DbSet<ProductType> ProductTypes { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Entry> Entries { get; set; }
    public DbSet<Exit> Exits { get; set; }
    //Table List End

    public InventoryManagementDbContext(DbContextOptions<InventoryManagementDbContext> options)
    : base(options)
    {}

    //Fluent API to make Composite Key
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // email address doesn't need to be in unicode, check it spec
        modelBuilder.Entity<User>().Property(u => u.UserName).IsUnicode(false);
        modelBuilder.Entity<User>().Property(u => u.Email).IsUnicode(false);
        modelBuilder.Entity<Role>().Property(r => r.Name).HasMaxLength(255);
    }
}

So, I have disabled Unicode for Email and Username and also set MaxLength for Role.Name.

Still if I am trying to execute this command-

Add-Migration

Then I am getting this error-

.....................................
.....................................

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE TABLE `AspNetUserLogins` (
          `LoginProvider` varchar(767) NOT NULL,
          `ProviderKey` varchar(767) NOT NULL,
          `ProviderDisplayName` text NULL,
          `UserId` int NOT NULL,
          PRIMARY KEY (`LoginProvider`, `ProviderKey`),
          CONSTRAINT `FK_AspNetUserLogins_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
      );
MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 1000 bytes
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, 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.ExecuteNonQuery()
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- 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 System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
failverbose: Found DbContext 'InventoryManagementDbContext'.
: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE TABLE `AspNetUserLogins` (
          `LoginProvider` varchar(767) NOT NULL,
          `ProviderKey` varchar(767) NOT NULL,
          `ProviderDisplayName` text NULL,
          `UserId` int NOT NULL,
          PRIMARY KEY (`LoginProvider`, `ProviderKey`),
          CONSTRAINT `FK_AspNetUserLogins_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
      );
MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 1000 bytes
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, 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.ExecuteNonQuery()
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
--- 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 System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.0.2-rtm-10011 initialized 'InventoryManagementDbContext' using provider 'MySql.Data.EntityFrameworkCore' with options: NoTracking CommandTimeout=60 
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.0.2-rtm-10011 initialized 'InventoryManagementDbContext' using provider 'MySql.Data.EntityFrameworkCore' with options: NoTracking CommandTimeout=60

My complete code can be found in here.

Can anyone please help?

Re-

This question is not solving my question because I was not facing any problem for General Keys, they are perfectly working for me.

I am getting problem when trying to use Identity.

asp.net-core
asp.net-identity
asp.net-core-2.0
ef-migrations
asked on Stack Overflow Mar 30, 2018 by Abrar Jahin • edited Mar 30, 2018 by Abrar Jahin

1 Answer

0

I'll leave this here even though it's been a while since the question, for anyone with the same problem.

You need to set the max length for a few properties for:

  • IdentityRole
  • IdentityUser
  • IdentityUserLogin
  • IdentityUserToken

In this case, you have extended the classes IdentityUser and IdentityRole for using an integer PK, so your code, using the fluent API, should be like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    int stringMaxLength = /* something like 100*/;
    // User IdentityRole and IdentityUser in case you haven't extended those classes
    builder.Entity<Role>(x => x.Property(m => m.Name).HasMaxLength(stringMaxLength));
    builder.Entity<Role>(x => x.Property(m => m.NormalizedName).HasMaxLength(stringMaxLength));
    builder.Entity<User>(x => x.Property(m => m.NormalizedUserName).HasMaxLength(stringMaxLength));

    // We are using int here because of the change on the PK
    builder.Entity<IdentityUserLogin<int>>(x => x.Property(m => m.LoginProvider).HasMaxLength(stringMaxLength));
    builder.Entity<IdentityUserLogin<int>>(x => x.Property(m => m.ProviderKey).HasMaxLength(stringMaxLength));

    // We are using int here because of the change on the PK
    builder.Entity<IdentityUserToken<int>>(x => x.Property(m => m.LoginProvider).HasMaxLength(stringMaxLength));
    builder.Entity<IdentityUserToken<int>>(x => x.Property(m => m.Name).HasMaxLength(stringMaxLength));

   // etc..

Hope it helps!

answered on Stack Overflow May 10, 2020 by Alejandro Techera

User contributions licensed under CC BY-SA 3.0