Identity Entity Platform Library - Update Database [MySQL]

Question

I just updated the Microsoft.AspNet.Identity.EntityFramework library to the latest version (2.0.0.0), and I found some errors when creating the tables. When I generate the migration code (Up and Down methods), I can’t upload the changes to the beacuse database due to an index problem while running the "Updata-Database"

The specified key was too long; The maximum key length is 767 bytes.

Code to execute:

public override void Up() { CreateTable( "dbo.AspNetRoles", c => new { Id = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), Name = c.String(nullable: false, maxLength: 256, storeType: "nvarchar"), }) .PrimaryKey(t => t.Id) .Index(t => t.Name, unique: true, name: "RoleNameIndex"); CreateTable( "dbo.AspNetUserRoles", c => new { UserId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), RoleId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), }) .PrimaryKey(t => new { t.UserId, t.RoleId }) .ForeignKey("dbo.AspNetRoles", t => t.RoleId, cascadeDelete: true) .ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true) .Index(t => t.UserId) .Index(t => t.RoleId); CreateTable( "dbo.AspNetUsers", c => new { Id = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), Email = c.String(maxLength: 256, storeType: "nvarchar"), EmailConfirmed = c.Boolean(nullable: false), PasswordHash = c.String(maxLength: 256, storeType: "nvarchar"), SecurityStamp = c.String(maxLength: 256, storeType: "nvarchar"), PhoneNumber = c.String(maxLength: 256, storeType: "nvarchar"), PhoneNumberConfirmed = c.Boolean(nullable: false), TwoFactorEnabled = c.Boolean(nullable: false), LockoutEndDateUtc = c.DateTime(precision: 0), LockoutEnabled = c.Boolean(nullable: false), AccessFailedCount = c.Int(nullable: false), UserName = c.String(nullable: false, maxLength: 256, storeType: "nvarchar"), }) .PrimaryKey(t => t.Id) .Index(t => t.UserName, unique: true, name: "UserNameIndex"); CreateTable( "dbo.AspNetUserClaims", c => new { Id = c.Int(nullable: false, identity: true), UserId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), ClaimType = c.String(maxLength: 256, storeType: "nvarchar"), ClaimValue = c.String(maxLength: 256, storeType: "nvarchar"), }) .PrimaryKey(t => t.Id) .ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true) .Index(t => t.UserId); CreateTable( "dbo.AspNetUserLogins", c => new { LoginProvider = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), ProviderKey = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), UserId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), }) .PrimaryKey(t => new { t.LoginProvider, t.ProviderKey, t.UserId }) .ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true) .Index(t => t.UserId); } public override void Down() { DropForeignKey("dbo.AspNetUserRoles", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserLogins", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserClaims", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserRoles", "RoleId", "dbo.AspNetRoles"); DropIndex("dbo.AspNetUserLogins", new[] { "UserId" }); DropIndex("dbo.AspNetUserClaims", new[] { "UserId" }); DropIndex("dbo.AspNetUsers", "UserNameIndex"); DropIndex("dbo.AspNetUserRoles", new[] { "RoleId" }); DropIndex("dbo.AspNetUserRoles", new[] { "UserId" }); DropIndex("dbo.AspNetRoles", "RoleNameIndex"); DropTable("dbo.AspNetUserLogins"); DropTable("dbo.AspNetUserClaims"); DropTable("dbo.AspNetUsers"); DropTable("dbo.AspNetUserRoles"); DropTable("dbo.AspNetRoles"); } 

When I use versión 1.0.0.0 from Microsoft.AspNet.Identity.EntityFramework, the code for updating the database is different and I have no problem.

 public override void Up() { CreateTable( "dbo.AspNetRoles", c => new { Id = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), Name = c.String(nullable: false, maxLength: 256, storeType: "nvarchar"), }) .PrimaryKey(t => t.Id); CreateTable( "dbo.AspNetUsers", c => new { Id = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), UserName = c.String(maxLength: 256, storeType: "nvarchar"), PasswordHash = c.String(maxLength: 256, storeType: "nvarchar"), SecurityStamp = c.String(maxLength: 256, storeType: "nvarchar"), Discriminator = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), }) .PrimaryKey(t => t.Id); CreateTable( "dbo.AspNetUserClaims", c => new { Id = c.Int(nullable: false, identity: true), ClaimType = c.String(maxLength: 256, storeType: "nvarchar"), ClaimValue = c.String(maxLength: 256, storeType: "nvarchar"), User_Id = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), }) .PrimaryKey(t => t.Id) .ForeignKey("dbo.AspNetUsers", t => t.User_Id, cascadeDelete: true) .Index(t => t.User_Id); CreateTable( "dbo.AspNetUserLogins", c => new { UserId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), LoginProvider = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), ProviderKey = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), }) .PrimaryKey(t => new { t.UserId, t.LoginProvider, t.ProviderKey }) .ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true) .Index(t => t.UserId); CreateTable( "dbo.AspNetUserRoles", c => new { UserId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), RoleId = c.String(nullable: false, maxLength: 128, storeType: "nvarchar"), }) .PrimaryKey(t => new { t.UserId, t.RoleId }) .ForeignKey("dbo.AspNetRoles", t => t.RoleId, cascadeDelete: true) .ForeignKey("dbo.AspNetUsers", t => t.UserId, cascadeDelete: true) .Index(t => t.UserId) .Index(t => t.RoleId); } public override void Down() { DropForeignKey("dbo.AspNetUserRoles", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserLogins", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserClaims", "UserId", "dbo.AspNetUsers"); DropForeignKey("dbo.AspNetUserRoles", "RoleId", "dbo.AspNetRoles"); DropIndex("dbo.AspNetUserLogins", new[] { "UserId" }); DropIndex("dbo.AspNetUserClaims", new[] { "UserId" }); DropIndex("dbo.AspNetUsers", "UserNameIndex"); DropIndex("dbo.AspNetUserRoles", new[] { "RoleId" }); DropIndex("dbo.AspNetUserRoles", new[] { "UserId" }); DropIndex("dbo.AspNetRoles", "RoleNameIndex"); DropTable("dbo.AspNetUserLogins"); DropTable("dbo.AspNetUserClaims"); DropTable("dbo.AspNetUsers"); DropTable("dbo.AspNetUserRoles"); DropTable("dbo.AspNetRoles"); } 

