Error Upgrading to 2019.7.1 to 2019.10.0

(jason.hess) #1

Good morning. We have tried to upgrade our Octopus server and have had to rollback because of the following error. Any ideas?

Thanks!
Jason.

2019-10-16 16:40:34.1249 6792 7 INFO Executing TSQL Database Server script ‘Octopus.Core.UpgradeScripts.Script0205 - Add OS details to worker table.sql’
2019-10-16 16:40:34.1419 6792 7 INFO Executing .NET Database Server script ‘Octopus.Core.UpgradeScripts.Script0206CopyOSDetailsFromWorkerHealthCheckJson.cs’
2019-10-16 16:40:34.1589 6792 7 INFO Moving OperatingSystemDetails from health check into Worker
2019-10-16 16:40:34.1719 6792 7 INFO Executing TSQL Database Server script ‘Octopus.Core.UpgradeScripts.Script0207 - Set OS Detail Columns To Unknown When Null.sql’
2019-10-16 16:40:34.2269 6792 7 INFO Executing TSQL Database Server script ‘Octopus.Core.UpgradeScripts.Script0208 - BuildInformation table.sql’
2019-10-16 16:40:34.4329 6792 7 INFO Caution: Changing any part of an object name could break scripts and stored procedures.
2019-10-16 16:40:34.4449 6792 7 INFO Caution: Changing any part of an object name could break scripts and stored procedures.
2019-10-16 16:40:34.4549 6792 7 INFO Caution: Changing any part of an object name could break scripts and stored procedures.
2019-10-16 16:40:34.5319 6792 7 ERROR SQLException has occured in statement #3 of this TSQL script: ‘Octopus.Core.UpgradeScripts.Script0208 - BuildInformation table.sql’; Line: 326; Procedure: sp_rename; Error Number: 15248; Excerpt: ‘’; Message: Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
2019-10-16 16:40:34.5319 6792 7 WARN Error occured when executing scripts, transaction will be rolled back
2019-10-16 16:40:34.7789 6792 7 ERROR Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
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.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) 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) 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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
at Octopus.Core.Initialization.DatabaseUpgrader.OctopusUpgradeScriptExecutor.<>c__DisplayClass7_0.b__0(Func1 commandFactory) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 360 at DbUp.Engine.Transactions.SingleTrasactionStrategy.Execute(Action1 action)
at Octopus.Core.Initialization.DatabaseUpgrader.OctopusUpgradeScriptExecutor.Execute(SqlScript script, IDictionary`2 variables) in C:\buildAgent\work\abb2fbfce959a439\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 291
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:4a5e8ae0-aa62-4952-8410-21b3a6ac4517
Error Number:15248,State:1,Class:11
ClientConnectionId before routing:33cda53d-76c3-44db-ac9f-e1c28a744511
Routing Destination:b8a135f57526.tr194.australiaeast1-a.worker.database.windows.net,11046
2019-10-16 16:40:34.8559 6792 7 FATAL We encountered an error during the schema upgrade. The schema upgrade was stopped and rolled back. Don’t worry, this means we haven’t made any changes to your data, and you should be able to get back up and running quickly.
Here is the summary of the problems we encountered:

SQL Error 15248 - Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
System.Data.SqlClient.SqlException
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.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) 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) 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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
at Octopus.Core.Initialization.DatabaseUpgrader.OctopusUpgradeScriptExecutor.<>c__DisplayClass7_0.b__0(Func1 commandFactory) in DatabaseUpgrader.cs:line 360 at DbUp.Engine.Transactions.SingleTrasactionStrategy.Execute(Action1 action)
at Octopus.Core.Initialization.DatabaseUpgrader.OctopusUpgradeScriptExecutor.Execute(SqlScript script, IDictionary`2 variables) in DatabaseUpgrader.cs:line 291
at DbUp.Engine.UpgradeEngine.PerformUpgrade()

(Shane Gill) #5

Hi,

Thanks for getting in touch. Sorry to see you’ve run into this problem when upgrading Octopus, we’ve created an issue that you can track and have a fix on the way: https://github.com/OctopusDeploy/Issues/issues/5913

We are stumped how this happened, would it be possible to send the task log of a recent System Integrity Check? You can find them on the Tasks page:

(You’ll need to “Include system tasks” in the Advanced Filters).

Cheers,
Shane

(jason.hess) #6

Here’s the latest check:

Task ID: ServerTasks-294907
Related IDs:
Task status: Failed
Task queued: Thursday, 17 October 2019 5:05:55 PM +10:00
Task started: Thursday, 17 October 2019 5:05:55 PM +10:00
Task completed: Thursday, 17 October 2019 5:05:59 PM +10:00
Task duration: 4 seconds
Server version: 2019.7.1+Branch.tags-2019.7.1.Sha.29be066d68f362d7da42fac0e1741e6329e06743
Server node: PRD-OCTOPUS-01

                | Failed: Check System Integrity

17:05:59 Info | Unexpected related document id format… [ Passed ]
17:05:59 Error | Schema… [ Failed ]
17:05:59 Info | Release channel belongs to the appropriate project… [ Passed ]
17:05:59 Info | Orphaned tenanted deployments… [ Passed ]
17:05:59 Info | Orphaned channels… [ Passed ]
17:05:59 Info | Orphaned releases… [ Passed ]
17:05:59 Info | Only one unfrozen deployment process per project… [ Passed ]
17:05:59 Fatal | One or more data errors were found.
|
| Failed: Schema
17:05:59 Error | Unexpected item: IDX dbo.PK_CommandLog CLUSTERED 0
| Unexpected item: IDX dbo.PK_CommandLog ID
| Unexpected item: USER_TABLE dbo.CommandLog.Command nvarchar(-1/0/0) 0
| Unexpected item: USER_TABLE dbo.CommandLog.CommandType nvarchar(120/0/0) 0
| Unexpected item: USER_TABLE dbo.CommandLog.DatabaseName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.EndTime datetime(8/23/3) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ErrorMessage nvarchar(-1/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ErrorNumber int(4/10/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ExtendedInfo xml(-1/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ID int(4/10/0) 0
| Unexpected item: USER_TABLE dbo.CommandLog.IndexName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.IndexType tinyint(1/3/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ObjectName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.ObjectType char(2/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.PartitionNumber int(4/10/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.SchemaName sysname(256/0/0) 1
| Unexpected item: USER_TABLE dbo.CommandLog.StartTime datetime(8/23/3) 0
| Unexpected item: USER_TABLE dbo.CommandLog.StatisticsName sysname(256/0/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.auth_scheme nvarchar(80/0/0) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.client_interface_name nvarchar(64/0/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.connect_time datetime(8/23/3) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.encrypt_option nvarchar(80/0/0) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.host_name nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.login_name nvarchar(256/0/0) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.login_time datetime(8/23/3) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.net_transport nvarchar(80/0/0) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.nt_domain nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.nt_user_name nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.original_login_name nvarchar(256/0/0) 0
| Unexpected item: VIEW dbo.vwConnectedSessions.program_name nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.session_id int(4/10/0) 1
| Unexpected item: VIEW dbo.vwConnectedSessions.status nvarchar(60/0/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.blocking_session_id smallint(2/5/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.command nvarchar(64/0/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.cpu_time int(4/10/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.dbName nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.granted_query_memory int(4/10/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.host_name nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.isolation_level varchar(14/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.last_wait_type nvarchar(120/0/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.logical_reads bigint(8/19/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.ModuleName nvarchar(514/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.nt_user_name nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.open_transaction_count int(4/10/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.Pct real(4/24/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.ProgramName nvarchar(256/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.reads bigint(8/19/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.RequestDuration varchar(46/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.row_count bigint(8/19/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.statement_text nvarchar(-1/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.status nvarchar(60/0/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.ThisSession smallint(2/5/0) 0
| Unexpected item: VIEW dbo.vwCurrentActivity.wait_type nvarchar(120/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.WaitTime varchar(46/0/0) 1
| Unexpected item: VIEW dbo.vwCurrentActivity.writes bigint(8/19/0) 0
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace NONCLUSTERED 0
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace PackageId
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace SpaceId
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace Version
|

(Shane Gill) #7

Thanks Jason,

The missing index is causing the issue:

| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace NONCLUSTERED 0
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace PackageId
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace SpaceId
| Missing item: IDX dbo.IX_PackageMetadataIdAndVersionPerSpace Version

If you re-created it you should be able upgrade. Alternatively a fix will be shipped in the coming days. You could re-create itby running the following SQL on your Octopus Server database:

CREATE NONCLUSTERED INDEX [IX_PackageMetadataIdAndVersionPerSpace] ON [dbo].[PackageMetadata] ([SpaceId], [PackageId], [Version])

Cheers,
Shane