Upgrade from 2.6 to 3.3.27 - schema change failed

Legacy Uncategorised
1

When we upgrade from version 2.6 to 3.3.27 Octopus server will not start. Recording to server log i can see that it had issue with apply script “Octopus.Core.UpgradeScripts.Script0026”:

Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0026 - Extend Feed and Account Id columns to fit Name length.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0026 - Extend Feed and Account Id columns to fit Name length.sql’
Script block number: 0; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): The object ‘PK_Account_Id’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id 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, 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)
ClientConnectionId:61c6da41-d43f-4a92-8dcc-54384d65f380
Error Number:5074,State:1,Class:16
ClientConnectionId before routing:b8e2c2fb-5f89-4437-9fac-a612dd93a2c3

ALTER TABLE [dbo].[Account]
ALTER COLUMN Id nvarchar NOT NULL
GO

ALTER TABLE [dbo].Feed
ALTER COLUMN Id nvarchar NOT NULL
GO

Its not allow to change primary key field.
Any suggestions, to upgrade to latest version of actopus?

2

Hi,

Thanks for getting in touch! We have seen this happen once or twice, we think this is caused by security permissions for AWS RDS not allowing altering primary keys.
We are pretty sure running the following attached SQL query should fix the issue and allow you to upgrade.

Let me know how you get on! The other customers with this problem did not confirm if it worked so we would love to hear back. :slight_smile:

Regards,
Daniel

DropConstraint.txt (694 Bytes)

3

The script worked, but now it came with a new error. It look like collation in our azure database is not match what Octopus expect.
Due you also have a fix for this ?

2016-08-23 12:27:02.4881 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScriptsRepeatable.Script0000 - Refresh Views.sql’
2016-08-23 12:27:02.9747 7 INFO SQL exception has occured in script: 'Octopus.Core.UpgradeScriptsRepeatable.Script0000 - Refresh Views.sql’
2016-08-23 12:27:02.9747 7 ERROR Script block number: 0; Block line 85; Message: sys.sp_refreshsqlmodule_internal
2016-08-23 12:27:02.9807 7 ERROR System.Data.SqlClient.SqlException (0x80131904): Cannot resolve collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CS_AS” in UNION ALL operator occurring in SELECT statement column 1.

Regards,
Rasmus

4

Fixed the problem with, export data from Octopus Manager and create a fresh install of octopus deploy. During the installation I let Octopus create a empty database. Last step was to import data again.