CODEWITHSUNDEEP
c#mysqlentity-framework
Amitai
Amitai

Reputation: 43

EF6 - Incorrect usage of spatial/fulltext/hash index and explicit index order

I'm trying to create a one to many relation with EF6

here are my entities.

Book:

public class Book
{
    public int BookId { get; set; }
    public string BookName { get; set; }
}

Publisher:

public class Publisher
{
    public int PublisherId { get; set; }
    public string PublisherName { get; set; }
    public ICollection<Book> Books { get; set; }
}

Context:

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class MyContext : DbContext
{
    public MyContext() : base("name=MySqlDbConnectionString")
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
    }

    public DbSet<Publisher> Publishers { get; set; }
    public DbSet<Book> Books { get; set; }
}

Program:

static void Main(string[] args)
    {
        using (MyContext entities = new MyContext())
        {
            Book user = new Book() { BookName = "gdsag" };
            entities.Books.Add(user);
            entities.SaveChanges();
        }
    }

The problem is being used to auto create the database. but when I run it I get the following error:

MySql.Data.MySqlClient.MySqlException: 'Incorrect usage of spatial/fulltext/hash index and explicit index order'

I'm using EF 6.2
MySql.Data 6.9
MySql.Data.Entity 6.9

any ideas what am I doing error?

Thanks in advance

Edit: those are the generate sql commands

create table `Books` (`BookId` int not null  auto_increment ,`BookName` longtext,`Publisher_PublisherId` int,primary key ( `BookId`) ) engine=InnoDb auto_increment=0
-- Executing at 7/2/2018 11:02:32 PM +03:00
-- Completed in 52 ms with result: 0

create table `Publishers` (`PublisherId` int not null  auto_increment ,`PublisherName` longtext,primary key ( `PublisherId`) ) engine=InnoDb auto_increment=0
-- Executing at 7/2/2018 11:02:32 PM +03:00
-- Completed in 49 ms with result: 0

CREATE index  `IX_Publisher_PublisherId` on `Books` (`Publisher_PublisherId` DESC) using HASH
-- Executing at 7/2/2018 11:02:32 PM +03:00
-- Failed in 2 ms with error: Incorrect usage of spatial/fulltext/hash index and explicit index order

Disposed transaction at 7/2/2018 11:02:32 PM +03:00

Upvotes: 3

Views: 1132

Answers (2)

Robert Ginsburg
Robert Ginsburg

Reputation: 149

Sure, what I did was inherit a class from MySqlMigrationSqlGenerator , then activated the class in my DbMigrationsConfiguration handler. The class is listed first, and an excerpt from the migration handler is also provided

public class FixedMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
{
    public FixedMySqlMigrationSqlGenerator()
        :base()
    {
    }

    /// <summary>
    /// we want BTREE because HASH is not correct for normal Keys on MySQL 8
    /// </summary>
    /// <param name="op"></param>
    /// <returns></returns>
    protected override MigrationStatement Generate(CreateIndexOperation op)
    {
        MigrationStatement migrationStatement = base.Generate(op);
        System.Diagnostics.Trace.WriteLine(migrationStatement.Sql);
        string fubarSql = migrationStatement.Sql.TrimEnd();

        if(fubarSql.EndsWith("using HASH",StringComparison.OrdinalIgnoreCase))
        {
            string modSql = fubarSql.Replace("using HASH", " using BTREE");
            migrationStatement.Sql = modSql;
        }
        return migrationStatement;
    }


}

I have a dbmigrationsconfiguration class as well

public sealed class LogDataMigration : DbMigrationsConfiguration<LogDataContext>
{
    public const string CONTEXT_KEY = "BalsamicSoftware.LogData";
    private static readonly HashSet<string> _InitializedConnections = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

    private static readonly object _LockProxy = new object();

    public LogDataMigration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
        ContextKey = CONTEXT_KEY;
        SetSqlGenerator("MySql.Data.MySqlClient", new FixedMySqlMigrationSqlGenerator());

    }
}

The migration configuration class is activated with a static call to 

Database.SetInitializer<LogDataContext>(new MigrateDatabaseToLatestVersion<LogDataContext, LogDataMigration>(true));

So its three steps, first create a migration configuration (if you don't already have one). Implement the sql generator, then invoke the sql generator from your migration configuration.

Upvotes: 3

Robert Ginsburg
Robert Ginsburg

Reputation: 149

I recently updated to the 8.X my sql client library for .NET. I was building a Framework application (not CORE) and I used mostly data annotations (not fluent) configuration for EF entities and I ran into the same issue. The data context's would not initialize and would generate the errro "Incorrect usage of spatial/fulltext/hash index and explicit index order". It turns out that the MySQL developers seem to ignore this sceniaro test suites. I ended up overriding the CreateIndexOperation generator in the MySqlMigrationSqlGenerator and replacing the default "using HASH" with "using BTREE". Now my indices are foreign key relationships create correctly.

Upvotes: 1

Related Questions