Getting an Entity Framework error running migrations on a MySQL database. "Incorrect usage of spatial/fulltext/hash index and explicit index order"

Question

Problem

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:

CreateTable(
    "dbo.AuditLog",
    c => new
    {
        Id = c.Int(nullable: false, identity: true),
        Name = c.String(maxLength: 1000, unicode: false),
        What = c.String(maxLength: 1000, unicode: false),
        When = c.DateTime(nullable: false, precision: 6),
        Why = c.String(maxLength: 1000, unicode: false),
        Where = c.Int(nullable: false),
        Who_Id = c.String(maxLength: 128, unicode: false),
    })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.AspNetUsers", t => t.Who_Id);
    .Index(t => t.Who_Id);

After using the -verbose flag on Update-Database, I see that the command causing this error is the following.

CREATE index  `IX_Who_Id` on `AuditLog` (`Who_Id` DESC) using HASH

Searching online hasn't been very useful to solve this problem. The closest I've seen is this Stack Overflow question but it did not work for me.

---

Background

Running the migration against an old (~3 years ago) MySQL databases works fine, but when I installed the new MySQL I was getting errors about password authentication, which would be resolved by updating the MySQL NuGet packages, or enforcing that users use a legacy password. See here for this problem.

I updated my MySQL NuGet packages to the latest version and that caused a different error (can be seen here) which I solved by downgrading to a newer package than the one I began with but lower than the latest as people had mentioned the APIs were not working correctly.

So at this stage it connects fine but it seems like the database itself does not like the index command that Entity Framework is generating.

I'm happy to provide more information if necessary.

Versions

• MySql.Data 6.10.7
• MySql.Data.Entity 6.10.7
• EntityFramework 6.2.0
• MySql Database 8.0.11 Community

Update

I managed to get it to run on MySql by manually by executing the SQL command and replacing the .Index() call when targeting MySql instances.

However even after running the migrations apparently successfully, MySql.Data(.Entity) kept on giving runtime errors.

When reverting the DB back to the previous major version of MySql 5, the code works perfectly without any need for change.

I'll wait a while for NuGet packages and the MySql database to update before giving it another go.