Identity Entity Framework Library - Update Database [MySQL]

Question

I just updated the library Microsoft.AspNet.Identity.EntityFramework to the last version (2.0.0.0) and I have found some errors creating the tables. When I generate the migration code (Up and Down methods), I can't upload the changes to the database beacuse of I have a problem with indexs when I execute the "Updata-Database"

Specified key was too long; max 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 the versión 1.0.0.0 of Microsoft.AspNet.Identity.EntityFramework the code to update the database is different and I don't have any 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 anyone help me try to solve the problem?

Thanks in advance!!

Answer 1

I know this is an old post, but I got the same problem today and investigated a bit around this, and want to share my findings and solution.

The problem is that Microsoft has between the versions added an unique index to the Name column of AspNetRoles, and since this column is of size 256 it breaks the index rules of MySql. This problem will also occur on the Name column on AspNetUsers.

So, I went to analyze how to fix this, and I think that the most correct to do is to reduce the length of the Name column (IMHO it is really not any reason to have a role/user with a name that long).

After investigating the code on the IdentityDbContext (which is the base class) I think that the most correct solution to this problem is to override the OnModelCreating in ApplicationDbContext and adjust the column sizes there like this:

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 will initialize the identity model with default configuration with the base.OnModelCreating call and then override the settings done in the base class with our own.

Since you are creating a migration project, you'll need to rerun the migration creation to get the updated model (or manually modify your model to adjust the column sizes).

This will solve the issue and you will have full functionality of the Asp.NET Identity system.

Of course to not get any errors on creation of the project you could just modify your migration code, but then you will have a difference with the model defined by your context which isn't good and could cause problems.

Answer 2

This is probably due to your database using an UTF8 collation.

One possible way of addressing this is to change the all the parts of code that contain maxLength: 256 to maxLength: 190.

Another is to change the UTF8 collation on your database to latin1 or similar collation.