Error when release has been deployed too many times

We have a release “0.0.1”. A quick explanation of what this release is for…

We have 2 Database projects, the main database project and a hotfix project. The main project deploys every 8 weeks as part of our release cycle, while the hotfixes are just SQL scripts that deploy in-between releases. The next main release would contain these hotfixes, so once a main release happens the previous hotfixes are irrelevant. Since there is no way to delete release information from Octopus I created a hotfix release “0.0.1” to denote that there are not currently any hotfixes deployed.

Today I went to deploy 0.0.1 to a tenant and received the error:

Octopus v2019.2.2

Error while executing SQL command in transaction 'https://octopus.xxx.xxx/api/Spaces-1/artifacts?skip=0&take=10&regarding=Releases-3651&order=asc 08e2b6ee961a4388a0ac34fd0157f392': The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. The command being executed was: SELECT COUNT(*) FROM dbo.[Artifact] WHERE ((([SpaceId] = 'Spaces-1'))) AND ([ServerTaskId] IN (@servertaskid0_1, @servertaskid1_2, @servertaskid2_3, @servertaskid3_4, @servertaskid4_5, @servertaskid5_6, @servertaskid6_7, @servertaskid7_8, @servertaskid8_9, @servertaskid9_10, @servertaskid10_11, @servertaskid11_12, @servertaskid12_13, @servertaskid13_14, @servertaskid14_15, @servertaskid15_16,
<large chunk removed>
@servertaskid2934_2935, @servertaskid2935_2936, @servertaskid2936_2937, @servertaskid2937_2938, @servertaskid2938_2939, @servertaskid2939_2940))
System.Exception
   at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable`1 commandTimeoutSeconds)
   at Nevermore.QueryBuilder`2.Count()
   at Nevermore.QueryBuilder`2.ToList(Int32 skip, Int32 take, Int32& totalResults)
   at Octopus.Server.Web.Api.Actions.ListArtifactsResponder.ExecuteRegistered()
   at Octopus.Server.Web.Infrastructure.Api.Responder`1.Respond(TDescriptor options, NancyContext context)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object x)
   at Nancy.Routing.Route.<>c__DisplayClass4.<Wrap>b__3(Object parameters, CancellationToken context)

