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.