Pomelo.entityframeworkcore.mysql: Specified Key was too long with IdentityContext and InnoDB

[Mats391]

This got reported before, but caleblloyd asked me to make new issue.
Original: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/81
When using the IdentityContext for asp.net Identity, I get the error "Specified key was too long; max key length is 767 bytes"
Stack trace:

MySql.Data.MySqlClient.MySqlException: Specified key was too long; max key length is 767 bytes
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetR
esult()
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadResultSetHeaderAsync>d__56.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at MySql.Data.MySqlClient.MySqlDataReader.<CreateAsync>d__50.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   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.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at MySql.Data.MySqlClient.CommandExecutors.TextCommandExecutor.<ExecuteNonQueryAsync>d__1.MoveNex
t()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at MySql.Data.MySqlClient.MySqlCommand.<ExecuteNonQueryAsync>d__46.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.<ExecuteAsync>d__3.MoveN
ext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConne
ction connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnecti
on)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalCo
nnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnectio
n connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEn
umerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targe
tMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.c
tor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Specified key was too long; max key length is 767 bytes

I already tried all the suggetions from the original issue. I set all keys to have a max length that should be small enough and made sure that the DB uses utf8_unicode_ci. I even tried way lower values then the ones below.

modelBuilder.Entity<UserEntity>(entity => entity.Property(m => m.Id).HasMaxLength(80));
modelBuilder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(80));
modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(80));
modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.ProviderKey).HasMaxLength(80));
modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(80));
modelBuilder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(80));
modelBuilder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(80));
modelBuilder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(80));
modelBuilder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(80));
modelBuilder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.Name).HasMaxLength(80));
modelBuilder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(80));
modelBuilder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(80));
modelBuilder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(80));
modelBuilder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(80));

Gist of Migration:
https://gist.github.com/Mats391/51a3b9f9103f4c3902dfa1bec5f1d678

The weird part is that it creates everything fine. Only the migration does not get added into the __efmigrationshistory. I suspect the error get thrown when it tries to write into the history. The history has PK MigrationId VARCHAR(95). I can use the DbContext fine, only applying new migrations forces me to recreate everything from scratch.
I found out that it only happens when I set it to use InnoDB, If I keep it as MyISAM everything works fine.

Versions
"Microsoft.EntityFrameworkCore": "1.1.0-preview1-final",
"Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.1.0-preview1-final",
"Pomelo.EntityFrameworkCore.MySql": "1.1.0-preview1-*"

[mguinness]

From MySQL documentation below:

The utf8 character set in MySQL has these characteristics:

• A maximum of three bytes per multibyte character.

Therefore, 80 chars will end up being 240 bytes. Do you have a composite key in one of your tables that might exceed that 767 byte limit?

[Mats391]

There are some composite keys. the biggest one has 3 columns, but even with that it should be fine with 80 characters. 80 * 3 * 3 = 720. This does not seem to be the issue.

[Mats391]

Found the issue. The 767 byte limit also applies to indexes and there were some with VARCHAR(256). Guess they use the term "key" loosely. Thanks for the help.

If anyone stumbled upon this with similar issue, this is what my fluent API looks like now:

modelBuilder.Entity<UserEntity>(entity => entity.Property(m => m.Id).HasMaxLength(85));
modelBuilder.Entity<UserEntity>(entity => entity.Property(m => m.NormalizedEmail).HasMaxLength(85));
modelBuilder.Entity<UserEntity>(entity => entity.Property(m => m.NormalizedUserName).HasMaxLength(85));
modelBuilder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(85));
modelBuilder.Entity<IdentityRole>(entity => entity.Property(m => m.NormalizedName).HasMaxLength(85));
modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.ProviderKey).HasMaxLength(85));
modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
modelBuilder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
modelBuilder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(85));
modelBuilder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
modelBuilder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
modelBuilder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.Name).HasMaxLength(85));
modelBuilder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
modelBuilder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
modelBuilder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
modelBuilder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(85));

[caleblloyd]

This is also covered in functional test DbContext here:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/1.1.0/test/Pomelo.EntityFrameworkCore.MySql.PerfTests/AppDb.cs#L66-L85

[yukozh]

@caleblloyd Could you set 127 as the key field default maxlength.

[togoshige]

Sorry if this reopens the issue, please close, just wanted to add a comment

@Mats391 your solution worked beautifully!

For any other newbies like me, I added Mats's modelBuilder.Entity() calls to new OnModelCreating() function of my Context

protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<ApplicationUser>(entity => entity.Property(m => m.Id).HasMaxLength(85)); . . . }

I copied out all my current migrations from Migrations folder and dropped database, then added migration and updated database with dotnet ef calls on command line

I exported all my database data beforehand and then imported it back in

[nairraf]

I hope this doesn't open this back up, but I thought I would add a note as well - please close if it opens it back up.

It seems that if I use MariaDB (10.1.22 || 5.5.54) with unicode collations (ex. utf8_unicode_ci, utf8_general_ci), I have to use similar methods from @stephen-cernota and @Mats391, namely I need this in my dbcontext OnModelCreating() override to avoid the max key length exceptions:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    builder.Entity<User>(entity => entity.Property(m => m.NormalizedEmail).HasMaxLength(200));
    builder.Entity<User>(entity => entity.Property(m => m.NormalizedUserName).HasMaxLength(200));
    builder.Entity<IdentityRole>(entity => entity.Property(m => m.NormalizedName).HasMaxLength(200));
    builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
    builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
    builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(200));
    builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
    builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
    builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(200));
}

