Failover plugin causes 'MySqlConnection is already in use' with EF Core / Pomelo MySqlConnector

[adomass]

Description

When using the failover plugin with Entity Framework Core (Pomelo.EntityFrameworkCore.MySql 9.0.0) and MySqlConnector, an Aurora writer failover causes the connection to enter a permanently broken state. After the first successful reconnection, all subsequent commands fail with MySqlConnection is already in use.

Environment

• AWS.AdvancedDotnetDataProviderWrapper.EntityFrameworkCore.MySqlConnector 1.0.1
• Pomelo.EntityFrameworkCore.MySql 9.0.0
• MySqlConnector 2.4.0 (transitive via Pomelo)
• .NET 10.0
• Aurora MySQL cluster (eu-west-1, 2 writer-eligible instances)

Steps to Reproduce

1. Configure EF Core with the wrapper:

optionsBuilder.UseAwsWrapper(
    connectionString + ";Plugins=failover;",
    wrappedOptions => wrappedOptions.UseMySql(connectionString, serverVersion));

2. Execute write queries continuously via DbContext.Database.ExecuteSqlRawAsync()

3. Trigger Aurora failover: aws rds failover-db-cluster --db-cluster-identifier <cluster>

4. Observe behavior

Observed Behavior

1. Queries 1-14: succeed (before failover)
2. Query 15: fails with connection error (failover starts) — DealWithOriginalExceptionAsync fires
3. Queries 16-17: connection errors (expected during failover)
4. Queries 18-26: succeed (wrapper reconnected to new writer — failover worked!)
5. Query 27+: ALL fail permanently with MySqlConnection is already in use

The wrapper successfully reconnects once, but then the underlying MySqlConnection's ServerSession gets stuck in State.Querying — every subsequent command hits StartQuerying() which throws because the session state was never reset.

Root Cause Analysis

The failover plugin calls SetCurrentConnection() to swap the underlying DbConnection to a new one pointing at the new writer. However:

1. EF Core's RelationalConnection caches a reference to the original DbConnection
2. When the failover plugin swaps the connection, the old MySqlConnection's ServerSession.m_state remains Querying
3. The wrapper's AwsWrapperConnection has ActiveWrapperCommands list — commands from the failed connection may not be properly unregistered
4. Subsequent commands attempt to execute on the old MySqlConnection reference (cached by EF Core), which has a session stuck in Querying state

The MySqlConnection is already in use check is in MySqlConnector.Core.ServerSession.StartQuerying():

if (m_state is State.Querying or State.CancelingQuery)
    throw new InvalidOperationException("This MySqlConnection is already in use.");

Additional Issues Found

During investigation we also found:

1. initialConnection plugin breaks EF Core migrations — it swaps the connection mid-migration transaction, causing DDL to auto-commit but __EFMigrationsHistory INSERT to be lost (tables created but not recorded)

2. EnableRetryOnFailure conflicts with failover plugin — EF Core's retry strategy tries to reuse the connection during DealWithOriginalExceptionAsync, causing the same "already in use" error immediately

3. Plugins= connection string key rejected by MySqlConnector — MySqlConnectionStringBuilder throws ArgumentException: Option 'plugins' not supported. The wrapper and EF Core need separate connection strings.

4. Allow User Variables=true required — the wrapper re-parses SQL through AwsWrapperCommand, causing MySqlConnector to treat @ user variables in stored procedure DDL as parameters

Expected Behavior

After a successful failover reconnection, subsequent commands should execute normally against the new writer without connection state corruption.

Workaround

Currently there is no workaround for the failover plugin with EF Core. Using only the initialConnection plugin (without failover) works for reader load balancing but provides no failover handling.

[adomass]

Updated findings after further testing

Issue 1: Failover plugin connects to reader instead of writer (confirmed, reproducible)

After Aurora failover, FailoverWriterAsync reconnects to a reader instance instead of the new writer. The topology returned by ForceRefreshHostListAsync during the failover window has stale role assignments.

