When importing projects that have a library variable set attached you might get a duplicate entry for some variables

[akirayamamoto]

Team

• [X] I've assigned a team label to this issue

Severity

Non-blocking

Version

2021.3.7082

Latest Version

No response

What happened?

When importing multiple projects with import/export (bento) that have a library variable set attached to them that have duplicate scoped values, you will get an extra duplicate entry for each import after the first.

If you attempt to delete these variables you get an error:

An item with the same key has already been added. Key: 080c13ed-f995-ea0a-c1dc-11d16d75670b

This problem is very similar to this other one. However, this one does not affect variables with duplicate scoped values since this bug was fixed. We noticed this problem happening when one variable had a role and another did not but they ended up with the same ID.

Reproduction

TBD

Error and Stacktrace

No response

More Information

No response

Workaround

Manually delete the variable in the variable set by running a update SQL command.

[nathanwoctopusdeploy]

Workaround

Requires LINQPad , .NET and VS Code

1. Dump the variable set to file

For Octopus Cloud

Using the Cloud Portal Run SQL Task, run (replacing the Id as appropriate)

SELECT [json] FROM dbo.[VariableSet] WHERE Id = 'variableset-LibraryVariableSets-TBC'

Copy the State json from the Run SQL Task into the LINQPad snippet. Run the snippet to extract the variable set as JSON and save it to a file.

<Query Kind="Statements">
  <NuGetReference>Newtonsoft.Json</NuGetReference>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Newtonsoft.Json.Linq</Namespace>
  <RuntimeVersion>7.0</RuntimeVersion>
</Query>

var json = 

"""
<<PASTE STATE HERE>>
""";

var wrapperJson = JsonConvert.DeserializeObject(json) as JObject;
var result = ((wrapperJson.SelectToken("Result") as JArray)[0] as JArray)[0] as JToken;
result.Dump();

For self-hosted

Run the following SQL statement to identify variable sets with duplicated variables

SELECT DISTINCT vs.Id, vs.JSON
FROM dbo.[VariableSet] vs
CROSS APPLY OPENJSON (vs.JSON, '$.Variables')
  WITH (
    Id VARCHAR(300) '$.Id',
    Name VARCHAR(300) '$.Name'
  ) AS vsj
WHERE vs.IsFrozen = 0
GROUP BY vs.Id, vsj.Id, vsj.Name, vs.JSON
HAVING COUNT(*) > 1
ORDER BY vs.Id

For each variable set returned, save the JSON field into a file.

2. Generate SQL to de-duplicate the variable set

For each variable set dumped to file, run the following code to de-duplicate the variable set, merge scopes and warn about differences in variable values that were merged. It produces a SQL statement that can be used to update the variable set.

In the snippet, make sure:

• The TBC suffix is set to the appropriate Id on the variableSetId variable
• originalJsonFilePath matches the path to one of the variable sets dumped to file from step 1

LINQPad snippet

The following code relies on internal schemas for Variable Sets in Octopus Server and may break if it changes.

<Query Kind="Program">
  <NuGetReference>Newtonsoft.Json</NuGetReference>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Newtonsoft.Json.Bson</Namespace>
  <Namespace>Newtonsoft.Json.Converters</Namespace>
  <Namespace>Newtonsoft.Json.Linq</Namespace>
  <Namespace>Newtonsoft.Json.Schema</Namespace>
  <Namespace>Newtonsoft.Json.Serialization</Namespace>
</Query>

void Main()
{
	var variableSetId = "variableset-LibraryVariableSets-TBC"; // Set this
	
	var filePrefix = "variableset";
	var originalJsonFilePath = @$"C:\temp\{filePrefix}-error.json";
	var originalJsonFilePathFormatted = @$"C:\temp\{filePrefix}-error-formatted.json";
	var fixedJsonFilePathFormatted = @$"C:\temp\{filePrefix}-fixed-formatted.json";
	var fixedJsonFilePath = @$"C:\temp\{filePrefix}-fixed.sql";

	var json = JsonConvert.DeserializeObject(File.ReadAllText(originalJsonFilePath)) as JObject;

	Order(json);
	
	File.WriteAllText(originalJsonFilePathFormatted, JsonConvert.SerializeObject(json, Newtonsoft.Json.Formatting.Indented));
	
	Fixup(json as JObject);

	File.WriteAllText(fixedJsonFilePath, $"UPDATE dbo.[VariableSet] SET JSON = '{JsonConvert.SerializeObject(json).Replace("'", "''")}' WHERE Id = '{variableSetId}'");
		
	File.WriteAllText(fixedJsonFilePathFormatted, JsonConvert.SerializeObject(json, Newtonsoft.Json.Formatting.Indented));

	var pi = new ProcessStartInfo
	{
		UseShellExecute = true,
		FileName = "code",
		Arguments = $"--diff {originalJsonFilePathFormatted} {fixedJsonFilePathFormatted}"
	};

	Process.Start(pi);
}

void Order(JObject json)
{
	var variables = json.SelectToken("Variables") as JArray;
	var ordered = variables.OrderBy(x => x.SelectToken("Id")).ToList();
	
	variables.RemoveAll();
	
	foreach (var item in ordered)
	{
		variables.Add(item);
	}
}

readonly JsonMergeSettings mergeSettings = new JsonMergeSettings
{
	MergeArrayHandling = MergeArrayHandling.Union
};

void Fixup(JObject json)
{
	var log = new StringBuilder();
	
	var variables = json.SelectToken("Variables") as JArray;
	var grouped = variables.GroupBy(x => x.SelectToken("Id")).ToList();
	var duplicates = grouped.Where(x => x.Count() > 1).ToList();

	foreach (var duplicate in duplicates)
	{
		var keep = duplicate.Take(1).Single();
		var KeepScope = keep.SelectToken("Scope") as JObject;
		var remove = duplicate.Skip(1).ToList();

		foreach (var item in remove)
		{
			if (keep.SelectToken("Value").Value<string>() != item.SelectToken("Value").Value<string>())
			{
				Console.WriteLine($"VALUES DIFFER for {keep.SelectToken("Id")} - '{keep.SelectToken("Value")}' vs '{item.SelectToken("Value")}'");
			}

			var itemScope = item.SelectToken("Scope") as JObject;

			KeepScope.Merge(itemScope, mergeSettings);

			variables.Remove(item);
		}
	}
}

[garrettdass]

Additional SQL query to find duplicate variables:

SELECT` DISTINCT vs.Id, vs.JSON
FROM dbo.[VariableSet] vs
CROSS APPLY OPENJSON (vs.JSON, '$.Variables')
  WITH (
    Id VARCHAR(300) '$.Id'
  ) AS vsj
WHERE vs.IsFrozen = 0
GROUP BY vs.Id, vsj.Id, vs.JSON
HAVING COUNT(*) > 1
ORDER BY vs.Id

[Octobob]

:tada: The fix for this issue has been released in:

Release stream	Release
2022.3	2022.3.10981
2022.4	2022.4.8362
2023.1	2023.1.7088
2023.2+	all releases