Issue upgrading Octopus 3.3.19 to 3.4.12

johngeddes_91octopusdeploy · October 18, 2016, 6:41am

Hi. I’ve noticed that there is a similar topic below but there does not seem to be a resolution to it.

We are trying to upgrade our octopus server version from 3.3.19 to 3.4.12.
When attempting the upgrade, it errors on:
System.Data.SqlClient.SqlException (0x80131904): There is already an object named ‘MachinePolicy’ in the database.
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, Boolean describeParameterEncryptionRequest) 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 DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)

This Database is running in Amazon’s RDS and is using sql server engine version 12 (sql server 2014).

How would we go about solving this issue?

Cheers~!

Edit: clarifying 3.4.x to 3.4.12

OctopusServerUpgradeError (50 KB)

Shane_Gill · October 18, 2016, 6:53am

Hi,

Thanks for getting in touch.

Would you mind running the following query on your Octopus database and sending the result:

SELECT * FROM [dbo].[SchemaVersions];

Is there a table called MachinePolicy in your Octopus database? The database upgrade script that is supposed to create that table seems to think it already exists. If so, you could try deleting the table and starting Octopus again.

Cheers,
Shane

system · October 18, 2016, 7:10am

Hi Shane.

There is no table called MachinePolicy in the database. There is a table called Machine.

This is the result of the SchemaVersions select:

Id ScriptName Applied
1 Octopus.Core.UpgradeScripts.Script0001 - Initial schema.sql 2016-04-12 04:59:42.770
2 Octopus.Core.UpgradeScripts.Script0002 - Views.sql 2016-04-12 04:59:43.123
3 Octopus.Core.UpgradeScripts.Script0003 - Initial indexes.sql 2016-04-12 04:59:43.140
4 Octopus.Core.UpgradeScripts.Script0004 - Database options.sql 2016-04-12 04:59:46.473
5 Octopus.Core.UpgradeScripts.Script0005 - Key allocation.sql 2016-04-12 04:59:46.603
6 Octopus.Core.UpgradeScripts.Script0006 - DeploymentSummary.sql 2016-04-12 04:59:46.613
7 Octopus.Core.UpgradeScripts.Script0006 - Increase MaxLength of NuGetPackage PackageId.sql 2016-04-12 04:59:50.190
8 Octopus.Core.UpgradeScripts.Script0008 - Lengthen Ids in Releases.sql 2016-04-12 04:59:51.877
9 Octopus.Core.UpgradeScripts.Script0009 - Lengthen Ids in DeploymentProcesses.sql 2016-04-12 04:59:51.903
10 Octopus.Core.UpgradeScripts.Script0010 - Lengthen Ids in VariableSets.sql 2016-04-12 04:59:51.923
11 Octopus.Core.UpgradeScripts.Script0011 - Fix NuGet package procedure.sql 2016-04-12 04:59:51.933
12 Octopus.Core.UpgradeScripts.Script0012 - Increase ServerTask Description to Max.sql 2016-04-12 04:59:52.140
13 Octopus.Core.UpgradeScripts.Script0013 - Fix Release_WithDeploymentProcess view.sql 2016-04-12 04:59:52.147
14 Octopus.Core.UpgradeScripts.Script0014 - Drop ActivityLog.sql 2016-04-12 04:59:52.163
15 Octopus.Core.UpgradeScripts.Script0015 - Dashboard View.sql 2016-04-12 04:59:52.223
16 Octopus.Core.UpgradeScripts.Script0016 - Add DeployedBy column.sql 2016-04-12 04:59:53.647
17 Octopus.Core.UpgradeScripts.Script0016 - Event join table.sql 2016-04-12 04:59:53.793
18 Octopus.Core.UpgradeScripts.Script0017 - Add DeploymentHistory table.sql 2016-04-12 04:59:54.227
19 Octopus.Core.UpgradeScripts.Script0018 - Add MaxConcurrentTask to OctopusServerNode.sql 2016-04-12 04:59:54.257
20 Octopus.Core.UpgradeScripts.Script0019 - Add IsInMaintenanceMode to OctopusServerNode.sql 2016-04-12 04:59:54.260
21 Octopus.Core.UpgradeScripts.Script0020 - Create mutex table.sql 2016-04-12 04:59:54.270
22 Octopus.Core.UpgradeScripts.Script0021 - Create Channels.sql 2016-04-12 04:59:54.390
23 Octopus.Core.UpgradeScripts.Script0022 - Add channel id to IdsInUse.sql 2016-04-12 04:59:54.407
24 Octopus.Core.UpgradeScripts.Script0024 - Created Release_LatestByProjectChannel view.sql 2016-04-12 04:59:54.457
25 Octopus.Core.UpgradeScripts.Script0025 - Updated Release_WithDeploymentProcess view to include ChannelId.sql 2016-04-12 04:59:54.460
26 Octopus.Core.UpgradeScripts.Script0026 - Extend Feed and Account Id columns to fit Name length.sql 2016-04-12 04:59:54.637
27 Octopus.Core.UpgradeScripts.Script0027 - Dashboard do not show canceled tasks that didnt start.sql 2016-04-12 04:59:54.660
28 Octopus.Core.UpgradeScripts.Script0028 - Lengthen VariableSetId references.sql 2016-04-12 04:59:54.667
29 Octopus.Core.UpgradeScripts.Script0029 - Add RelatedDocumentIds column.sql 2016-04-12 04:59:54.677
30 Octopus.Core.UpgradeScripts.Script0030PopulateVariableSetRelatedDocumentIds.cs 2016-04-12 04:59:54.680
31 Octopus.Core.UpgradeScripts.Script0031PopulateDeploymentProcessRelatedDocumentIds.cs 2016-04-12 04:59:54.683
32 Octopus.Core.UpgradeScripts.Script0032 - Update Release_WithDeploymentProcess to include RelatedDocumentIds.sql 2016-04-12 04:59:54.690
33 Octopus.Core.UpgradeScripts.Script0033 - MoveDeploymentStateToOctopusCoreNamespace.sql 2016-04-12 04:59:54.703
34 Octopus.Core.UpgradeScripts.Script0033UpdatePackagesWithNullFileExtensionToNupkg.cs 2016-04-12 04:59:54.710
35 Octopus.Core.UpgradeScripts.Script0034ConvertServerScriptSteps.cs 2016-04-12 04:59:54.717
36 Octopus.Core.UpgradeScripts.Script0035DefaultAzureUseChecksum.cs 2016-04-12 04:59:54.720
37 Octopus.Core.UpgradeScripts.Script0036- FixCasingOfUseChecksum.sql 2016-04-12 04:59:54.723
38 Octopus.Core.UpgradeScripts.Script0037 - Remove RunOnServer from ScriptTemplates.sql 2016-04-12 04:59:54.740
39 Octopus.Core.UpgradeScripts.Script0038ConvertStepActionChannelRuleLink.cs 2016-06-03 00:44:02.820
40 Octopus.Core.UpgradeScripts.Script0039RemoveScriptStepPropertiesThatDoNotApplyToTheScriptSource.cs 2016-06-03 00:44:02.890
41 Octopus.Core.UpgradeScripts.Script0040RemoveTemplateScriptStepPropertiesThatDoNotApplyToTheScriptSource.cs 2016-06-03 00:44:02.917
42 Octopus.Core.UpgradeScripts.Script0041FixOrDeleteChannelReferencesForVariablesAndReleaseCreationOptions.cs 2016-06-03 00:44:03.193
43 Octopus.Core.UpgradeScripts.Script0042 - Dashboard view potential slowness fix.sql 2016-06-23 17:08:07.917
44 Octopus.Core.UpgradeScripts.Script0043 - Index in EventRelatedDocument.sql 2016-06-23 17:08:07.953
45 Octopus.Core.UpgradeScripts.Script0044 - FixMutexIdLength.sql 2016-06-23 17:08:08.063

