Pomelo.EntityFrameworkCore.MySql: Specified key was too long; max key length is 767 bytes

Steps to reproduce

Create a Visual Studio dotnet core project with single authentication
Change the SQL Server settings to MySQL using Pomela
Execute dotnet ef database update
Error will occur Specified key was too long; max key length is 767 bytes

The issue

UTF8 only accepts 767 chars. Need character set to latin1 collate latin1_general_ci

Exception message:
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.GetResult()
   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.MySqlCommand.<ExecuteReaderAsync>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.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.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 Microsoft.EntityFrameworkCore.Storage.Internal.MySqlRelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Tools.Cli.DatabaseUpdateCommand.<>c__DisplayClass0_0.<Configure>b__0()
   at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
   at Microsoft.EntityFrameworkCore.Tools.Cli.Program.Main(String[] args)
Specified key was too long; max key length is 767 bytes

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 19 (9 by maintainers)

Most upvoted comments

I want to share my solution, I solved this by manually adding those two commands to the beginning of the Up method in the initial migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"SET default_storage_engine=InnoDB;");
    migrationBuilder.Sql(@"ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_unicode_ci;");
    // auto-generated code
}

Enjoy coding!

raky291 on Feb 14, 2019

@caleblloyd Sorry for bumping this, I am still getting this error when using IdentityContext even if I set the max length on every key through fluent API and making sure charset is set to UTF8_unicode_ci. It is still the same error reported in first post.

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(150). Does anyone have an idea what could cause this?

Mats391 on Dec 2, 2016

I can run it just fine using UTF8 char set, UTF8_unicode_ci collation. What collation are you using?

Either way, you can adjust the Identity model key sizes in OnModelCreating if you need smaller PKs. Here’s how to do that:

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/82/commits/8cb800389af08b6cc091844b8519dc53d4de8662#diff-8c29e7d21446c5ca06e3ec06c3360ab1R36

Not a bug in Pomelo, but I added a example in the test library so that we can ensure that we have examples for common use cases with Identity.

caleblloyd on Oct 11, 2016

Move discussion to #84

yukozh on Oct 12, 2016