Errors occurred when using both MySQL version 8 and MySQL version 9

[EthanZqh]

[ERR] [Microsoft.EntityFrameworkCore.Database.Command] [] [] - 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 @__p_1
)

[ismaildasci]

Hey! 👋

It looks like the error is happening when trying to delete records with a subquery that uses LIMIT and OFFSET. MySQL can sometimes struggle with this type of query in a DELETE statement.

Here are a few potential workarounds:

1. Refactor the Query into Two Steps

Instead of trying to DELETE with LIMIT and OFFSET directly in the subquery, you could try selecting the records to delete in a separate step and then deleting them. Here’s an example:

1. First, select the IDs of the records you want to delete:

   SELECT w0.Id
   FROM WorkflowInboxMessages AS w0
   WHERE w0.ExpiresAt <= @__now_0
   LIMIT @__p_2 OFFSET @__p_1;

2. Then, delete using those IDs in a separate query:

   DELETE FROM WorkflowInboxMessages
   WHERE Id IN (/* result from previous query */);

2. Use a Temporary Table

You could select the records you want to delete into a temporary table and then delete from that table. This might help avoid issues with complex subqueries in the DELETE statement.

3. Check for MySQL-Specific Optimizations

MySQL doesn’t always handle LIMIT in DELETE statements efficiently, so check if any MySQL-specific optimizations or settings (such as adjusting query optimizer flags) might improve the performance.

If these suggestions don’t resolve the issue, consider sharing the specific error message you’re getting to help narrow down the problem. 😊

[EthanZqh]

The MySQL server version is community-based mysql-8.0.30-winx64 or community-based mysql-9.0.1-winx64
Limit&IN/ALL/ANY/OME subqueries are not yet supported
Solution problem: 1. Which MySQL version supports the "Limit&IN/ALL/ANY/OME subquery" query?
2. It is to modify the nested source code query, but I cannot modify the Elsa source code. I am referencing the Elsa Dll dynamic library.