Rock v16.5 Upgrade Failure, DB cannot be migrated, Rock can't load

[mwidjaja1]

Description

I took a Rock v13.8 DB Database and updated it from 13.8 to 16.4 successfully. When I update to Rock 16.5, the update fails upon first initialization of Rock's Database. I am able to consistently reproduce this error using our Rock v16.4 Code & Database on multiple Virtual Machines.

I am not using any custom themes. I'm using versions of the default Stark & Rock Themes, with some CSS Stylesheet modifications.

The issue seems to be a string that's RockRMS/SQL is trying to convert to a numerical type, which of course doesn't work. The string in question is '35 or 74 or 76 or 81 or 82 or 84'

Actual Behavior

Actual Behavior:

Upon going to Rock, I get this page with the error message pasted below:

[SqlException (0x80131904): Conversion failed when converting the nvarchar value '35 or 74 or 76 or 81 or 82 or 84' to data type int.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +277
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +764
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4620
   System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +1344
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +420
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +391
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +958
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) +490
   System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) +125
   System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) +169
   System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) +124
   System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) +94
   System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) +211
   System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) +462
   System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection) +693
   System.Data.Entity.SqlServer.<>c__DisplayClass2_0.<Execute>b__0() +18
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +234
   System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction) +270
   System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto) +1208
   System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) +513
   System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) +102
   System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) +192
   System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) +205
   System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) +602
   System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +578
   System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +116
   Rock.WebStartup.RockApplicationStartupHelper.MigrateDatabase(Boolean hasPendingEFMigrations) +421
   Rock.WebStartup.RockApplicationStartupHelper.RunApplicationStartup() +453
   Rock.AssemblyInitializer.Initialize() +76

[RockStartupException: Error occurred in RunApplicationStartup:Conversion failed when converting the nvarchar value '35 or 74 or 76 or 81 or 82 or 84' to data type int.]
   RockWeb.Global.Application_Start(Object sender, EventArgs e) +785

[RockStartupException: Error occurred during application startup:Error occurred in RunApplicationStartup:Conversion failed when converting the nvarchar value '35 or 74 or 76 or 81 or 82 or 84' to data type int.]
   RockWeb.Global.Application_Start(Object sender, EventArgs e) +941

[HttpException (0x80004005): Error occurred during application startup:Error occurred in RunApplicationStartup:Conversion failed when converting the nvarchar value '35 or 74 or 76 or 81 or 82 or 84' to data type int.]
   System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +517
   System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +185
   System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +168
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +277
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +369

[HttpException (0x80004005): Error occurred during application startup:Error occurred in RunApplicationStartup:Conversion failed when converting the nvarchar value '35 or 74 or 76 or 81 or 82 or 84' to data type int.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +532
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +111
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +714

Expected Behavior

I expected to... not get a horrifying crash that locks me out of my site.

Steps to Reproduce

• I had a Rock v13.8 database + installation.
• I updated it from v13.8 to v16.4 successfully
• I installed Rock v16.5 and got this error.

Since I did this rodeo before with great failure, in my second attempt, I ran this SQL Query to prove that the string in question is not in my database:

I also searched GitHub and unless I'm missing something, I do not see this text anywhere.

Issue Confirmation

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

Rock Version

16.5

Client Culture Setting

en-US

[chead4]

@mwidjaja1 Hi Matt - Oh no, this shouldn't have happened and is quite concerning. Can you clarify if you experienced the upgrade failure on your production system and are you back up and running? If the upgrade failure wasn't on your production environment, can you provide details on how your dev/test system was set up?

We haven't been able to recreate the issue and we're concerned that a plugin on your system might be causing the issue on start up. Please provide a screenshot of the System Information/Diagnostics tab - focusing specifically on the Plugin Assembly section as shown in the example below.

[mwidjaja1]

Hi @chead4. This was indeed on Production and we reverted back since. We then tried again on our Sandbox/Development to which it crashed again and that error is still live at https://admin.dev.epic.church. I'd be happy to jump on a screen share if there's anything you'd like me to show you.

As to your request, here's our Diagnostics tab on Production. Our Sandbox site would have had a similar setup.

[mwidjaja1]

@chead4 I should also add 9Embers initially setup our Rock Instance. They did install some custom blocks along the way which I screenshotted below.

I have Git Bash installed on both VMs. I used grep to try to find any matching strings like I shared and were unable to find any on both VMs.

We operate purely on Azure. We have two VMs and two SQL databases, one of each for a production and development instance. Nothing particularly fancy beyond what I just shared though. Happy to answer any other questions and/or jump on a screen share if it helps.

[nairdo]

@mwidjaja1 Off topic... For all those plugin blocks that have a "0" (no instances of that block) -- do you know why they are on your system?

[mwidjaja1]

@mwidjaja1 Off topic... For all those plugin blocks that have a "0" (no instances of that block) -- do you know why they are on your system?

This is probably the wrong answer but... fear. Because I was not a volunteer with the Church when these blocks were installed by our former consultants, and thus don't know the 'why', there was always a fear I had that if I were to remove the blocks, it'd break something else in the system and/or be a block we need in the future again. Of course, if there's a theory that these blocks could in fact be causing the crash, that makes this decision a bit easier for me, and I'd be willing to give that a try on our sandbox after I resuscitate it.

[chead4]

@mwidjaja1 Hi Matthew - We need a few more pieces of information from your failed sandbox environment to proceed with our investigation.

Please email the following to colleen@sparkdevnetwork.org

1. Please run this sql and let me know the result
   SELECT TOP 1 * FROM __MigrationHistory ORDER BY MigrationId DESC
2. In the email include, the MigrationLog.csv file found under \App_Data\Logs

[nairdo]

For anyone else watching this issue, we've pinpointed the issue and are working with Matt offline. There was a small data issue on his system that we'll be walking him through to fix (after he tests our fix which should also be able to skip around the data issue during the upgrade). Therefore, we'll be closing this issue now.