[BUG]What is the problem with using MySQL database Elsa service error?

[EthanZqh]

hello,teacher:
What is the problem with using MySQL database Elsa service error?

[sfmskywalker]

It looks like that the MySql server version you're using doesn't yet support the LIMIT, IN, ALL, ANY and SOME subquery syntax.

[EthanZqh]

It looks like that the MySql server version you're using doesn't yet support the LIMIT, IN, ALL, ANY and SOME subquery syntax.

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.

[sfmskywalker]

The EF Core migrations were generated against MySql version 9.0.0.
One thing you could try is to generate your own migrations. The simplest way might be to copy the Elsa.EntityFrameworkCore.MySql project and rename it, and change the MySql version to an older version.

The MySQL server version is community-based mysql-8.0.30-winx64 or community-based mysql-9.0.1-winx64

Do you mean that you are not sure which one you are using? If so, probably you're using version 8. So, if possible for you, upgrading to version 9 might solve your issue and then you don't need to generate custom migrations.

[EthanZqh]

Upgrading MySQL to the community version MySQL-9.0.1-winx64 does not solve the problem and supports' Limit&IN/ALL/ANY/OME subqueries'. It may be necessary to change the source code nesting to solve the problem,
The code is as follows: DELETE w
FROM WorkflowInboxMessages AS w
WHERE w.Id IN (
SELECT w0.Id
FROM (select w1.Id from WorkflowInboxMessages AS w1
WHERE w1.ExpiresAt <= @__now_0
LIMIT 3 OFFSET 2) AS w0
)
I am unable to modify the source code. I hope the teacher can modify the source code to solve this problem. Thank you!

[sfmskywalker]

Let's keep the issue in one place: #6096