Octopus Server 3.0.24 -> 3.1.1 Database upgrade failed

Legacy Uncategorised
1

Hi,

I am trying to upgrade Octopus Server from 3.0.24 to 3.1.1. The install completes fine, but the service stops immediately after starting, with the following error in the log:

System.Exception: Database upgrade failed: Column names in each table must be unique. Column name 'DeployedBy' in table 'dbo.Deployment' is specified more than once.
Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0016 - Add DeployedBy column.sql'
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0016 - Add DeployedBy column.sql'
Script block number: 0; Block line 1; Message: 
System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'DeployedBy' in table 'dbo.Deployment' is specified more than once.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, TaskCompletionSource`1 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.<Execute>b__4(Func`1 dbCommandFactory)
   at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
ClientConnectionId:-snip-
Error Number:2705,State:4,Class:16
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Column names in each table must be unique. Column name 'DeployedBy' in table 'dbo.Deployment' is specified more than once.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, TaskCompletionSource`1 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.<Execute>b__4(Func`1 dbCommandFactory)
   at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:-snip-
Error Number:2705,State:4,Class:16
 ---> System.Data.SqlClient.SqlException: Column names in each table must be unique. Column name 'DeployedBy' in table 'dbo.Deployment' is specified more than once.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, TaskCompletionSource`1 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.<Execute>b__4(Func`1 dbCommandFactory)
   at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary`2 variables)
   at DbUp.Engine.UpgradeEngine.PerformUpgrade()
   --- End of inner exception stack trace ---
   at Octopus.Core.Initialization.DatabaseUpgrader.Upgrade(IRelationalStore store) in Y:\work\refs\heads\master\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 40
   at Octopus.Core.Initialization.StoreInitializer.Initialize() in Y:\work\refs\heads\master\source\Octopus.Core\Initialization\StoreInitializer.cs:line 22
   at Octopus.Server.OctopusServerEngine.Start() in Y:\work\refs\heads\master\source\Octopus.Server\OctopusServerEngine.cs:line 49
   at Octopus.Server.Commands.RunCommand.Start() in Y:\work\refs\heads\master\source\Octopus.Server\Commands\RunCommand.cs:line 37
   at Octopus.Shared.Startup.AbstractCommand.Octopus.Shared.Startup.ICommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions) in Y:\work\refs\heads\master\source\Octopus.Shared\Startup\AbstractCommand.cs:line 57
   at Octopus.Shared.Startup.WindowsServiceHost.<>c__DisplayClass1_0.<Run>b__0() in Y:\work\refs\heads\master\source\Octopus.Shared\Startup\WindowsServiceHost.cs:line 19
   at Octopus.Shared.Startup.WindowsServiceAdapter.RunService() in Y:\work\refs\heads\master\source\Octopus.Shared\Startup\WindowsServiceAdapter.cs:line 59
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

I reinstalled 3.0.24 on top and the server is working fine for now.

I’ve also tried upgrading from 3.0.24 -> 3.1.0 instead with the same results.

2

Hi Rodrick,

Thanks for getting in touch. Unfortunately you’ve run into a bug in 3.1.0 and 3.1.1 where this upgrade script takes too long to run, times out, and when you attempt to start the Octopus Server again, the script will continue to fail because that column already exists. This should only occur if you have kept a lot of deployment history.

We are going to fix this [GitHub Issue](https://github.com/OctopusDeploy/Issues/issues/2021] but in the meantime you could use these steps to work around the problem and keep going with 3.1.1.

  1. Run this SQL Script (the part that timed out):
update dbo.Deployment set DeployedBy = ev.Username
 from dbo.Deployment d
    inner join dbo.[Event] ev on ev.Category = 'DeploymentQueued' and ev.RelatedDocumentIds LIKE ('%|' + d.Id + '|%')
  1. Then run this script to record that the database upgrade has completed:
INSERT INTO [Octopus].[dbo].[SchemaVersions]
           ([ScriptName]
           ,[Applied])
     VALUES
           ('Octopus.Core.UpgradeScripts.Script0016 - Add DeployedBy column.sql',
           GETUTCDATE())
GO

Hope that helps!
Mike

3

Hi Mike,

That worked, thanks!

4

Hi Rodrick,

Thanks for getting back to me. For my interest how long did the script take to run when you did it manually?

Mike

5

It took about 2 hours. That did seem unusually slow. It could be some issue on our end.

6

Hi Rodrick,

Thanks for getting back to me. That query could be really long-running if you have a lot of history and your retention policy is to keep everything.

Either way you’re up and running which is the best news.

Happy Deployments!
Mike

7

Still an issue on 3.2.n - I had to abort the last upgade. Was hoping this might be resolved by now.

8

Hi Matt,

Thanks for getting in touch. Did you get a timeout running the same script (Octopus.Core.UpgradeScripts.Script0016 - Add DeployedBy column.sql) or a different script?

If it’s the same problem Rodrick encountered you can use the same workaround.

Looking at the code, we allow that particular script to be run multiple times (it is idempotent) and we also increased the command timeout to .WithExecutionTimeout(TimeSpan.FromSeconds(int.MaxValue)).

If none of this has helped, please start a new private thread and send through your server logs so I can see exactly what’s going wrong and get you up and running.

Hope that helps!
Mike