在MySQL资料库上执行迁移时，出现Entity Framework错误.“不正确使用空间/全文/哈希索引和显式索引顺序" [英] Getting an Entity Framework error running migrations on a MySQL database. "Incorrect usage of spatial/fulltext/hash index and explicit index order"

问题描述

问题

对新安装的MySQL数据库(对SQL Server数据库运行良好)运行迁移时，它在第一个创建表上失败，并显示以下错误:

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

在尝试运行以下 Index 方法时会发生这种情况:

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);

在 Update-Database 上使用 -verbose 标志后，我看到导致此错误的命令如下.

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.

背景

在旧的(约3年前)MySQL数据库上运行迁移工作正常，但是当我安装新的MySQL时，我遇到了有关密码身份验证的错误，可以通过更新MySQL NuGet软件包或强制该用户来解决使用旧密码.为此，请参见此处问题.

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.

我将我的MySQL NuGet软件包更新为最新版本，并导致了另一个错误(可以看到

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.

因此，在此阶段它可以正常连接，但似乎数据库本身不喜欢Entity Framework生成的index命令.

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.

版本

• MySql.Data 6.10.7
• MySql.Data.Entity 6.10.7
• EntityFramework 6.2.0
• MySql数据库8.0.11社区

更新

我设法通过在执行Mysql实例时执行SQL命令并替换 .Index()调用来手动使其在MySql上运行.

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.

但是，即使在显然成功地运行了迁移之后，MySql.Data(.Entity)仍然会给出运行时错误.

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

将数据库还原到MySql 5的先前主要版本时，代码无需更改即可完美运行.

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

我将稍等片刻，等待NuGet软件包和MySql数据库更新，然后再进行更新.

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

推荐答案

请参阅此答案

在此我创建一个继承类，重写一个函数，并在configuration.cs上设置我的自定义类

on this i create a inherit class, override a function and set my custom class on configuration.cs

这篇关于在MySQL资料库上执行迁移时，出现Entity Framework错误.“不正确使用空间/全文/哈希索引和显式索引顺序"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！