Upgrade to V17.3 have sql exception

[laputaworkuk]

🐛 Bug Summary

After upgrading Umbraco from v16 or v17.2 to v17.3, the application fails to run due to a SQL Server exception.

🔁 Steps to Reproduce

1. Upgrade an existing Umbraco project from v16 or v17.2 to v17.3
2. Run the application

✅ Expected Result

The application starts successfully without errors.

❌ Actual Result

The application throws an unhandled SQL exception during execution.

📄 Error Message

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): 
The incoming request has too many parameters. The server supports a maximum of 2100 parameters. 
Reduce the number of parameters and resend the request.

   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)

[github-actions]

Hi there @laputaworkuk!

Firstly, a big thank you for raising this issue. Every piece of feedback we receive helps us to make Umbraco better.

We really appreciate your patience while we wait for our team to have a look at this but we wanted to let you know that we see this and share with you the plan for what comes next.

• We'll assess whether this issue relates to something that has already been fixed in a later version of the release that it has been raised for.
• If it's a bug, is it related to a release that we are actively supporting or is it related to a release that's in the end-of-life or security-only phase?
• We'll replicate the issue to ensure that the problem is as described.
• We'll decide whether the behavior is an issue or if the behavior is intended.

We wish we could work with everyone directly and assess your issue immediately but we're in the fortunate position of having lots of contributions to work with and only a few humans who are able to do it. We are making progress though and in the meantime, we will keep you in the loop and let you know when we have any questions.

Thanks, from your friendly Umbraco GitHub bot 🤖 🙂

[AndyButland]

Are you able to share any more of that stack trace please @laputaworkuk? I'm hoping you should see some reference to Umbraco's code so we can see where this is coming from.

[laputaworkuk]

@AndyButland
Thanks for response
please find the detail when I run it

SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)

SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, bool callerHasConnectionLock, bool asyncClose)
Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, string resetOptionsString, bool isInternal, bool forDescribeParameterEncryption, bool shouldCacheForAlwaysEncrypted)
Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, bool isAsync, int timeout, out Task task, bool asyncWrite, bool inRetry, SqlDataReader ds, bool describeParameterEncryptionRequest)
Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, bool returnStream, TaskCompletionSource<object> completion, int timeout, out Task task, out bool usedCache, bool asyncWrite, bool inRetry, string method)
Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource<object> completion, bool sendToPipe, int timeout, out bool usedCache, bool asyncWrite, bool inRetry, string methodName)
Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in ProfiledDbCommand.cs
Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteNonQuery>b__32_0()
Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand+<>c__DisplayClass38_0<T>.<Execute>b__0()
Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction<TResult>(Func<TResult> func)
Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.Execute<T>(Func<T> f)
Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteNonQuery()
NPoco.Database+<>c__DisplayClass338_0.<ExecuteNonQueryHelper>b__0()
NPoco.Database.ExecutionHook<T>(Func<T> action)
NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
NPoco.Database.Execute(string sql, CommandType commandType, object[] args)
NPoco.Database.Execute(Sql Sql)
NPoco.Database.Execute(string sql, object[] args)
NPoco.Linq.DeleteQueryProvider<T>.Execute()
Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.DocumentUrlRepository.Save(IEnumerable<PublishedDocumentUrlSegment> publishedDocumentUrlSegments)
Umbraco.Cms.Core.Services.DocumentUrlService.CreateOrUpdateUrlSegmentsAsync(IEnumerable<IContent> documentsEnumerable)
Umbraco.Cms.Core.Services.DocumentUrlService.RebuildAllUrlsAsync()
Umbraco.Cms.Core.Services.DocumentUrlService.InitAsync(bool forceEmpty, CancellationToken cancellationToken)
Umbraco.Cms.Core.Services.DocumentUrlServiceInitializerNotificationHandler.HandleAsync(UmbracoApplicationStartingNotification notification, CancellationToken cancellationToken)
Umbraco.Cms.Core.Events.INotificationAsyncHandler<TNotification>.HandleAsync(IEnumerable<TNotification> notifications, CancellationToken cancellationToken)
Umbraco.Cms.Core.Events.EventAggregator.PublishCoreAsync<TNotification>(IEnumerable<Func<IEnumerable<TNotification>, CancellationToken, Task>> allHandlers, IEnumerable<TNotification> notifications, CancellationToken cancellationToken)
Umbraco.Cms.Core.Events.NotificationAsyncHandlerWrapperImpl<TNotificationType>.HandleAsync<TNotification, TNotificationHandler>(IEnumerable<TNotification> notifications, CancellationToken cancellationToken, ServiceFactory serviceFactory, Func<IEnumerable<Func<IEnumerable<TNotification>, CancellationToken, Task>>, IEnumerable<TNotification>, CancellationToken, Task> publish)
Umbraco.Cms.Core.Events.EventAggregator.PublishNotificationsAsync<TNotification, TNotificationHandler>(IEnumerable<TNotification> notifications, CancellationToken cancellationToken)
Umbraco.Cms.Core.Events.EventAggregator.PublishAsync<TNotification, TNotificationHandler>(IEnumerable<TNotification> notifications, CancellationToken cancellationToken)
Umbraco.Cms.Core.Events.EventAggregator.PublishAsync<TNotification>(TNotification notification, CancellationToken cancellationToken)
Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken, bool isRestarting)
Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken)
Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__14_1(IHostedService service, CancellationToken token)
Microsoft.Extensions.Hosting.Internal.Host.ForeachService<T>(IEnumerable<T> services, CancellationToken token, bool concurrent, bool abortOnFirstException, List<Exception> exceptions, Func<T, CancellationToken, Task> operation)
Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)

