Octopus Server 3.0.24 -> 3.1.1 Database upgrade failed

Rodrick_Yu · 24 September 2015 17:31

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.

Michael_Noonan · 25 September 2015 04:36

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.

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 + '|%')

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

Rodrick_Yu · 25 September 2015 21:36

Hi Mike,

That worked, thanks!

Michael_Noonan · 25 September 2015 21:47

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

Rodrick_Yu · 25 September 2015 22:09

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

Michael_Noonan · 25 September 2015 22:15

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

MattLaw · 29 December 2015 10:13

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

Michael_Noonan · 29 December 2015 23:57

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