[Solved] "Max Key length is 3072 bytes" error in AspNet Core Identity with MySql

Hello Geeks,

As you all know everyone nowadays loves to work on open source languages, So Microsoft has also done a great job releasing .Net core as an open-source framework.

That means we can also code and deploy it on Linux which is tremendous.

Also .Net core can support various database servers like MySQL, SQLite, Postgre SQL, MongoDB, and many more. Personally, I like to work on MySQL.

Currently, I was working on a personal project on .Net Core 3 and I decided to use Mysql as a database. I 1st created an Entity Class, Then I added Aspnetcore Identity to Entity.

When I tried to update the database, I got this error:

Specified key was too long: max key length is 3072 bytes.

Hmm, interesting… Well as I am no expert in MySql, I searched for the reason, I got that, for relationship-related columns, there is the limitation for length in MySql.

I think MySQL has done a good job limiting this to 3072 bytes. Otherwise, people would create all kinds of useless indexes that would lead to a performance bottleneck.

Solution:

Well, the simple answer is to reduce the length of columns that are causing max memory allocation warning.

If you look into your latest migration class, you can see there are some columns having datatype string and database data type “varchar(767)“.

Mainly Primary keys are using that long type because generally Asp.Net core Identity use GUID as Id of any table, We just need to reduce the size of the columns.

1st we need to revert the migration and remove the database and migration files from our project.

Then we have to add the below code to OnModelCreating() function inside the database context class:

modelBuilder.Entity<ApplicationUser>(entity => entity.Property(m => m.NormalizedEmail).HasMaxLength(200));
            modelBuilder.Entity<ApplicationUser>(entity => entity.Property(m => m.Id).HasMaxLength(200));
            modelBuilder.Entity<ApplicationUser>(entity => entity.Property(m => m.NormalizedUserName).HasMaxLength(200));


            modelBuilder.Entity<IdentityRole>(entity => entity.Property(m => m.NormalizedName).HasMaxLength(200));
            modelBuilder.Entity<IdentityRole>(entity => entity.Property(m => m.Id).HasMaxLength(200));


            modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
            modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.LoginProvider).HasMaxLength(200));
            modelBuilder.Entity<IdentityUserLogin<string>>(entity => entity.Property(m => m.ProviderKey).HasMaxLength(200));


            modelBuilder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
            modelBuilder.Entity<IdentityUserRole<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(200));


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

modelBuilder.Entity<IdentityUserClaim<string>>(entity => entity.Property(m => m.UserId).HasMaxLength(200));
modelBuilder.Entity<IdentityRoleClaim<string>>(entity => entity.Property(m => m.RoleId).HasMaxLength(200));

I have looked into all the columns required to change the size and added them above. But I strongly recommend you review your migration once if any more columns are added in your case.

Then add a new migration and update the database. It will work like a charm.

Explanation:

OnModelCreating() function is the most powerful method of configuration and allows configuration to be specified without modifying your entity classes. Fluent API configuration has the highest precedence and will override conventions and data annotations.

You can take a look at these two Syntaxes which I used in our case to solve the issue.

public virtual ModelBuilder Entity<TEntity>([NotNullAttribute] Action<EntityTypeBuilder<TEntity>> buildAction) where TEntity : class;

The upper syntax for ModelBuilder has the following role: ” Performs configuration of a given entity type in the model. If the entity type is not already part of the model, it will be added to the model. This overload allows configuration of the entity type to be done inline in the method call rather than being chained after a call to Microsoft.EntityFrameworkCore.ModelBuilder.Entity“1. This allows additional configuration at the model level to be chained after configuration for the entity type.”

public virtual PropertyBuilder<TProperty> HasMaxLength(int maxLength);

The upper syntax for ModelBuilder has the following role: “Configures the maximum length of data that can be stored in this property. Maximum length can only be set on array properties (including System.String properties).”

Hope this article will help you to solve this problem, If you find the information useful, then a like to this post will be fabulous.

Consider a visit to my website: www.rakeshsahu.in

[Solved] “Max Key length is 3072 bytes” error in AspNet Core Identity with MySql