Hangfire.SqlServer.CountersAggregator: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"

[dazbradbury]

After enabling logging I'm seeing the following error:

Error occurred during execution of 'Hangfire.SqlServer.CountersAggregator' process. Execution will be retried (attempt #11) in 00:01:51 seconds.:[SqlException][Hangfire: Hangfire.SqlServer.CountersAggregator] : Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   at Hangfire.SqlServer.CountersAggregator.<>c__DisplayClass6_0.<Execute>b__0(DbConnection connection)
   at Hangfire.SqlServer.SqlServerStorage.<>c__DisplayClass19_0.<UseConnection>b__0(DbConnection connection)
   at Hangfire.SqlServer.SqlServerStorage.UseConnection[T](Func`2 func)
   at Hangfire.SqlServer.SqlServerStorage.UseConnection(Action`1 action)
   at Hangfire.SqlServer.CountersAggregator.Execute(CancellationToken cancellationToken)
   at Hangfire.Server.ServerProcessExtensions.Execute(IServerProcess process, BackgroundProcessContext context)
   at Hangfire.Server.AutomaticRetryProcess.Execute(BackgroundProcessContext context) : 

Should I be modifying my table manually to fix the collation conflict, or is this something the Hangfire SQL scripts should be doing?

Configuration

• Hangfire Core 1.6.12
• with Hangfire.SqlServer
• with default SqlServerStorageOptions
• no custom job filters

[dazbradbury]

OK - so I tried changing the [HangFire].[Counter].[Key] collation from Latin1_General_CI_AS -> SQL_Latin1_General_CP1_CI_AS, but to no avail. Still the same exception. As an alternative to altering the DB, you could force the collation type on any comparisons that might be causing this issue:

http://stackoverflow.com/a/1607725/892397

As an aside, I noticed the Counter table currently has 17,761,615 rows! Is this expected? I'd guess I could remove a fair number manually, but could there be a process managing this?

Perhaps as I've followed an upgrade path from an old Hangfire version something has been lost / misconfigured? I'm hesitant to move to a new DB for fear of losing jobs. If you have a DB migration strategy, I'd also happily try that too.

Thanks again!

[burningice2866]

@dazbradbury its the job of the CountersAggregator process to manage the number of records in the Counter table, so i guess that's expected to see the number of records rising as long as the CountersAggregator can't run.

[dazbradbury]

@burningice2866 / @odinserj - understood. Any idea what is causing the collation conflict so I can get the process to run without error?

[dazbradbury]

thanks @odinserj! - any chance you have a temp fix I can deploy? My error logs are filling up, and I'd rather not disable logging altogether given I still have the following outstanding: https://github.com/HangfireIO/Hangfire/issues/851

If you have an inkling which column is causing the issue I'll happily manually update the collation.

[odinserj]

@dazbradbury, if not too late, you can change the collation manually. Hangfire's migration scripts doesn't specify any collation. If you created your database with one collation, then changed it to another one, and then ran migration from schema V3 to V4, then you might cause issues like this. So looks like there's need to manually specify collation for some of the columns. Since we can't do it in patch releases, I'll bump it to 1.7.0

[dazbradbury]

@odinserj OK - happy to change manually as I tried before, but it seemed it didn't make a difference.

Which columns need to be altered? And what collation is expected? It seemed hard to find out from the error message, and my best guess didn't make a difference.

[odinserj]

Oh, I've found another reason, but it can be changed only in 1.7.0 too. Looks like your tempdb has another default collation. You need to set the same collation for [AggregatedCounter].[Key] and [Counter].[Key] – CountersAggregator creates a temporary table.

[dazbradbury]

Thanks @odinserj! I set both to default collation and now the CountersAggregator is slowly deleting the 20m+ rows that had built up. 👍

[odinserj]

Great, thanks for your patience! A new migration is coming for 1.7.0 (#898), I'll find a way how to handle this issue.

[drauch]

I ran into the same problem after migrating from MS SQL Server to Azure SQL. However, I can't simply change the collation because some indices rely on the column which prevents me from editing the collation.

How to workaround that?

@dazbradbury Did you run into the same problem? How did you workaround it?

[dazbradbury]

I was able to set them to default collation without any issues, sorry. Could you drop the indices briefly while the change is made?

[drauch]

Hmpf, yes,..

I created backup-create-scripts for the indices, dropped them, altered the columns and then recreated the indices. Had to do that for 20 columns. Sucked.

[odinserj]

I've added COLLATE DATABASE_DEFAULT to avoid this behavior in 1.7.0. If somebody needs this in 1.6.X, just let me know and I'll backport it.