.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 wouldn't recommend leaving MySQL completely for these issues. It has been the most used DB for a while now. Since you have already spent time tackling these issues and learnt from them, it would be worth to try out complete cycles and get the complete understanding.

As far as you are using Pomelo, you should be fine. I would recommend sticking to that. It does have more downloads on both git and nuget and also has more git stars. Oracle's nuget, even its "release" version is still not completed. It was reported to have unimplemented functions https://bugs.mysql.com/bug.php?id=82981. If MS has officially mentioned Pomelo next to the official provider it should really be something.

Regarding your main issue, you should be able to do something like this in your OnModelCreate:

builder.Entity<ApplicationUser>()
    .Property(u => u.Id)
    .HasMaxLength(36);

Note that according to the IdentityUser<TKey> class, Id = Guid.NewGuid().ToString(), it only needs 36 characters. Alternatively, you can advantage of the fact that Id is a virtual field and do this in your ApplicationUser class:

[MaxLength(36)]
public override string Id { get; set; }

Also, I don't mean to contradict what I said, but no harm in trying SQL Server too for some projects. I did find couple of conveniences in SQL Server as compared to MySQL. I don't think licensing is a reason to not try SQL Server. There are several reasons why licensing doesn't really matter in many scenarios.

1. You can host up to 10 GB free in linux and windows. For many cases 10 GB is a lot for a database.
2. The "web" version is totally free and storage is limited to VM space. At least this is the case with azure, you only pay for the service. Not sure of the rest
3. If you are trying .NET, it is used mostly by enterprises who wouldn't mind the licensing. If you ever happen to work or freelance for them, you wouldn't worry about the licence.

Answer 2

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));
        }

Answer 3

Below setup worked for Asp.Net 5.0 with customized identity:

builder.Entity<AppUser>(entity => entity.Property(m => m.Id).HasMaxLength(85));
builder.Entity<AppRole>(entity => entity.Property(m => m.Id).HasMaxLength(85));

builder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.Id).HasMaxLength(85));
builder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.Id).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<IdentityUserToken<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(85));
builder.Entity<IdentityUserToken<string>>(entity => entity.Property(m => m.Name).HasMaxLength(85));

I used MaxLength only for primary keys and indexes.

Answer 4

A better option is to set the MaxLengthForKeys for all keys in the Identity Store (inside the ConfigureServices method).

services.AddDefaultIdentity<User>(options =>
{
    options.SignIn.RequireConfirmedAccount = true;
    options.Stores.MaxLengthForKeys = 85;
})
.AddEntityFrameworkStores<ApplicationDbContext>();

Here is the note from the documentation

If set to a positive number, the default OnModelCreating will use this value as the max length for any properties used as keys, i.e. UserId, LoginProvider,

Answer 5

The ones that were needed for me to modify due to their length (ASP NET Core 6) were

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

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