Specified key was too long; max key length is 767 bytes Mysql error in Entity Framework 6

Question

I have start working on Asp.net Mvc-5 application using visual studio 2012. So I have downloaded Entity Framework-6 and MySQL 6.8.3.0 from nuget. When I tried to create database by using db Context command

dbContext.Database.CreateIfNotExists();

This exception thrown.

Specified key was too long; max key length is 767 bytes

I have done search on it, but cannot find any solution. One thing that I got during my search, this can be Unicode characters problem. I don't know how to deal with this issue.

Updated

I am using following configuration

<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
</configuration>

My DB Context class. I have removed all the models just keep left one model

public class MyContext : DbContext
{
    public MyContext()
        : base("myconn")
    {
        this.Configuration.ValidateOnSaveEnabled = false;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();
        base.OnModelCreating(modelBuilder);
    }

    public DbSet<ModelOne> ModelOne { get; set; }

}

Model class

public class  ModelOne
{
        [Key]
        public int CreatedId { get; set; }
        public Nullable<int> UserId { get; set; }
        public Nullable<DateTime> Date { get; set; }
        public string Description { get; set; }
  }

Can anyone help me with this issue?

Thank you.

Answer 1

I have changed the DbConfigurationType of DbContext.

Got from this this link stackoverflow

Now it is working

[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
public class MyContext : DbContext
{
    public MyContext()
        : base("myconn")
    {
        this.Configuration.ValidateOnSaveEnabled = false;
    }

    static MyContext()
    {
            DbConfiguration.SetConfiguration(new MySql.Data.Entity.MySqlEFConfiguration());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>();
        base.OnModelCreating(modelBuilder);
    }

    public DbSet<ModelOne> ModelOne { get; set; }

}

Answer 2

If you're using ASP.NET Identity then there are 3 places where this is happening

1. In the migration history table
2. The Name property of IdentityRole
3. The Username and Email property of ApplicationUser

Most articles I came across has a solution that basically reduce the length of Username and Email to 128 character. However, i found this is unacceptable because the official spec for email address is 256 ansi characters.

My solution was to :

1. Turn unicode off for Email and Username
2. override MySqlMigrationSqlGenerator and tell it to use latin1_general_ci collate, which is ansi character set.
3. reduce the length of role name. This is acceptable as role name doesn't need to be that long
4. reduce key length for history migration as described in various articles on the Internet.

You can find my solution at https://github.com/timdinhdotcom/MySql.AspNetIdentity