Can someone help me try to solve the problem?

Thanks in advance!

Answer 1

You can check out this tutorial: http://www.asp.net/mvc/tutorials/security/aspnet-identity-using-mysql-storage-with-an-entityframework-mysql-provider - specifically, the section "Adding a user context MigrationHistory ", which explains how to configure a custom HistoryContext to solve the primary key size problem.

Answer 2

I know this is an old post, but today I have the same problem, and I understand this a bit, and want to share my conclusions and solution.

The problem is that between versions of Microsoft a unique index has been added to the Name AspNetRoles column, and since this column is 256, it violates the MySql index rules. This problem also occurs in the Name column on AspNetUsers .

So, I decided to analyze how to fix this, and I think that the most correct is to reduce the length of the Name column (IMHO, in fact, this is not the reason for the role / user with a long name).

After studying the code in IdentityDbContext (which is the base class), I believe that the most correct solution to this problem is to override OnModelCreating in ApplicationDbContext and adjust the column sizes, for example:

 public class ApplicationDbContext : IdentityDbContext<ApplicationUser> { //... default code for ApplicationDbContext protected override void OnModelCreating(DbModelBuilder modelBuilder) { if (modelBuilder == null) { throw new ArgumentNullException("modelBuilder"); } base.OnModelCreating(modelBuilder); modelBuilder.Entity<ApplicationUser>().Property(u => u.UserName).HasMaxLength(128); //Uncomment this to have Email length 128 too (not neccessary) //modelBuilder.Entity<ApplicationUser>().Property(u => u.Email).HasMaxLength(128); modelBuilder.Entity<IdentityRole>().Property(r => r.Name).HasMaxLength(128); } } 

This code initializes the authentication model with the default configuration with a call to base.OnModelCreating , and then overrides the settings made in the base class using our own.

Since you are creating a migration project, you need to restart the migration creation to get an updated model (or manually change your model to adjust the column sizes).

This will solve the problem and you will have the full functionality of the Asp.NET authentication system.

Of course, in order not to get any errors when creating the project, you could just change your transition code, but then you will have a difference with the model defined by your context, which is not very good and can cause problems.

Answer 3

This is probably related to your database using UTF8 sorting.

One possible solution to this problem is to change all parts of the code containing maxLength: 256 to maxLength: 190 .

Another is to change the UTF8 sort in your database to latin1 or a similar sort.