具有MySql和迁移的实体框架由于“最大密钥长度为767字节”而失败。 [英] Entity Framework with MySql and Migrations failing because "max key length is 767 bytes"
问题描述
此问题已解决！请参阅帖子结尾处的说明。
好的，这个线程是旧的，而较新版本的MySQL Connector已经用MySQL EF解析器处理了这个问题。在这个线程上寻找@KingPong答案。我没有测试过，但是，我正在尝试使用MySql和EntityFramework进行迁移，但似乎有些错误。
当我在包管理器控制台中输入 Update-Database -Verbose 时，EF会执行一些查询，将镜像我的模型类，以及一切完美，然后EF尝试执行这个查询：
创建表`__MigrationHistory`
（
`MigrationId` varchar（150）not null
，`ContextKey` varchar（300）not null
，`Model` longblob not null
，`ProductVersion` varchar（32）not null
，主键（`MigrationId`，`ContextKey`）
）engine = InnoDb auto_increment = 0
结果是：指定的键太长;最大密钥长度为767字节
我尝试将数据库排序规则更改为utf-8，但仍然相同。也许密钥长度是450个字符，做UTF-8数学（可能是错误的），我想这是试图创建大约1800字节长度的密钥。
由于我是EF的新手，我遵循了一些教程，他们告诉我这样做：
public Configuration（）
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator（MySql.Data.MySqlClient，新的MySql.Data.Entity.MySqlMigrationSqlGenerator（））;
}
也许这个SQL生成器正在做错事，或者EF本身要求该发生器使一个键高达767字节。
如何解决这个问题，避免这个问题，让它与MySql一起使用？
好的，这个问题解决了。你必须告诉EF它必须改变生成__MigrationHistory表的方式。
我做了什么：
首先，创建一个名为 MySqlHistoryContext.cs （或任何你想要的）这个内容：
...
使用System.Data.Common;
使用System.Data.Entity;
使用System.Data.Entity.Migrations.History;
命名空间[你的名字] .Migrations //你可以把任何命名空间放在这里，但确保你会在下一个文件中使用说明语句。只需创建一个新类：D
{
public class MySqlHistoryContext：HistoryContext
{
public MySqlHistoryContext（DbConnection connection，string defaultSchema）：base（connection，defaultSchema）
{
}
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
base.OnModelCreating（modelBuilder）;
modelBuilder.Entity< HistoryRow>（）。Property（h => h.MigrationId）.HasMaxLength（100）.IsRequired（）;
modelBuilder.Entity< HistoryRow>（）。Property（h => h.ContextKey）.HasMaxLength（200）.IsRequired（）;
}
}
}
您可能会有一个名为 Configuration.cs 在 Migrations 文件夹中。如果是，进行必要的调整，否则创建一个新文件。实际上，如果您没有此文件，您将无法获取此错误，因为当您 Add-Migration [name] 时，EF会自动创建该错误。 / p>
命名空间[你的名字] .Migrations
{
using System;
使用System.Data.Entity;
使用System.Data.Entity.Migrations;
使用System.Linq;
内部密封类配置：DbMigrationsConfiguration< CodeFirstMySql.Models.Context>
{
public Configuration（）
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator（MySql.Data.MySqlClient，新的MySql.Data.Entity.MySqlMigrationSqlGenerator（））; //它将生成MySql命令而不是SqlServer命令。
SetHistoryContextFactory（MySql.Data.MySqlClient，（conn，schema）=> new MySqlHistoryContext（conn，schema））; //这里的东西
}
protected override void Seed（CodeFirstMySql.Models.Context context）{} // ommited
}
}
然后更新数据库-Verbose 并且玩得开心！
EF6使用 MigrationHistory 表来跟踪模型更改并确保数据库模式与概念模式之间的一致性。默认情况下，此表不适用于MySQL，因为主键太大。要解决这种情况，您需要缩小该表的密钥大小。
本质上，EF6允许您使用MigrationId / ContextKey索引列修改密钥大小流畅的API，如下所示：
modelBuilder.Entity< HistoryRow>（）。属性（h => h.MigrationId）.HasMaxLength （100）.IsRequired（）;
modelBuilder.Entity< HistoryRow>（）。Property（h => h.ContextKey）.HasMaxLength（200）.IsRequired（）;
[Edit] This problem was solved! See the instructions at the end of the post.
[Edit 2] Ok, this thread is old, and the newer versions of MySQL Connector already handle this with MySQL EF resolvers. Look for @KingPong answer on this thread. I haven't tested it, though.
I'm trying to use MySql and EntityFramework with Migrations, but something seems to be wrong.
When I enter Update-Database -Verbose in the Package Manager Console, EF executes some queries that will "mirror" my model classes, and everything goes perfect, BUT then EF tries to execute this query:
create table `__MigrationHistory`
(
`MigrationId` varchar(150) not null
,`ContextKey` varchar(300) not null
,`Model` longblob not null
,`ProductVersion` varchar(32) not null
,primary key ( `MigrationId`,`ContextKey`)
) engine=InnoDb auto_increment=0
And the result is: Specified key was too long; max key length is 767 bytes
I tried to change my database collation to utf-8, but still the same. Perhaps the key lenght is 450 characters, doing the UTF-8 math (which I may be wrong), I think it's trying to create a key around 1800 bytes length.
Since I'm new to EF, I followed some tutorials and they told me to do this:
public Configuration()
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
}
Perhaps this SQL generator is doing the wrong thing, or EF itself is asking to the generator to make a key up to 767 bytes.
How can I fix that, avoid this problem and get it to work with MySql?
[Edit] Ok, this problem was solved. You have to tell EF it has to change the way it generates the __MigrationHistory table.
What I did:
First, create a file called MySqlHistoryContext.cs (or whatever you want) with this content:
...
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;
namespace [YOUR NAMESPACE].Migrations //you can put any namespace here, but be sure you will put the corret using statement in the next file. Just create a new class :D
{
public class MySqlHistoryContext : HistoryContext
{
public MySqlHistoryContext(DbConnection connection, string defaultSchema):base(connection,defaultSchema)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
}
}
}
You might have a file called Configuration.cs inside your Migrations folder. If yes, make the necessary adjustments, otherwise create a new file. Actually you kinda won't be able to get to this error if you didn't have this file, since EF creates it automatically when you Add-Migration [name].
namespace [YOUR NAMESPACE].Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<CodeFirstMySql.Models.Context>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); //it will generate MySql commands instead of SqlServer commands.
SetHistoryContextFactory("MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema)); //here s the thing.
}
protected override void Seed(CodeFirstMySql.Models.Context context){}//ommited
}
}
Then Update-Database -Verbose and have fun!
Answer paraphrased from Adding custom MigrationHistory context...
EF6 uses a MigrationHistory table to keep track of model changes and to ensure the consistency between the database schema and conceptual schema. This table does not work for MySQL by default because the primary key is too large. To remedy this situation, you will need to shrink the key size for that table.
Essentially, EF6 allows you to modify the key size for the MigrationId/ContextKey index columns using Fluent API like so:
modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
这篇关于具有MySql和迁移的实体框架由于“最大密钥长度为767字节”而失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
