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

[DMW007]

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

[caleblloyd]

For the __efmigrationshistory issue: this should work in 1.0.1

For the Specified key was too long issue: right now the max key length for all primary keys is 767 bytes, this is an InnoDB limitation. This can be achieved by setting the MaxLength on properties that make up keys and indices.

If you're using Identity V3, then you need to shorten it's key lengths

[DMW007]

Thanks, but how can I get 1.0.1 for Ubuntu? On the official microsoft page https://www.microsoft.com/net the repo only contains 1.0.0 and also the manual downloads like .deb files are avaliable for the older 1.0.0 version only. I already had a similar though, but couldn't find any release for linux, only windows...

Or do you mean Pomelo.EntityFrameworkCore.MySql? There I couldn't find any update using NuGet.
_EDIT:_ Ok my fault, seems like having pre-release status currently.

[caleblloyd]

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

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

[yukozh]

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

[DMW007]

Using version "1.0.1" gave me an error, but "1.0.1-*" worked. Now the MigrationHistory table is created automatically. But I can't get ride of the issues with the too long primary key. I find the GUIDs which were used as primary key of ASP.NET and ASP.NET Core very annoying: They waste space, are badly readable and cause issues like this, especially when used in MySQL instead of MSSQL.

So I would finally solve this by replacing them by an auto increment integer like described here: http://stackoverflow.com/questions/37166098/how-to-make-ef-core-use-a-guid-instead-of-string-for-its-id-primary-key Now all my primary keys are integers with a length of 11. But I get still the error that a specified key was too long. Cant understand why. The longest PK is from the MigrationHistory table as VARCHAR(150).

Its strange that after the exception ALL tables are generated on the target SQL server. And that this problem only occurs on linux. On Windows 10 I NEVER noticed any issues before, although I used the GUID UserIds like in the default template of ASP.NET Identity. It doesn't matter if I use Visual Studio or call dotnet ef database update in the command line, on Windows 10 everything works fine, on linux it breaks... Also the column limitations from https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/master/test/Pomelo.EntityFrameworkCore.MySql.PerfTests/AppDb.cs#L54-L77 doesn't help.

Is there a way to get debug information from Pomelo.EntityFrameworkCore.MySql like writing the SQL query to stdout so that I can see on which query the exception occur? But I need the output on dotnet ef database update not dotnet run.

EDIT
After the migration I tried to run the application, and it works just fine... All tables are created and the database-operations do their job as in the Windows environment. I have no idea where the exception from the too long PK came from...

[yukozh]

Which charset are you using? Our type mapper is designed for utf8. MySQL has limited the max PK length to be 767 bytes. So we set 255 as the default string PK length.

[DMW007]

