OrchardCore: Updating from OrchardCore.Application.Cms.Core.Targets 1.2.0 1.3.0 throws migration errors

Describe the bug

When updating OrchardCore.Application.Cms.Core.Targets from v1.2.0 to v1.3.0 and running the application we are met with the following error logged:

Npgsql.PostgresException (0x80004005): 23503: update or delete on table "Document" violates foreign key constraint "fk_openidappbyrolenameindex_document_documentid" on table "OpenIdAppByRoleNameIndex_Document"

DETAIL: Key (Id)=(1053) is still referenced from table "OpenIdAppByRoleNameIndex_Document".
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at YesSql.Commands.CreateIndexCommand.<AddToBatch>b__6_0(DbDataReader dr)
   at YesSql.Commands.BatchCommand.ExecuteAsync(DbConnection connection, DbTransaction transaction, ISqlDialect dialect, ILogger logger)
   at YesSql.Session.FlushAsync()
   at YesSql.Session.FlushAsync()
   at OrchardCore.OpenId.YesSql.Migrations.OpenIdMigrations.UpdateFrom7Async()
   at OrchardCore.Data.Migration.DataMigrationManager.UpdateAsync(String featureId)
  Exception data:
    Severity: ERROR
    SqlState: 23503
    MessageText: update or delete on table "Document" violates foreign key constraint "fk_openidappbyrolenameindex_document_documentid" on table "OpenIdAppByRoleNameIndex_Document"
    Detail: Key (Id)=(1053) is still referenced from table "OpenIdAppByRoleNameIndex_Document".
    SchemaName: public
    TableName: OpenIdAppByRoleNameIndex_Document
    ConstraintName: fk_openidappbyrolenameindex_document_documentid
    File: ri_triggers.c
    Line: 2541
    Routine: ri_ReportViolation

The application appears to function, but I never like to just ignore errors. Are there any other steps required when upgrading apart from bumping the Nuget package?

To Reproduce

Steps to reproduce the behavior:

Update Nuget package
Run application
Peruse logs

Expected behavior

Database logs should be empty

About this issue

Original URL
State: open
Created 2 years ago
Comments: 24 (13 by maintainers)

Most upvoted comments

@mwpowellhtx I explained how to do it manually and kindly took my own free time to do so. Normally this migration will work. Of course, this is an open-source project so everyone is welcome to contribute. I did not let @jameswoodley without answers at least. I’m currently working on also other important things.

That or perhaps OrchardCore is not a good fit for us after all.

This is your call, not everyone wants to dig in the code or fix issues when they happen. Though, we try to support each others here. If there is an urgency then try to reach out to people that can do it quickly and don’t forget to give sponsorship to them. We do this for free most of the time.

If you read again what I said is that he needs to clean the database manually with some SQL Queries so that his Index Constraint doesn’t block him when he tries to drop it. That doesn’t mean to restart from scratch. And, I can’t do this for him either. I would need to have his database and make it work. So that’s a maintenance task that requires some SQL skills.

Also, about being productive with this YESTERDAY that is not up to me. Of course, there is a learning curve in Orchard Core like with any other CMS and/or framework out there.

Your comment looked a little cynical to me, sorry if we did not meet your standards yet. Issues happen in every complex systems and this is not how this will get fixed faster.

Skrypt on Mar 12, 2022

I’m looking at the issue.

Skrypt on Mar 11, 2022

Okay makes sense, we are using a Nuget packages solution so that bodes well. I’ll fix it manually, but if you need any help reproing I’m happy to be guinea pig!

Thanks for the help today

jameswoodley on Mar 11, 2022