MySqlConnector.MySqlException (0x80004005): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[lujie19930803]

Description

when i use mysql to perisist data. i meet the following error. and my elsa version is 3.2.2. mysql version is 8.0.29.

Steps to Reproduce

i configure elsa like that.

services.AddElsa(elsa =>
        {
            elsa.UseWorkflowRuntime(runtime => runtime.UseEntityFrameworkCore(ef => ef.UseMySql(connectionStr)));
            elsa.UseWorkflowManagement(management => management.UseEntityFrameworkCore(ef => ef.UseMySql(connectionStr)));
            elsa.UseIdentity(identity =>
            {
                identity.TokenOptions = options => options.SigningKey = "sufficiently-large-secret-signing-key"; // This key needs to be at least 256 bits long.
                identity.UseAdminUserProvider();
            });
            elsa.UseDefaultAuthentication(auth => auth.UseAdminApiKey());
            elsa.UseWorkflowsApi();
            elsa.UseRealTimeWorkflows();
            elsa.UseCSharp();
            elsa.UseJavaScript();
            elsa.UseFileStorage();
            elsa.UseHttp();
            elsa.UseScheduling();
        });

i found when i comment UseWorkflowRuntime. this error disappeared.

@sfmskywalker please help me.

[r-kernberger]

@lujie19930803 I had the same error with v3.2. Give v3.3.0-rc3 a try. It was fixed for me then

[sfmskywalker]

It sounds familiar to what is happening in #6126

If the issue persists despite @r-kernberger 's suggestion, here are two other options to consider:

1. Upgrade to the latest MySql (>= v9) version.
2. Generate EF Core migrations manually and use them instead.

The MySql EF Core migrations shipped with Elsa target the latest MySql version and are included for convenience, however these are not required. Some users even prefer to maintain their own migrations.

[sfmskywalker]

I performed some tests with MySQL 8.0.0 and Elsa running on .NET 8, and I ran into some issues.
I was able to work around those issues by enabling primitive support and automatically translating parameterized collections to constants, as explained here.

After that, the MySql provider seems to work well, so far. I'll conduct more tests.

[sfmskywalker]

The MySql provider + migrations seems to operate well on MySql 8.0. I would encourage you to try out the latest preview build. I'll close this issue as resolved, but please let me know if you still run into issues.