Error when upgrading from 11.22 to 12.8.1

[ajplopez]

I have an older DNN 9.9.1 site on 11.22. When I went to patch from 11.22 to 12.8.1 I got the following installation error:

Anyone run into a 2sxc upgrade from 11.22 that looks like this:

Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException (0x80131904): The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ToSIC_EAV_AttributesInSets_ToSIC_EAV_Attributes". The conflict occurred in database "stagingsite", table "dbo.ToSIC_EAV_Attributes", column 'AttributeID'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query) at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec) ClientConnectionId:e4a10f26-5458-42d8-8cc5-700917bdeae6 Error Number:547,State:0,Class:16 IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ToSIC_EAV_AttributesInSets_ToSIC_EAV_Attributes]') AND parent_object_id = OBJECT_ID(N'[dbo].[ToSIC_EAV_AttributesInSets]')) ALTER TABLE [dbo].[ToSIC_EAV_AttributesInSets] WITH CHECK ADD CONSTRAINT [FK_ToSIC_EAV_AttributesInSets_ToSIC_EAV_Attributes] FOREIGN KEY([AttributeID]) REFERENCES [dbo].[ToSIC_EAV_Attributes] ([AttributeID]) ON DELETE CASCADE

This error has seemed to half install things and now my staging site is hooped because the 2sxc modules don't load.

How can I avoid this error when patching?

[iJungleboy]

This is the first time this error has ever appeared so I don't have any clear idea why this would happen.

The SQL seems to be from the v12.00 upgrade line 718.

This is the code that should run:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ToSIC_EAV_AttributesInSets_ToSIC_EAV_Attributes]') AND parent_object_id = OBJECT_ID(N'[dbo].[ToSIC_EAV_AttributesInSets]'))
ALTER TABLE [dbo].[ToSIC_EAV_AttributesInSets]  WITH CHECK ADD  CONSTRAINT [FK_ToSIC_EAV_AttributesInSets_ToSIC_EAV_Attributes] FOREIGN KEY([AttributeID])
REFERENCES [dbo].[ToSIC_EAV_Attributes] ([AttributeID])
ON DELETE CASCADE
GO

IMHO it tries to add some some safety rules ensuring that values in the ToSIC_EAV_AttributesInSets so that AttributeId must always exist in the ToSIC_EAV_Attributes AttributeID.

Since this is fairly old code and you're the first to hit this problem, I assume something in your data was wrong before that so it refused to do it. See for eg. https://stackoverflow.com/questions/21839309/the-alter-table-statement-conflicted-with-the-foreign-key-constraint.

My recommendations

1. Find the conflicts in the DB (the broken or the original one) by finding all records in ...AttributesInSets having at AttributeId which doesn't exist in the ToSIC_EAV_Attributes. These seem to say that the set (ContentType) has an attribute which doesn't exist in the DB.
2. I'm going to guess you just have 1-2, since this error has never happened. My guess is that if you remove them in the original pre-updated DB, the upgrade should just run as expected.

Note that if you do run into other problems, you should try the following:

1. Get a copy of the original DB
2. Try to run the upgrade scripts from the https://github.com/2sic/2sxc/blob/master/Src/Dnn/ToSic.Sxc.Dnn/DnnPackageBuilder/SqlDataProvider/12.00.00.SqlDataProvider manually by running them in the SQL manager. This is the same things as DNN does, but you will be faster and better able to debug any further problems. Note that you can also run parts of it at a time, like the first 100-200 lines, then the next set etc. Just make sure you always copy a SQL block down to the next GO statement (so you don't copy half statements)

I'll close this task for now, as I assume it's very specific to your case and not relevant to the rest of the users. Feel free to post further findings / questions.

[ajplopez]

Thanks @iJungleboy . This helped me troubleshoot the problem with my developer. Using this insight, we were able to identify 3 of my custom apps that somehow were causing the upgrade to fail. So I deleted those apps entirely (exported them first) and then ran the upgrades to jump to 12, 13, and then 14 and it went smoothly. Then I reimported the 3 apps and it seems fine now.

[iJungleboy]

Perfect, glad this worked 👍🏾