[Suggestion] Increasing nvarchar column sizes to prevent a database truncation error
Hi friends,
We're encountering a database truncation error that's causing our Kentico 13 media library files migration process to Content Hub assets to fail. The issue appears to be related to column size limitations in the Xperience by Kentico CMS tables.
Error Details:
String or binary data would be truncated in table 'demo.dbo.CMS_ContentItemLanguageMetadata', column 'ContentItemLanguageMetadataDisplayName'.
Truncated value: '2_3_9_blog_article_rebuilding_a_content_large_engineering_lead_practice_starting_with_existing_staff_and_more_even_36'
The statement has been terminated.
Proposed Solution: I'd like to suggest increasing the size of the following NVarchar columns from their current limit of 100 characters:
- CMS_ContentItemLanguageMetadata table:
ContentItemLanguageMetadataDisplayName
column
- CMS_ContentItem table:
ContentItemName
column
Recommendation: Increase both columns from NVARCHAR(100)
to NVARCHAR(200)
This would provide sufficient space for longer content names and metadata display names, especially for those large names that were allowed previously in Kentico 13 which often exceed the current 100-character limit.
So we're clearly hitting the current limit. Doubling the size to 200 characters would provide a comfortable buffer for future content while not significantly impacting database performance.
Maybe include this minor upgrade as part of 30.8.1 or 30.8.2? please
Thanks in advance
Environment
Xperience by Kentico version: 30.8.0
.NET version: 8
Execution environment: Private cloud (Azure/AWS/Virtual machine)
Answers
Hi Victor,
This may be helpful for you: https://github.com/Kentico/xperience-by-kentico-kentico-migration-tool/issues/403
Thanks David! That is what I ended doing as well, truncating the name. Not the ideal, because we have hundreds of file names like these, ideal will be only a few, that is why increasing maybe even 20 or 50 chars more can bring more room not only me but others as well:
2_3_9_blog_article_rebuilding_a_content_large_engineering_lead_practice_starting_with_existing_staff_and_more_even_36
2_3_9_blog_article_rebuilding_a_content_large_engineering_lead_practice_starting_with_existing_staff_and_more_even_1
2_3_9_blog_article_rebuilding_a_content_large_engineering_lead_practice_starting_with_existing_staff_and_more_even_2
2_3_9_blog_article_rebuilding_a_content_large_engineering_lead_practice_starting_with_existing_staff_and_more_even-3
2_3_9_blog_article_rebuilding_a_content_large_engineering_lead_practice_starting_with_existing_staff_and_more_even-4
....
So, when we truncate them, we cannot make a correct naming convention for those because of different sizes and last characters of the string.
So, at the end we will force the editors to rename the hundreds of file names that they added to the Kentico 13 media library.
Maybe try using AI, like Milan Lund did in the upgrade he was working on.
You could tell the LLM the constraints for the name length and have it rewrite them to some shorter convention.
This could either be done as a .csv populated by Gemini or ChatGPT and then used during your data migration, or you could call the AI API at migration time, transforming each name one at a time.
We can't casually make a column size change because that will definitely have performance impacts on all the existing XbyK projects (and all future XbyK projects). The media library in KX13 isn't the "standard" we want to support.
If you need to make these assets easier to find in the Content hub, then consider using a dedicated "media" taxonomy that includes tags to categorize the content items - these can be used for organization in the Content hub only and don't need to be exposed to visitors.
You can also use content folders, smart folders, and usage tracking to improve organization and discovery.
oh! That is true! I totally forgot about the smart folders along with tags, I think I can use them to identify the truncated ones and so we can easily update them.
And it makes sense about the db column size, not a problem :)
Thanks Sean for the recommendation.
To answer this question, you have to login first.