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.


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.

MySQL Entity Framework: Can't create index, Entity framework with mysql database migrations fail, when creating indexes doesn't like the SQL generated particularly "hash" when trying to create indexes. Entity framework with mysql database migrations fail, when creating indexes Entity Framework 6 EF 6 Home Articles Knowledge Base Online Examples


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.

MySQL Bugs: #73278: EF6 MySql Migrations Code Generator Bug , Whenever the migrations contains a creation of a Index my migration fails when running the update-database command. The error message and  When running a migration against a freshly installed MySQL database (which works fine against a SQL Server Database), it fails on the first create table with the error: Incorrect usage of spatial/fulltext/hash index and explicit index order. This happens when it tries to run the following Index method:


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)

Migrations that used to work, now fail with 2.1.0-rc1-final · Issue , Description: I am using code first migrations with Entity Framework 6 on Add-​Migration to create the initial database creation migration code, The CodeGenerator in MySql is generating sql that is missing quotes around index names. Afterwards the project should fail to build because the UQ_FirstName  Hello Richard Collette, Thank you for the bug report. As per internal discussion with developers and they confirm that index in EF Core migrations are not supporting. Thank you for your interest in MySQL. Thanks, Chiranjeevi.


Handling Entity Framework Core database migrations in production , Data.MySqlClient.MySqlException (0x80004005): Cannot drop index Error to create model by context CodeFirst with Pomelo. released pomelo version, compared to EF Core 2.1 and new pomelo version where it fails. It derives from the context, running through an Entity Framework partial that will tell the migration to create the database if it doesn't already exist. We then implement the Seed method, which can be used to seed the database with data when running a migration.


Code First Migrations with an existing database, NET Core” in which he used “migrating a database” as an example of There are five ways to create a migration in EF Core of the change – should that property be nullable? do I need an index? etc. Here is an example unit test that will fail if the database schema doesn't match EF Core's Model. EnsureCreated() bypasses Migrations to create the schema, which causes Migrate() to fail. Next steps For more information, see Entity Framework Core tools reference - EF Core .


Using Migration Scripts in Database Deployments, Screencasts; Step 1: Create a model; Step 2: Enable Migrations; Step 3: Add with an existing database, one that wasn't created by Entity Framework. your local database may fail because Migrations will try to drop indexes  You may also end up with extra indexes as Migrations will create indexes on foreign key columns by default – this may not have been the case in your original local database. Not all database objects are represented in the model. Database objects that are not part of your model will not be handled by Migrations.