search

HangfireIO / Hangfire

An easy way to perform background job processing in .NET and .NET Core applications. No Windows Service or separate process required
https://www.hangfire.io
Other
9.43k stars 1.7k forks source link

Hangfire server Exception when upgrade to version 1.8.15 "'Must declare the scalar variable "@key"" #2465

Closed Kamagigan closed 1 week ago

Kamagigan commented 2 weeks ago

Hello,

We have an hangfire server running inside a console App in .Net 8 connected to a SQL Server with hangfire database schema version 9 in it. We use the following Nuget packages :

With the version 1.8.14 of hangfire, everything runs fine with no errors. We can enqueue jobs, hangfire server process it and if there is an exception in jobs processing, we have the AutomaticRetry attribute to retry these jobs.

When the package version 1.8.15 have been publish, we have updgraded it in our application and since there, each time we start the server and wait around 30 seconds we got two same exceptions (full exception stack in the end) :

Execution RecurringJobScheduler is in the Failed state now due to an exception, execution will be retried in no more than 00:00:25 Microsoft.Data.SqlClient.SqlException (0x80131904): La variable scalaire "@key" doit être déclarée. (Must declare the scalar variable "@key)

After these exceptions, Hangfire keep running. If a job is enqueued in hangfire db, the server process it with success. But if an exception occurs during the job proccessing, Hangfire is unable to retry this job and it stay in SCHEDULED state forever. and we must renqueue manually the job to retry it.

For troubleshooting this issue, i tried to reduce the options used to isolate and find one probematic with no result. I tried to recreate the hangfire database still with no result.

Finally, i tried to rollback to the version 1.8.14 and everything resume to working fine with no exceptions and retries working fine.

Do you have any ideas, how to resolve this issue ?

I have attached a log exemple : hangfire-server-exception.txt

Best Regards

Full Exception : 

2024-11-07 08:45:35.276 +01:00 [ERR] Execution RecurringJobScheduler is in the Failed state now due to an exception, execution will be retried in no more than 00:00:25
Microsoft.Data.SqlClient.SqlException (0x80131904): La variable scalaire "@key" doit être déclarée.
   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.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   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, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1156
   at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1184
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 815
   at Hangfire.SqlServer.SqlServerConnection.<>c.<GetFirstByLowestScoreFromSet>b__18_0(SqlServerStorage storage, DbConnection connection, KeyValuePair`2 pair) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerConnection.cs:line 415
   at Hangfire.SqlServer.SqlServerStorage.UseConnection[TContext,TResult](DbConnection dedicatedConnection, Func`4 func, TContext context) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerStorage.cs:line 279
   at Hangfire.SqlServer.SqlServerConnection.GetFirstByLowestScoreFromSet(String key, Double fromScore, Double toScore, Int32 count) in C:\projects\hangfire-525\src\Hangfire.SqlServer\SqlServerConnection.cs:line 410
   at Hangfire.Server.RecurringJobScheduler.<>c__DisplayClass27_0.<EnqueueNextRecurringJobs>b__0(IStorageConnection connection) in C:\projects\hangfire-525\src\Hangfire.Core\Server\RecurringJobScheduler.cs:line 216
   at Hangfire.Server.RecurringJobScheduler.UseConnectionDistributedLock[T](JobStorage storage, Func`2 action) in C:\projects\hangfire-525\src\Hangfire.Core\Server\RecurringJobScheduler.cs:line 425
   at Hangfire.Server.RecurringJobScheduler.EnqueueNextRecurringJobs(BackgroundProcessContext context) in C:\projects\hangfire-525\src\Hangfire.Core\Server\RecurringJobScheduler.cs:line 203
   at Hangfire.Server.RecurringJobScheduler.Execute(BackgroundProcessContext context) in C:\projects\hangfire-525\src\Hangfire.Core\Server\RecurringJobScheduler.cs:line 176
   at Hangfire.Server.BackgroundProcessDispatcherBuilder.ExecuteProcess(Guid executionId, Object state) in C:\projects\hangfire-525\src\Hangfire.Core\Server\BackgroundProcessDispatcherBuilder.cs:line 82
   at Hangfire.Processing.BackgroundExecution.Run(Action`2 callback, Object state) in C:\projects\hangfire-525\src\Hangfire.Core\Processing\BackgroundExecution.cs:line 118
ClientConnectionId :c006cc8e-dbad-4aeb-a52f-e1681691470f
Error Number :137,State :2,Class :15
odinserj commented 2 weeks ago

Thanks a lot for reporting this! For some reason Dapper is unable to fetch the parameter name correctly from the pair.Key property, leading to error.

https://github.com/HangfireIO/Hangfire/blob/18e5935dab600d3db5e21256d28f1c87cd37db44/src/Hangfire.SqlServer/SqlServerConnection.cs#L417

However, it looks like it works fine in one environments, and doesn't in others, because tests never failed on my machines or on build server ones. I tried to also check with the newest Microsoft.Data.SqlClient package, but all the tests also passed. I will submit the fix as soon as possible regardless of reproduction anyway and ensure other parameters are populated explicitly.

odinserj commented 1 day ago

By the way, do you use trimming in your application, e.g. whether you have <IsTrimmable>true</IsTrimmable> in your csproj file?