Intermittent publishing issue after upgrading from Sitecore Publishing Service 5.x to 7.x

Background

Over the past few months, we've been diligently working on upgrading our platform from version 10.1.1 to 10.3.1. If you are using Publishing Service, you are required to upgrade to upgrade it to version 7.x per Sitecore's compatibility article: https://support.sitecore.com/kb?id=kb_article_view&sysparm_article=KB0761308

The Problem

We followed the upgrade steps very carefully, however we noticed that if we did a big related item publish (home related and children) the following error was thrown, and the publish would fail:

[Error] Error in the "VariantsRelatedNodesTargetProducer"
System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass8_0`1.<b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.ExecuteAsync[T](DbConnection connection, Func`1 sqlWork, Func`3 commandRetryPolicy, CancellationToken cancellationToken)
at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.ExecuteAsync[T](Func`2 dbWork)
at Sitecore.Framework.Publishing.Data.Classic.ClassicItemRelationshipRepository.GetAllRelationships(String source, IReadOnlyCollection`1 uris, IReadOnlyCollection`1 outFieldIdsWhitelist, IReadOnlyCollection`1 inFieldIdsWhitelist, Predicate`1 outRelPostFilter, Predicate`1 inRelPostFilter)
at Sitecore.Framework.Publishing.ManifestCalculation.PublishCandidateSource.GetRelatedNodes(IReadOnlyCollection`1 locators, Boolean includeRelatedContent, Boolean includeClones)
at Sitecore.Framework.Publishing.ManifestCalculation.VariantsRelatedNodesTargetProducer.ProcessCandidatesBatch(IList`1 locators)

The failure with error would only happen during large publishes of related items. Smaller page level publishes were perfectly fine.

After digging deeper into the logs, we noticed lot of OutOfMemory exceptions. All System.OutOfMemoryException instances were occuring when either creating a dictionary collection with certain capacity or when resizing dictionary collection to add more elements:

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
   at System.Collections.Generic.Dictionary`2.Initialize(Int32 capacity)
   at System.Collections.Generic.Dictionary`2..ctor(Int32 capacity, IEqualityComparer`1 comparer)

--------------------------------------------

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.

   at System.Collections.Generic.Dictionary`2.Resize(Int32 newSize, Boolean forceNewHashCodes)

   at System.Collections.Generic.Dictionary`2.Resize()

   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)

The Fix

When Publishing Service runs as a 32-bit application a large publish will produce an OutOfMemory exception when populating a dictionary as it reaches virtual memory limit very early. 

Even though the App Service in the Azure portal was set to 64 bit, we had to modify the Publishing Service web.config and set the "processPath" setting to "D:\Program Files\dotnet\dotnet.exe" instead of the "dotnet" environment variable.

After making this change, an App Service restart is required.

That's it! Back in business with those big publishes!

Read More

Missing language versions of items after database cleanup during upgrade

Background

Over the past few months, we've been diligently working on upgrading our system from version 10.1.1 to 10.3.1. However, during this process, a significant issue came to our attention. It became evident that certain language versions had been inadvertently removed from some of our items.

To clarify, the database prior to the upgrade did indeed contain these language versions, but after the upgrade, they were conspicuously absent. The specific item in question, which prompted this issue, lacked language versions in Danish (da-dk).

When examining the user interface (UI), we observed that the items were missing the expected language versions. Additionally, a verification revealed that these language versions were missing from the VersionedFields table in the master database.

We meticulously followed the instructions outlined in the upgrade guide and identified two potentially problematic actions within it:

1. The Sitecore.UpdateApp tool

2. The clean-up operation for databases in the control panel

This led us to conclude that one of these actions must have been the culprit behind the issue.

Root Cause

After some investigation, I pinpointed the root cause as the database clean-up operation, which unintentionally removed the item versions.

Going deeper into the issue, I noticed a disparity in the letter casing. As indicated in the screenshot, the regional code and name for Danish language appeared as "da-dk" with "dk" in lowercase, whereas all other languages were in uppercase after the dash. I also observed that in the database, the removed item version had the language set to "da-DK."

The problem arose because the language name was set as "da-dk," but actually created entries in the VersionedFields table with "da-DK." Subsequently, when running the database clean-up, it mass-deleted all entries in the VersionedFields table that did not match "da-dk." This behavior is inherent to the system and beyond user control.

The issue is compounded by the absence of validation when creating a new language. It's remarkably easy for someone to input their preference without adhering to the proper letter casing. A simple change to lowercase can inadvertently lead to this significant problem.

Conclusion

As of now, this issue is marked as a bug, and we anticipate that it will be addressed in a future release. Until a fix is available, it's essential to remain vigilant for this particular issue.

In the meantime, you can use a SQL update command like the one below to rectify your language setting:

UPDATE [XP0.103d_Master].[dbo].[Items]

SET [Name] = 'da-DK'

WHERE [ID] LIKE '%B8DDF4A0-0DDD-4BC7-B6D9-FCA8C38FB740%'

Please note that this SQL command can be used as a temporary solution until the bug is officially resolved in a subsequent release.

Read More