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

With MySQL Connector/Net 6.8 and Entity Framework version 6, you can solve this problem using MySQL's built-in support for EF. The trick is to tell Entity Framework to use the MySQL resolvers. From the Connector/Net Developer Guide:

This can be done in three ways:

• Adding the DbConfigurationTypeAttribute on the context class:

  [DbConfigurationType(typeof(MySqlEFConfiguration))]

• Calling DbConfiguration.SetConfiguration(new MySqlEFConfiguration()) at the application startup

• Set the DbConfiguration type in the configuration file:

  <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">

It is also possible to create a custom DbConfiguration class and add the dependency resolvers needed.

When I followed those instructions (I used the configuration file approach), the table was created successfully. It used the following DDL:

create table `__MigrationHistory` ( `MigrationId` nvarchar(150) not null, `ContextKey` nvarchar(300) not null, `Model` longblob not null, `ProductVersion` nvarchar(32) not null, primary key ( `MigrationId`) ) engine=InnoDb auto_increment=0 

While the accepted answer by ChAmp33n had resolved the problem raised by the questioner, however I think there were some "breaking" changes after that answer. I had applied the accepted answer but the exceptions remained.

Through checking the SQL queries generated by EF, I had found that the problem was related to UserName (varchar utf8 256) in table AspNetUsers and Name (varchar utf8 256) in table AspNetRoles, while the table for HistoryRow was fine.

So the following codes resolved the problem.

 public class WebPortalDbContext : IdentityDbContext<ApplicationUser> { public WebPortalDbContext() : base("IdentityConnection") { } public static WebPortalDbContext Create() { return new WebPortalDbContext(); } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<Microsoft.AspNet.Identity.EntityFramework.IdentityRole>() .Property(c => c.Name).HasMaxLength(128).IsRequired(); modelBuilder.Entity<Microsoft.AspNet.Identity.EntityFramework.IdentityUser>().ToTable("AspNetUsers")//I have to declare the table name, otherwise IdentityUser will be created .Property(c => c.UserName).HasMaxLength(128).IsRequired(); } } 

To clarify the solution, here's the libraries I am using:

1. EF 6.1.0
2. Microsoft ASP.NET Identity EntityFramework 2.0.0
3. ASP.NET Identity Owin 2.0.0
4. MySql .NET libraries 6.8.3

And this solution also works for

1. EF 6.1.1
2. Microsoft ASP.NET Identity EntityFramework 2.0.1
3. ASP.NET Identity Owin 2.0.1

For those who still get this headache like me, I found a solution myself. Go to the Migration file (201603160820243_Initial.cs in my case), edit all the maxLength: 256 to 128. Run Update-database again and it works for me.

Still don't know why nvarchar(256) (512 bytes) is not satisfied 767 bytes and the updates is not for the keys columns.

Take a look at this workitem on the EF CodePlex site. If you are using EF6 you can configure the migrations history table and make columns shorter - here is an article that shows how to do this.

The primary key you specified is 450 unicode characters long. MySQL assumes the worst case for character size when checking column size limits - 2 or 4 bytes probably, depending on your character set and collation - so that's either 900 or 1800 bytes, both too long.

Primary keys really shouldn't be aggregate keys. Primary keys govern how the table is laid out on disk; that's performance suicide. Make the primary key an integer, and attach a secondary key with the desired structure.

This is because foreign key name calculated by entity framework can be too long. We can specify foreign key name in migartion.cs file. Fourth parameter of ForeignKey() method is ForeignKeyName.

By default it look something like this:

.ForeignKey("xxx", t => t.yyy) <br/> Modify it as: <br/> .ForeignKey("xxx", t => t.yyy,false, "YourForeignKeyName") 

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 = "[email protected]"; 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.