Pomelo.EntityFrameworkCore.MySql: MigrationHistory table from EntityFramework is not generated on database update/Specified key was too long

Hello,

I wrote an ASP.NET Core MVC application using Pomelo.EntityFrameworkCore.MySql. I also used migrations, which works on my Windows 10 dev-machine. For testing reasons, I started the app on a Ubuntu 16.04.1 VM as surely the app should run on a linux server later. But it seems that the initial creation of the tables which had worked fine under windows has some issues here.

$ sudo dotnet ef database update
Project MyProject (.NETCoreApp,Version=v1.0) was previously compiled. Skipping compilation.
Pomelo.Data.MySql.MySqlException: Table 'myDatabase.__efmigrationshistory' doesn't exist
   at Pomelo.Data.MySql.MySqlStream.ReadPacket()
   at Pomelo.Data.MySql.NativeDriver.GetResult(Int64& affectedRow, Int64& insertedId)
   at Pomelo.Data.MySql.Driver.NextResult(Int32 statementId, Boolean force)
   at Pomelo.Data.MySql.MySqlDataReader.NextResult()
   at Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.GetAppliedMigrations()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Tools.Cli.DatabaseUpdateCommand.<>c__DisplayClass0_0.<Configure>b__0()
   at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
   at Microsoft.EntityFrameworkCore.Tools.Cli.Program.Main(String[] args)
Table 'myDatabase.__efmigrationshistory' doesn't exist

I tried to create the table manually using the following SQL

CREATE TABLE `__efmigrationshistory` (
  `MigrationId` varchar(150) NOT NULL,
  `ProductVersion` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This gave me another exception:

$ sudo dotnet ef database update
Project MyProject (.NETCoreApp,Version=v1.0) was previously compiled. Skipping compilation.
Pomelo.Data.MySql.MySqlException: Specified key was too long; max key length is 767 bytes
   at Pomelo.Data.MySql.MySqlStream.ReadPacket()
   at Pomelo.Data.MySql.NativeDriver.GetResult(Int64& affectedRow, Int64& insertedId)
   at Pomelo.Data.MySql.Driver.NextResult(Int32 statementId, Boolean force)
   at Pomelo.Data.MySql.MySqlDataReader.NextResult()
   at Pomelo.Data.MySql.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at Pomelo.Data.MySql.MySqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Tools.Cli.DatabaseUpdateCommand.<>c__DisplayClass0_0.<Configure>b__0()
   at Microsoft.Extensions.CommandLineUtils.CommandLineApplication.Execute(String[] args)
   at Microsoft.EntityFrameworkCore.Tools.Cli.Program.Main(String[] args)
Specified key was too long; max key length is 767 bytes

Details about the environment (ubuntu machine)

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.1 LTS
Release:        16.04
Codename:       xenial

$ dotnet --version
1.0.0-preview2-003121

Used Version of Pomelo.EntityFrameworkCore.MySql: 1.0.0 SQL-Server: MariaDB 10.1 x64 running on Windows

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 34 (14 by maintainers)

Most upvoted comments

@ekarlso Which issue exactly? If “MigrationHistory table from EntityFramework is not generated on database update” then you can create table manually before running dotnet ef database update command.

CREATE TABLE `__EFMigrationsHistory` (
	`MigrationId` nvarchar(150) NOT NULL,
	`ProductVersion` nvarchar(32) NOT NULL,
	PRIMARY KEY (`MigrationId`)
)

If the issue is “Specified key was too long” then look at issue https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/136 for further guidance.

mguinness on Dec 6, 2016

@DMWOO7 1.0.1 for this library. In your project.json:

  "dependencies": {
    "Pomelo.EntityFrameworkCore.MySql": "1.0.1-*",

caleblloyd on Oct 28, 2016

It looks like if the Schema gets created by something that is not Entity Framework then no migration history table is there. Then, if a user tries to update the database it throws the Table 'pomelo_test.__EFMigrationsHistory' doesn't exist exception.

Let me see if I can force it to create the migration history table on an empty database that already exists.

caleblloyd on Jan 22, 2017

What are we trying to achieve here? If migrations is generating the table then updates should be able to reference the same table (case sensitivity should not present a problem), or am I missing something?

mguinness on Jan 20, 2017

You can specify the tablename when adding DbContext as follows:

services.AddDbContext<ApplicationDbContext>(options =>
  options.UseMySql(Configuration.GetConnectionString("DefaultConnection"),
    x => x.MigrationsHistoryTable("efmigrationshistory"))
);

There is also an overload to specify the schema (or database name):

services.AddDbContext<ApplicationDbContext>(options =>
  options.UseMySql(Configuration.GetConnectionString("DefaultConnection"),
    x => x.MigrationsHistoryTable(HistoryRepository.DefaultTableName, "mydb"))
);

mguinness on Jan 20, 2017

Is there any update on this?

I am hitting the same issue on Ubuntu 16.04 with .NET CORE 1.1.0

ekarlso on Dec 6, 2016