Cheers~!

Shane_Gill · October 19, 2016, 2:11am

Hi,

I’ve set up an AWS RDS instance with SQL engine version 12 and done an upgrade from 3.3.19 to 3.4.12. It looks like our upgrade scripts work, at least on a brand new instance. We’ve had similar issues in the past and another person is failing at the same point as you, all on AWS RDS.

The script we are trying to run is:

CREATE TABLE dbo.MachinePolicy (
	Id NVARCHAR(50) NOT NULL CONSTRAINT PK_MachinePolicy_Id PRIMARY KEY,
	[Name] NVARCHAR(200) NOT NULL,
	[IsDefault] BIT NOT NULL DEFAULT 0,
	JSON NVARCHAR(MAX) NOT NULL
);
GO

Would you mind trying to run that query on your database to see if it works outside of the context Octopus is trying to run it in? You should end up with the MachinePolicy table.

Cheers,
Shane

system · October 19, 2016, 2:42am

The create table script ran fine and I can see the table in the database (the machinepolicy table).

Maybe a bit more background context, this octopus instance was moved from one account in AWS to another, so the place it’s living is NOT the place it was originally installed as set up. The migration seemed to work just fine from what we can tell, and have been using it for a few months now without too much trouble.

I can supply a database backup (~10Mb) over direct message if need be.

Cheers~!

Shane_Gill · October 19, 2016, 3:42am

Hi,

I wonder if there is a permissions issue creating new tables with the account that the Octopus Service is connecting to your database as. Are you able to check the connection string in C:\Octopus\Octopus.Server.config on your Octopus Server box and verify that the user has full access to the Octopus database?

You can delete the MachinePolicy table that you created, that will cause the upgrade to fail.

Cheers,
Shane

system · October 20, 2016, 5:28am

Hi Shane.

The user in the Octopus Server Config has db_owner for the Octopus Database. The same user is able to create the table manually.

Cheers~!

Shane_Gill · October 24, 2016, 12:18am

Hi,

I’ve asked around and we are stumped what could be causing this.

There is someone else experiencing a similar issue and for them the database update for 3.4 seems to have happened but wasn’t recorded in the database migration table.

You didn’t restore a previous database when the 3.4 update failed? Would you mind running this one on your Octopus Database and sending the result:

SELECT * FROM sysobjects WHERE xtype='U';

Thanks
Shane

system · October 24, 2016, 1:04am

We have restored a backup.

I’ll see if we can line up another go and if it fails, we’ll run that query.

Cheers~!

Shane_Gill · October 25, 2016, 2:00am

HI,

If you restored from a backup that would explain why the MachinePolicy table no longer exists.

I have a feeling the first run of the upgrade is failing silently. When you give it another go would you mind sending along your Octopus Server logs as well?

Thanks
Shane

system · December 2, 2016, 12:54am

Hi All.

Just to wrap this up, we couldn’t work out why it kept failing and simply created a brand new 4.5.2 server and copy pasted projects and variables over.