Test output:

Writer before: ip-172-20-0-130
Writer after:  ip-172-20-1-212
Write attempts: 72
Write successes: 15
Errors during failover: 57
Writes per host:
  ip-172-20-0-130: 14 writes
  ip-172-20-1-212: 1 writes

Error messages: "Running in read-only mode" (MySqlErrorCode.OptionPreventsStatement)

The wrapper reconnects after failover (1 write on new host), but then subsequent writes fail with read-only mode — the connection landed on a reader, not the new writer. It never recovers to the actual writer on its own.

Workaround: Catch FailoverSuccessException and OptionPreventsStatement, clear all connection pools, and retry. The next connection goes through DNS and reaches the correct writer.

Issue 2: "MySqlConnection is already in use" (intermittent, may be secondary)

In some test runs (especially when triggering failover rapidly), the connection enters a permanently broken state with MySqlConnection is already in use. This appears to happen when a second network disruption occurs during the cluster's instability window (first failover works, second breaks permanently). This may be caused by InvalidateCurrentConnectionAsync calling CloseAsync() on a connection whose ServerSession is still in Querying state.

Issues 3-4 from original report remain valid

• initialConnection plugin swaps connection mid-migration, breaking EF Core migrations
• Allow User Variables=true required because wrapper re-parses SQL with @ user variables
• Plugins= connection string key rejected by MySqlConnectionStringBuilder, requiring separate connection strings for wrapper vs Pomelo

[adomass]

Minimal repro: Allow User Variables required with wrapper but not without

The same CREATE PROCEDURE SQL with MySQL user variables (@var) works through EF Core + Pomelo without the wrapper, but fails with the wrapper unless Allow User Variables=true is explicitly set.

Repro

using AwsWrapperDataProvider.Dialect.MySqlConnector;
using Microsoft.EntityFrameworkCore;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

var connStr = "Server=<aurora-cluster>;Database=<db>;User Id=<user>;Password=<pass>;sslMode=Required;";

var sql = """
    CREATE PROCEDURE IF NOT EXISTS _test_user_vars()
    BEGIN
        SET @myVar = CONCAT('SELECT ', '1');
        PREPARE stmt FROM @myVar;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END;
    """;

// Test 1: EF Core + Pomelo, no wrapper
var opts1 = new DbContextOptionsBuilder<TestDbContext>()
    .UseMySql(connStr, ServerVersion.AutoDetect(connStr))
    .Options;
using var ctx1 = new TestDbContext(opts1);
await ctx1.Database.ExecuteSqlRawAsync(sql); // PASS

// Test 2: EF Core + Pomelo + Wrapper
MySqlConnectorDialectLoader.Load();
var opts2 = new DbContextOptionsBuilder<TestDbContext>()
    .UseAwsWrapper(connStr + "Plugins=failover;",
        wrapped => wrapped.UseMySql(connStr, ServerVersion.AutoDetect(connStr)))
    .Options;
using var ctx2 = new TestDbContext(opts2);
await ctx2.Database.ExecuteSqlRawAsync(sql);
// FAIL: MySqlException: Parameter '@myVar' must be defined.

// Test 3: Wrapper + Allow User Variables=true
var connStr3 = connStr + "Allow User Variables=true;";
var opts3 = new DbContextOptionsBuilder<TestDbContext>()
    .UseAwsWrapper(connStr3 + "Plugins=failover;",
        wrapped => wrapped.UseMySql(connStr3, ServerVersion.AutoDetect(connStr3)))
    .Options;
using var ctx3 = new TestDbContext(opts3);
await ctx3.Database.ExecuteSqlRawAsync(sql); // PASS

public class TestDbContext(DbContextOptions<TestDbContext> options) : DbContext(options) { }

Results

Test	Wrapper	Allow User Variables	Result
1	No	default (false)	PASS
2	Yes	default (false)	FAIL
3	Yes	true	PASS

