c# - 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

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...