2018.7.4 to 2019.3.3 Schema update failing

Legacy Execution
1

When doing the SQL upgrade we get the following error.

Error: Executing .NET Database Server script ‘Octopus.Core.UpgradeScripts.Script0127FixUpDeploymentProcessRelatedDocuments.cs’
Error: You have many projects and releases, this may take some time
Error: Processed 100 of 1854
Error: Processed 200 of 1854
Error: Processed 300 of 1854
Error: Processed 400 of 1854
Error: Processed 500 of 1854
Error: Processed 600 of 1854
Error: Processed 700 of 1854
Error: Processed 800 of 1854
Error: Processed 900 of 1854
Error: Processed 1000 of 1854
Error: Processed 1100 of 1854
Error: Processed 1200 of 1854
Error: Processed 1300 of 1854
Error: Processed 1400 of 1854
Error: Processed 1500 of 1854
Error: Processed 1600 of 1854
Error: Processed 1700 of 1854
Error: Processed 1800 of 1854
Error: Executing .NET Database Server script ‘Octopus.Core.UpgradeScripts.Script0127MigrateStepPackageReferences.cs’
Error: Converting references to deployment-steps (where the step is assumed to be 1:1 with a package) to use DeploymentActionPackage
Error: Versioning Strategy for project ‘Platform.Reporting.FileProcessor’ could not be migrated, and will have to be reconfigured manually. Could not find deployment action with ID a0263aa8-993d-42f9-99f8-b806397ce354
Error: Processed 100 of 129 projects
Error: Versioning Strategy for project ‘Communication AppServer’ could not be migrated, and will have to be reconfigured manually. Could not find deployment action with ID 3a047f44-45c7-4e64-8225-e3293ed36e3a
Error: Versioning Strategy for project ‘Framework.Foundation’ could not be migrated, and will have to be reconfigured manually. Could not find deployment action with ID a0263aa8-993d-42f9-99f8-b806397ce354
Error: Versioning Strategy for project ‘Product.ReimbursementAccounts.BalanceService’ could not be migrated, and will have to be reconfigured manually. Could not find deployment action with ID a0263aa8-993d-42f9-99f8-b806397ce354
Error: Versioning Strategy for project ‘Platform.Communication’ could not be migrated, and will have to be reconfigured manually. Could not find deployment action with ID 9ce8eb5d-688f-4869-90f0-b1654e5e13cf
Error: Executing .NET Database Server script ‘Octopus.Core.UpgradeScripts.Script0128MoveK8SAccountToSubClass.cs’
Error: Executing TSQL Database Server script ‘Octopus.Core.UpgradeScripts.Script0129 - Release and Event DataVersion.sql’
Error: SQLException has occured in statement #0 of this TSQL script: ‘Octopus.Core.UpgradeScripts.Script0129 - Release and Event DataVersion.sql’; Line: 5; Procedure: ; Error Number: 42029; Excerpt: ‘ALTER TABLE dbo.Event ADD DataVersion timestamp’; Message: An internal error happened while generating a new DBTS for database “OctoDB”. Please retry the operation.
Error: The statement has been terminated.
Error: Error occured when executing scripts, transaction will be rolled back
Error: Upgrade failed due to an unexpected exception:
Error: System.Data.SqlClient.SqlException (0x80131904): An internal error happened while generating a new DBTS for database “OctoDB”. Please retry the operation.
Error: The statement has been terminated.
Error: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) Error: at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) Error: at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) Error: at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() Error: at System.Data.SqlClient.SqlDataReader.get_MetaData() Error: at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) Error: at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) Error: at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
Error: at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
Error: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
Error: at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
Error: at Octopus.Core.Initialization.DatabaseUpgrader.OctopusUpgradeScriptExecutor.<>c__DisplayClass7_0.b__0(Func1 commandFactory) Error: at DbUp.Engine.Transactions.SingleTrasactionStrategy.Execute(Action1 action)
Error: at Octopus.Core.Initialization.DatabaseUpgrader.OctopusUpgradeScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
Error: at DbUp.Engine.UpgradeEngine.PerformUpgrade()
Error: ClientConnectionId:aeed8433-4d50-4c5a-a500-1b6e23df1824
Error: Error Number:42029,State:1,Class:16

