Specified key was too long; max key length is 767

问题:

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.

回答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; }

}

回答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

回答3:

Take a look at this article and see if it helps. Especially the MySqlHistoryContext.cs, Configuration.cs and MySqlInitializer.cs classes added.

This is where you will find your solution.

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();
        }
    }

回答4:

Use the configuration code below... This solved my problem:

internal sealed class Configuration : DbMigrationsConfiguration<MDbContext>
{       
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        CommandTimeout = 3600;

        DbConfiguration.SetConfiguration(new MySql.Data.Entity.MySqlEFConfiguration());
        SetSqlGenerator(MySql.Data.Entity.MySqlProviderInvariantName.ProviderName, new MySql.Data.Entity.MySqlMigrationSqlGenerator());
        SetHistoryContextFactory(MySql.Data.Entity.MySqlProviderInvariantName.ProviderName, (connection, schema) => new MySql.Data.Entity.MySqlHistoryContext(connection, schema));
    }

}

回答5:

If you have tried all the answers in this post and still getting the error, then try running this command on MySQL server:

set GLOBAL storage_engine='InnoDb';

The bug was reported here: http://bugs.mysql.com/bug.php?id=4541

回答6:

You have to see this link https://stackoverflow.com/a/27082231/929740

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

回答7:

I got rid of this issue by running:

Enable-Migrations -EnableAutomaticMigrations -Force -ContextTypeName ApplicationDbContext

And without creating an Initial migration:

Update-Database

The tables were created without any problem nor errors

回答8:

There are simple way to change NVARCHAR fields to varchar in MYSQl add this lines in IdentityModels.cs

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class ApplicationDbContext : IdentityDbContext<ApplicationUser, Role, int, UserLogin, UserRole, UserClaim>
{
    protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        //troca todos os campos NVARCHAR por varchar
        modelBuilder.Properties().Where(x =>
          x.PropertyType.FullName.Equals("System.String") &&
         !x.GetCustomAttributes(false).OfType<ColumnAttribute>().Where(q => q.TypeName != null && q.TypeName.Equals("varchar(max)", StringComparison.InvariantCultureIgnoreCase)).Any())
          .Configure(c =>
             c.HasColumnType("varchar(65000)"));

        modelBuilder.Properties().Where(x =>
          x.PropertyType.FullName.Equals("System.String") &&
         !x.GetCustomAttributes(false).OfType<ColumnAttribute>().Where(q => q.TypeName != null && q.TypeName.Equals("nvarchar", StringComparison.InvariantCultureIgnoreCase)).Any())
          .Configure(c =>
             c.HasColumnType("varchar"));
    }

    public ApplicationDbContext()  : base("DefaultConnection")
    {

    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}