2100 parameter limit when querying artifacts

[TomPeters]

The bug

Hit the endpoint /api/artifacts?regarding=Releases-1234. If the release has more than 2100 associated deployments, the underlying sql query will fail because it hits the 2100 sql parameter limit.

What I expected to happen

The request should succeed

Log exerpt

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)

Affected versions

Octopus Server: 2019.2.2 and earlier

Links

https://help.octopus.com/t/error-when-release-has-been-deployed-too-many-times/22485

[TomPeters]

Here is a linqpad script which can be used to reproduce the bug on 2019.2.2
http://share.linqpad.net/dsw7ni.linq

<Query Kind="Program">
  <Connection>
    <ID>d5f6c4e6-737e-453b-8a6f-725d2db979e0</ID>
    <Persist>true</Persist>
    <Server>.</Server>
    <Database>OctopusDeploy-master</Database>
    <ShowServer>true</ShowServer>
  </Connection>
  <NuGetReference>Microsoft.Tpl.Dataflow</NuGetReference>
  <NuGetReference>Octopus.Client</NuGetReference>
  <Namespace>Octodiff</Namespace>
  <Namespace>Octodiff.Core</Namespace>
  <Namespace>Octodiff.Diagnostics</Namespace>
  <Namespace>Octopus.Client</Namespace>
  <Namespace>Octopus.Client.Editors</Namespace>
  <Namespace>Octopus.Client.Editors.Async</Namespace>
  <Namespace>Octopus.Client.Exceptions</Namespace>
  <Namespace>Octopus.Client.Extensibility</Namespace>
  <Namespace>Octopus.Client.Extensibility.Attributes</Namespace>
  <Namespace>Octopus.Client.Extensibility.Extensions.Infrastructure.Configuration</Namespace>
  <Namespace>Octopus.Client.Extensions</Namespace>
  <Namespace>Octopus.Client.Logging</Namespace>
  <Namespace>Octopus.Client.Model</Namespace>
  <Namespace>Octopus.Client.Model.Accounts</Namespace>
  <Namespace>Octopus.Client.Model.Accounts.Usages</Namespace>
  <Namespace>Octopus.Client.Model.DeploymentProcess</Namespace>
  <Namespace>Octopus.Client.Model.Endpoints</Namespace>
  <Namespace>Octopus.Client.Model.Forms</Namespace>
  <Namespace>Octopus.Client.Model.Migrations</Namespace>
  <Namespace>Octopus.Client.Model.Triggers</Namespace>
  <Namespace>Octopus.Client.Model.Triggers.ScheduledTriggers</Namespace>
  <Namespace>Octopus.Client.Model.Versioning</Namespace>
  <Namespace>Octopus.Client.Operations</Namespace>
  <Namespace>Octopus.Client.Repositories</Namespace>
  <Namespace>Octopus.Client.Repositories.Async</Namespace>
  <Namespace>Octopus.Client.Serialization</Namespace>
  <Namespace>Octopus.Client.Util</Namespace>
  <Namespace>Octopus.Client.Validation</Namespace>
  <Namespace>System.ComponentModel.DataAnnotations</Namespace>
  <Namespace>System.ComponentModel.DataAnnotations.Schema</Namespace>
  <Namespace>System.Net.Http</Namespace>
  <Namespace>System.Net.Http.Headers</Namespace>
  <Namespace>System.Numerics</Namespace>
  <Namespace>System.Threading.Tasks</Namespace>
  <Namespace>System.Threading.Tasks.Dataflow</Namespace>
</Query>

async Task Main()
{
	using(var client = await Extensions.CreateClient()) 
	{
		var (spaceRepo, space) = await Extensions.CreateSingleUseSpace(client, "bug-5371-artifacts");
		await ToggleTaskQueue(true); // since we are creating a ton of deployments, let's stop the task queue
		
		var project = await CreateProjectWithScriptStep();
		var release = await CreateRelease();
		var environment = await CreateEnvironment();
		
		var deployments = await CreateDeployments();
		var artifacts = await spaceRepo.Artifacts.FindRegarding(release); // This should blow up with that many deployments

		async Task<ProjectResource> CreateProjectWithScriptStep()
		{
			var defaultLifecycle = await spaceRepo.Lifecycles.FindOne(l => true);
			var defaultProjectGroup = await spaceRepo.ProjectGroups.FindOne(g => true);
			var projectEditor = await spaceRepo.Projects.CreateOrModify("The Project", defaultProjectGroup, defaultLifecycle);
			var deploymentProcessEditor = await projectEditor.DeploymentProcess;
			deploymentProcessEditor.AddOrUpdateStep("hello world").AddOrUpdateScriptAction("hello world", ScriptAction.InlineScript(ScriptSyntax.PowerShell, @"Write-Host ""Hello world"""), ScriptTarget.Server);
			await projectEditor.Save();
			return projectEditor.Instance;
		}
		async Task<ReleaseResource> CreateRelease()
		{
			return await spaceRepo.Releases.Create(new ReleaseResource() {
				ProjectId = project.Id,
				Version = "0.0.1"
			});
		}

		Task<EnvironmentResource> CreateEnvironment()
		{
			return spaceRepo.Environments.Create(new EnvironmentResource() {Name = "dev"});
		}
		
		async Task ToggleTaskQueue(bool isQueueStopped) {
			space.TaskQueueStopped = isQueueStopped;
			await client.ForSystem().Spaces.Modify(space);
		}

		async Task<DeploymentResource[]> CreateDeployments()
		{
			var numberOfDeployments = 2100;
			$"Creating {numberOfDeployments} deployments...".Dump();
			var ds = await Enumerable.Range(0, numberOfDeployments).Throttle(_ => {
				return CreateDeployment();
			});
			$"Created {numberOfDeployments} deployments".Dump();
			return ds;
		}

		async Task<DeploymentResource> CreateDeployment()
		{
			return await spaceRepo.Deployments.Create(new DeploymentResource() {
				ProjectId = project.Id,
				EnvironmentId = environment.Id,
				ReleaseId = release.Id
			});
		}
	}
}

