AoutroutePartRecord Index under MySQL Issue

[orchardbot]

veskok created: https://orchard.codeplex.com/workitem/20951

Unfortunately MySQL does not support text columns in indexes longer than 767 bytes. In Orchard.Autoroute migrations we have SchemaBuilder.AlterTable("AutoroutePartRecord", table => table .CreateIndex("IDX_AutoroutePartRecord_DisplayAlias", "DisplayAlias"));

, but DisplayAlias is 2048 bytes long, far from the constraint of 767 bytes.

A workaround of this is to change like: SchemaBuilder.AlterTable("AutoroutePartRecord", table => table .CreateIndex("IDX_AutoroutePartRecord_DisplayAlias", "DisplayAlias(767)"));

, which probably will work only on MySQL.

The same issue we have in Orchard.MediaLibrary SchemaBuilder.AlterTable("MediaPartRecord", t => t .CreateIndex("IDX_MediaPartRecord_FolderPath", "FolderPath"));

, where FolderPath is far from 767 bytes long, so the workaround is SchemaBuilder.AlterTable("MediaPartRecord", t => t .CreateIndex("IDX_MediaPartRecord_FolderPath", "FolderPath(767)"));

Actually we can add the following in migrations private readonly ISessionFactoryHolder _sessionFactoryHolder; private readonly Dialect _dialect;

    public Migrations(ISessionFactoryHolder sessionFactoryHolder)
    {
        this._sessionFactoryHolder = sessionFactoryHolder;
        var configuration = _sessionFactoryHolder.GetConfiguration();
        this._dialect = Dialect.GetDialect(configuration.Properties);
    }

and to check if dialect is MySQL var dialect = this._dialect as MySQLDialect; if (dialect != null) { SchemaBuilder.AlterTable("AutoroutePartRecord", table => table .CreateIndex("IDX_AutoroutePartRecord_DisplayAlias", "DisplayAlias(767)")); } else { SchemaBuilder.AlterTable("AutoroutePartRecord", table => table .CreateIndex("IDX_AutoroutePartRecord_DisplayAlias", "DisplayAlias")); }

[orchardbot]

@sebastienros commented:

I assume you can fix it in MySQL:

When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

[orchardbot]

Sofling76 commented:

Hi,

I am experiencing same issue with throwing exception Table 'oct_orchard_autoroute_autoroutepartrecord' already exists. Having made code changes to FolderPath(767) and DisplayAlias(767) and running set global innodb_large_prefix = true in MySql, didn't fix the exception. Also, I could not resolve Dialect for line (private readonly Dialect _dialect). In addition to all of these changes, followed instructions for http://orchard.codeplex.com/workitem/20950.

Please advice how to resolve the issue. Should CustomPattern and DisplayAlias fields be altered to mediumtext?

Thanks, S

[luciofassio]

I'm trying: Orchard 1.9.2 MySql 5.6

I get: BLOB/TEXT column 'DisplayAlias' used in key specification without a key length BLOB/TEXT column 'FolderPath' used in key specification without a key length

even having applied modifications with key length (both 767 and 2048) to Migrations.cs of the affected modules and used: [mysqld] bind-address = * innodb_large_prefix = true

in mysql.ini.

Curiously we had the same issue in Orchard 1.8 in spring, with the same error, but it did not prevent Orchard from starting, while now we have:

Server Error in '/' Application.

The resource cannot be found.

Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable. Please review the following URL and make sure that it is spelled correctly.

Requested URL: /

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34249

Any other ideas?

Thanks in advance.

[Thierry-S]

I've pasted my error related to DisplayAlias in issue #5366 . But I'd rather not have the fix suggested by @sebastienros

When the innodb_large_prefix configuration option is enabled, this length limit is raised to 3072 bytes, for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.

Because COMPRESSED innoDB tables are not supported by AWS Aurora, which otherwise is fully compatible with MySql 5.6. http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Migrate.html in a table "Limitation/Guideline".