asp.net identity - .NET Core 2.0 with MySQL: Specified key was too long; max key length is 3072 bytes

Question

I'm a PHP/MySQL developer trying to move over to other technologies like .NET Core. I'm not sure if I like the idea of switching to SQL Server over MySQL (due to licensing), so I've been trying to get .NET Core to play nicely with MySQL.

I've set up a fairly basic .NET Core 2.0 project using Identity as the authentication method and everything was working fine while the DB driver was SqlLite with a local database. I tried switching to a MySQL database and ran my migrations and they failed.

I received the error: Specified key was too long; max key length is 3072 bytes when trying to run the following table-creation query:

CREATE TABLE `AspNetUserTokens` (
    `UserId` varchar(767) NOT NULL,
    `LoginProvider` varchar(767) NOT NULL,
    `Name` varchar(767) NOT NULL,
    `Value` text NULL,
    PRIMARY KEY (`UserId`, `LoginProvider`, `Name`)
);

My Mysql database uses a character set of UTF8mb4 so it cannot use the primary key specified above which works out to be around 9204 bytes.

How can I customize the way that the default Identity tables are created? If possible, I would like to replace the huge key with a more sane primary key on this table that is just an auto increment integer.

Is there any way to customize how the default Identity related tables are created without manually hacking at the migration files. Is there any way I can do something in the OnModelCreating method of my ApplicationDbContext? Should I stop fighting with MySQL and succumb to MS SQL?

Edit

I was able to workaround the issue by using the Pomelo MySQL Driver rather than Oracle. The Pomelo driver seems to default string fields to max length 127 characters which allows the above key to fit in the 3072 byte limit. However I would still be interested in knowing how to fine-tune and customize the base Identity tables provided in the .NETCore Scaffolding.

Answer 1

I just wanted to add to @neville answer, for anyone else to take solution with default MySql.Data.EntityFrameworkCore nuget package.

you need to add the max length of the following keys in your dbcontext OnModelCreating and then run add-migration to take effect.

have a sample created here https://github.com/k-siddharatha/DotNetCoreEFMySql

just for this issue.

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


            builder.Entity<IdentityUser>(entity => entity.Property(m => m.Id).HasMaxLength(85));
            builder.Entity<IdentityUser>(entity => entity.Property(m => m.NormalizedEmail).HasMaxLength(85));
            builder.Entity<IdentityUser>(entity => entity.Property(m => m.NormalizedUserName).HasMaxLength(85));

            builder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(85));
            builder.Entity<IdentityRole>(entity => entity.Property(m => m.NormalizedName).HasMaxLength(85));

            builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
            builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.ProviderKey).HasMaxLength(85));
            builder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
            builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));

            builder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(85));

            builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
            builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
            builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.Name).HasMaxLength(85));

            builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
            builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(85));
            builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
            builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(85));
        }