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

Question

[Edit] This problem was solved! See the instructions at the end of the post.

[Edit 2] Ok, this thread is old, and the newer versions of MySQL Connector already handle this with MySQL EF resolvers. Look for @KingPong answer on this thread. I haven't tested it, though.

I'm trying to use MySql and EntityFramework with Migrations, but something seems to be wrong.

When I enter Update-Database -Verbose in the Package Manager Console, EF executes some queries that will "mirror" my model classes, and everything goes perfect, BUT then EF tries to execute this query:

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

And the result is: Specified key was too long; max key length is 767 bytes

I tried to change my database collation to utf-8, but still the same. Perhaps the key lenght is 450 characters, doing the UTF-8 math (which I may be wrong), I think it's trying to create a key around 1800 bytes length.

Since I'm new to EF, I followed some tutorials and they told me to do this:

    public Configuration()
    {
        AutomaticMigrationsEnabled = false;

        SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
    }

Perhaps this SQL generator is doing the wrong thing, or EF itself is asking to the generator to make a key up to 767 bytes.

How can I fix that, avoid this problem and get it to work with MySql?

[Edit] Ok, this problem was solved. You have to tell EF it has to change the way it generates the __MigrationHistory table.

What I did: First, create a file called MySqlHistoryContext.cs (or whatever you want) with this content:

...
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;


namespace [YOUR NAMESPACE].Migrations //you can put any namespace here, but be sure you will put the corret using statement in the next file. Just create a new class :D
{
    public class MySqlHistoryContext : HistoryContext
    {

        public MySqlHistoryContext(DbConnection connection, string defaultSchema):base(connection,defaultSchema)
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired(); 
        }
    }
}

You might have a file called Configuration.cs inside your Migrations folder. If yes, make the necessary adjustments, otherwise create a new file. Actually you kinda won't be able to get to this error if you didn't have this file, since EF creates it automatically when you Add-Migration [name].

namespace [YOUR NAMESPACE].Migrations
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;
    using System.Linq;

    internal sealed class Configuration : DbMigrationsConfiguration<CodeFirstMySql.Models.Context>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;

            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); //it will generate MySql commands instead of SqlServer commands.

            SetHistoryContextFactory("MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema)); //here s the thing.



        }

        protected override void Seed(CodeFirstMySql.Models.Context context){}//ommited
    }
}

Then Update-Database -Verbose and have fun!

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Answer paraphrased from Adding custom MigrationHistory context...

EF6 uses a MigrationHistory table to keep track of model changes and to ensure the consistency between the database schema and conceptual schema. This table does not work for MySQL by default because the primary key is too large. To remedy this situation, you will need to shrink the key size for that table.

Essentially, EF6 allows you to modify the key size for the MigrationId/ContextKey index columns using Fluent API like so:

modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();

Complete Instructions Here...

Answer 7

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.

Answer 8

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.