elsa-workflows / elsa-core

A .NET workflows library
https://v3.elsaworkflows.io/
MIT License
6.49k stars 1.2k forks source link

An exception occurred while executing CleanupExpiredMessages with MySQL database in Elsa3 #4810

Open faith0831 opened 10 months ago

faith0831 commented 10 months ago

MySQL version: 8.0.32

Here is the exception information:

Elsa.Workflows.Runtime.HostedServices.WorkflowInboxCleanupHostedService[0] Entering expired workflow inbox messages cleanup service loop fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (1ms) [Parameters=[@now_0='?' (DbType = DateTimeOffset), @__p_2='?' (DbType = Int32), @p_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] DELETE w FROM WorkflowInboxMessages AS w WHERE w.Id IN ( SELECT w0.Id FROM WorkflowInboxMessages AS w0 WHERE w0.ExpiresAt <= @now_0 LIMIT @__p_2 OFFSET @p1 ) fail: Elsa.Workflows.Runtime.HostedServices.WorkflowInboxCleanupHostedService[0] An error occurred while cleaning up expired workflow inbox messages MySqlConnector.MySqlException (0x80004005): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/ServerSession.cs:line 936 at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in //src/MySqlConnector/Core/ResultSet.cs:line 37 at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in //src/MySqlConnector/MySqlDataReader.cs:line 130 at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56 at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 309 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQueryAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Elsa.EntityFrameworkCore.Common.Store2.DeleteWhereAsync(Func2 query, CancellationToken cancellationToken) at Elsa.EntityFrameworkCore.Common.Store2.DeleteWhereAsync(Func`2 query, CancellationToken cancellationToken) at Elsa.EntityFrameworkCore.Modules.Runtime.EFCoreWorkflowInboxMessageStore.DeleteManyAsync(WorkflowInboxMessageFilter filter, PageArgs pageArgs, CancellationToken cancellationToken) at Elsa.Workflows.Runtime.HostedServices.WorkflowInboxCleanupHostedService.CleanupExpiredMessages(CancellationToken cancellationToken) at Elsa.Workflows.Runtime.HostedServices.WorkflowInboxCleanupHostedService.ExecuteAsync(CancellationToken stoppingToken)

faith0831 commented 10 months ago

The error occurs because MySQL 8.0 does not support the use of the LIMIT keyword in subqueries. Here is an explanation from the official MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/subquery-errors.html.

mohdali commented 9 months ago

@faith0831 I noticed similar issue while testing MySql connection, do you have a suggested change to the query?

remitrev commented 9 months ago

@faith0831 @mohdali I have the same error. To avoid this error, you would need to modify the DeleteManyAsync function in the MemoryWorkflowInboxMessageStore class.

faith0831 commented 8 months ago

@mohdali Hello, I have submitted a PR, removing the LIMIT keyword.