The wrapper changes how MySqlConnector interprets @ prefixed variables inside stored procedure DDL, even though AwsWrapperCommand does not modify the SQL text. Verified by reading the AwsWrapperCommand source — CommandText is passed through unchanged.

[adomass]

Root cause found: Allow User Variables issue

The cause

Pomelo.EntityFrameworkCore.MySql has a MySqlConnectionStringOptionsValidator (source) that forces two mandatory settings on every connection:

• AllowUserVariables=true
• UseAffectedRows=false

Without the wrapper, Pomelo's MySqlRelationalConnection.CreateDbConnection() goes through this validator — the settings are injected automatically.

With the wrapper, AwsWrapperRelationalConnection.CreateDbConnection() creates new AwsWrapperConnection(typeof(MySqlConnection), connectionString) — bypassing Pomelo's validator entirely. The underlying MySqlConnection gets the raw connection string without these mandatory settings.

Fix suggestion

AwsWrapperRelationalConnection.CreateDbConnection() should apply Pomelo's mandatory connection string options before creating the wrapper connection. Either:

1. Call MySqlConnectionStringOptionsValidator.EnsureMandatoryOptions(ref connectionString) on the wrapper connection string before passing it to new AwsWrapperConnection()
2. Or always append AllowUserVariables=True;UseAffectedRows=False; to the connection string in the EF Core MySqlConnector integration package

Workaround

Users must explicitly add Allow User Variables=true;Use Affected Rows=false; to their connection strings when using the wrapper with Pomelo.

[kenrickyap]

Thanks for the detailed report on this issue. The issue has been acknowledge and we are currently in the process of doing our own investigation.

In the mean time, if you would be able to provide any avaliable driver logs for the issue that would be really helpful as well.

[kenrickyap]

Summarizing the above comments to the following 5 issues:

1. MySqlConnection is already in use after failover recovery loop
2. EnableRetryOnFailure interaction with failover handling
3. Plugins= key being rejected by MySqlConnectionStringBuilder
4. Wrapper path not consistently applying Pomelo-required options (AllowUserVariables, UseAffectedRows)
5. initialConnection behavior during EF migrations / history consistency

We have now created a PR that addresses the Plugins= handling and mandatory MySQL option normalization in the wrapper path (items 3 and 4).

However we are still in the process of investigating the remaining issues. I have tried the provided steps to reproduce item 1 but am not able to do so. This is the code I am running to recreate the scenario:

 var options = new DbContextOptionsBuilder<ManualIssue268DbContext>()
        .UseAwsWrapper(wrapperConnectionString, wrapped => wrapped.UseMySql(pomeloConnectionString, ServerVersion))
        .Options;
    var cancellationToken = TestContext.Current.CancellationToken;
    await using var db = new ManualIssue268DbContext(options);
    await db.Database.ExecuteSqlRawAsync(CreateWriteProbeTableSql, cancellationToken);
    await db.Database.OpenConnectionAsync(cancellationToken);
    var iterationDelay = TimeSpan.FromMilliseconds(10);
    for (var i = 1; i <= TotalIterations; i++)
    {
        try
        {
            await db.Database.ExecuteSqlRawAsync(InsertWriteProbeSql, cancellationToken);
            await Console.Out.WriteLineAsync($"[{DateTime.UtcNow:O}] query {i}/{TotalIterations}: OK");
        }
        catch (OperationCanceledException) when (cancellationToken.IsCancellationRequested)
        {
            throw;
        }
        catch (Exception ex)
        {
            await Console.Out.WriteLineAsync(
                $"[{DateTime.UtcNow:O}] query {i}/{TotalIterations}: ERROR: {ex.Message}");
        }
        await Task.Delay(iterationDelay, cancellationToken);
    }

I will continue to investigate the issue and try to reproduce this issue, and update you with any new findings soon!