--Inner Exception--
SQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
System.Data.SqlClient.SqlException
   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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, 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& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass11_0.<ExecuteScalarWithRetry>b__0()
   at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Nevermore.RelationalTransaction.ExecuteScalar[T](String query, CommandParameterValues args, Nullable`1 commandTimeoutSeconds)

I would actually prefer a way to remove deployment info over making this 0.0.1 release work. Another use case for being able to remove release info is our QA environment. Our projects are all tenanted and during the initial onboarding of a tenant we create them a QA environment. Some large tenants always have projects and improvements so having a permanent QA environment makes sense for them. We have other tenants that have not changed in years. I can drop their physical QA DB, but Octopus’s dashboard will always show the last version that was deployed to them.

Hi,

Thanks for getting in touch.

Firstly, I have raised a bug for the specific error that you have encountered. You can track the bug here. It should be a simple bug to fix, so I would expect it to be fixed and released in the next week or two. I presume this is not currently blocking you because in the meantime you can avoid deploying the 0.0.1 release. Let me know if that assumption is incorrect and you are actually blocked by this problem!

It is also currently possible to delete a deployment through the API but not through the UI (as far as I can tell). You can do this by submitting a DELETE request to the /api/deployments/{id} endpoint. You can see this through our Swagger API documentation. This could be something that you perform programatically. Alternatively, you can also delete releases through the UI, which should delete all of the deployments associated with those releases.

Another common option is to change your process so that there is a single project that handles both the “main” database deployments as well as the hotfixes. For example, you might deploy version 2.0.0 (which might be a “major” release). Then you could deploy version 2.0.1 to the same database, which contains a hotfix on top of version 2.0.0. There are some tools that may help you with this approach. At Octopus, we like using DbUp.

Finally, one of the next major features that we want to implement in Octopus is called “Operations Processes” and will make Octopus a more natural fit for these kinds of problems, where you just have some operations process that you want to run on target, without the other features that are better suited for continually deployed products. I think this might be a good fit for your hotfix database deployments. We want to ship this feature later this year, so stay tuned!

Hope that helps!

Thank you for your help so far Tom, I have been wanting a delete endpoint for a long time instead of using the 0.0.1 release idea.

I was able to delete the most recent deployment using the API, but once I did that it just showed the deployment that occurred prior to the most recent… and now this release info does not get returned by the API.

This is all done in Powershell, here are the variables I was using
$OctoURL = "https://octopus.XXX.com"
$OctoAPIKey = "API-XXXXXXXXXXXXXX"
$Header = @{ "X-Octopus-ApiKey" = $OctoAPIKey }
$EnvironmentID = "Environments-2" # 2 = QA
$Projects = "Projects-41" # Hotfix
$Tenant = "CHRIS"

To get the Tenant info
$TenantObj = (Invoke-RestMethod "$OctoURL/api/tenants/all" -Method GET -Headers $Header) | Where-Object {$_.Name -eq $Tenant}

To get the most recent deployment ID:
$DeploymentID = ((Invoke-RestMethod "$OctoURL/api/deployments?environments=$EnvironmentID&projects=$Project" -Method GET -Headers $Header).items | Where-Object {$_.TenantID -eq $TenantObj.ID}).Id

To Delete that deployment
Invoke-RestMethod "$OctoURL/api/deployments/$DeploymentID" -Method DELETE -Headers $Header

Up to this point everything worked as expected. When I look at the overview for the hotfix project I see the previous release that was deployed for that tenant. so I tried get that deploymentID using
((Invoke-RestMethod "$OctoURL/api/deployments?environments=$EnvironmentID&projects=$Project" -Method GET -Headers $Header).items | Where-Object {$_.TenantID -eq $TenantObj.ID}).Id
But that does not return any deployment information anymore. Hovering over the release on the Overview UI does give me the DeploymentID and hitting the API at
Invoke-RestMethod "$OctoURL/api/deployments/Deployments-41061" -Method GET -Headers $Header
does return the info including the correct TenantId.

I guess what I am looking for is an API endpoint that will return an array of all releases for a tenant in an environment that I can loop through, or the /api/deployments endpoint to return the release that the UI is now showing after the most recent deploy is deleted. I did try api/deployments and api/Spaces-1/deployments… both return the same results.

I figured it out… I needed to use “take” in the API call that I get all the Deployment IDs. Is there a wild card to take all instead of using a huge number like 21474833647?

Here is the script in case anyone finds this later

$OctoURL = # Your Octopus Server URL
$OctoAPIKey = # Your API Key
$Header = @{ "X-Octopus-ApiKey" = $OctoAPIKey }
$EnvironmentID = # Your Environment ID
$Project = # Your Project ID

# Get Tenant Object
$TenantObj = (Invoke-RestMethod "$OctoURL/api/tenants/all" -Method GET -Headers $Header) | Where-Object {$_.Name -eq $Tenant}

# Get DeploymentIDs
$DeploymentIDs = ((Invoke-RestMethod "$OctoURL/api/deployments?take=2147483647&amp;environments=$EnvironmentID&amp;projects=$Project" -Method GET -Headers $Header).items | Where-Object {$_.TenantID -eq $TenantObj.ID}).Id

# Delete Deployments
foreach ($DeploymentID in $DeploymentIDs){
Invoke-RestMethod "$OctoURL/api/deployments/$DeploymentID" -Method DELETE -Headers $Header
}

Hi Chris,

I’m glad you worked it out!

There is no wild card that can be used for the take parameter. This is because the data returned from this endpoint is paginated, so the expectation is that the client requests each subsequent page of data as required. The resource you will receive from the request against /api/deployments will contain a Links property. This is an object which contains a few properties such as Page.Next which is a URL that you can query to get the subsequent page of data.

Instead of rolling your own Powershell with Invoke-RestMethod, I would instead highly recommend that you use our .Net Octopus client library, which you can find on Github. This handles a lot of the complexity for you, and also makes your scripts more future proof (i.e. we are more likely to make breaking changes to our API, but preserve backwards compatibility in our client library).

Using this library, you can invoke a method like FindMany, which will Paginate through all of the deployments and return those that match your condition.

You can find a number of examples demonstrating how to use this client library here.

Good luck :slight_smile:
Tom

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.