search

elsa-workflows / elsa-core

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

[BUG]This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' #6096

Open EthanZqh opened 2 weeks ago

EthanZqh commented 2 weeks ago

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?

  1. 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.

elsa-mysql-err

sfmskywalker commented 1 week ago

I tried with Docker container mysql:9.1.0, which works well. If you need to support an older version of MySql, you can always generate your own EF Core migration files. Basically, copy the Elsa.EntityFrameworkCore.MySql project, rename the project and its classes and methods to be your own, and update the MySqlDesignTimeDbContextFactory class to use the version of MySQL that you want to use.

sfmskywalker commented 1 week ago

I just noticed you had already posted this exact same issue here: #6091 I will close that one, since this issue now contains some feedback.

Please refrain from posting the same issues multiple times. Thank you.

EthanZqh commented 1 week ago

I tried with Docker container mysql:9.1.0, which works well. If you need to support an older version of MySql, you can always generate your own EF Core migration files. Basically, copy the Elsa.EntityFrameworkCore.MySql project, rename the project and its classes and methods to be your own, and update the MySqlDesignTimeDbContextFactory class to use the version of MySQL that you want to use.

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 commented 1 week ago

Hi @EthanZqh , you don't need to change the source code. Instead, you can add your own class library project that contains the necessary migrations.

EthanZqh commented 1 week ago

Hi @EthanZqh , you don't need to change the source code. Instead, you can add your own class library project that contains the necessary migrations.

I copied Elsa Entity Framework Core. MySQL project, and update MySqlDesignTimeVNet Factory class to use the MySQL version I want to use. How to generate EF Core migration files? migrations