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

[11 Jun 2016 20:51] Harel Mazor
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.
[14 Jun 2016 7:41] Chiranjeevi Battula
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.
[14 Jun 2016 7:42] Chiranjeevi Battula
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
[31 Jul 2017 19:33] Weston Pace
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.
[31 Jul 2017 19:39] Harel Mazor
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...

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.