Upgrade from 3.1.7 to 3.2.2.2 fails with AWS RDS database

hal_smithstevens · 9 February 2016 00:44

I have a great set up with Octopus Deploy 3.1.7 in my Amazon VPC which uses an RDS MS SQL Express instance for its database. The MS SQL instance is SQL Server Express version 11.00.5058.0.v1.

So I put the Octopus Server into maintenance mode and run the 3.2.2.2 installer…

This is what comes out in the logs:

2016-02-09 00:41:17.6561 27 INFO System dashboard loaded in: 121.4809ms 2016-02-09 00:41:56.2488 26 INFO Stopping the Windows Service 2016-02-09 00:41:56.2488 26 INFO The Octopus server is shutting down... 2016-02-09 00:41:56.2488 26 INFO Shutting down the embedded web server 2016-02-09 00:41:56.3740 26 INFO listen://[::]:10943/ 26 Listener stopped 2016-02-09 00:41:56.3740 26 INFO The Windows Service has stopped 2016-02-09 00:42:03.6176 7 INFO Browse your Octopus server at: http://localhost:80/ 2016-02-09 00:42:03.6176 7 INFO The Octopus server is starting: Initializing database and performing migrations... 2016-02-09 00:42:04.4936 7 INFO Beginning database upgrade 2016-02-09 00:42:04.4936 7 INFO Fetching list of already executed scripts. 2016-02-09 00:42:04.8036 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0021 - Create Channels.sql' 2016-02-09 00:42:04.8556 7 INFO SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0021 - Create Channels.sql' 2016-02-09 00:42:04.8556 7 ERROR Script block number: 0; Block line 1; Message: 2016-02-09 00:42:04.8556 7 ERROR System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Channel' in the database. 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, Boolean describeParameterEncryptionRequest) 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 DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.<Execute>b__4(Func``1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary``2 variables) ClientConnectionId:7bbc3e67-1ec6-4225-af34-6e85fc91d5e0 Error Number:2714,State:6,Class:16 2016-02-09 00:42:04.8556 7 ERROR Upgrade failed due to an unexpected exception: System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'Channel' in the database. 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, Boolean describeParameterEncryptionRequest) 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 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:7bbc3e67-1ec6-4225-af34-6e85fc91d5e0 Error Number:2714,State:6,Class:16 2016-02-09 00:42:04.8656 7 FATAL Database upgrade failed: There is already an object named 'Channel' in the database. Database upgrade logs: Beginning database upgrade Fetching list of already executed scripts. Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0021 - Create Channels.sql' SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0021 - Create Channels.sql'

Michael_Noonan · 11 February 2016 06:18

Hi Hal,

Thanks for getting in touch! It looks like there must already be a table called Channel in that database. Can I confirm a couple of things with you:

Did you let Octopus create the database when you installed Octopus?
Are you running Octopus in the same database as another application? (This isn’t supported)
When you installed Octopus 3.2, did it fail to start the first time? You should be able to search through your Octopus Server Logs and see.

The script we use to create that table is:

create table dbo.Channel (
	Id nvarchar(50) not null constraint PK_Channel_Id primary key,
	[Name] NVARCHAR(200) NOT NULL, 
	ProjectId nvarchar(50) not null,
	LifecycleId nvarchar(50) NULL,
	JSON nvarchar(MAX) not null
);
GO

CREATE INDEX IX_Channel_ProjectId
	ON dbo.Channel (ProjectId)
GO

ALTER TABLE Channel 
	ADD CONSTRAINT [UQ_ChannelUniqueNamePerProject] UNIQUE([Name], [ProjectId])
GO

Is there a table in your database that looks like that?

Could you also confirm if there are any Channels in that table, and if so, what are they?

Hope that helps!
Mike

hal_smithstevens · 17 February 2016 20:03

Hi Mike,

I see, yes the MSSQL instance is shared with a teamcity db… That is the cause of the issue then is it? I rolled the db back to the 3.1.7 state and checked to confirm that the new channel table wasn’t there but it still failed when I then tried to upgrade to 3.2.

3.2 Failed to start at all after the failed db migration.

Thanks,

Hal

Michael_Noonan · 17 February 2016 23:58

Hi Hal,

Thanks for getting back to me. You can use the same SQL Server, but we only support Octopus running in its own Database within that Server. The reason for this is that we automatically maintain our own schema, and if there are multiple applications running in the same Database there could be collisions on table names, like in this instance.

Could you confirm if this is the case? Is Octopus and TeamCity running inside the exact same Database?

I would like to help you keep moving through this upgrade.

Hope that helps.
Mike

hal_smithstevens · 18 February 2016 18:05

Hi Mike,

Thanks, so I have teamcity and octoups databases running on the same MSSQL instance. They don’t share a database. Sorry I was unclear as to exactly what you meant.

I cant look through the logs because I did a reinstall from scratch and rebuilt my projects and environments on the new db instance. I initially attempted to upgrade from 3.1.7 to 3.3 beta and I got the same error. I then rolled back the db and tried upgrading to 3.2.2 and got the same error again. Then I just did an install of 3.3 beta and allowed it to create a new db. That worked. Its weird because I did a straight upgrade from 3.1.7 to 3.3 beta on our local CD pipeline and it worked seamlessly.
Hence my mentioning AWS RDS in the title of this post.

Cheers,

Hal

Michael_Noonan · 19 February 2016 01:43

Hi Hal,

Thanks for getting back to me. From what it sounds like you’re up and running again now? If that’s the case, and you don’t have the previous server logs, there’s not much we can do to investigate the issue further.

I’m really saddened to hear you had to recreate your projects, but I’m glad to hear you are progressing now.

Please don’t hesitate to reach out in the future.

Happy Deployments!
Mike

hal_smithstevens · 23 February 2016 03:08

Yeah,

No worries,

Thanks Mike!