0

I am using EF code-first migration to create database schema on MySQL. But it is giving error when I am going to execute command for update database as below.

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

I followed all the steps given on MySQL support

I have tried to search on google and also in stackoverflow. But I couldn't get permanent solution to fix this issue.

  <connectionStrings>
   <add name="DefaultConnection" connectionString="Server=localhost;port=3306;database=testdb;Uid=root;password=*******;" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

I using using MySQL Workbench 6.3.10. MySQL Server version is 8.0.11.

For the entity framework code-first, I am using below packages..

  • EntityFramework - 6.2.0
  • MySql.Data - 6.10.7
  • MySql.Data.Entity - 6.9.12

When I used latest version of MySql.Data (8.0.11), it is giving me diffrent error :

The provider did not return a ProviderManifestToken string.

Dharmesh
  • 107
  • 1
  • 16
  • I found solution for this in https://stackoverflow.com/questions/50102420/ef-incorrect-usage-of-spatial-fulltext-hash-index-and-explicit-index-order. – Nandan Acharya Jun 09 '18 at 12:39
  • I checked that solution earlier. But its not permanent solution. I need to replace manually everywhere. – Dharmesh Jun 14 '18 at 01:44
  • see this https://stackoverflow.com/a/51756143/7782179 on this i create a inherit class, override a function and set my custom class on configuration.cs – henoc salinas Aug 10 '18 at 14:45

1 Answers1

1

You can try to run Update-Database -verbose to see more logs and identify that EF try to execute a script with CREATE INDEX `IX_id` on `Table` (`col_Id` DESC) using HASH

This error is caused by using HASH. The MySQL Server return the following message, if you try to create index using HASH.

Error Code: 1221. Incorrect usage of spatial/fulltext/hash index and explicit index order

locate .Index(t => t.col_Id) or CreateIndex("dbo.table", "col_Id") in your migration scripts and change it as follow:

Try to use BTrees like this:

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

or 

CreateIndex("dbo.table", "col_Id" , anonymousArguments: new { Type = "BTrees"}))

This worked for me.

MoSad
  • 31
  • 2