Entity Framework with MySql and Migrations failing because "max key length is 767 bytes"

None of these answers worked for me because I was trying to work with an existing database, but here's what I did, taking a little bit from some of the other answers.

This is easier with some more direct access to your database such as MySQL Workbench or phpMyAdmin to run MySQL commands. You could also code some of the SQL commands into the C#, it's just not how I did it.

1. I'm not sure if this first step is relevant to everyone, but I wish I'd done it first instead of having to run Add-Migration/Update-Database a few extra times later. In order to seed the initial admin account into the database, I had to

   1. Change my ApplicationDbContext class in IdentityModels.cs to:

       public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
       {
           public static ApplicationDbInitializer adi = new ApplicationDbInitializer();
      
           public ApplicationDbContext() : base("DefaultConnection", throwIfV1Schema: false)
           {
               adi.InitializeDatabase(this); //Delete after first run:
           }
      
           static ApplicationDbContext()
           {
               Database.SetInitializer<ApplicationDbContext>(adi); //Delete after first run
           }
           public static ApplicationDbContext Create()
           {
               return new ApplicationDbContext();
           }
      
      
           /* Optional: If you'd like to rename your tables, 
           *  you can do it by overriding this "OnModelCreating()" method.
           *  Be sure to change the table names in your 
           *  "Migrations\#######EmptyMigrations.cs" file to match the new table names
           *  before running "Update-Database" in the PM Console. */
           protected override void OnModelCreating(DbModelBuilder modelBuilder)
           {
               base.OnModelCreating(modelBuilder);
      
               /* Note that for the AspNetUsers table
               *  —because the "IdentityUser" class was extended 
               *  into "ApplicationUser" earlier in IdentityModels.cs—
               *  You must refer to the new class rather than the base class */
      
               //modelBuilder.Entity<ApplicationUser>().ToTable("tblIdentityUsers");
               //modelBuilder.Entity<IdentityUserClaim>().ToTable("tblIdentityUserClaims");
               //modelBuilder.Entity<IdentityUserLogin>().ToTable("tblIdentityUserLogins");
               //modelBuilder.Entity<IdentityUserRole>().ToTable("tblIdentityUserRoles");
               //modelBuilder.Entity<IdentityRole>().ToTable("tblIdentityRoles");
           }
       }
      

   2. I don't know much about how OWIN works but the stock code was giving me nulls for userManager and roleManager in the InitializeIdentityForEF method of IdentityConfig.cs so I used these constructors instead. Obviously, change the admin account to whatever you want to use.

       public static void InitializeIdentityForEF(ApplicationDbContext db) {
           //var userManager = HttpContext.Current.GetOwinContext().GetUserManager<ApplicationUserManager>();
           //var roleManager = HttpContext.Current.GetOwinContext().Get<ApplicationRoleManager>();
           var roleStore = new RoleStore<IdentityRole>(db);
           var roleManager = new RoleManager<IdentityRole>(roleStore);
           var userStore = new UserStore<ApplicationUser>(db);
           var userManager = new UserManager<ApplicationUser>(userStore);
           //In normal usage I would probably wrap these in a series of using() statements as well.
      
           const string name = "Administrator";
           const string email = "foo@bar.com";
           const string password = "Password123";
           const string roleName = "Admin";
      
           //Create Role Admin if it does not exist
           var role = roleManager.FindByName(roleName);
           if (role == null) {
               role = new IdentityRole(roleName);
               var roleresult = roleManager.Create(role);
           }
      
           var user = userManager.FindByName(name);
           if (user == null) {
               user = new ApplicationUser { UserName = name, Email = email };
               var result = userManager.Create(user, password);
               result = userManager.SetLockoutEnabled(user.Id, false);
           }
      
           // Add user admin to Role Admin if not already added
           var rolesForUser = userManager.GetRoles(user.Id);
           if (!rolesForUser.Contains(role.Name)) {
               var result = userManager.AddToRole(user.Id, role.Name);
           }
       }
      

2. Open Package Manager Console

   1. Run PM> Enable-Migrations
   2. Run PM> Add-Migration EmptyMigration (this can be named anything)

3. Open the newly created Project\Migrations\###########EmptyMigration.cs

   1. In the Up() method, find the CreateTable() calls for "dbo.AspNetRoles" and "dbo.AspNetUsers" (or whatever you might have renamed them).

   2. Comment out the .Index() method on those and add a semicolon to end those two method chains at .PrimaryKey()

      It should look something like:

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

      and

      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(unicode: false),
                   SecurityStamp = c.String(unicode: false),
                   PhoneNumber = c.String(unicode: false),
                   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");
      

4. Go back to Package Manager Console and run PM> Update-Database -Verbose

5. Go to your MySQL Workbench or phpMyAdmin or whatever you're using to run SQL commands directly.

   1. Run the following commands to change the key column character set to something smaller:

      ALTER TABLE `DatabaseName`.`AspNetUsers` 
          MODIFY `UserName` VARCHAR(256) 
          CHARACTER SET latin1;
      ALTER TABLE `DatabaseName`.`AspNetRoles` 
          MODIFY `Name` VARCHAR(256) 
          CHARACTER SET latin1;
      

   2. Create the indexes that it wasn't able to do the first time:

       CREATE UNIQUE INDEX `UserNameIndex` 
           ON `DatabaseName`.`AspNetUsers` (`UserName` DESC) USING HASH;
       CREATE UNIQUE INDEX `RoleNameIndex` 
           ON `DatabaseName`.`AspNetRoles` (`Name` DESC) USING HASH;
      

p.s. I've noticed that in the Down() method when trying to revert the migration, it only worked after I took the "dbo." out of the table names. Your mileage may vary.