Show raw exception details
Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 263
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteNonQuery>b__32_0()
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<>c__DisplayClass38_0`1.<Execute>b__0()
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.Execute[T](Func`1 f)
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteNonQuery()
   at NPoco.Database.<>c__DisplayClass338_0.<ExecuteNonQueryHelper>b__0()
   at NPoco.Database.ExecutionHook[T](Func`1 action)
   at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
   at NPoco.Database.Execute(String sql, CommandType commandType, Object[] args)
   at NPoco.Database.Execute(Sql Sql)
   at NPoco.Database.Execute(String sql, Object[] args)
   at NPoco.Linq.DeleteQueryProvider`1.Execute()
   at Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement.DocumentUrlRepository.Save(IEnumerable`1 publishedDocumentUrlSegments)
   at Umbraco.Cms.Core.Services.DocumentUrlService.CreateOrUpdateUrlSegmentsAsync(IEnumerable`1 documentsEnumerable)
   at Umbraco.Cms.Core.Services.DocumentUrlService.RebuildAllUrlsAsync()
   at Umbraco.Cms.Core.Services.DocumentUrlService.InitAsync(Boolean forceEmpty, CancellationToken cancellationToken)
   at Umbraco.Cms.Core.Services.DocumentUrlServiceInitializerNotificationHandler.HandleAsync(UmbracoApplicationStartingNotification notification, CancellationToken cancellationToken)
   at Umbraco.Cms.Core.Events.INotificationAsyncHandler`1.HandleAsync(IEnumerable`1 notifications, CancellationToken cancellationToken)
   at Umbraco.Cms.Core.Events.EventAggregator.PublishCoreAsync[TNotification](IEnumerable`1 allHandlers, IEnumerable`1 notifications, CancellationToken cancellationToken)
   at Umbraco.Cms.Core.Events.NotificationAsyncHandlerWrapperImpl`1.HandleAsync[TNotification,TNotificationHandler](IEnumerable`1 notifications, CancellationToken cancellationToken, ServiceFactory serviceFactory, Func`4 publish)
   at Umbraco.Cms.Core.Events.EventAggregator.PublishNotificationsAsync[TNotification,TNotificationHandler](IEnumerable`1 notifications, CancellationToken cancellationToken)
   at Umbraco.Cms.Core.Events.EventAggregator.PublishAsync[TNotification,TNotificationHandler](IEnumerable`1 notifications, CancellationToken cancellationToken)
   at Umbraco.Cms.Core.Events.EventAggregator.PublishAsync[TNotification](TNotification notification, CancellationToken cancellationToken)
   at Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken, Boolean isRestarting)
   at Umbraco.Cms.Infrastructure.Runtime.CoreRuntime.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__14_1(IHostedService service, CancellationToken token)
   at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
ClientConnectionId:d41d35cc-7c97-420d-b2d9-e99ea82e5ce5
Error Number:8003,State:1,Class:16

[AndyButland]

I'm investigating this @laputaworkuk and can see fairly easily where the exception comes from and how to resolve it, which I've done in the PR linked above. I'm trying also to replicate the problem though but struggling to find the correct scenario to trigger it.

Could you let me know a few details about your setup please?

• Do you have a multi-language setup? If so, how many languages? And how is your site organised? Specifically, are you using variant content (i.e. document types that vary by culture) and creating different language versions of your content? Or do you have a invariant content, and root nodes defined with hostname/language settings so you have a different language per root node of the site.
• Do you have any custom IUrlSegmentProvider implementations?

[adavidovic92]

@AndyButland I’m not sure if this is related, but after upgrading Umbraco from 17.2.2 to 17.3.0, our integration tests for notification handlers started freezing. Some tests pass, then execution hangs on one test and runs indefinitely.

We’re using Visual Studio 2022, and with “Common Language Runtime Exception” enabled, we see the following:

After investigating, it seems the issue may be related to how IHostedService instances are started. We are using Hangfire in our project. When we apply the following piece of code for each integration tests set, the issue is resolved:

 protected static void DisableBackgroundHostedServices(IUmbracoBuilder builder)
 {
     var hostedServices = builder.Services.Where(x => x.ServiceType == typeof(IHostedService));
     foreach (var service in hostedServices)
         builder.Services.Remove(service);
 }

[AndyButland]

This original issue raised I believe will be resolved via #22340, which we'll include in a 17.3.1 release next week.

@adavidovic92 - I don't see what you've raised as being related, but please consider raising a new issue for it if you believe there is a problem. If it's possible that can come with a cut-down integration test that verifies the problem - i.e. one that passes on 17.2.2 and fails on 17.3 - that'll make it a lot easier to diagnose and try to resolve.