The testing SQL server which I used as target on the Ubuntu machine used utf8, but the SQL server on the development Windows machine had latin1. This may explains why there were no errors in the windows environment. But not why I get the exception on Ubuntu since there are no PKs any more with long strings, all were switched to integers (except for the MigrationHistory, but this shouldn't be a problem with only 150 chars).

[rockxl1]

Excuse me get into the conversation but I'm trying to use "Pomelo.EntityFrameworkCore.MySql": "1.0.1-prerelease-10010"
But migrations table are not created automatic. I need to set any thing?
I am runing mysql on windows 10 just for testing. The target will be unbuto.

There is my Context:

public class EmployeesContext :  DbContext
    {
        public EmployeesContext()
        : base(GetOptions())
        {
            this.Database.EnsureCreated();
        }
        private static DbContextOptions GetOptions()
        {
            var optionsBuilder = new DbContextOptionsBuilder<EmployeesContext>();
            optionsBuilder.UseMySql("server=127.0.0.1;userid=root;pwd=;port=3306;database=testeRafa;");
            return optionsBuilder.Options;

        }
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Empresa> Empresas { get; set; }
    }

what is missing? sorry and thanks

[DMW007]

@rockxl1 Database.EnsureCreated() creates the database only, not the tables of your entities. In the old ASP.NET stack there were initializers for the database, e.g. always delete and create the tables (sometimes usefull for development) or update them if the model had changed.

This has changed in ASP.NET Core, here its not directly possible to do this in code. Instead, you're able to run the migrations from the commandline. For example, with dotnet ef database update you could apply ALL migrations, so that your database gets updated to the latest version. This will create the database and tables too if necessary.

[rockxl1]

Just run the command to update database? Lets say i create database for the first time with 2 tables. I change the model and add new fields, run again, don´t create the fields. where i set the dotnet ef database update ? in windows? cmd? but where i say to command what database is?

[DMW007]

When you change the model, then its required to create a migration first. It contains the changes like adding a new field. Typically this is done during development so you can use the package-manager console from Visual Studio to do this. There you could directly use the Add-Migration CmdLet and later Update-Database to apply the latest migration to the database-server. You could also run this using dotnet ef prefix as I showed in the last post. This requires a cmd-window in your project folder. The database isn't declared here cause those information is placed in a connection-string in the app-config.

Your questions sound like basic knowledge how EntityFramework Core migrations works is missing. So I would recommend you to get more information about this topic to get familiar with them. A good point to start is this article: http://www.entityframeworktutorial.net/code-first/code-based-migration-in-code-first.aspx and https://docs.efproject.net/en/latest/platforms/aspnetcore/existing-db.html The first one was written for the old EntityFramework but this applys also to Core (expect the Database.SetInitializer() call, this was removed in EF Core).

[rockxl1]

You didn´t understand my question. I know how to use EF, but using MySQL as
storage has a diferent behavior. It can´t create migration table on sql,
because primary key is to long ... So there is a workaround for this and is
what i am looking for. I try the new EF core on MsSQL and create the
__EFMigrationsHistory with no problem!

I know that exist a HistoryRepository Class with the DefaultTableName =
"__EFMigrationsHistory" but i don´t know how to use it.

The question is simple. I Create a set of tables based on my model and now
i want update some fields without drop and recreate.

---

Pense no meio ambiente antes de imprimir este e-mail.

2016-10-29 21:43 GMT+01:00 DMWOO7 [email protected]:

When you changed the model, then its required to create a migration.
Typically this is done during development so you can use the
package-manager console to do this. There you could directly use the
Add-Migration CmdLet and later Update-Database to apply the latest
migration to the database-server. You could also run this using dotnet ef
prefix as I showed in the last post. This requires a cmd-window in your
project folder. The database isn't declared here cause those information is
placed in a connection-string in the app-config.

Your questions sound like basic knowledge how EntityFramework Core
migrations works is missing. So I would recommend you to get more
information about this topic to get familiar with them. A good point to
start is this article: http://www.entityframeworktutorial.net/
code-first/code-based-migration-in-code-first.aspx and
https://docs.efproject.net/en/latest/platforms/aspnetcore/existing-db.html
The first one was written for the old EntityFramework but this applys also
to Core (expect the Database.SetInitializer() call, this was removed in EF
Core).

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/99#issuecomment-257114968,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AKXqyP8QdhGjaewb-1vwKzsjrTRcU2feks5q46__gaJpZM4Kj0YY
.

[mguinness]

@caleblloyd There still appears to be a problem creating __EFMigrationsHistory table using dotnet ef database update,

Using dotnet ef migrations script it does show the table creation script, but I got the following error when I tried:

Table 'classicmodels.__EFMigrationsHistory' doesn't exist

This appears to be a problem with the official MySql provider as well:

http://stackoverflow.com/questions/40597534/ef-core-table-efmigrationshistory-doesnt-exist

[yukozh]

@mguinness which charset are you using?

[yukozh]

@mguinness We have narrow down the id length to varchar(95), could you confirm this in 1.1.0-rtm-10007?

[mguinness]

@Kagamine Sorry, still not working same error as before. Database charset is latin1 and MigrationId field in that table is varchar(150).

[yukozh]

@mguinness Could you try to use utf8?

[mguinness]

No difference I'm afraid. I used the following to make the change, is there something else I need to do?

ALTER DATABASE classicmodels CHARACTER SET utf8 COLLATE utf8_unicode_ci;

[yukozh]

Maybe you need to add charset=utf8 in your conn str.

[mguinness]

@Kagamine I appended "charset=utf8" to connection string, but it made no difference.

As a side note, I'm now getting the following error after I manually create __EFMigrationsHistory:

MySql.Data.MySqlClient.MySqlException: Specified key was too long; max key length is 767 bytes

The largest PK is AspNetUserTokens table with 3 fields at 255 chars = 765 (under 767 byte limit). Or is the real PK size 1,530 bytes since it's double byte?

[ekarlso]

Is there any update on this?

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

[mguinness]

@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.

[joacar]

Running dotnet ef migrations script prior to any migrations has been added generates the above script. (using rtm-100{something})

[douglasg14b]

@mguinness I am also having the same issue, where the migrations fail if the __EFMigrationsHistory table has not been created yet. I thought the migrations would create that table when needed?

[mguinness]

@douglasg14b As I understand it now you first have to run dotnet ef migrations add initial and then dotnet ef database update for subsequent changes.

[domdeger]

This problem occures for me when i use mysql Version >= 5.7. I can reproduces the problem in SQL. 5.7 does not seem to support fully qualified adressing anymore.

If i run the following command on a 5.6 instance i get the rows of the migration table

select * from `mydb`.`__EFMigrationsHistory`

but on the 5.7 i get the error that the table does not exist.
On the 5.7 instance i have to preselect the database first with the USE-Statement:

use vulndb;
select * from `__EFMigrationsHistory`

Can someone check if i am right and maybe fix the problem?

As a sidenote migrations work well for me on my 5.6 instance, but when i switch to the 5.7 instance nothing is working anymore.

Thanks for your help 👍

---

Got the solution myself. The problem was the configuration of my 5.7 instance, which used case-sensitive table names. this caused ef to go nuts.
The workaround was to set

[mysqld] lower_case_table_names=1

Hope it helps some of you ;)

[yukozh]

@mguinness Could we use efmigrationshistory(without underscores and upper cases) as default history table name to avoid this issue?

[mguinness]

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

[yukozh]

I want this to be a default behavior.

[caleblloyd]

I want this to be a default behavior.

Wouldn't that be a huge BC breaking change?

[mguinness]

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?

[yukozh]

@caleblloyd We use __EFMigrationsHistory as mig table name, but why will cause this issue? Is there any convertions will make __EFMigrationsHistory to be lowercase caused this issue?

[caleblloyd]

@Kagamine if we change the default name of the migration history table, EF will create a new table that is empty e.g. efmigrationshistoryinstead of __EFMigrationsHistory

Next time a user runs migrations, EF will look for existing migrations that have already been applied in the new table, which is empty. It will try to run all migrations again from the start. These initial migrations will fail because they have already been run.

I think it is pretty important at this point that we do not change the default. Is the problem really case sensitivity? I tend to doubt it is. Let me look at this issue in more detail tomorrow and see if I can find a fix that does not break BC.

[caleblloyd]

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.