7.0.11 gives __EFMigrationsHistory already exists on dbContext.Database.Migrate();

[TDroogers]

After updating from 7.0.4 to 7.0.11 I get the error: Npgsql.PostgresException: '42P07: relation "__EFMigrationsHistory" already exists' on running dbContext.Database.Migrate(); from the program.cs in an winforms (core 7.0.11) application.

My connect string looks like this: "Host=localhost;Database=dev_copy;Username=postgres;Password=postgres; SearchPath = schema_prod"

After downgrading Npgsql back to 7.0.4 it works again as expected.

[TDroogers]

Given the only change in 7.0.11 is #2787 and this is related to schema I suspect migrations on a different schema now always default to public and the default schema is not used in this development db.

[TDroogers]

@roji Sorry to tag you, but have you seen this issue?

[roji]

Yes, but I'm currently busy with other urgent matters... I'll try to take a look at this as soon as I can.

[zlepper]

As a temporary workaround you can explicitly set the MigrationHistoryTable schema. We do something like this:

    var connectionStringBuilder = new NpgsqlConnectionStringBuilder(connectionString);
    var searchPaths = connectionStringBuilder.SearchPath?.Split(',');

    options.UseNpgsql(connectionString, o =>
        {
            configure(o);

            if (searchPaths is {Length: > 0})
            {
                var mainSchema = searchPaths[0];
                o.MigrationsHistoryTable(HistoryRepository.DefaultTableName, mainSchema);
            }
        })

It's not very elegant, but it works.

[TDroogers]

Thanks @zlepper this works!

[Kaffeetasse]

Thanks @zlepper this helped me also in Npgsql Version 6.0.10 (Npgsql.EntityFrameworkCore.PostgreSQL Version 6.0.22).

[bassem-mf]

I am seeing the same issue with version 8.0.0

Here are the steps to reproduce this issue.

1. Connect to the "postgres" database and execute this command to create a user.

CREATE ROLE mydbusr WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'password';

2. Then execute this command to create a database.

CREATE DATABASE "MyDb"
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

3. Connect to the "MyDb" database and execute this command to create a schema.

CREATE SCHEMA mydbusr AUTHORIZATION mydbusr;

4. Create a class library project and add the EF Core packages.

dotnet new classlib -o EfCoreBug
cd EfCoreBug
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 8.0.0
dotnet add package Microsoft.EntityFrameworkCore.Design --version 8.0.0

5. Copy/Paste this code into the class library project.

using Microsoft.EntityFrameworkCore;

namespace EfCoreBug;

public class MyContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseNpgsql("Host=localhost;Username=mydbusr;Password=password;Database=MyDb");
}

public class MyEntity
{
    public int Id { get; set; }
}

6. Create the Entity Framework migration and update the database.

dotnet ef migrations add InitialCreate
dotnet ef database update

7. Change the model.

using Microsoft.EntityFrameworkCore;

namespace EfCoreBug;

public class MyContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseNpgsql("Host=localhost;Username=mydbusr;Password=password;Database=MyDb");
}

public class MyEntity
{
    public int Id { get; set; }
+    public string? NewColumn { get; set; }
}

8. Create a new migration.

dotnet ef migrations add AddNewColumn

9. Try to apply the new migration.

dotnet ef database update

You should get the following error output.

Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);
Npgsql.PostgresException (0x80004005): 42P07: relation "__EFMigrationsHistory" already exists
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlMigrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42P07
    MessageText: relation "__EFMigrationsHistory" already exists
    File: heap.c
    Line: 1146
    Routine: heap_create_with_catalog
42P07: relation "__EFMigrationsHistory" already exists

[mcbainuk12]

As a temporary workaround you can explicitly set the MigrationHistoryTable schema. We do something like this:

    var connectionStringBuilder = new NpgsqlConnectionStringBuilder(connectionString);
    var searchPaths = connectionStringBuilder.SearchPath?.Split(',');

    options.UseNpgsql(connectionString, o =>
        {
            configure(o);

            if (searchPaths is {Length: > 0})
            {
                var mainSchema = searchPaths[0];
                o.MigrationsHistoryTable(HistoryRepository.DefaultTableName, mainSchema);
            }
        })

It's not very elegant, but it works.

Thanks, just suffered this issue and added this code snippet to get around the issue. Only affects my databases where public isn't the main schema.

[roji]

Everyone, please don't post +1 comments, upvote (👍) the top post instead.

[anhhtca]

configure(o);

Thanks ! It's works !

But what is function configure ?

[ArneRanvik]

Will this be fixed in a future release?

[happy-saturn]

Version 8.0.4 also have this problem

[roji]

tl;dr this should be fixed for 9.0.0-rc.2 - it would be great to have people's help to confirm on the daily builds (9.0.0-rc.2-ci.20240923T214327 and above)!

I can see that this would happen in cases where you have SearchPath set to some non-default schema foo:

• The provider has logic to detect whether the table already exists. Fix migration history table lookup #2788 indeed changed this: before, if you didn't define a schema via the MigrationsHistoryTable() API (like in this workaround), the logic checked whether any table named __EFMigrationsHistory was present in the database; that's clearly a bug, so the fallback to public was added.
• On the other hand, if the check determines that the table doesn't exist, then we try to create it; at that point, if no schema is defined MigrationsHistoryTable(), EF generates CREATE TABLE "_EFMigrationsHistory" without a schema, which default to whatever your search_path is set to.

In other words, the check whether the table exists and the creation of the table look at different schemas, which is the source of this bug. If anyone is running into issues without settings SearchPath, I'd like to know about it because there may be something else going on (@bassem-mf your repro above didn't set the search path, and indeed doesn't cause an error for me).

