I attempted to upgrade our Octopus server from 3.13.7 to 3.15.8 this afternoon and ran into some issues.
The installer of course went fine it was when it attempted to upgrade the instance that it started to have problems. We were able to resolve the first error because it turned out to be a security check we were running on our SQL Server instance that was the problem. However I then tried to run the database upgrade again and got a new error that we have not been able to resolve.
2017-07-25 16:09:35.5952 5712 1 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0089 - ChangeOctopusServerNodeIdFormat.sql'
2017-07-25 16:09:35.6032 5712 1 INFO SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0089 - ChangeOctopusServerNodeIdFormat.sql'
2017-07-25 16:09:35.6032 5712 1 ERROR Script block number: 0; Block line 1; Message:
2017-07-25 16:09:35.6032 5712 1 ERROR System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'CONSTRAINT'.
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:78e9079f-2594-4f11-9fe1-56d409410386
Error Number:102,State:1,Class:15
2017-07-25 16:09:35.6032 5712 1 ERROR Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'CONSTRAINT'.
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:78e9079f-2594-4f11-9fe1-56d409410386
Error Number:102,State:1,Class:15
2017-07-25 16:09:35.6242 5712 1 FATAL Failed to upgrade the database 'OctopusDeploy' on SQL Server at 'SAVAGE' to the expected schema running as 'OctopusDeploy': Incorrect syntax near 'CONSTRAINT'.
If you know what the problem is and how to fix it, please fix the problem and then run the same command to try again. Otherwise you should contact our support team for help resolving this problem.
My DBA tells me that it’s attempting to run the following though he’s not sure his logging is picking up the full query that is failing.
-- extend id length since we are adding a prefix to name now
DECLARE @ObjectName NVARCHAR(100) SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS WHERE [object_id] = OBJECT_ID('[dbo].[OctopusServerNode]') AND [name] = 'MaxConcurrentTasks'; EXEC('ALTER TABLE [dbo].[OctopusServerNode] DROP CONSTRAINT ' + @ObjectName)
Out of curiosity we went ahead and started up the Octopus server and it does appear to be operating fine and I have been able to execute a couple of deployments with now issue so there is no obvious impact on the application yet that I’ve been able to see. I’ve attached the server log file just in case that might have more information you might find useful. Please let me know if there is anything else I can provide to resolve the problem.
OctopusServer.txt (398 KB)