Entity framework with mysql database migrations fail, when creating indexes

Question

Entity framework with mysql database migrations fail, when creating indexes

what causes this error in MySQL with entity framework? I can generate the migration script and connect to the database but it doesn't like the SQL generated particularly "hash" when trying to create indexes.

Example:

CREATE index  `IX_Facility_ID` on `Contact.Address` (`Facility_ID` DESC) using HASH

Error:

MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect usage of spatial/fulltext/hash index and explicit index order

Is there any way around this? This is with EF 6 and the latest mysql dlls.

mysql

entity-framework-6

mysql-connector

asked on Stack Overflow Apr 26, 2018 by

andy gaskins • edited Apr 26, 2018 by

Ironcache

3 Answers

i haved the same problem, after i read on posts, i decided create a class inherits ofMySqlMigrationSqlGenerator and override protected override MigrationStatement Generate ( CreateIndexOperation op ), then on configuration of migration i add : SetSqlGenerator ( "MySql.Data.MySqlClient", new myMigrationSQLGenerator ( ) );

this is the code of class:

public class myMigrationSQLGenerator : MySqlMigrationSqlGenerator
{
    private string TrimSchemaPrefix ( string table )
    {
        if ( table.StartsWith ( "dbo." ) )
            return table.Replace ( "dbo.", "" );
        return table;
    }

    protected override MigrationStatement Generate ( CreateIndexOperation op )
    {
        var u = new MigrationStatement ( );
        string unique = ( op.IsUnique ? "UNIQUE" : "" ), columns = "";
        foreach ( var col in op.Columns )
        {
            columns += ( $"`{col}` DESC{( op.Columns.IndexOf ( col ) < op.Columns.Count - 1 ? ", " : "" )}" );
        }
        u.Sql = $"CREATE {unique} INDEX `{op.Name}` ON `{TrimSchemaPrefix ( op.Table )}` ({columns}) USING BTREE";
        return u;
    }
}

and this is the code on Migrations\Configuration.cs:

    public Configuration ()
    {           
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator ( "MySql.Data.MySqlClient", new myMigrationSQLGenerator ( ) );
    }

this work for me.

answered on Stack Overflow Aug 8, 2018 by

henoc salinas

Looking the MySql source code, you need to add this(anonymousArguments: new { Type = "BTrees" }) on index definitions:

.Index(t => t.GroupId, anonymousArguments: new { Type = "BTrees" });

This is not a fix, but for the moment you can apply the migration code.

answered on Stack Overflow Apr 30, 2018 by

user2671336 • edited May 1, 2018 by

user2671336

I think thread locale change prior DEFAULT generate methods is better solution than henoc salinas. There can be another hidden defects inside standard implementation as well as you will need to support this every time you update MySql packages.

Just change culture information prior calls and restore if you need afterwards.

Here is reworked version:

public class CustomizedMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
{
    #region Override members

    protected override MigrationStatement Generate(CreateIndexOperation op)
    {
        var currentCulture = Thread.CurrentThread.CurrentCulture;
        try
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en");
            return base.Generate(op);
        }
        finally
        {
            Thread.CurrentThread.CurrentCulture = currentCulture;
        }
    }

    ...
    //Same for all overriden Generate() methods

    #endregion
}

Also you can just specify thread locale before SetSqlGenerator method call if you don't care locale persistence (code first model)

answered on Stack Overflow Feb 15, 2019 by

BlackGad

User contributions licensed under CC BY-SA 3.0