Upgrading to 5.0.12/13 on MSSQL fails

PowerShell Universal
1

I’ve been away for a while and wanted to check out the newer versions to see if there has been some fixes I can upgrade my test environment to.

Tried the newest 5.0.13 and that didn’t work, 5.0.12 had the same issue, 5.0.11 installed and started up successfully.

The installer fails trying to start the service, and in the log the following error is present:

2024-10-31 13:26:24.633 +01:00 [FTL] Fatal error starting PowerShell Universal.
Microsoft.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_JobPipelineOutput_Job_JobId". The conflict occurred in database "PSU", table "dbo.Job", column 'Id'.
Cannot insert the value NULL into column 'JobId', table 'PSU.dbo.JobPipelineOutput'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at PowerShellUniversal.SQL.DatabaseFeature.InitializeDatabase(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\PowerShellUniversal.SQL\Plugin.cs:line 68
   at Universal.Server.Services.SystemPluginService..ctor(IConfiguration configuration) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Services\SystemPluginService.cs:line 55
   at Universal.Server.Startup.ConfigureServices(IServiceCollection services) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Startup.cs:line 66
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
   at System.Reflection.MethodBaseInvoker.InvokeDirectByRefWithFewArgs(Object obj, Span`1 copyOfArgs, BindingFlags invokeAttr)
   at System.Reflection.MethodBaseInvoker.InvokeWithOneArg(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.AspNetCore.Hosting.ConfigureServicesBuilder.InvokeCore(Object instance, IServiceCollection services)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.UseStartup(Type startupType, HostBuilderContext context, IServiceCollection services, Object instance)
   at Microsoft.AspNetCore.Hosting.GenericWebHostBuilder.<>c__DisplayClass7_0.<UseStartup>b__0(HostBuilderContext context, IServiceCollection services)
   at Microsoft.Extensions.Hosting.HostBuilder.InitializeServiceProvider()
   at Microsoft.Extensions.Hosting.HostBuilder.Build()
   at Universal.Server.Program.Main(String[] args) in C:\actions-runner\_work\universal\universal\src\Universal.Server\Program.cs:line 36
ClientConnectionId:e3a11809-761b-4d5c-87c2-5511fe0eb783
Error Number:547,State:0,Class:16


Product: PowerShell Universal
Version: 5.0.8 and 5.0.11 (Version might not matter)
2

It looks like you have orphaned jobpipelinoutput data. I’m not clear why that would have happened but it looks like 5.0.12 introduced a nonullable on the JobId column of that table. You could try clearing out the orphaned records and retrying the upgrade.

DELETE FROM JobPipelineOutput WHERE JobID IS NULL
1 Like
3

That did the trick, looks like there was 99 of those jobs and they were all back from early 2023 :+1: