search

microsoft / durabletask-mssql

Microsoft SQL storage provider for Durable Functions and the Durable Task Framework
MIT License
87 stars 32 forks source link

DurableClient.ListInstancesAsync() throws Microsoft.Data.SqlClient.SqlException : @ExcludeSubOrchestrations is not a parameter for procedure _QueryManyOrchestrations #129

Closed scale-tone closed 2 years ago

scale-tone commented 2 years ago

DfMon fails to work with Microsoft.DurableTask.SqlServer.AzureFunctions v1.0.0 and higher. When trying to list orchestrations, DurableClient throws the following:

image

QueryCondition values should be visible on the screenshot. Here is the entire stack trace:

Exception has occurred: CLR/System.AggregateException
An exception of type 'System.AggregateException' occurred in System.Private.CoreLib.dll but was not handled in user code: 'One or more errors occurred.'
 Inner exceptions found, see $exception in variables window for more details.
 Innermost exception     Microsoft.Data.SqlClient.SqlException : @ExcludeSubOrchestrations is not a parameter for procedure _QueryManyOrchestrations.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__203_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at DurableTask.SqlServer.SqlUtils.<WithRetry>d__37`1.MoveNext() in /_/src/DurableTask.SqlServer/SqlUtils.cs:line 529
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at DurableTask.SqlServer.SqlUtils.<WithRetry>d__37`1.MoveNext() in /_/src/DurableTask.SqlServer/SqlUtils.cs:line 529
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at DurableTask.SqlServer.SqlUtils.<ExecuteSprocAndTraceAsync>d__33`1.MoveNext() in /_/src/DurableTask.SqlServer/SqlUtils.cs:line 453
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at DurableTask.SqlServer.SqlOrchestrationService.<GetManyOrchestrationsAsync>d__39.MoveNext() in /_/src/DurableTask.SqlServer/SqlOrchestrationService.cs:line 804
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at DurableTask.SqlServer.AzureFunctions.SqlDurabilityProvider.<GetOrchestrationStateWithPagination>d__19.MoveNext() in /_/src/DurableTask.SqlServer.AzureFunctions/SqlDurabilityProvider.cs:line 153

With prior versions everything works fine. To repro you can configure DurableFunctionsMonitor.DotNetBackend with SQL durability provider and run it locally.

cgillum commented 2 years ago

Thanks for testing this out with the latest version of the SQL backend!

Looking at the v1.0.0 code, it looks like @ExcludeSubOrchestrations is actually a parameter of the _QueryManyOrchestrations stored procedure.

https://github.com/microsoft/durabletask-mssql/blob/d9d1eab37beff17cf4b1a76906dc2ba3bb178092/src/DurableTask.SqlServer/Scripts/logic.sql#L1045-L1054

Is it possible that this database was created with an older version of the SQL backend? That could explain the discrepancy since @ExcludeSubOrchestrations was added more recently. Does it work if you create a new database using the v1.0.0 version of the SQL backend?

scale-tone commented 2 years ago

Hi Chris, thanks for clarifying this. I can confirm that on a freshly created DB version 1.0.1 works. So, was this backward compatibility deliberately not intended or are you planning to restore it?

cgillum commented 2 years ago

The plan is to support backwards compatibility for releases after v1.0.0 (which is the first “stable” release).

More info here: https://github.com/microsoft/durabletask-mssql/issues/124

scale-tone commented 2 years ago

Makes sense, thanks. Then I'll have to upgrade DfMon accordingly.