Cannot deploy or create releases since upgrade to 3.3.22 (dead in the water)

We originally noticed this problem (and reported it) as cannot create Octo release from command line (http://help.octopusdeploy.com/discussions/problems/46613-octopus-command-line-failing-to-create-release-since-upgrade-to-3322) but the problem is much more severe. It turns out that we cannot manually create releases or deploy them using the server GUI either.

I’ve attached server information and a log from an attempt to create a release using the command line tool during a TeamCity build. Below is the error from an attempt to deploy an existing release from the server.

Error while executing SQL command: The correlation name ‘ALIAS_AAAAA’ is specified multiple times in a FROM clause.
The command being executed was:
SELECT COUNT() FROM (SELECT * FROM dbo.[Deployment] WHERE (ProjectId in (@projectIds_0)) AND (EnvironmentId in (@environments_0))) ALIAS_AAAAA INNER JOIN (SELECT * FROM dbo.[ServerTask] WHERE (State = @taskState)) ALIAS_AAAAA ON ALIAS_AAAAA.TaskId = ALIAS_AAAAA.Id
System.Exception: Error while executing SQL command: The correlation name ‘ALIAS_AAAAA’ is specified multiple times in a FROM clause.
The command being executed was:
SELECT COUNT(
) FROM (SELECT * FROM dbo.[Deployment] WHERE (ProjectId in (@projectIds_0)) AND (EnvironmentId in (@environments_0))) ALIAS_AAAAA INNER JOIN (SELECT * FROM dbo.[ServerTask] WHERE (State = @taskState)) ALIAS_AAAAA ON ALIAS_AAAAA.TaskId = ALIAS_AAAAA.Id —> System.Data.SqlClient.SqlException: The correlation name ‘ALIAS_AAAAA’ is specified multiple times in a FROM clause.
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.ExecuteScalar()
at Octopus.Core.RelationalStorage.TransientFaultHandling.IDbCommandExtensions.<>c__DisplayClass11_0.b__0() in Y:\Work\refs\tags\3.3.22\source\Octopus.Core\RelationalStorage\TransientFaultHandling\IDbCommandExtensions.cs:line 119
at Octopus.Shared.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) in Y:\Work\refs\tags\3.3.22\source\Octopus.Shared\TransientFaultHandling\RetryPolicy.cs:line 213 at Octopus.Core.RelationalStorage.RelationalTransaction.ExecuteScalar[T](String query, CommandParameters args) in Y:\Work\refs\tags\3.3.22\source\Octopus.Core\RelationalStorage\RelationalTransaction.cs:line 392 --- End of inner exception stack trace --- at Octopus.Core.RelationalStorage.RelationalTransaction.ExecuteScalar[T](String query, CommandParameters args) in Y:\Work\refs\tags\3.3.22\source\Octopus.Core\RelationalStorage\RelationalTransaction.cs:line 400 at Octopus.Core.RelationalStorage.QueryBuilder1.ToList(Int32 skip, Int32 take, Int32& totalResults) in Y:\Work\refs\tags\3.3.22\source\Octopus.Core\RelationalStorage\QueryBuilder.cs:line 121
at Octopus.Server.Web.Api.Actions.DeploymentsQueryResponder.Execute() in Y:\Work\refs\tags\3.3.22\source\Octopus.Server\Web\Api\Actions\DeploymentsQueryResponder.cs:line 64
at Octopus.Server.Web.Infrastructure.Api.Responder`1.Respond(TDescriptor options, NancyContext context) in Y:\Work\refs\tags\3.3.22\source\Octopus.Server\Web\Infrastructure\Api\Responder.cs:line 162
at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
at Octopus.Server.Web.Api.OctopusRestApiModule.<>c__DisplayClass0_0.<.ctor>b__0(Object o) in Y:\Work\refs\tags\3.3.22\source\Octopus.Server\Web\Api\OctopusRestApiModule.cs:line 46
at Nancy.Routing.Route.<>c__DisplayClass4.b__3(Object parameters, CancellationToken context)

OctopusDeploy-636044382025384218.zip (163 KB)

Server_Side_Octopus_Release_8.9.18105.2217_1831_.log.zip (4 KB)

Rebooting the server corrected the issue. Still concerned about understanding why this happened and need some confidence that it won’t occur again.

HI Tom,

I’m very sorry to hear you ran into this problem.

As mentioned by Damian on the thread below, this is an issue we are working on that will be fixed by 3.4. Currently the only workaround is to reboot the Octopus server, which is something we definitely hate to recommend.

http://help.octopusdeploy.com/discussions/problems/46002-octopus-db-error

Once again apologies for the inconveniences.

Regards,
Dalmiro

Any chance this was fixed in 3.3.23. It is occurring surprisingly frequently and has become a problem for the team.

Hi,

I’m very sorry to hear this is bothering you more frequently now. Unfortunately the change is a bit big the include it in a 3.3.x release, reason why we’ll be introducing it in the next major release (3.4).

Once again deep apologies for the inconveniences caused by this issue.

Best regards,
Dalmiro

Was the correction implemented in the 3.4.x? We are running 3.4.3 and are having the same error pop up randomly today.

Hi Michael,

Could you please send through the logs when you received this error? I’ve checked with the team and also looked through the code and the fix was released as part of 3.3.27 so it is very strange that you would be receiving the error in 3.4.3.

Thank you and best regards,
Henrik

we’ve been running 3.3.27 for awhile now and have not seen this problem again.

Logs attached. Rebooting the Octopus sever resolved the issue.

OctopusServer.0.txt (7 MB)

Hi Michael,

Great to hear that you were able to resolve the issue. Looking through the logs I can only see that error happening while your Octopus Server was running version 3.3.22 which would explain why you were receiving the error, but it is a bit strange as it didn’t look like the Octopus service had been restarted after you upgraded to 3.4 (this should happen automatically after the installation has been completed.).

Thank you and best regards,
Henrik

Notice:

This issue has been closed due to inactivity. If you encounter the same or a similar issue and require help, please open a new discussion (if we asked for logs or extra details in this thread, consider including them in the new thread). If you are the creator of this thread and believe it should not be closed let us know via our support email.