search

btcpayserver / btcpayserver-docker

Docker resources for hosting BTCPayServer easily
MIT License
560 stars 345 forks source link

upgrade from 1.11.7 to 1.13.1 for docker btcpayserver fails MigrationStartupTask, container restarts - duplicate key value PK_StoreRoles #901

Closed dme1sc closed 3 weeks ago

dme1sc commented 4 weeks ago

Running a docker upgrade of btcpayserver from 1.11.7 to 1.13.1. Server is ubuntu 22. It looks clean on the upgrade but btcpayserver container restarts every few seconds... MessageText: duplicate key value violates unique constraint "PK_StoreRoles"

I'm not sure how to get the excluded detail data to know what table/field is affected. disk isn't full, at 76%. btcpayserver docker is restarting frequently. Seems related to this issue: https://github.com/btcpayserver/btcpayserver/issues/5233 But this issue was resolved in 1.11.4 (this is 1.11.7 going to 1.13.1).

logs show a duplicate key...

fail: BTCPayServer.Hosting.MigrationStartupTask: Error on the MigrationStartupTask Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_StoreRoles"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage) at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.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.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 Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken) at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlMigrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken) at BTCPayServer.Hosting.MigrationStartupTask.Migrate(CancellationToken cancellationToken) in /source/BTCPayServer/Hosting/MigrationStartupTask.cs:line 859 at BTCPayServer.Hosting.MigrationStartupTask.ExecuteAsync(CancellationToken cancellationToken) in /source/BTCPayServer/Hosting/MigrationStartupTask.cs:line 85 Exception data: Severity: ERROR SqlState: 23505 MessageText: duplicate key value violates unique constraint "PK_StoreRoles" Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information. SchemaName: public TableName: StoreRoles ConstraintName: PK_StoreRoles File: nbtinsert.c Line: 649 Routine: _bt_check_unique Unhandled exception. Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_StoreRoles"

dme1sc commented 3 weeks ago

I found this is a problem with a fresh docker install with a subsequent upgrade. Running the docker install per https://docs.btcpayserver.org/Docker/ gets you to 1.11.7. Then running the upgrade scripts gets you to 1.13.2 but the btcpayserver container restarts constantly with the unique constraint error.

NicolasDorier commented 3 weeks ago

hey @dme1sc can you DM me on chat.btcpayserver.org, I will fix that

NicolasDorier commented 3 weeks ago

So the problem is that you probably have a Employee or Manager role, the offending script is attempting to add those in the default of btcpay, but yours already exists.

Can you confirm?

petzsch commented 3 weeks ago

Trying to reproduce on my site. I've installed a btcpayserver with 1.11.7 and the following roles exist under server settings:

grafik

After running the upgrade to 1.13.2 (switching from this commit hash to master)

I see the following roles:

grafik

The migration scripts did not run into an error for me - can't reproduce.

More info is needed.

--

After creating the Manager and Employee roles manually:

grafik

and running an upgrade I ended up with this error:

fail: BTCPayServer.Hosting.MigrationStartupTask: Error on the MigrationStartupTask
Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_StoreRoles"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
   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.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 Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlMigrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at BTCPayServer.Hosting.MigrationStartupTask.Migrate(CancellationToken cancellationToken) in /source/BTCPayServer/Hosting/MigrationStartupTask.cs:line 859
   at BTCPayServer.Hosting.MigrationStartupTask.ExecuteAsync(CancellationToken cancellationToken) in /source/BTCPayServer/Hosting/MigrationStartupTask.cs:line 85
  Exception data:
    Severity: ERROR
    SqlState: 23505
    MessageText: duplicate key value violates unique constraint "PK_StoreRoles"
    Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
    SchemaName: public
    TableName: StoreRoles
    ConstraintName: PK_StoreRoles
    File: nbtinsert.c
    Line: 649
    Routine: _bt_check_unique
Unhandled exception. Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_StoreRoles"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
   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.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 Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQueryAsync(IEnumerable`1 migrationCommands, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.Internal.NpgsqlMigrator.MigrateAsync(String targetMigration, CancellationToken cancellationToken)
   at BTCPayServer.Hosting.MigrationStartupTask.Migrate(CancellationToken cancellationToken) in /source/BTCPayServer/Hosting/MigrationStartupTask.cs:line 859
   at BTCPayServer.Hosting.MigrationStartupTask.ExecuteAsync(CancellationToken cancellationToken) in /source/BTCPayServer/Hosting/MigrationStartupTask.cs:line 85
   at Microsoft.AspNetCore.Hosting.WebHostExtensions.StartWithTasksAsync(IWebHost webHost, CancellationToken cancellationToken) in /source/BTCPayServer/Extensions/WebHostExtensions.cs:line 19
   at BTCPayServer.Program.Main(String[] args) in /source/BTCPayServer/Program.cs:line 76
   at BTCPayServer.Program.Main(String[] args) in /source/BTCPayServer/Program.cs:line 103
   at BTCPayServer.Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 23505
    MessageText: duplicate key value violates unique constraint "PK_StoreRoles"
    Detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
    SchemaName: public
    TableName: StoreRoles
    ConstraintName: PK_StoreRoles
    File: nbtinsert.c
    Line: 649
    Routine: _bt_check_unique
dme1sc commented 3 weeks ago

I have a manager role present already...

dme1sc commented 3 weeks ago

That is odd that the unique constraint didnt show up for @petzsch as I blew away postgres and btcpay containers and reran the install that got me to 1.11.7. Then attempted to upgrade to 1.13.2 and the btcpay container kept failing to stay up due to the unique constraint. Also, we didnt build Managers role from scratch. This was an existing role in BTCPay.

dme1sc commented 3 weeks ago

I went ahead and deleted the managers from the btcpay UI. Then I went in to postgres and deleted the Manager role. I upgraded to 1.13.2. It has stayed up for several minutes now but is unreachable.
It seems to have lost the docker variables for the ports... btcpayserver/btcpayserver:1.13.2 "/app/docker-entrypo…" 5 minutes ago Up 4 minutes 49392/tcp

dme1sc commented 3 weeks ago

this is what it was prior to the upgrade: btcpayserver/btcpayserver:1.11.7 "/app/docker-entrypo…" 4 days ago Up 6 minutes 0.0.0.0:49392->49392/tcp, :::49392->49392/tcp

dme1sc commented 3 weeks ago

We got the fix. Deleted Managers from the UI. Then deleted role Manager from postgres container.
Proceded with the upgrade. This install is an Apache (not NGINX), had to add the Ports section to the Generated docker-compose file: services: btcpayserver: ports: – “49392:49392″ Then run systemctl restart btcpayserver, it resets the container, and you should now be able to load BTCPayServer!

dme1sc commented 3 weeks ago

thanks to Petzche and Nicolas Dorier for the assist.

NicolasDorier commented 3 weeks ago

This migration error was due to @dme1sc having a Employee or Manager role before migrating. Removing those roles fix it.