MySQL Bugs: #81817: Error: Specified key was too long; max key length is 767 bytes

Bug #81817	Error: Specified key was too long; max key length is 767 bytes
Submitted:	11 Jun 2016 20:51	Modified:	31 Jul 2017 19:39
Reporter:	Harel Mazor	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.8	OS:	Windows (Window 10)
Assigned to:		CPU Architecture:	Any
Tags:	asp.net, Identity Framework, migrations, RoleNameIndex, update-database

Description:
Using code first, mysql and Identity framework there is a problem creating the database, the following error appears:
Specified key was too long; max key length is 767 bytes
when running this line:
CREATE UNIQUE index `RoleNameIndex` on `AspNetRoles` (`Name` DESC) using HASH
which is generated using update-database entity framework command.

How to repeat:
The long story: I have a project in asp.net webapi2 that uses SQL server and wanted to migrate to mysql.
So I created a database Installed everything that was needed in visual studio, updated the connection string in my web.config, added 
[DbConfigurationType(typeof(MySqlEFConfiguration))]
above my database context and ran update-database from package manager console in order to allow EF to create my database scheme.
the following error appeared, although some tables were created.
Specified key was too long; max key length is 767 bytes

Suggested fix:
I ran across this question and the answer helped me:
http://stackoverflow.com/questions/23562585/identity-entity-framework-library-update-datab...
the first answer is old and outdated but the second answer did the trick. 
below is code that needs to be added to solve this issue:
I was wondering if this code can be added to one of the classes in EF MySQL in order to make the life of other developers easier.
Also, I would recommend a section for asp.net+EF+Mysql in your documentation as I suppose other like me would find it helpful. 
Since I managed to make it work I don't mind sharing my steps to creating a web app with EF+MYSQL+WebAPI2 if needed.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            if (modelBuilder == null)
            {
                throw new ArgumentNullException(nameof(modelBuilder));
            }

            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<ApplicationUser>().Property(u => u.UserName).HasMaxLength(128);

            //Uncomment this to have Email length 128 too (not neccessary)
            //modelBuilder.Entity<ApplicationUser>().Property(u => u.Email).HasMaxLength(128);

            modelBuilder.Entity<IdentityRole>().Property(r => r.Name).HasMaxLength(128);
        }

Thanks ahead,
Harel M.

Hello Harel Mazor,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (Asp.Net) and Connector/NET 6.9.8.

Thanks,
Chiranjeevi.

create table `AspNetRoles` (`Id` nvarchar(128)  not null ,`Name` nvarchar(256)  not null ,primary key ( `Id`) ) engine=InnoDb auto_increment=0
CREATE UNIQUE index  `IX_Name` on `AspNetRoles` (`Name` DESC) using HASH
MySql.Data.MySqlClient.MySqlException (0x80004005): Specified key was too long; max key length is 767 bytes
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
   at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
Specified key was too long; max key length is 767 bytes

I ran into this issue as well.  It appears the max key length is defined in MySQLTypeMapper.cs as 767 and that is what is used to create the migrations table:

CREATE TABLE `__EFMigrationsHistory` (
    `MigrationId` varchar(767) NOT NULL,
    `ProductVersion` text NOT NULL,
    PRIMARY KEY (`MigrationId`)
)

This only works if the character set is latin1.  My character set was currently set to utf8.  A simpler fix for me was to change the default character set to latin1.

I suggest you take your things and run as fast as you can.
This bug is now open for more than a year.
I have opened another bug and it seems that no one wants to fix them.
I have been in contact with a MySQL representative in order to fix those bugs and migrate from MSSQL to MySQL but nothing was done and I completely abandoned my attempt to migrate, I suggest you do the same...