Inedo Community Forums Forums
    • Recent
    • Tags
    • Popular
    • Login

    Welcome to the Inedo Forums! Check out the Forums Guide for help getting started.

    If you are experiencing any issues with the forum software, please visit the Contact Form on our website and let us know!

    Upgrading ProGet from 6.0.14 to 22.0.17 fails with DB Error

    Scheduled Pinned Locked Moved Support
    7 Posts 2 Posters 23 Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • R Offline
      rosario.digiovanni_1930
      last edited by

      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?

      atrippA 1 Reply Last reply Reply Quote 0
      • atrippA Offline
        atripp inedo-engineer @rosario.digiovanni_1930
        last edited by

        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

        1 Reply Last reply Reply Quote 0
        • R Offline
          rosario.digiovanni_1930
          last edited by 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?

          atrippA 1 Reply Last reply Reply Quote 0
          • atrippA Offline
            atripp inedo-engineer @rosario.digiovanni_1930
            last edited by

            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.

            1 Reply Last reply Reply Quote 0
            • R Offline
              rosario.digiovanni_1930
              last edited by

              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)?

              atrippA 1 Reply Last reply Reply Quote 0
              • atrippA Offline
                atripp inedo-engineer @rosario.digiovanni_1930
                last edited by

                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

                1 Reply Last reply Reply Quote 0
                • R Offline
                  rosario.digiovanni_1930
                  last edited by

                  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.

                  1 Reply Last reply Reply Quote 0

                  Hello! It looks like you're interested in this conversation, but you don't have an account yet.

                  Getting fed up of having to scroll through the same posts each visit? When you register for an account, you'll always come back to exactly where you were before, and choose to be notified of new replies (either via email, or push notification). You'll also be able to save bookmarks and upvote posts to show your appreciation to other community members.

                  With your input, this post could be even better 💗

                  Register Login
                  • 1 / 1
                  • First post
                    Last post
                  Inedo Website Home • Support Home • Code of Conduct • Forums Guide • Documentation