Hangfire keeps breaking after awhile with this exception. It helps to know that the there one hangfire database for multiple servers

bug?
, ,
1

Log Name: Application
Source: .NET Runtime
Date: 08/04/2023 14:00:50
Event ID: 1000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: WIN-xxxxxxxxxx
Description:
Category: Hangfire.Processing.BackgroundExecution
EventId: 0

Execution RecurringJobScheduler is in the Failed state now due to an exception, execution will be retried no more than in 00:00:25

Exception:
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint “FK_HangFire_State_Job”. The conflict occurred in database “hangfire”, table “HangFire.Job”, column ‘Id’.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Hangfire.SqlServer.SqlCommandSet.ExecuteNonQuery() at Hangfire.SqlServer.SqlCommandBatch.ExecuteNonQuery() at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.<Commit>b__12_0(DbConnection connection, DbTransaction transaction) at Hangfire.SqlServer.SqlServerStorage.<>c__DisplayClass34_0.<UseTransaction>b__0(DbConnection connection, DbTransaction transaction) at Hangfire.SqlServer.SqlServerStorage.<>c__DisplayClass35_01.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, Action2 action)
at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.Commit()
at Hangfire.Server.RecurringJobScheduler.ScheduleRecurringJob(BackgroundProcessContext context, IStorageConnection connection, String recurringJobId, RecurringJobEntity recurringJob, DateTime now)
at Hangfire.Server.RecurringJobScheduler.TryEnqueueBackgroundJob(BackgroundProcessContext context, IStorageConnection connection, String recurringJobId, DateTime now)
at Hangfire.Server.RecurringJobScheduler.<>c__DisplayClass18_0.b__0(IStorageConnection connection)
at Hangfire.Server.RecurringJobScheduler.UseConnectionDistributedLock[T](JobStorage storage, Func2 action) at Hangfire.Server.RecurringJobScheduler.EnqueueNextRecurringJobs(BackgroundProcessContext context) at Hangfire.Server.RecurringJobScheduler.Execute(BackgroundProcessContext context) at Hangfire.Server.BackgroundProcessDispatcherBuilder.ExecuteProcess(Guid executionId, Object state) at Hangfire.Processing.BackgroundExecution.Run(Action2 callback, Object state)
ClientConnectionId:5ad95f84-d0a6-453b-8316-2bc3987290e4
Error Number:547,State:0,Class:16

Event Xml:



1000
2
0
0x80000000000000

1004514
Application
WIN



Category: Hangfire.Processing.BackgroundExecution
EventId: 0

Execution RecurringJobScheduler is in the Failed state now due to an exception, execution will be retried no more than in 00:00:25

Exception:
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint “FK_HangFire_State_Job”. The conflict occurred in database “hangfire”, table “HangFire.Job”, column ‘Id’.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Hangfire.SqlServer.SqlCommandSet.ExecuteNonQuery() at Hangfire.SqlServer.SqlCommandBatch.ExecuteNonQuery() at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.&lt;Commit&gt;b__12_0(DbConnection connection, DbTransaction transaction) at Hangfire.SqlServer.SqlServerStorage.&lt;&gt;c__DisplayClass34_0.&lt;UseTransaction&gt;b__0(DbConnection connection, DbTransaction transaction) at Hangfire.SqlServer.SqlServerStorage.&lt;&gt;c__DisplayClass35_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, Action2 action)
at Hangfire.SqlServer.SqlServerWriteOnlyTransaction.Commit()
at Hangfire.Server.RecurringJobScheduler.ScheduleRecurringJob(BackgroundProcessContext context, IStorageConnection connection, String recurringJobId, RecurringJobEntity recurringJob, DateTime now)
at Hangfire.Server.RecurringJobScheduler.TryEnqueueBackgroundJob(BackgroundProcessContext context, IStorageConnection connection, String recurringJobId, DateTime now)
at Hangfire.Server.RecurringJobScheduler.<>c__DisplayClass18_0.<EnqueueNextRecurringJobs>b__0(IStorageConnection connection)
at Hangfire.Server.RecurringJobScheduler.UseConnectionDistributedLock[T](JobStorage storage, Func2 action) at Hangfire.Server.RecurringJobScheduler.EnqueueNextRecurringJobs(BackgroundProcessContext context) at Hangfire.Server.RecurringJobScheduler.Execute(BackgroundProcessContext context) at Hangfire.Server.BackgroundProcessDispatcherBuilder.ExecuteProcess(Guid executionId, Object state) at Hangfire.Processing.BackgroundExecution.Run(Action2 callback, Object state)
ClientConnectionId:5ad95f84-d0a6-453b-8316-2bc3987290e4
Error Number:547,State:0,Class:16


2

Please upgrade your schema to the latest version, see Upgrading to Hangfire 1.7 — Hangfire Documentation for details.

3

I dont know if it helps but ihave been using hangfire 1.7.25 for close to a year now and never once did i see this issue. I just recently switched to the latest stable 1.7.34 do i have to upgrade the schema still?.

4

This problem relates more to database – previously int type was used instead of the bigint type, so it was possible to overflow the job is field when a lot of jobs processed. Newest schemas don’t contain this problem.

5

Oh sorry, you have completely other problem, needs more investigation. Can you downgrade to the previous version meanwhile?

6

Can you please execute the following queries to understand whether we are running out of the values for the int field and to understand what’s your current schema version?

SELECT TOP (1) Id FROM [HangFire].[Job] ORDER BY [ID] DESC
SELECT TOP (1) Id FROM [HangFire].[State] ORDER BY [ID] DESC
SELECT * FROM [HangFire].[Schema]
7

So in continuation i noticed that the schema was not matching hangfire version and I corretcted it using the migation script provided. Its now [HangFire].[Schema] v7 it was v5. The values of the selects provided are as

SELECT TOP (1) Id FROM [HangFire].[Job] ORDER BY [ID] DESC = 1254933
SELECT TOP (1) Id FROM [HangFire].[State] ORDER BY [ID] DESC =8692563
SELECT * FROM [HangFire].[Schema] 7

Now the recurring jobs have ERROR on the next execution column.If I trigger they run and sometime they dont.

Is there a way to add errored jobs back to normal incase a solution ia found

8

But after a month the issue recurs though some recurring jobs succeed

9

So after looking around the database everything was bigint. The problem is identity seed was reset to value less than the max id in table hangfire.Job. Setting it to value higher than greatest Id fixed it.

Not sure if it’s permanent fix but I’ll monitor