Error 500 when trying to restore NuGet packages

[power_pille]

I found out that the Stored Procedure dbo.PackageNameIds_GetOrCreatePackageNameId might be the problem. If I call it manually on the database I receive the above mentioned error:

BEGIN TRAN
EXEC dbo.PackageNameIds_GetOrCreatePackageNameId 'nuget', NULL, 'NuGet.CommandLine', 'Y'
ROLLBACK

BUT if I call it like this, it is successful:

BEGIN TRAN
EXEC dbo.PackageNameIds_GetOrCreatePackageNameId 'nuget', NULL, 'Nuget.CommandLine', 'Y'
ROLLBACK

Note the small g in Nuget in the second statement.

The database already contains a record for NuGet.CommandLine:

[dean-houston]

Hi @power_pille ,

That's strange; I'm wondering if there's some kind of collation problem on your database.

Can you run this?

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server', collation_name  as 'Database' FROM sys.databases WHERE name = 'ProGet';

What are the collation you're seeing?

ProGet must be SQL_Latin1_General_CP1_CI_AS

-- Dean

[power_pille]

Hi @dean-houston ,

Seems ok:

The stored procedure does comparisons by providing the collation explicitly.

[pmsensi]

I'm having the same problem, and I can see in the database the duplicated entries.

[power_pille]

Hi @dean-houston,

thanks to the post of @pmsensi I also had another look at the records in the database. I always only found one record for e.g. Nuget.CommandLine. But then I noticed that the Column "PackageNameIds.Package_Name" uses a case sensitive collation:

So if I query the column with case insensitive collation I also receive 2 records. This was unexpected as the collation of the server and database were both case insensitive. This may have to do with the problem I guess, but I'm not sure why.

SELECT *
FROM PackageNameIds
WHERE PackageType_Name = 'nuget'
AND Package_Name LIKE 'NuGet.CommandLine' COLLATE Latin1_General_CI_AS

[power_pille]

I created a copy of the Stored Procedure [dbo].[PackageNameIds_GetOrCreatePackageNameId] and replaced the statement that tries to find the correctly cased record with this:

-- prefer an exact case match if there are multiple entries
        
        WITH AvailablePackageOrderedByCasing AS
        (
        	SELECT TOP 1 *
        	 FROM [PackageNameIds]
        	WHERE [PackageType_Name] = @PackageType_Name
        	  AND (([PackageGroup_Name_Lower] IS NULL AND @PackageGroup_Name IS NULL) OR ([PackageGroup_Name_Lower] = LOWER(@PackageGroup_Name)))
        	  AND [Package_Name_Lower] = LOWER(@Package_Name)
        	ORDER BY CASE WHEN [Package_Name] = @Package_Name COLLATE Latin1_General_CS_AS   
        		  AND ISNULL([PackageGroup_Name],'') = ISNULL(@PackageGroup_Name,'')   
        		  THEN 0 ELSE 1 END, [PackageName_Id] DESC
        )
        
        SELECT @PackageName_Id = [PackageName_Id],
        	   @Actual_PackageGroup_Name = [PackageGroup_Name],
        	   @Actual_Package_Name = [Package_Name]
        FROM AvailablePackageOrderedByCasing

When I call the modified stored procedure I get the Id of the correctly cased record without an error.

So I think the issue might be the part that tries to find the correctly cased existing record.

[atripp]

@power_pille @pmsensi thanks for helping to investigate this

What I can say is this is most definitely a regression in ProGet 2025.8 caused by PG-3047 -- I'm not sure if we want to revert that change or fix it. But rolling back to ProGet 2024.7 will definitely address the issue.

As for casing, it's all really strange to honest - some package ecosystems (rpm) are case sensitive, and different packages that differ by casing. Most are not insane like that.

However, due to a bug from a while back, "bad data" snuck into the PackageNameIds table from a vulnerability database update -- and that's why NuGet has those duplicates. Clearing them "blindly" from databases in the field isn't easy, but the feed reindex was a step in that direction.

Anyway we're going to continue researching how to address this

[atripp]

hi @power_pille @pmsensi ,

Just as an update, we will revert the change via PG-3097 in the next maintenance release.

We will solve duplicate package names another day -- but we'll do it the "hard way", which is to update all the related tables when it occurs.

Thanks,
Alana

[power_pille]

Hi @atripp,

ok, in the meantime I applied the above mentioned fix to the stored procedure so our builds will run again.

[pmsensi]

Hi @atripp ,

thank you for the update. I reverted to 2025.07 and I will wait for the next maintenance release.

@power_pille thank you too, for your findings and suggestions.