EF Core - Table '*.__EFMigrationsHistory' doesn't exist

Question

I want to stress out that this is .NET Core and the threads about EF 6.0 does not apply to this problem

I created my DbContext and added it in DI, however when I do dotnet ef database update -v it does not want to create the migrations table __EFMigrationsHistory.

Is there some other command that I should do first or this is a bug of EF Core MySQL adapter?

MainDbContext

using Microsoft.EntityFrameworkCore;
using MySQL.Data.EntityFrameworkCore.Extensions;
using Web.Models;

namespace Web.Infrastructure
{
    public class MainDbContext : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySQL("connection-string-here");
            base.OnConfiguring(optionsBuilder);
        }
    }
}

Error

Finding DbContext classes...

Using context 'MainDbContext'.

Using database 'db' on server 'localhost'.

MySql.Data.MySqlClient.MySqlException: Table 'db.__EFMigrationsHistory' doesn't exist

Table 'db.__EFMigrationsHistory' doesn't exist ```

project.json

{
  "dependencies": {
    "Microsoft.NETCore.App": {
      "version": "1.0.1",
      "type": "platform"
    },
    "Microsoft.AspNetCore.Diagnostics": "1.0.0",
    "Microsoft.AspNetCore.Mvc": "1.0.1",
    "Microsoft.AspNetCore.Razor.Tools": {
      "version": "1.0.0-preview2-final",
      "type": "build"
    },
    "Microsoft.AspNetCore.Routing": "1.0.1",
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.1",
    "Microsoft.AspNetCore.StaticFiles": "1.0.0",
    "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
    "Microsoft.Extensions.Configuration.Json": "1.0.0",
    "Microsoft.Extensions.Logging": "1.0.0",
    "Microsoft.Extensions.Logging.Console": "1.0.0",
    "Microsoft.Extensions.Logging.Debug": "1.0.0",
    "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
    "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0",
    "BundlerMinifier.Core": "2.2.301",
    "WebMarkupMin.AspNetCore1": "2.2.1",
    "MySql.Data.EntityFrameworkCore": "7.0.6-IR31",
    "Microsoft.EntityFrameworkCore.Design": "1.0.0-preview2-final"
  },
  "tools": {
    "Microsoft.AspNetCore.Razor.Tools": "1.0.0-preview2-final",
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.0.0-preview2-final",
    "Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
  },
  "frameworks": {
    "netcoreapp1.0": {
      "imports": [
        "dotnet5.6",
        "portable-net45+win8"
      ]
    }
  },
  "buildOptions": {
    "emitEntryPoint": true,
    "preserveCompilationContext": true
  },
  "runtimeOptions": {
    "configProperties": {
      "System.GC.Server": true
    }
  },
  "publishOptions": {
    "include": [
      "wwwroot",
      "**/*.cshtml",
      "appsettings.json",
      "web.config"
    ]
  },
  "scripts": {
    "prepublish": [ "bower install", "dotnet bundle" ],
    "postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
  }
}

Temp Solution

By executing dotnet ef migrations script I get SQL code that I can execute directly in MySQL. After that migrations table is created and everything works normally. This is temp-solution which is bad. I still wonder what is the "correct" way of enabling migrations.

Electroencephalograph

Please specify the versions of the packages that you use. – Recalcitrate

Does this connection string gives you access to an account, which has permission to create tables? In other words, are you able to create the table manually using this connection string? – Recalcitrate

@Recalcitrate I added packages.json. Of course I have full permissions to database. Once I add the table manually migrations work and my User model table gets created for me and I can do operations with it. – Electroencephalograph

@Electroencephalograph I just encountered the same problem with ef core + mysql trying to follow learn.microsoft.com/en-us/ef/core/get-started/aspnetcore/new-db Add-Migrations works ok but Update-Database throws this error – Willem

A workaround is to create the following table manually before running "dotnet ef database update" CREATE TABLE `__EFMigrationsHistory` ( `MigrationId` nvarchar(150) NOT NULL, `ProductVersion` nvarchar(32) NOT NULL, PRIMARY KEY (`MigrationId`) ); – Jubal

Answer 1

Turning Mark G's comment into an answer.

Once the __EFMigrationsHistory table has been created, the rest of the update should run.

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

Alternatively, generate the script of your migration(s) and apply to the database manually using this command in Package Manager Console:

Script-Migration

If you need to generate All scripts, you can use this command:

Script-Migration -from 0

Swept

This fixed it for me. +1 from me :) – Laterality

Answer 2

Encountered the same problem while using standard Oracle provider.

According to this question Dot Net Entity Framework database update doesn't create tables in mysql database it doesn't have the migrations feature implemented yet.

I followed the suggestions the switched to SapientGuardian provider and it does seem to be the best way to go now.

Edit: as suggested in comments Pomelo is the best option as of beggining of 2018. I've chosen it over other providers since my original answer.

Willem

I mark this as an answer, however I think that better is to just directly execute the script manually once. I am pretty sure this bug is fixed anyway already. – Electroencephalograph

Unfortunately it looks like Oracle doesn't give a shit and last time I checked it wasn't. You may execute script manually once if you have final db structure already. In my case it was constantly changing (the system had some initial shape but it beta stage there were many changes so the orm had to be updated accordingly). It's nice that someone proves it is possible to stick with the default provider. Best of luck! – Willem

Pomelo is now the recommanded one. github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql – Humpbacked

no. it's not fixed yet. and yes, Pomelo.EntityFrameworkCore.MySql seems to be recommended by ms, has more downloads than oracle and SapientGuardian, also has more git stars and more frequently downloaded – Gingrich

Problem remains with official MySql package, but Pomelo seems to work – Yashmak

Answer 3

I run into the same issue, OP context might be slightly different but here is my answer for sake of completeness.

One of the ways you can run into this problem is if:

• You create a migration and update the database,
• Later for some reason you drop your tables (not the database) and try to run the update-databse command once again.

In that case you will get the error reported by OP

MySql.Data.MySqlClient.MySqlException: Table 'db.__EFMigrationsHistory' doesn't exist

The solution in this case, is to drop the complete database. After that the update-databse command runs sucessfuly.

I'm not sure if its related to mysql only, but to resume :

• If you drop the tables but use an existing database (which had previous migrations), the update command will give you an exception.
• If you drop the complete database, the update command will run perfectly.

Friseur

this helped .. with aspnet core .. latest is better .. so always look for recent replies or posts. – Christly

You solution works for more than just mysql. I've been beating my head against a wall trying to figure out why I couldn't get postgres to migrate properly. In my case I could run locally on windows 10 with no trouble, but on the RHEL6 box where it has to run it choked. In this case, dropping the schema (roughly like dropping just the tables) worked in windows but not RHEL, it was only when I dropped the database itself that things started working. – Macdougall

Just ensure that the user you are utilizing has server level privileges to create, and it works great! – Apothecium

Answer 4

I had the same issue but with a slightly different environment.

The issue was I tried to run the migrations with the database already existing before the migrations ever ran.

In short, Make sure that you DO NOT create the database yourself. Let the migrations do it for you.

Coming from a PHP/Laravel background, that wasn't obvious to me :)

---

MySQL server on MacOS if you're interested in what my environment is.

Leontineleontyne

Answer 5

Had same issue with official Oracle MySQL provider.

Just added package: Install-Package SapientGuardian.EntityFrameworkCore.MySql and migration worked !

Dric

Pomelo is now the recommanded one: github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql – Humpbacked

@Humpbacked as of the end of 2018, Pomelo is in search of someone who can maintain the project – Carboniferous