Upgrading ProGet from 6.0.14 to 22.0.17 fails with DB Error

rosario.digiovanni_1930

Hello, I am trying to upgrade our installation of ProGet from 6.0.14 to 22.0.17. I use the Inedo Hub offline Installer, because the hosting server has no internet access. The ProGet DB is hosted on a seperate DB Server with SQL Server 2019.
The first try failed with following error:

...
...
...
INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.3.Events_RaiseEvent.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.4.PackageIds_GetPackageId.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/1.ApiKeys_CreateOrUpdateApiKey.sql...
    ERROR:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    ERROR:    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    ERROR:    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    ERROR:    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
    ERROR:    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    ERROR:    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    ERROR:    at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteQueryWithSplitter(String query, SqlTransaction transaction)
    ERROR:    at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteScripts(IEnumerable`1 scripts, ChangeScriptState state)
    ERROR:    at Inedo.DbUpdater.Program.Update(String scriptPath, String connectionString, Boolean force)
    ERROR: Unhandled Exception: System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.__AddStoredProcInfo'.
    ERROR:    at Inedo.DbUpdater.Program.Run(ArgList args)
    ERROR:    at Inedo.DbUpdater.Program.Main(String[] args)
    ERROR: Process exited with code: -532462766 (failure)

After this ProGet was inaccessible and I downgraded back to 6.0.14.
All following attempts to install 22.0.17 failed with the next error:

...
...
...
 INFO: Executing untracked script OBJECTS/2.VIEWS/1.LicenseRules_Extended.sql...
     INFO: Executing untracked script OBJECTS/2.VIEWS/1.Licenses_Extended.sql...
     INFO: Executing untracked script OBJECTS/2.VIEWS/1.Licenses_Usage.sql...
    ERROR:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    ERROR:    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    ERROR:    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    ERROR:    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
    ERROR:    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    ERROR:    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    ERROR:    at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteQueryWithSplitter(String query, SqlTransaction transaction)
    ERROR:    at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteScripts(IEnumerable`1 scripts, ChangeScriptState state)
    ERROR:    at Inedo.DbUpdater.Program.Update(String scriptPath, String connectionString, Boolean force)
    ERROR:    at Inedo.DbUpdater.Program.Run(ArgList args)
    ERROR:    at Inedo.DbUpdater.Program.Main(String[] args)
    ERROR: Unhandled Exception: System.Data.SqlClient.SqlException: Invalid object name 'PackageLicenses'.
    ERROR: Process exited with code: -532462766 (failure)

My investigation so far is, that there is no table 'PackageLicenses' on the DB, so the second error makes sense to me.

Can some help me?

atripp

Hi @rosario-digiovanni_1930 ,

Unfortunately, it looks like there's strange "corruption" with your database. Objects that should exist don't seem to exist.

For example, __AddStoredProcInfo is a stored procedure that's dropped/created early on in the update process. You should see something like INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.1.AddStoredProcInfo.sql..., above all the other items.

PackageVersions is a table that's created in v2022., before running all the stored procedure creation scripts. That is a very simple CREATE TABLE script, so I can't imagine how it failed.

Any ideas? Is there some strangeness with with DB Schemas or anything? The installer should crash if your user isn't part of the dbo schema, but we've also seen that detection fail because of schema aliasing configuration on the server. That's a very rare, obscure setting.

Did you have past failed installations? You can use the inedosql tool to find out what errors exist in the database: https://github.com/Inedo/inedosql#errors

You can find an inedosql.exe within the Manual Install Files for your version:: https://my.inedo.com/downloads/installers

rosario.digiovanni_1930

Hi @atripp ,
thank you for the tips so far.
My user and the security settings should be fine - my DB-team checked this again and more access as my user has is not possible . So, the dbo schema should be no problem.

We reset the DB to a state before the first updgrade attempt. Then i tryed the inedosql error command and got this:

PS C:\Users\XXXXX\Downloads\ProGet-22.0.17\SqlScripts> .\inedosql.exe errors --connection-string="Data Source=dbname;Initial Catalog=ProGet;Integrated Security=True" --all
ed36e7c2-8ca9-4674-80b4-9e3a956fe1b6 DDL-DML/v1-3/0002. 0.1.0/21 CREATE ROLE ProGetUser_Role.sql
PS C:\Users\XXXXX\Downloads\ProGet-22.0.17\SqlScripts> .\inedosql.exe error ed36e7c2-8ca9-4674-80b4-9e3a956fe1b6 --connection-string="Data Source=dbname;Initial Catalog=ProGet;Integrated Security=True"
GUID: ed36e7c2-8ca9-4674-80b4-9e3a956fe1b6
Name: DDL-DML/v1-3/0002. 0.1.0/21 CREATE ROLE ProGetUser_Role.sql
Executed: 14.07.2020 14:41:44
Resolved: 14.07.2020 16:04:44

Resolution:


Error:
User, group, or role 'ProGetUser_Role' already exists in the current database.

