Umbraco 8 - SQL Server 2022 NuCache Error Using JSON Serializer

[justin-nevitech]

Prerequisites

• I have added steps to test this contribution in the description below

Description

When using the latest version of Umbraco 8 on SQL Server 2022, there is an error when rebuilding the NuCache database cache when the serializer is set JSON and not MsgPack in the web.config (which older sites most likely will be). This can happen from the Settings > Published Status screen in the back-office or when booting, making the site error completely and never load. If you are able to login to Umbraco the same error can also happen when saving document types.

The following exception is thrown:

-> Umbraco.Core.Exceptions.BootFailedException: Boot failed.

-> System.Data.SqlClient.SqlException: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
  at System.Data.SqlClient.SqlConnection.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, Boolean shouldCacheForAlwaysEncrypted)
  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 StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 284
  at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteNonQuery>b__31_0() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\FaultHandling\RetryDbConnection.cs:line 209
  at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.<>c__DisplayClass33_0`1.<Execute>b__0() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\FaultHandling\RetryDbConnection.cs:line 222
  at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\FaultHandling\RetryPolicy.cs:line 174
  at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.Execute[T](Func`1 f) in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\FaultHandling\RetryDbConnection.cs:line 219
  at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteNonQuery() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\FaultHandling\RetryDbConnection.cs:line 209
  at Umbraco.Core.Persistence.NPocoDatabaseExtensions.BulkInsertRecordsWithCommands[T](IUmbracoDatabase database, T[] records) in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\NPocoDatabaseExtensions-Bulk.cs:line 107
  at Umbraco.Core.Persistence.NPocoDatabaseExtensions.BulkInsertRecords[T](IUmbracoDatabase database, IEnumerable`1 records, Boolean useNativeBulkInsert) in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Persistence\NPocoDatabaseExtensions-Bulk.cs:line 89
  at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.RebuildContentDbCacheLocked(IContentCacheDataSerializer serializer, IScope scope, Int32 groupSize, IEnumerable`1 contentTypeIds) in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Web\PublishedCache\NuCache\PublishedSnapshotService.cs:line 1549
  at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.Rebuild() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Web\PublishedCache\NuCache\PublishedSnapshotService.cs:line 1474
  at Umbraco.Web.PublishedCache.NuCache.NuCacheSerializerComponent.RebuildDatabaseCacheIfSerializerChanged() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Web\PublishedCache\NuCache\NuCacheSerializerComponent.cs:line 60
  at Umbraco.Web.PublishedCache.NuCache.NuCacheSerializerComponent.Initialize() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Web\PublishedCache\NuCache\NuCacheSerializerComponent.cs:line 37
  at Umbraco.Core.Composing.ComponentCollection.Initialize() in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Composing\ComponentCollection.cs:line 32
  at Umbraco.Core.Runtime.CoreRuntime.Boot(IRegister register, DisposableTimer timer) in D:\My Documents\Projects\Open Source\Umbraco-CMS\src\Umbraco.Core\Runtime\CoreRuntime.cs:line 219

This works perfectly fine on SQL Server 2019 but not SQL Server 2022.

1. Make sure you are using SQL Server 2022
2. Comment out the following line in the web.config:
   <add key="Umbraco.Web.PublishedCache.NuCache.Serializer" value="MsgPack"/>
3. Run Umbraco and try and rebuild the database cache from settings. If it won't let you get that far it will fail at boot with the error described above.

SQL Server 2019 Express (15.0.4312) - The error does not occur.
SQL Server 2022 Express (16.0.4025) - The error occurs as described above.

I have tested the fix on both SQL Server 2019 and SQL Server 2022 and they both work fine.

There are 3 failing tests but none of them are due to these changes.

This is quite a fundamental issue so I think it really needs merging.

[github-actions]

Hi there @justin-nevitech, thank you for this contribution! 👍

While we wait for one of the Core Collaborators team to have a look at your work, we wanted to let you know about that we have a checklist for some of the things we will consider during review:

