Umbraco-CMS: System.Data.SqlClient.SqlException: String or binary data would be truncated in table 'Umbraco.dbo.umbracoLog',

We have added a custom component that creates and saves pages on all languages that have english as fallback, to help the editor create default pages. But occasionally this fails with an audit log.

Here is the stack trace:

System.Data.SqlClient.SqlException: String or binary data would be truncated in table 'Umbraco.dbo.umbracoLog', column 'parameters'. Truncated value: 'English (United Kingdom), English, Spanish (Spain), Estonian (Estonia), Finnish (Finland), French (B'. The statement has been terminated.

Stacktrace

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.SqlDataReader.TryConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteScalar()

   at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

   at NPoco.Database.ExecuteScalarHelper(DbCommand cmd)

   at NPoco.Database.InsertImp[T](PocoData pocoData, String tableName, String primaryKeyName, Boolean autoIncrement, T poco)

   at NPoco.Database.Insert[T](String tableName, String primaryKeyName, Boolean autoIncrement, T poco)

   at NPoco.Database.Insert[T](T poco)

   at Umbraco.Core.Persistence.Repositories.Implement.AuditRepository.PersistUpdatedItem(IAuditItem entity)

   at Umbraco.Core.Services.Implement.ContentService.Save(IContent content, Int32 userId, Boolean raiseEvents)

   at Umbraco.Web.Editors.ContentController.SaveAndNotify(ContentItemSave contentItem, Func`2 saveMethod, Int32 variantCount, Dictionary`2 notifications, SimpleNotificationModel globalNotifications, String invariantSavedLocalizationKey, String variantSavedLocalizationKey, String cultureForInvariantErrors, Boolean& wasCancelled)

   at Umbraco.Web.Editors.ContentController.PostSaveInternal(ContentItemSave contentItem, Func`2 saveMethod, Func`2 mapToDisplay)

   at lambda_method(Closure , Object , Object[] )

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters)

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

  at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()

Umbraco version

I am seeing this issue on Umbraco version: 8.5.5

Reproduction

Create a website with a lot of langauges that fallback to English. Add this component and try to save a new english page.

Here is the custom component:

public class CustomEditorComponent : IComponent
    {
        private readonly ILocalizationService _localizationService;

        public CustomEditorComponent(ILocalizationService localizationService)
        {
            _localizationService = localizationService;
        }

        public void Initialize()
        {
            ContentService.Saving += ContentServiceOnSaving;
        }

        public void Terminate()
        {

        }

        private void ContentServiceOnSaving(IContentService sender, ContentSavingEventArgs args)
        {
            string defaultLanguageIso = _localizationService.GetDefaultLanguageIsoCode();
            string[] englishFallbackLanguages = _localizationService.GetAllLanguages()
                .Select(l => l.CultureInfo.Name)
                .ToArray();

            foreach (IContent content in args.SavedEntities)
            {
                if (content.ContentType.VariesByCulture() && args.IsSavingCulture(content, defaultLanguageIso))
                {
                    string defaultName = content.GetCultureName(defaultLanguageIso);
                    foreach (string culture in englishFallbackLanguages)
                    {
                        if (string.IsNullOrWhiteSpace(content.GetCultureName(culture)))
                        {
                            content.SetCultureName(defaultName, culture);
                        }
                    }
                }
            }
        }
    }

Bug summary

Specifics

Steps to reproduce

Expected result

Actual result

This item has been added to our backlog AB#9875

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 30 (15 by maintainers)

Most upvoted comments

No of course it shouldn’t. We’ll fix!

nul800sebastiaan on Jan 6, 2021

We have just run into this issue as well.

Of course it’s annoying to replicate manually as you need to update say 30+ variants on one node, then save/publish them all in one go. You could argue that people wouldn’t be updating that many different variants without saving more frequently!

However we hit the problem because uSync was publishing a new piece of content with 71 variants to a different environment. So of course one node was being updated for 71 different languages in one go. And this error appears to be preventing uSync from properly creating all the variants. Perhaps Umbraco Deploy might have the same problem if people try pushing content with a large number of languages?

I have implemented the following workaround locally:

ALTER TABLE umbracoLog ALTER COLUMN [parameters] nvarchar(4000) NULL;

I then re-ran the uSync import and this time it all worked fine and all the content variants were created properly.

FYI when Umbraco logs the ‘PublishVariant’ activity as a result of this, the ‘parameters’ column contains:

English (United States), English (United Kingdom), Spanish (Spain), Amharic (Ethiopia), Bulgarian (Bulgaria), Czech (Czech Republic), Danish (Denmark), German (Germany), Greek (Greece), English (Australia), English (Belgium), English (Canada), English (Ghana), English (Ireland), English (India), English (Kenya), English (Nigeria), English (New Zealand), English (Singapore), English (South Africa), Spanish (Chile), Spanish (Colombia), Spanish (Mexico), Spanish (Peru), Spanish (United States), Spanish (Uruguay), Spanish (Bolivarian Republic of Venezuela), Finnish (Finland), French (Belgium), French (Canada), French (Cameroon), French (France), Hindi (India), Croatian (Croatia), Hungarian (Hungary), Indonesian (Indonesia), Italian (Italy), Japanese (Japan), Korean (Korea), Burmese (Myanmar), Dutch (Belgium), Dutch (Netherlands), Norwegian, Polish (Poland), Portuguese, Portuguese (Brazil), Romanian (Romania), Russian (Russia), Swedish (Sweden), Kiswahili (Kenya), Thai (Thailand), Ukrainian (Ukraine), Vietnamese (Vietnam), Chinese (Traditional, Hong Kong S.A.R.), Chinese (Traditional, Taiwan)

Those 71 variants have a character count of 1104.

If we implement this workaround on our production database, and then this column is changed in a future release to a smaller size it sounds like that migration would fail. Which is not ideal. But this is show stopper for us as it is preventing us from being able to push content to production, so not sure we have any choice. So maybe we should up it to 1500 which is big enough for our use case and just hope for the best?!

LottePitcher on Nov 4, 2021

Ah I see! Well, I don’t know what we’re going to do. But I’ll update here when we do. Until then, there’s a small risk we don’t set it to max, for sure! 😅

nul800sebastiaan on Jan 6, 2021

In src\Umbraco.Core\Services\Implement\ContentService.cs line 809, the code actually puts the langs string into both the Message column (max length 4000) and the Parameters column (max length 500).

        public OperationResult Save(IContent content, int userId = Constants.Security.SuperUserId, bool raiseEvents = true)
        {
        ...
            if (culturesChanging != null)
            {
                var langs = string.Join(", ", _languageRepository.GetMany()
                    .Where(x => culturesChanging.InvariantContains(x.IsoCode))
                    .Select(x => x.CultureName));
                Audit(AuditType.SaveVariant, userId, content.Id, $"Saved languages: {langs}", langs); // <-- line 809, where the 'magic happens'
            }
            else
                Audit(AuditType.Save, userId, content.Id);
        ...
        }

@nul800sebastiaan Do we have any reason to fill the data in twice, in the same row?

The reason it fails is because alot of countries are being saved at once, then the logger receives a string longer than 500 characters, generated by the ‘langs’ variable. So this could actually happen, if you created enough language variants at once, without using the component @kimschurmann included.

EDIT: I just tested this without the component, if i have enough languages in Umbraco to surpass total character length of 500 by combining all of the languages, and create a new piece of content, go through all of the variants and give them a name, i get the same error.

kevinstampe on Jan 6, 2021