Rock Update 16.7 fails and does not recover if the "Default Check-in Kiosk Ads" Content Channel has been deleted before update is initialized

[KennySnow-BEMA]

Description

When updating Rock to v16.7, if the "Default Check-in Kiosk Ads" Content Channel has been deleted the update will fail and Rock will not reload/start. User is presented with an IIS Application Error page.

Actual Behavior

When updating to 16.7, if the "Default Check-in Kiosk Ads" Content Channel (GUID "a57bdbcd-fa77-4a6e-967d-1c5ace962587", created in 16.5) is missing, Rock will fail to update and will not start/reload correctly.

After the update attempt Rock Initialization will loop with Initialization Errors:

Application Starting
Checking for EntityFramework Migrations
Application Ended: InitializationError (Process ID: 5832)

A Rock Exception is created in RockExceptions.csv:
System.Data.SqlClient.SqlException,"Cannot insert the value NULL into column 'ContentChannelId', table 'ncbc-rock-dev.dbo.ContentChannelItem'; column does not allow nulls. INSERT fails.
due to the migration script failing: Rock.Migrations/Migrations/Version 16.0/Version 1.16.7/202410101546296_AddDefaultCheckInAdvertisement.cs.
If the content channel is missing @ContentChannelId is Null in the declaration on line 64.

An ASP.Net Warning Event will also be generated in the Windows Application Log noting the failed insert attempt.

Workaround:
Stop IIS/Application Pool
Re-create the Content Channel using the script from lines 392-403 of Rock.Migrations/Migrations/Version 16.0/Version 1.16.5/202404112335123_Rollup_20240411.cs
Rest Application Pool/IIS
The update continues and Rock reloads correctly.

Expected Behavior

The Default Check-in Kiosk Ads Content Channel is not protected or marked as critical, and therefore a Rock user can delete it without realizing potential issues.

When updating, if the Content Channel is missing it should probably be recreated in the migration script or an error should be provided on the update screen preventing the user from initiating the v16.7 update until the channel has been recreated with the correct Guid.

Steps to Reproduce

On a Rock instance with v16.5 or 16.6:

• Delete the Content Channel "Default Check-in Kiosk Ads"
• Attempt to update to v16.7
• Rock will fail to load after reboot
• IIS will present an application error page

Note: I did not reproduce the problem on the demo site, as it is v16.9 and I would not want to crash the site if it were the correct version to reproduce.

Issue Confirmation

• Perform a search on the Github Issues to see if your bug or enhancement is already reported.
• Reproduced the problem on a fresh install or on the demo site.

Rock Version

16.7

Client Culture Setting

en-US

[echoots1]

I was pointed to this issue after I found my own fix (Using AI). Below is step by step what I did and if there is a potential issue with my fix please let me know so I can revert to my backup before to much in my environment changes.

Temporarily modify the [ContentChannelItem].[ContentChannelId] column to allow NULL:
ALTER TABLE [dbo].[ContentChannelItem] ALTER COLUMN [ContentChannelId] INT NULL

Perform the Rock update/migration again. Let it complete.

Inspect if any new rows got created with ContentChannelId = NULL. If so, fix them:
SELECT Id, ContentChannelId, * FROM [ContentChannelItem] WHERE ContentChannelId IS NULL

If you’ve identified a specific row in the ContentChannelItem table that is null remove it (for example, the one with an ID of 207), you can delete it with a simple SQL statement like:
DELETE FROM [dbo].[ContentChannelItem] WHERE [Id] = 207;

When SQL says “The index IX_ContentChannelId is dependent on column ContentChannelId,” it means you must remove (or modify) that index before you can alter the column definition back to NOT NULL.

Drop the existing index on ContentChannelId:
DROP INDEX [IX_ContentChannelId] ON [dbo].[ContentChannelItem];

Alter the column to NOT NULL:
ALTER TABLE [dbo].[ContentChannelItem] ALTER COLUMN [ContentChannelId] INT NOT NULL;

Recreate the index:
CREATE NONCLUSTERED INDEX [IX_ContentChannelId] ON [dbo].[ContentChannelItem] ([ContentChannelId]);