Pomelo.entityframeworkcore.mysql: Specified key was too long; max key length is 767 bytes

[farajfarook]

Steps to reproduce

1. Create a Visual Studio dotnet core project with single authentication
2. Change the SQL Server settings to MySQL using Pomela
3. Execute dotnet ef database update
4. 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

[caleblloyd]

Post your model. You probably have a key that is way too long in your model.

[farajfarook]

I don't have any of my custom models yet. This is a fresh auto generated project I'm working with. All are auto generated and Microsoft Library models when I created a project in VS2015.

• Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityRole
• Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityRoleClaim
• Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityUserClaim
• Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityUserLogin
• etc...

namespace Core.Models
{
    // Add profile data for application users by adding properties to the ApplicationUser class
    public class ApplicationUser : IdentityUser
    {
    }
}

Yes, the key is too long which is inside the Identity model which are in the framework. To make it work, we have to switch the database from UTF8.

[caleblloyd]

can you upload the migrations that are getting generated in the Migrations folder

It'd be nice if you could upload them to a gist and post the link here

[farajfarook]

Here is the gist

https://gist.github.com/farajfarook/aa887ce1317d556da77e97bcb15d06fd

[caleblloyd]

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.

[yukozh]

A PK maxlength is 767 bytes, in utf8 charset we have limited a PK maxlength to be 255 which the developer not specified max length, because a utf8 char takes 3 bytes. But it will not fit to the other charset automatically. if you are not using utf8 charset, you have to specify the max length by fluent api.

It is dificault to auto detect charset and caculate the length of max length. The first chanllenge is statistics. We have to know each of charset how many bytes does a char take. Then need detect the current charset when executing create table operation. And migrations to PK will be broken.

[caleblloyd]

IMO, any PK key over 255 bytes is a database design problem in the first place. InnoDB stores the PK along with every secondary indices, and having a PK that big is going to cause the database to grow very large.

If PKs are getting that big, the database designer should consider hashing all of the unique data for the PK, and storing the unhashed data separately in a non-indexed fields, such as TEXT

[yukozh]

https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/master/src/Pomelo.EntityFrameworkCore.MySql/Storage/Internal/MySqlTypeMapper.cs#L43

We have designed the default MaxLength for utf8 PKs, 255 × 3 = 765 Bytes. but 255 × Latin char length overflowed 767 bytes.

[yukozh]

Move discussion to #84

[tuanbs]

I got this error when I used:
public string Other { get; set; } = DateTimeOffset.UtcNow.ToString();

[Mats391]

@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?

[yukozh]

@Mats391 Could you confirm is the dbcontext works ok with .EnsureCreated() ?

[Mats391]

Did EnsureCreated() and it created the schema w/o printing any errors. The context works fine, but it works with migrations as well. The only issue I have is that I cannot apply any migrations after the first as the history is empty.
EnsureCreated() just skips the history completely, so I guess that is why it does not print any errors.

[yukozh]

Could you open a new issue?

[jlberrocal]

I had this issue running a migration which added IdentityUsers and IdentityRoles, previously i used to run the EnsureCreated() method, but now i need a more flexible way to handle my DB.

I noticed that migration add some fields with MaxLength 256, those fields were the ones causing me the issue, i manually change them to 255 to "fix" the issue

[alejandroldev]

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!

[gabmartinez]

Hello, I fixed the same issue creating the missing table __EFMigrationsHistory as

CREATE TABLE __EFMigrationsHistory (
    MigrationId varchar(767) NOT NULL,
    ProductVersion text NOT NULL,
    PRIMARY KEY (MigrationId)
)

I hope that can help you guys. :)

Regards,

[hokumus]

if you want quickly solution, run below script on the management tool.

Script Link

[lauxjpn]

if you want quickly solution, run below script on the management tool.

Script Link

@hokumus Please add some information about what this solves and how, what versions are involved etc.

---

Generally speaking, if you are fine with using HasMaxLength() on your properties, to restrict your columns to a lower length, than there is nothing wrong with that solution.

However nowadays, we recommend using HasPrefixLength() on the key or index, where you can specify how many characters of the field should be part of the index.
That means, that e.g. you could have a larger field Id with a varchar length of lets say 1024, but only use the first 128 characters of it as part of the index:

c# modelBuilder.Entity<IdentityUser<string>>(entity => { entity.HasKey(e => e.Id).HasPrefixLength(128); });

The same works for indexes as well. You would need to define the prefix length for all keys and indexes, that would exceed the maximum allowed key length.