Upgrade from 3.3.20 to 3.3.21 database upgrade failed

Julian_Boden · July 13, 2016, 1:50pm

Hi,
I’ve just tried to upgrade to 3.3.21 from the previous version and during the database upgrade the service stops.
Database: SQL 2008R2
See below for event log entry

2016-07-13 11:28:08.5475 7 FATAL Database upgrade failed: Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql’
Script block number: 1; Block line 13; Message:
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
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) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:c886be7e-fcc9-41aa-bf90-cad110f8adc7
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
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) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:c886be7e-fcc9-41aa-bf90-cad110f8adc7

System.Exception: Database upgrade failed: Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql’
Script block number: 1; Block line 13; Message:
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
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) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:c886be7e-fcc9-41aa-bf90-cad110f8adc7
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
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) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:c886be7e-fcc9-41aa-bf90-cad110f8adc7
—> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column ‘ChannelId’, table ‘OctopusDb.dbo.Deployment’; column does not allow nulls. UPDATE fails.
The statement has been terminated.
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) 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 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
— End of inner exception stack trace —
at Octopus.Core.Initialization.DatabaseUpgrader.Upgrade(IRelationalStore store) in Y:\Work\refs\tags\3.3.21\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 40
at Octopus.Core.Initialization.StoreInitializer.Initialize() in Y:\Work\refs\tags\3.3.21\source\Octopus.Core\Initialization\StoreInitializer.cs:line 22
at Octopus.Server.OctopusServerEngine.Start() in Y:\Work\refs\tags\3.3.21\source\Octopus.Server\OctopusServerEngine.cs:line 49
at Octopus.Server.Commands.RunCommand.Start() in Y:\Work\refs\tags\3.3.21\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\tags\3.3.21\source\Octopus.Shared\Startup\AbstractCommand.cs:line 57
at Octopus.Shared.Startup.WindowsServiceHost.<>c__DisplayClass1_0.b__0() in Y:\Work\refs\tags\3.3.21\source\Octopus.Shared\Startup\WindowsServiceHost.cs:line 19
at Octopus.Shared.Startup.WindowsServiceAdapter.RunService() in Y:\Work\refs\tags\3.3.21\source\Octopus.Shared\Startup\WindowsServiceAdapter.cs:line 44

fxprogrm · July 13, 2016, 9:25pm

I’ve just tried to upgrade from 3.3.20 to 3.3.21. Ironically one instance on the machine upgraded fine and the other instance failed the same as the above user.

Database: SQL 2014

The 1st instance:
2016-07-13 16:13:35.2056 7 INFO Beginning database upgrade
2016-07-13 16:13:35.2212 7 INFO Fetching list of already executed scripts.
2016-07-13 16:13:35.2368 7 INFO No new scripts need to be executed - completing.
2016-07-13 16:13:35.8305 7 INFO The Octopus server is starting: Starting message distributor…

The 2nd instance:
2016-07-13 16:08:06.6779 7 INFO Fetching list of already executed scripts.
2016-07-13 16:08:06.7149 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql’
2016-07-13 16:08:12.7622 7 INFO SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0045 - MoveDeploymentChannelIdToColumn.sql’
2016-07-13 16:08:12.7672 7 ERROR Script block number: 1; Block line 13; Message:
2016-07-13 16:08:12.8062 7 ERROR System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column ‘ChannelId’, table ‘OD_REDACTED.dbo.Deployment’; column does not allow nulls. UPDATE fails.

Vanessa_Love · July 13, 2016, 11:08pm

Hi Folks,

For anyone that is having this issue with only a single instance or all instances having the same issue, the temporary solution will be to rerun the 3.3.20 MSI as no database changes have gone through and your Octopus instance/s will start again.
The issue appears to be mismatched and missing data. We may require a query to determine why this has happened and I might update the thread if needed.

We are working on updating the script to account for this missing data and should have a fix out today.

If you are in the case of one instance that did upgrade and another that didn’t. We believe that you should be able to use the above method and both instances should work again. The upgraded one won’t attempt the change when the fix comes out. We however are currently testing this is a 100% confirmed viable solution for you.

I will update this thread with information, or questions as we progress through a fix. If I ask for data or information you do not want to share on the forum, email it through to us at support at octopus dot com.

Sorry for the problems, thanks for your patience!
Vanessa

Vanessa_Love · July 13, 2016, 11:14pm

Hi Folks,

After you have done the above to get a working Octopus instance again, I would really appreciate those who can running the following SQL and sending us the results (via email is fine!). This will allow us to properly test the fix to the upgrade script.

gist.github.com

https://gist.github.com/vanessalove/ad2c6b4d4a450d53048b5557189e79bb

check-deployment-channels.sql

select * from dbo.Deployment d LEFT JOIN dbo.Channel c
	ON d.[JSON] LIKE '%"ChannelID":"' + c.Id + '"%'
	WHERE c.Id is null

select * from dbo.Deployment d LEFT JOIN dbo.Release r ON d.ReleaseId = r.Id
WHERE r.ChannelId IS NULL

select * from dbo.Deployment d  cross apply (
select top 1 c.Id as ChannelId from project p inner join channel c on p.Id = c.projectId
 where p.Id = d.ProjectId

This file has been truncated. show original

Please let me know how you get on.
Vanessa

Vanessa_Love · July 13, 2016, 11:23pm

Hi @ fxprogrm,

Sorry but you are not going to be to use the workaround above as you will end up with a different set of corrupted data in the instance that did upgrade successfully. You will have to wait until our fix is out. However sending us the query results from the query I posted on the failed upgrade instance will help us get the fix tested quicker.

Really sorry for the troubles.
Vanessa

fxprogrm · July 14, 2016, 2:23am

Thanks Vanessa…

I have just emailed support with the output of both instances in hopes that it will help determine the issue and a resolution.

Julian_Boden · July 14, 2016, 8:28am

I’ve just emailed support with output requested, I hope this helps.

Mark_Rydstrom · July 14, 2016, 10:05am

Hi folks,

We’ve just released 3.3.22 which contains a fix for this issue. You can get it from our download page. Please take a look and let us know if it works for you.

Sorry again for your problems, and thank you for sending through the extra data we asked for to help us sort this out.

Mark

fxprogrm · July 14, 2016, 2:31pm

Hi All,

I just applied the new release and the 2nd instance that failed on .21 is successful on .22.

2016-07-14 09:29:45.1987 7 INFO The Octopus server is starting: Initializing database and performing migrations…
2016-07-14 09:29:45.4467 7 INFO Beginning database upgrade
2016-07-14 09:29:45.4467 7 INFO Fetching list of already executed scripts.
2016-07-14 09:29:45.4827 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0046 - MoveDeploymentChannelIdToColumnRedux.sql’
2016-07-14 09:29:45.6637 7 INFO Upgrade successful
2016-07-14 09:29:46.2838 7 INFO The Octopus server is starting: Starting message distributor…

Thanks for the quick turnaround.

Julian_Boden · July 14, 2016, 2:52pm

Hi All

All good for me too, 3.3.22 installed no issues.

Thanks for the great support.

Julian