I think this should be no problem. But the installation of ProGet 22.0.17 failed again with error:

     INFO: Executing untracked script OBJECTS/3.TRIGGERS/1.TR__DockerRepositoryTags__ValidateImage.sql...
     INFO: Executing untracked script OBJECTS/3.TRIGGERS/X.TR__Users__ValidateAnonymous.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.1.AddStoredProcInfo.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.2.HandleError.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.3.Events_RaiseEvent.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.4.PackageIds_GetPackageId.sql...
     INFO: Executing untracked script OBJECTS/4.PROCEDURES/1.ApiKeys_CreateOrUpdateApiKey.sql...
    ERROR:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    ERROR:    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    ERROR:    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    ERROR:    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
    ERROR: Unhandled Exception: System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.__AddStoredProcInfo'.
    ERROR:    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    ERROR:    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    ERROR:    at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteQueryWithSplitter(String query, SqlTransaction transaction)
    ERROR:    at Inedo.DbUpdater.SqlServer.SqlServerDatabaseConnection.ExecuteScripts(IEnumerable`1 scripts, ChangeScriptState state)
    ERROR:    at Inedo.DbUpdater.Program.Update(String scriptPath, String connectionString, Boolean force)
    ERROR:    at Inedo.DbUpdater.Program.Run(ArgList args)
    ERROR:    at Inedo.DbUpdater.Program.Main(String[] args)
    ERROR: Process exited with code: -532462766 (failure)

As you can see there is a message with INFO: Executing untracked script OBJECTS/4.PROCEDURES/0.1.AddStoredProcInfo.sql.... This is running well and I can confirm that the DB has a table dbo.__StoredProcInfo.
I really don't understand what I am doing wrong.

I even looked for the not found procedure and can find it. There is a __AddStoredProcInfo entry in table dbo.__StoredProcInfo.

Do you have any other ideas?

atripp

Hi @rosario-digiovanni_1930 ,

Thanks for sharing the details; from here, I would recommend just working with inedosql.exe and the SqlSCripts.zip file that's in the ProGet.SqlScripts-22.0.17 package. You can just run inedosql update after extracting the SQLScripts.zip file

The error is from from a while ago (14.07.2020 14:41:44), so I guess it's nothing to worry about. Perhaps it's the same underlying issue?

There's definitely something wrong... I just have no idea what it is. The only guess is schema/permissions, but you've already checked it.

I will describe to you what's happening, and hopefully you can troubleshoot with your database team better, especially since you'll have all the scripts that are run...

The script 0.1.AddStoredProcInfo.sql does the following:

drop/create table __StoredProcInfo
drop/create stored procedure __AddStoredProcInfo
drop/create stored procedure __GetStoredProcInfo
execute __AddStoredProcInfo

Then later, the 1.ApiKeys_CreateOrUpdateApiKey.sql script is executed. This script starts by executing __AddStoredProcInfo again, but it's called with the [dbo].[__AddStoredProcInfo] prefix instead.

So I think the issue is schema-related.

But as I mention, we try to mitigate this earlier with 00. EnsureDbo.sql . This script works like so:

IF (SCHEMA_NAME() <> 'dbo') BEGIN
   DECLARE @SQL NVARCHAR(MAX) = 'ALTER USER [' + CURRENT_USER + '] WITH DEFAULT_SCHEMA = [dbo]'
   PRINT 'Changing default schema ("' + SCHEMA_NAME() + '") to dbo via: ' + @SQL
   EXEC sp_executesql @SQL
   PRINT 'Schema changed.'
END

Anyway, please let us know what you find. We'd love to make these scripts work in more scenarios, and not cause problems like this.

rosario.digiovanni_1930

Hi @atripp,
we did the installation again with my user as member of the db admin users. Additionally we checked the default schema is dbo. The errors occurred again. After this try we found the missing tables - but created with my user as prefix instead of dbo.
For example the table __StoredProcInfo was created as COMPANY\myUser.__StoredProcInfo instead of dbo.__StoredProcInfo. This should be the cause of the problem , but we have no clue how and why this happened.

A question to be sure: Are these sql files from the installation compatible with SQL Server 2019 (15.0.4261.1)?

atripp

Hi @rosario-digiovanni_1930,

Ah - so it is schema related.

Well, it's definitely compatible with SQL 2019... and we also have no idea why or how that happened. We don't see other users having this issue.

We also tested 00. EnsureDbo.sql works on just about every configuration we could imagine:

user has no schema
user has different schema
user has own schema
user doesn't have permission to change schema
etc.

What does SELECT SCHEMA_NAME() return?

Any idea why the following isn't working for you?

IF (SCHEMA_NAME() <> 'dbo') BEGIN
   DECLARE @SQL NVARCHAR(MAX) = 'ALTER USER [' + CURRENT_USER + '] WITH DEFAULT_SCHEMA = [dbo]'
   PRINT 'Changing default schema ("' + SCHEMA_NAME() + '") to dbo via: ' + @SQL
   EXEC sp_executesql @SQL
   PRINT 'Schema changed.'
END

FYI, here is Microsoft's docs on SCHEMA_NAME:
https://learn.microsoft.com/en-us/sql/t-sql/functions/schema-name-transact-sql

rosario.digiovanni_1930

Hello @atripp,
today we upgraded successfully to 2.0.17. We believe there were problems with Windows Active Directory Users and the permissions granted per group. Our solution was a new sql user and a corresponding change of the connection string in the ProGet.config.
Thank you for your help.