Hangfire throws error "Violation of PRIMARY KEY constraint 'PK_HangFire_Hash'" when manually trigger job from the dashboard

Question

We recently encountered an issue with Hangfire. Our project is utilizing the following package versions:

Hangfire.AspNetCore: 1.8.0
Hangfire.Core: 1.8.0
Hangfire.SqlServer: 1.8.0

The problem arises when attempting to manually trigger a job from the dashboard, and we occasionally encounter the error provided below. It appears to be related to a unique constraint issue, though we are uncertain about the root cause.

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_HangFire_Hash'. Cannot insert duplicate key in object 'HangFire.Hash'. The duplicate key value is (recurring-job:IWorkOrderBackgroundService.WatchInProgressWorkOrders, LastExecution).
Violation of PRIMARY KEY constraint 'PK_HangFire_Hash'. Cannot insert duplicate key in object 'HangFire.Hash'. The duplicate key value is (recurring-job:IWorkOrderBackgroundService.WatchInProgressWorkOrders, NextExecution).
Violation of PRIMARY KEY constraint 'PK_HangFire_Hash'. Cannot insert duplicate key in object 'HangFire.Hash'. The duplicate key value is (recurring-job:IWorkOrderBackgroundService.WatchInProgressWorkOrders, LastJobId).
Violation of PRIMARY KEY constraint 'PK_HangFire_Set'. Cannot insert duplicate key in object 'HangFire.Set'. The duplicate key value is (recurring-jobs, IWorkOrderBackgroundService.WatchInProgressWorkOrders).
The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, 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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.Data.SqlClient.SqlCommandSet.ExecuteNonQuery() at Hangfire.SqlServer.SqlCommandBatch.ExecuteNonQuery() at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.b__17_0(DbConnection connection, DbTransaction transaction) at Hangfire.SqlServer.SqlServerStorage.<>c__DisplayClass39_0.b__0(DbConnection connection, DbTransaction transaction) at Hangfire.SqlServer.SqlServerStorage.<>c__DisplayClass40_01.<UseTransaction>b__0(DbConnection connection) at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](DbConnection dedicatedConnection, Func2 func) at Hangfire.SqlServer.SqlServerStorage.UseTransaction[T](DbConnection dedicatedConnection, Func3 func, Nullable1 isolationLevel) at Hangfire.SqlServer.SqlServerStorage.UseTransaction(DbConnection dedicatedConnection, Action`2 action) at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.Commit() at Hangfire.RecurringJobManager.TriggerJob(String recurringJobId) at Hangfire.RecurringJobManager.Trigger(String recurringJobId) at Hangfire.Dashboard.DashboardRoutes.<>c.<.cctor>b__4_27(IRecurringJobManager manager, String jobId) at Hangfire.Dashboard.RouteCollectionExtensions.<>c__DisplayClass4_0.b__0(DashboardContext context, String jobId) at Hangfire.Dashboard.BatchCommandDispatcher.Dispatch(DashboardContext context) at Hangfire.Dashboard.AspNetCoreDashboardMiddleware.Invoke(HttpContext httpContext) at

Answer 1

Check if the IGNORE_DUP_KEY feature is set on the tables, see the install script for reference:

https://github.com/HangfireIO/Hangfire/blob/master/src/Hangfire.SqlServer/Install.sql

    -- SqlServerStorageOptions.UseIgnoreDupKeyOption will yield much better results with INSERT/UPDATE operators
    -- instead of MERGE for [Set] and [Hash] tables. This change is also compatible with older clients, since
    -- MERGE operator is used there, so those clients are forward-compatible with these changes.
    ALTER TABLE [$(HangFireSchema)].[Set] REBUILD WITH (IGNORE_DUP_KEY = ON);
    PRINT 'Enabled IGNORE_DUP_KEY option for [$(HangFireSchema)].[Set] table';

    ALTER TABLE [$(HangFireSchema)].[Hash] REBUILD WITH (IGNORE_DUP_KEY = ON);
    PRINT 'Enabled IGNORE_DUP_KEY option for [$(HangFireSchema)].[Hash] table';