Upgrading 3.1.10 to 3.4.0.0. Database upgrade issue

gustavo_sabas · August 25, 2016, 9:58am

Hi,

I’m trying to upgrade 3.1.10 to 3.4.0.0… As expected, when server starts database migrations start… and failed for me.
I see 2 errors in Logs:
First, Script0046ConvertHealthCheckStatus.cs fails due to “Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: ‘Newtonsoft.Json.Linq.JValue’ does not contain a definition for ‘Status’”. Apparently, migrations don-t consider it blocker, because migrations continue.

But then Script0058 - SetDeployedByToNonNullable.sql fails and does stop the execution. The error message is: System.Data.SqlClient.SqlException (0x80131904): The index ‘nci_wi_Deployment_42B5A062-E02E-46A6-B583-65D9109C65CB’ is dependent on column ‘DeployedBy’.

Attached the log file.

Thanks.

OctopusServer.txt (91 KB)

James · August 25, 2016, 7:24pm

We are experiencing the same problem. The upgrade to 3.4.1 FAILS. The event log contains the following error:

2016-08-25 21:18:20.4500 7 FATAL Database upgrade failed: The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0058 - SetDeployedByToNonNullable.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0058 - SetDeployedByToNonNullable.sql’
Script block number: 1; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:686981b3-07b2-420e-8aff-93c74dae4558
Error Number:5074,State:1,Class:16
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:686981b3-07b2-420e-8aff-93c74dae4558
Error Number:5074,State:1,Class:16

System.Exception: Database upgrade failed: The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0058 - SetDeployedByToNonNullable.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0058 - SetDeployedByToNonNullable.sql’
Script block number: 1; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:686981b3-07b2-420e-8aff-93c74dae4558
Error Number:5074,State:1,Class:16
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:686981b3-07b2-420e-8aff-93c74dae4558
Error Number:5074,State:1,Class:16
—> System.Data.SqlClient.SqlException: The index ‘ixDeployment_Environment’ is dependent on column ‘DeployedBy’.
The index ‘ixDeployment_EnvPrj’ is dependent on column ‘DeployedBy’.
ALTER TABLE ALTER COLUMN DeployedBy failed because one or more objects access this column.
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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
— End of inner exception stack trace —
at Octopus.Core.Initialization.DatabaseUpgrader.RunScripts(IRelationalStore store, IScriptProvider scriptProvider, Boolean runEveryTime) in Y:\Work\refs\tags\3.4.1\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 55
at Octopus.Core.Initialization.DatabaseUpgrader.Upgrade(IRelationalStore store) in Y:\Work\refs\tags\3.4.1\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 27
at Octopus.Core.Initialization.StoreInitializer.Initialize() in Y:\Work\refs\tags\3.4.1\source\Octopus.Core\Initialization\StoreInitializer.cs:line 22
at Octopus.Server.OctopusServerEngine.Start() in Y:\Work\refs\tags\3.4.1\source\Octopus.Server\OctopusServerEngine.cs:line 59
at Octopus.Server.Commands.RunCommand.Start() in Y:\Work\refs\tags\3.4.1\source\Octopus.Server\Commands\RunCommand.cs:line 39
at Octopus.Shared.Startup.AbstractCommand.Octopus.Shared.Startup.ICommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions) in Y:\Work\refs\tags\3.4.1\source\Octopus.Shared\Startup\AbstractCommand.cs:line 57
at Octopus.Shared.Startup.OctopusProgram.Start(ICommandRuntime commandRuntime) in Y:\Work\refs\tags\3.4.1\source\Octopus.Shared\Startup\OctopusProgram.cs:line 221
at Octopus.Shared.Startup.WindowsServiceHost.<>c__DisplayClass1_0.b__0() in Y:\Work\refs\tags\3.4.1\source\Octopus.Shared\Startup\WindowsServiceHost.cs:line 19

henrik · August 25, 2016, 11:12pm

Hi Gustavo and James,

Could you please confirm for me what version of Octopus you are upgrading from?

Thank you,
Henrik

gustavo_sabas_delpino · August 25, 2016, 11:23pm

Hi Henrik

It was 3.1.1.0 to 3.4.0.0 in my case.

henrik · August 26, 2016, 2:10am

Hi Gustavo,

Thanks for the extra info, we’ve raised an issue #2666 to handle the issue in theScript0046ConvertHealthCheckStatus migration script.

Regarding your second error, this is not one of our indexes, could it be possible that this index has been added as a result of running SQL Database Advisor and adding some suggested indexes?

Can I confirm with you if you are running on SQL Azure (from the logs it looks like you are)?

James, the index that your upgrade is failing on also doesn’t look like one of our indexes either, could it also be that this is an index that has been added as a result of running SQL Database Advisor?

Thank you,
Henrik

jcaradocdavies · August 26, 2016, 7:08am

Hi there,

It appears that an overzealous developer attempted to optimize the database as the deployments were taking longer and longer each time.

Removing the indexes resolved the issue.

Thanks.

gustavo_sabas_delpino · August 26, 2016, 9:24am

Hi Henrik.

It did solve my second issue as well. It was Azure’s performance recommendations, it had proposed the index and the recommendation had been accepted.

Thanks!

henrik · August 29, 2016, 2:46am

Hi Gustavo and James,

Great to hear you guys got past your issues upgrading!

We’re discussing within the team how we can prevent this situation so that it doesn’t break our customers upgrades.

Thank you and best regards,
Henrik

jcaradocdavies · August 29, 2016, 7:50am

Hi Henrik,

Thanks for the great service.

The reason we tampered with the database is clear – we’re frustrated at the slowdown as the number of releases grows. It would be great if you could provide general information on how to keep the system operating in a quick and responsive manner so we don’t go breaking things.

Regards,

James Caradoc-Davies

henrik · August 30, 2016, 9:44am

Hi James,

This is one thing we’re discussing within the team to try and come up with a way that we can prevent these types of issues and we’re also trying to figure out how we can get this kind of feedback about performance enhancements from our customers so that we can incorporate them into the core of the application.

Once we have something to share about this, we’ll most likely post it up on our blog.

I hope that helps!

Thank you and warm regards,
Henrik