767 length limitation in mysql

[HengzheLi]

Create a new tenant using mysql as database. Occur below error while setting it up

2018-05-15 23:47:49,304 [42] Orchard.Data.Migration.DataMigrationManager - fanyong - Error while running migration version 0 for Orchard.Autoroute. 
System.Reflection.TargetInvocationException:  ---> 
MySql.Data.MySqlClient.MySqlException: Specified key was too long; max key length is 767 bytes
   在 MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   在 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   在 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   在 MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   在 MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   在 Orchard.Data.Migration.Interpreters.DefaultDataMigrationInterpreter.RunPendingStatements() 

Got the sql from vs debugger, it is: create indexfy_IDX_AutoroutePartRecord_DisplayAliasonfy_Orchard_Autoroute_AutoroutePartRecord(DisplayAlias(767))

From SO answer https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes/31474509#31474509. I guess may be create indexfy_IDX_AutoroutePartRecord_DisplayAliasonfy_Orchard_Autoroute_AutoroutePartRecord(DisplayAlias(255)) can work

[sebastienros]

Dev branch?

[HengzheLi]

Yes

[BenedekFarkas]

Does this mean that the length of the DisplayAlias column should be restricted to 255 characters?

[sebastienros]

Or not index this field on mysql

[BenedekFarkas]

OK, so a generic solution might be that SchemeBuilder.CreateIndex ignores columns longer than 255 characters for MySQL databases.

[BenedekFarkas]

@HengzheLi which MySQL version are you using?

[BenedekFarkas]

@sebastienros there are some deeper issues here: It seems that we have migrations in the built-in features that create indices on columns that cause problems in different database engines (and engine versions), SQL Server included.

Index size limits:

• MySQL
  • Version is earlier than 5.7 or the row format is redundant or compact: 767 bytes, which equals to 191 or 255 characters, depending on the encoding.
  • Version is at least 5.7 or the row format is dynamic or compressed: 3072 bytes, which equals to 768 or 1024 characters, depending on the encoding.
• SQL Server
  • Version is earlier than 2016: 900 bytes = 450 characters.
  • Version is at least 2016: 1700 bytes = 850 characters.
• SQL CE: I didn't find an exact answer, but based on testing, for version 4.0, it's more than SQL Server.
• PostgreSQL: I didn't find an exact answer here either.

The MySQL command interpreter modifies the create index command so that values will be trimmed if the character length is over 767, so I suspect that @HengzheLi is using an earlier version where this limit is much lower. Anyway, this logic does not handle clustered indices, so the setup fails on the latest engine version on another migration.

The SQL Server command interpreter does not have this check, so you won't see setup errors, but a runtime error can easily be triggered: AutoroutePartRecord.DisplayAlias allows 2048 characters, but saving a content item with a Title over 450 or 850 characters will fail to save because of the index.

So the migrations and index creations have to be retroactively modified and tested, but we need to figure out what limitations should we apply.

[HengzheLi]

@HengzheLi which MySQL version are you using?

@BenedekFarkas I used version 5.7

[echodreamz]

Ran into this as well with 1.10.3...

at System.RuntimeMethodHandle.InvokeMethod(Object t2022-02-09 16:33:04,235 [15] Orchard.Data.Migration.DataMigrationManager - Default - Error while running migration version 0 for Orchard.Recipes. [removed] System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> MySql.Data.MySqlClient.MySqlException: Specified key was too long; max key length is 3072 bytes at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, 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 Orchard.Data.Migration.Interpreters.DefaultDataMigrationInterpreter.RunPendingStatements() in C:\Agent2\work\ORCH-Release\src\Orchard\Data\Migration\Interpreters\DefaultDataMigrationInterpreter.cs:line 380 at Orchard.Data.Migration.Interpreters.DefaultDataMigrationInterpreter.ExecuteCustomInterpreter[T](T command) in C:\Agent2\work\ORCH-Release\src\Orchard\Data\Migration\Interpreters\DefaultDataMigrationInterpreter.cs:line 398 at Orchard.Data.Migration.Interpreters.DefaultDataMigrationInterpreter.Visit(StringBuilder builder, AddIndexCommand command) in C:\Agent2\work\ORCH-Release\src\Orchard\Data\Migration\Interpreters\DefaultDataMigrationInterpreter.cs:line 227 at Orchard.Data.Migration.Interpreters.DefaultDataMigrationInterpreter.Visit(AlterTableCommand command) in C:\Agent2\work\ORCH-Release\src\Orchard\Data\Migration\Interpreters\DefaultDataMigrationInterpreter.cs:line 153 at Orchard.Data.Migration.Schema.SchemaBuilder.AlterTable(String name, Action1 table) in C:\Agent2\work\ORCH-Release\src\Orchard\Data\Migration\Schema\SchemaBuilder.cs:line 58 at Orchard.Recipes.Migrations.Create() --- End of inner exception stack trace --- at System.RuntimeMethodHandle.InvokeMethod(Object t

Running Percona 5.7.36-39, which is MySQL 5.7.37.