public static class Extensions
{
	public static async Task<IOctopusAsyncClient> CreateClient(string server = null, string password = null, string apiKey = null)
	{
		server = server ?? "http://localhost:8065";
		password = password ?? Util.GetPassword("Shared_Octopus_Password");
		"Connecting...".Dump();
		var endpoint = apiKey == null ? new OctopusServerEndpoint(server) : new OctopusServerEndpoint(server, apiKey);
		var client = await OctopusAsyncClient.Create(endpoint);
		if (apiKey == null)
		{
			await client.SignIn(new LoginCommand() { RememberMe = true, Username = "admin", Password = Util.GetPassword("Shared_Octopus_Password") });
		}
		"Connected!".Dump();
		return client;
	}

	public static async Task<(IOctopusSpaceAsyncRepository spaceRepo, SpaceResource space)> CreateSingleUseSpace(this IOctopusAsyncClient client, string spaceName)
	{
		var repository = client.ForSystem();
		var existingSpace = await repository.Spaces.FindByName(spaceName);
		if (existingSpace != null)
		{
			$"Existing space found ({existingSpace.Id}). Deleting this space to make way for a brand new one.".Dump();
			existingSpace.TaskQueueStopped = true;
			await repository.Spaces.Modify(existingSpace);
			await WaitForTasksToComplete(existingSpace);
			await repository.Spaces.Delete(existingSpace);
			"Deleted existing space".Dump();
		}

		var currentUser = await repository.Users.GetCurrent();
		var space = await repository.Spaces.Create(new SpaceResource()
		{
			Name = spaceName,
			SpaceManagersTeamMembers = new ReferenceCollection(currentUser.Id)
		});
		$"Created new space {space.Name} ({space.Id})".Dump();
		return (client.ForSpace(space), space);

		async Task WaitForTasksToComplete(SpaceResource theExistingSpace)
		{
			var existingSpaceRepository = client.ForSpace(theExistingSpace);
			var statesArgument = string.Join(",", new[] { TaskState.Cancelling, TaskState.Executing });
			var runningTasks = await existingSpaceRepository.Tasks.FindAll(pathParameters: new { states = statesArgument });
			if (runningTasks.Any())
			{
				var taskIdsAsString = string.Join(", ", runningTasks.Select(t => t.Id));
				$"Before deleting this space, we must wait for {runningTasks.Count} tasks to complete ({taskIdsAsString})".Dump();
				await Task.WhenAll(runningTasks.Select(async t =>
				{
					await existingSpaceRepository.Tasks.WaitForCompletion(t);
					$"Task {t.Id} completed".Dump();
				}));
				"All tasks completed.".Dump();
			}
		}
	}

	public static Task<TOutput[]> Throttle<TInput, TOutput>(this IEnumerable<TInput> inputs, Func<TInput, Task<TOutput>> createTask)
	{
		return inputs.Throttle(createTask, 20);
	}

	public static async Task<TOutput[]> Throttle<TInput, TOutput>(this IEnumerable<TInput> inputs, Func<TInput, Task<TOutput>> createTask, int numberOfConcurrentTasks)
	{
		var progressBar = new Util.ProgressBar()
		{
			HideWhenCompleted = true,
			Fraction = 0d
		}.Dump();
		var inputsArray = inputs.ToArray();
		var createTaskBuffer = new TransformBlock<TInput, TOutput>(createTask, new ExecutionDataflowBlockOptions() { MaxDegreeOfParallelism = numberOfConcurrentTasks });
		var outputList = new List<TOutput>();
		var outputAction = new ActionBlock<TOutput>(outputItem =>
		{
			outputList.Add(outputItem);
			progressBar.Fraction = (double)outputList.Count / (double)inputsArray.Count();
		}, new ExecutionDataflowBlockOptions()
		{
			// Keep this single threaded for simplicity
			MaxDegreeOfParallelism = 1
		});
		createTaskBuffer.LinkTo(outputAction, new DataflowLinkOptions() { PropagateCompletion = true });
		foreach (var item in inputs)
		{
			await createTaskBuffer.SendAsync(item);
		}
		createTaskBuffer.Complete();
		await createTaskBuffer.Completion;
		await outputAction.Completion; // Completion is propogated during LinkTo
		progressBar.Fraction = 1;
		return outputList.ToArray();
	}
}

[octoreleasebot]

Release Note: Fixed a bug where you could not fetch artifacts for releases with a large number of deployments

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. If you think you've found a related issue, please contact our support team so we can triage your issue, and make sure it's handled appropriately.