I understand that it’s a quite outdated version we’re trying to move from. If there’s an upgrade path that’s more incremental I can follow, I’ll take that route, I just haven’t been able to find anything.

3

Hi Ethan,

Thanks for getting in touch and sorry you’ve hit an upgrade issue.

The key error message here is:

Error Number: 42029; Excerpt: ‘ALTER TABLE dbo.Event ADD DataVersion timestamp’; Message: An internal error happened while generating a new DBTS for database “OctoDB”. Please retry the operation.

One of our upgrade scripts needs to add a new column to your Event table, and this query is generating an internal error with your SQL server (likely due to the size of your Event table).

Could you please confirm the size of your Event table by running this query against your Octopus database?

select count(id) from [event]

If you could supply us a backup of your Octopus database, that will help us see how much data you’re working with.

We’ve created a private upload location here. Could you please let us know when you’ve uploaded your database (.bak) file? This will only be viewed by Octopus staff and deleted immediately after we’ve finished reviewing.

In regards to alternative upgrade paths, you can find previous versions of Octopus available here. This upgrade script in question was bundled with Octopus version 2018.9.4, so you’d likely encounter this issue with any version after (and including) 2018.9.4.

Another option, though not ideal, would be to spin up a fresh Octopus instance running 2019.3.3, then migrate data from your older 2018.7.4 instance using our data migrator. But hopefully when we look at your database, we will find an easier alternative.

Cheers
Mark

4

I’ll have to get a greenlight from a few people before I can upload the database, not that I’m concerned, others just might be.

But, I did run the query, and the return was 600,506.

5

Mark, Looking at the Events table, it seems to be fairly non crucial data, only directed associated with the Event Documents table, which simply references what the event was about. It doesn’t seem like anything else foreign keys into these two tables from our looking. Would it be a potential fix to just trim that table size down and call it good? We obviously want to retain deployment logs and so forth, but it doesn’t look like these would be affected.

Another idea: Would it be possible to get our hands on the schema upgrade scripts for that specific version, so we can try to run them ourselves to step over that version?

I asked about uploading our database backup, and was told that since we don’t know exactly what all sensitive data may be stored in there, I would need to get Legal involved to draft up an NDA, so I’d rather pursue other options

6

Hi Ethan,

That’s fair enough.

Regarding the Events table, this relates to your audit trail (Configuration > Audit screen) and is non critical for deployments, but is used for historical purposes so you can see who did what and when. So if you ever get audited, you have a clear history of everything that’s ever occurred. None of these event records should be referenced by anything else in the system, so they are safe to delete (as long as you’re aware that you’re deleting audit history data).

We’d recommend backing up your SQL database before running any of these queries.

To trim the Event table, you can delete in batches, based on date.

It’s useful to firstly run a select count, to see how many records you’d be trimming. E.g. In this query, we’re finding all events that occurred before this given date:

select count([Id]) from [event] where [Occurred] < '2005-05-21 0:00:00.0000000 +00:00'

And just keep adjusting the year in that date until you start seeing older data you can safely remove.

Then just turn this into a delete statement:

delete from [event] where [Occurred] < '2005-05-21 0:00:00.0000000 +00:00'

If, after you downsize that Event table, you’re still having issues with that upgrade script, please let us know and we can try and setup a reproduction based on the number of event records you have. But 600,000 events is not really that big a table.

Could you confirm the specs of the environment hosting your SQL server? An alternative may be to bump the specs on the SQL server temporarily during the upgrade, more memory and CPU may help it get over whatever load it’s getting stuck on.

Let me know how you go.

Cheers
Mark

7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.