However, using MySQL (5.7.17), I do NOT have to do anything at all (I do not need a custom OnModelCreating() override in my DbContext)- just the project defaults, and I do not get any of the key length errors when using IdentityDbContext

I just thought that was worth mentioning, as there seems to be a difference between MariaDB and MySQL in this test at least.

[cycbluesky]

I got the error too. I generate the sql and find the next statements cause the error.
CREATE UNIQUE INDEX RoleNameIndex ON AspNetRoles (NormalizedName);
CREATE INDEX EmailIndex ON AspNetUsers (NormalizedEmail);
CREATE UNIQUE INDEX UserNameIndex ON AspNetUsers (NormalizedUserName);

My database character set is uft8('utf8', 'utf8_general_ci', 'UTF-8 Unicode', '3'
), I never changed the default identity model, why it can not run?

CREATE TABLE __EFMigrationsHistory (
MigrationId varchar(95) NOT NULL,
ProductVersion varchar(32) NOT NULL,
CONSTRAINT PK___EFMigrationsHistory PRIMARY KEY (MigrationId)
);

CREATE TABLE AspNetRoles (
Id int NOT NULL AUTO_INCREMENT,
ConcurrencyStamp longtext,
Name varchar(256),
NormalizedName varchar(256),
CONSTRAINT PK_AspNetRoles PRIMARY KEY (Id)
);

CREATE TABLE AspNetUserTokens (
UserId int NOT NULL,
LoginProvider varchar(127) NOT NULL,
Name varchar(127) NOT NULL,
Value longtext,
CONSTRAINT PK_AspNetUserTokens PRIMARY KEY (UserId, LoginProvider, Name)
);

CREATE TABLE AspNetUsers (
Id int NOT NULL AUTO_INCREMENT,
AccessFailedCount int NOT NULL,
ConcurrencyStamp longtext,
Email varchar(256),
EmailConfirmed bit NOT NULL,
LockoutEnabled bit NOT NULL,
LockoutEnd datetime,
NormalizedEmail varchar(256),
NormalizedUserName varchar(256),
PasswordHash longtext,
PhoneNumber longtext,
PhoneNumberConfirmed bit NOT NULL,
SecurityStamp longtext,
TwoFactorEnabled bit NOT NULL,
UserName varchar(256),
CONSTRAINT PK_AspNetUsers PRIMARY KEY (Id)
);

CREATE TABLE AspNetRoleClaims (
Id int NOT NULL AUTO_INCREMENT,
ClaimType longtext,
ClaimValue longtext,
RoleId int NOT NULL,
CONSTRAINT PK_AspNetRoleClaims PRIMARY KEY (Id),
CONSTRAINT FK_AspNetRoleClaims_AspNetRoles_RoleId FOREIGN KEY (RoleId) REFERENCES AspNetRoles (Id) ON DELETE CASCADE
);

CREATE TABLE AspNetUserClaims (
Id int NOT NULL AUTO_INCREMENT,
ClaimType longtext,
ClaimValue longtext,
UserId int NOT NULL,
CONSTRAINT PK_AspNetUserClaims PRIMARY KEY (Id),
CONSTRAINT FK_AspNetUserClaims_AspNetUsers_UserId FOREIGN KEY (UserId) REFERENCES AspNetUsers (Id) ON DELETE CASCADE
);

CREATE TABLE AspNetUserLogins (
LoginProvider varchar(127) NOT NULL,
ProviderKey varchar(127) NOT NULL,
ProviderDisplayName longtext,
UserId int NOT NULL,
CONSTRAINT PK_AspNetUserLogins PRIMARY KEY (LoginProvider, ProviderKey),
CONSTRAINT FK_AspNetUserLogins_AspNetUsers_UserId FOREIGN KEY (UserId) REFERENCES AspNetUsers (Id) ON DELETE CASCADE
);

CREATE TABLE AspNetUserRoles (
UserId int NOT NULL,
RoleId int NOT NULL,
CONSTRAINT PK_AspNetUserRoles PRIMARY KEY (UserId, RoleId),
CONSTRAINT FK_AspNetUserRoles_AspNetRoles_RoleId FOREIGN KEY (RoleId) REFERENCES AspNetRoles (Id) ON DELETE CASCADE,
CONSTRAINT FK_AspNetUserRoles_AspNetUsers_UserId FOREIGN KEY (UserId) REFERENCES AspNetUsers (Id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX RoleNameIndex ON AspNetRoles (NormalizedName);

CREATE INDEX IX_AspNetRoleClaims_RoleId ON AspNetRoleClaims (RoleId);

CREATE INDEX IX_AspNetUserClaims_UserId ON AspNetUserClaims (UserId);

CREATE INDEX IX_AspNetUserLogins_UserId ON AspNetUserLogins (UserId);

CREATE INDEX IX_AspNetUserRoles_RoleId ON AspNetUserRoles (RoleId);

CREATE INDEX EmailIndex ON AspNetUsers (NormalizedEmail);

CREATE UNIQUE INDEX UserNameIndex ON AspNetUsers (NormalizedUserName);

INSERT INTO __EFMigrationsHistory (MigrationId, ProductVersion)
VALUES ('20170621032430_version2db', '1.1.2');

[mguinness]

@Cassaba Please re-read this thread, you will find that your answer is to override OnModelCreating.