• It's clear what problem this is solving, there's a connected issue or a description of what the changes do and how to test them
• The automated tests all pass (see "Checks" tab on this PR)
• The level of security for this contribution is the same or improved
• The level of performance for this contribution is the same or improved
• Avoids creating breaking changes; note that behavioral changes might also be perceived as breaking
• If this is a new feature, Umbraco HQ provided guidance on the implementation beforehand
• 💡 The contribution looks original and the contributor is presumably allowed to share it

Don't worry if you got something wrong. We like to think of a pull request as the start of a conversation, we're happy to provide guidance on improving your contribution.

If you realize that you might want to make some changes then you can do that by adding new commits to the branch you created for this work and pushing new commits. They should then automatically show up as updates to this pull request.

Thanks, from your friendly Umbraco GitHub bot 🤖 🙂

[justin-nevitech]

Apologies if this is the wrong base branch to use, let me know if I need to change it.

[mikecp]

Thanks @justin-nevitech for spotting and fixing this.

Someone from the team will have a look at it soon. Regarding the branch, I'm not sure but @nul800sebastiaan will certainly be able to help 😁

Cheers!

[justin-nevitech]

Just to add a bit more context, when the NuCache is rebuilt using the JSON serializer, the binary raw data is null. For a null value, NPoco assumes it is a string and creates an NVARCHAR(4000) parameter which causes this error.

The solution is to ensure the parameter is a VARBINARY(MAX) for a null raw data. This must be a change in SQL Server as it worked in 2019.

I tried adding an NPoco mapper but this did not get called as the value was null.

A workaround is to change the serialiser to MsgPack in the web.config.

[nul800sebastiaan]

Thanks @justin-nevitech I've left a few comments.

I don't see any build errors (the CodeScene ones are just warnings, no need to look at those) so everything is completely fine!

The branch is also correct 👍

[ElisabethHansen]

Hi! I have run into this issue on a few clients that are moving to a new hosting solution, which involves upgrading our SQL server to 2022. Is there anything we can do to help implement this solution in Umbraco? :)

[justin-nevitech]

Hi! I have run into this issue on a few clients that are moving to a new hosting solution, which involves upgrading our SQL server to 2022. Is there anything we can do to help implement this solution in Umbraco? :)

Hi Elisabeth,

If you add/change the serializer in your web.config to the following as a work-around it should work.

<add key="Umbraco.Web.PublishedCache.NuCache.Serializer" value="MsgPack"/>

Regards,
Justin

[nul800sebastiaan]

Thanks again @justin-nevitech and sorry for the delay, this is good to go now. That said, I am unsure when we'll create a new release of v8, but at least there's a workaround for now.

Thanks!

[si25]

Hi @justin-nevitech @nul800sebastiaan,

I'm running into the issue on Umbraco 8.18.8 but I don't see this line in the web.config

Just to confirm do I need to add this into the config to fix the issue? And will it have any adverse effects on Umbraco Cloud hosting?

(sry if this is a question with an obvious answer, pretty new to this side of Umbraco)

Thanks,
Simon

[justin-nevitech]

Hi @si25

Yes, you will need to add the following to your web.config:

<add key="Umbraco.Web.PublishedCache.NuCache.Serializer" value="MsgPack"/>

I've not noticed any issues doing this on my local development machine but I've not had to do it on any production sites or Umbraco Cloud. Maybe try it on a staging copy of the site first just to be sure. I don't know what version of SQL Server is used on Umbraco Cloud as to whether this is a problem or not.

Regards,
Justin

[si25]

Thanks @justin-nevitech!

[keithlfs]

Just upgraded my developer workstation and ran straight into this issue, took a while to find this link. Is there any information available on what effects the 'set Serialiser to MsgPack` solution has? I wasn't able to find any reference documentation pertaining to Umbraco v8. Not sure if we need/want this setting in production.

[justin-nevitech]

Hi @keithlfs

It mentions here what the MsgPack change was:

https://umbraco.com/blog/umbraco-815-release/

I believe it was the default from 8.15 onwards, so you should be ok to use it as far as I am aware.