In #3275 (for 9.0), I already made a change (for unrelated reasons) to no longer check for the existence of the table before creating it, but rather to attempt to create it (with CREATE TABLE IF EXISTS) and to catch and ignore exceptions in case it already does (since CREATE TABLE IF EXISTS isn't concurrency safe). This still leaves GetAppliedMigrations, which we can override and reimplement in the same way.

Submitted #3294 to do this, for 9.0.0-rc.2. In the meantime, if someone wants to give the daily build a try (9.0.0-rc.2-ci.20240923T214327 and above), that would be great.

[ArneRanvik]

tl;dr this should be fixed for 9.0.0-rc.2 - it would be great to have people's help to confirm on the daily builds (9.0.0-rc.2-ci.20240923T214327 and above)!

I can see that this would happen in cases where you have SearchPath set to some non-default schema foo:

• The provider has logic to detect whether the table already exists. Fix migration history table lookup #2788 indeed changed this: before, if you didn't define a schema via the MigrationsHistoryTable() API (like in this workaround), the logic checked whether any table named __EFMigrationsHistory was present in the database; that's clearly a bug, so the fallback to public was added.
• On the other hand, if the check determines that the table doesn't exist, then we try to create it; at that point, if no schema is defined MigrationsHistoryTable(), EF generates CREATE TABLE "_EFMigrationsHistory" without a schema, which default to whatever your search_path is set to.

In other words, the check whether the table exists and the creation of the table look at different schemas, which is the source of this bug. If anyone is running into issues without settings SearchPath, I'd like to know about it because there may be something else going on (@bassem-mf your repro above didn't set the search path, and indeed doesn't cause an error for me).

In #3275 (for 9.0), I already made a change (for unrelated reasons) to no longer check for the existence of the table before creating it, but rather to attempt to create it (with CREATE TABLE IF EXISTS) and to catch and ignore exceptions in case it already does (since CREATE TABLE IF EXISTS isn't concurrency safe). This still leaves GetAppliedMigrations, which we can override and reimplement in the same way.

Submitted #3294 to do this, for 9.0.0-rc.2. In the meantime, if someone wants to give the daily build a try (9.0.0-rc.2-ci.20240923T214327 and above), that would be great.

I want to comment that use "Search Path" parameter isn't always possible, for example if you are making a query to a Postgres database through connection pooler PgBouncer, PgBouncer doesn't support that parameter in the connection string.

I have an application where search path isn't used. But the bug appeared since the database context uses schema "data".

[roji]

I want to comment that use "Search Path" parameter isn't always possible, for example if you are making a query to a Postgres database through connection pooler PgBouncer, PgBouncer doesn't support that parameter in the connection string.

Nothing in the provider requires you to use "Search Path", AFAICT.

I have an application where search path isn't used. But the bug appeared since the database context uses schema "data".

Then I'm going to need to see a repro. The fact that the context is configured to use a non-default schema shouldn't in iteslf be problematic in any way.

[bassem-mf]

Hi @roji ! I tested 9.0.0-rc.2-ci.20240923T214327 and the issue seems to be fixed. Thank you so much for that!

I was able to use the dotnet-ef tool to apply the first and second migrations. The only thing is that I got a red error in the output when I applied the first "InitialCreate" migration which was a bit alarming. But the command executed successfully and made all the necessary changed to the database.

[bassem-mf]

For anyone who is interested in testing this daily build, here is how you do it.

Add a file named "NuGet.config" to the root of the project or solution. Make sure you get the file name capitalization right or it will not work. Paste the following into the file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <packageSources>
        <add key="dotnet9" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet9/nuget/v3/index.json" />
        <add key="dotnet-eng" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet-eng/nuget/v3/index.json" />
        <add key="npgsql-vnext" value="https://www.myget.org/F/npgsql-vnext/api/v3/index.json" />
        <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
    </packageSources>
</configuration>

Run the following command to get the specific build of the "Npgsql.EntityFrameworkCore.PostgreSQL" package.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 9.0.0-rc.2-ci.20240923T214327

If you need to use "dotnet-ef" tool to create migrations, run the following command to get the latest release candidate of the Microsoft.EntityFrameworkCore.Design package.

dotnet add package Microsoft.EntityFrameworkCore.Design --version 9.0.0-rc.1.24451.1

Run the following command to update the dotnet-ef tool to the latest release candidate version.

dotnet tool update -g dotnet-ef --version 9.0.0-rc.1.24451.1 --add-source https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet9/nuget/v3/index.json

After you are done testing, downgrade the dotnet-ef tool to the latest stable version.

dotnet tool uninstall dotnet-ef -g
dotnet tool install dotnet-ef -g

[roji]

@bassem-mf thanks testing and confirming!

The only thing is that I got a red error in the output when I applied the first "InitialCreate" migration which was a bit alarming.

Yes, that's indeed expected, since the provider basically switched away from "let's check first if the table is there" to "try to use the table and identify errors in case it's not there". I'll try to take a look at removing this, but in the worst case it's a one-time error log, which isn't the end of the world.

[roji]

I'll try to take a look at removing this, but in the worst case it's a one-time error log, which isn't the end of the world.

Yeah, that unfortunately doesn't look like it's going to be feasible...

[vikasillumina]

@roji We are seeing this same issue as part of .NET core upgrade from 6 to 8. Do you have any suggestions? I am working on creating a new git issue and will be referring this issue.

[roji]

@vikasillumina see the milestone and this comment above - this has been fixed for 9.