Set aurora_replica_read_consistency to "session" before DML commands .Net6.0 + EFCore6 + Pomelo6 + AWS Aurora (MySQL 5.7)

[adapter7]

The issue

I am using AWS Aurora MySQL. Following below article, I have created a secondary readonly cluster and enabled write-forwarding on it so that all write queries sent to secondary readonly cluster are forwarded to primary cluster.

https://aws.amazon.com/blogs/database/building-globally-distributed-mysql-applications-using-write-forwarding-in-amazon-aurora-global-database/

As mentioned in above post, it also requires to set parameter aurora_replica_read_consistency at session level. And I've set this in the init_connect like this: set @@aurora_replica_read_consistency=SESSION .. so that it gets executed automatically when new client connects.

When testing write-queries in MySQL Workbench, it works perfectly and no additional change is required. The same behavior was expected in our .net6.0 application. However, when issuing a write-query in .net6.0 application using Pomelo 6.0.1, it throws an error as if aurora_replica_read_consistency is set to blank.

Exception message:
Stack trace: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> MySqlConnector.MySqlException (0x80004005): The MySQL server is running with the --read-only option so it cannot execute this statement
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 954
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 44
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 127
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 456
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 330
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 323
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()

Do we need additional changes when using write-forward on AWS Aurora MySQL with Pomelo?

Further technical details

MySQL version: 5.7.12-log Operating system: AWS RDS Aurora Pomelo.EntityFrameworkCore.MySql version: 6.0.1 Microsoft.AspNetCore.App version:

Other details about my project setup: TargetFramework: .net6.0 WebAPI Project

[mguinness]

Your best bet would be to use DbConnectionInterceptor, specifically ConnectionOpened or ConnectionOpenedAsync.

[lauxjpn]

Your best bet would be to use DbConnectionInterceptor, specifically ConnectionOpened or ConnectionOpenedAsync.

@adapter7 You can find more information about Interceptors and some sample code in the official EF Core docs.

[adapter7]

I used DbConnectionInterceptor.ConnectionOpened to execute this statement: set @@aurora_replica_read_consistency=SESSION

It was an update operation with SaveChanges() and I got a different error this time:

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithoutPropagationAsync(Int32 commandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass33_0`2.<<ExecuteAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

[mguinness]

Your latest exception is unrelated to the original issue. See Handling Concurrency Conflicts for more details.

Suggest you ask on Stack Overflow for assistance regarding this exception. EDIT: see this related question.

[eric-davis]

I don't think this is a simple concurrency conflict handling issue. I am seeing the same behavior as the original poster. My application is attempting to use an EF context to delete a number of records in a single table that match specific PK ID values. And, just like the original poster, I get the DbUpdateConcurrencyException referenced above.

The replica DB has the Aurora write forwarding feature enabled and replica cluster has the init_connect parameter set with SET aurora_replica_read_consistency="SESSION".

The captured SQL that is generated by EF is as follows:

DELETE FROM ApiResources
WHERE Id = 1;
SELECT ROW_COUNT();

DELETE FROM ApiResources
WHERE Id = 2;
SELECT ROW_COUNT();

DELETE FROM ApiResources
WHERE Id = 3;
SELECT ROW_COUNT();

DELETE FROM ApiResources
WHERE Id = 4;
SELECT ROW_COUNT();

DELETE FROM ApiResources
WHERE Id = 5;
SELECT ROW_COUNT();

If I connect MySQL Workbench to the same replica DB and execute the same SQL (wrapped in a transaction), I get a value of 1 from the row count queries.

[eric-davis]

There seems to be 2 issues here:

1. Why is the init_connect parameter executed for connections initiated from MySQL Workbench, but not for connections initiated from Pomelo/EF?
2. Why does SELECT ROW_COUNT() return the expected value when executed from MySQL Workbench, but not EF?

[eric-davis]

Your best bet would be to use DbConnectionInterceptor, specifically ConnectionOpened or ConnectionOpenedAsync.

@mguinness @lauxjpn - Why would this be required if the init_connect parameter is in place to execute the statement??? How is a connection initiated from EF and some other client different where the client connection session is affected by init_connect, but the EF connection is not?

[mguinness]

Are you using root user in the connection string? If so init_connect would be ignored. Otherwise you should create an issue in the underlying MySqlConnector library to determine why it's not working. However I would think it's a server side (Aurora) issue rather than a client side (MySqlConnector) issue.

[eric-davis]

Are you using root user in the connection string? If so init_connect would be ignored. Otherwise you should create an issue in the underlying MySqlConnector library to determine why it's not working. However I would think it's a server side (Aurora) issue rather than a client side (MySqlConnector) issue.

This doesn't seem to be the case. I've tested the init_connect functionality via MySQL Workbench with both the root account and a non-root service account. Both appear to honor the init_connect SQL. If I have init_connect set to SET aurora_replica_read_consistency="SESSION" on the replica cluster, open a connection to that cluster endpoint with either creds, and execute SELECT @@aurora_replica_read_consistency I get SESSION back as a result. Alternatively, if I remove the init_connect value from the cluster and retry the test for both accounts, I get a blank value back from the same query for both...as expected.

Now when I attempt to validate this behavior in my .NET Core application using the Pomelo EF provider, I get some strange results. I have added a DbConnectionInterceptor implementation and overridden the ConnectionOpened and ConnectionOpenedAsync methods to open the newly created connection and execute the same SELECT @@aurora_replica_read_consistency query via a DB scalar command whenever a DB connection is created. Oddly, the first connection that gets created executes this query and gets back a result of SESSION, but subsequent connections execute the same query but get a blank result. Any idea why that would happen? Why would the init_connect SQL only get applied to the first connection created, but not the subsequent ones?

[mguinness]

This doesn't seem to be the case. I've tested the init_connect functionality via MySQL Workbench with both the root account and a non-root service account. Both appear to honor the init_connect SQL.

That seems contrary to what the documented behavior should be. Have you tried this on a local database instead of Aurora?

The server does not execute init_connect system variable content when CONNECTION_ADMIN clients connect.

As for the initial connection in Pomelo only getting the correct value from the global variable that seems odd. Have you looked at the MySQL logs to see if there are any warning messages? Is there a proxy involved in this setup? Again I would try with a local database and see if it exhibits the same behavior. @bgrainger Do you have any insight into the mechanism of init_connect as I assume this has nothing to do with connection management in MySqlConnector.

[eric-davis]

Not sure why testing with a local DB would be relevant here? The behavior re: init_connect is inconsistent when connecting to the same DB from the same machine with the same credentials from MySQL Workbench and via Pomelo. And even if I force the aurora_replica_read_consistency issue by using a DbConnectionInterceptor to set the session variable when init_connect does not, I still see inconsistent results from the ROW_COUNT() function when executing via MySQL Workbench vs Pomelo as well...which is the primary blocking issue within this whole thread.

[mguinness]

Not sure why testing with a local DB would be relevant here?

Aurora is a closed fork of MySQL, so although it's protocol-compatible it's not the same codebase so there will be differences. Since you've already commented on the related question in SO you probably already saw the following.

This seems to be an issue with RDS where row_count() does not recognize the immediate previously executed DML command. We have ticket opened with AWS but their response is that don't know when they'll have any answer for us.

[eric-davis]

Yes, but that doesn't explain why the same SQL executed against the same database using the same credentials executed from MySQL Workbench completes without error (and returns the expected values from the ROW_COUNT() calls), but the same executed via Pomelo does not.

[eric-davis]

Per this doc, ROW_COUNT() will not function reliably if using statement-based replication, but should with row-based replication. All my testing has been against clusters that have the binlog_format setting set to ROW.

[bgrainger]

Oddly, the first connection that gets created executes this query and gets back a result of SESSION, but subsequent connections execute the same query but get a blank result. Any idea why that would happen? Why would the init_connect SQL only get applied to the first connection created, but not the subsequent ones?

My guess would be connection pooling. MySqlConnection.Open doesn't always result in a new connection being established to the database server (for performance). Most of the time, an existing connection is reused. Moreover, that connection is reset (to clear session variables, temp tables, etc.). My guess would be that in Aurora, this also clears the aurora_replica_read_consistency setting.

You could test this theory by adding Connection Reset = false; to your connection string.

[bgrainger]

Per previous comments, the right way to handle this in EF is to use ConnectionOpened to execute per-connection SQL (rather than using init_connect), as it will allow you to execute SQL for each logical connection, rather than just the physical connections.

[bgrainger]

One can also use MySqlConnection.StateChange to execute SQL when the connection is opened: https://github.com/mysql-net/MySqlConnector/issues/519#issuecomment-397829071.

[eric-davis]

@bgrainger - Ok. The connection pooling theory makes sense. And adding Connection Reset = false to the DB conn string worked as you expected. Thank you for explaining the root cause of that behavior! Now that just leaves the main issue of ROW_COUNT() behaving differently between MySQL Workbench and EF.

And I don't have an issue with using the interceptor to ensure that session variable is set. I just wanted to understand why it was recommended/necessary when using EF + Pomelo.

[mguinness]

You should try running that SQL under ADO.NET with MySqlConnector directly instead of via Pomelo as it might help isolate the issue, You can also try changing the UseAffectedRows connection string option to see what difference that makes.

[bgrainger]

I thought about mentioning UseAffectedRows, but (according to the documentation) it should only apply to UPDATE statements, not DELETE, so I'm not sure if it's relevant.

[eric-davis]

You should try running that SQL under ADO.NET with MySqlConnector directly instead of via Pomelo as it might help isolate the issue, You can also try changing the UseAffectedRows connection string option to see what difference that makes.

I guess I can try the ADO.NET option. I tried testing the effect of the UseAffectedRows connection string option yesterday, but it appears Pomelo currently overrides it and always forces it to be false. See code here.

[eric-davis]

I tried to put a similar SQL operation together and execute it via MySqlConnector directly via the code below. But unfortunately, the SELECT ROW_COUNT() result is 0 and not 1 as would be expected. Also, in my actual application the EF-generated SQL includes multiple SELECT ROW_COUNT() statements. Does MySQL support multiple result sets? How does Pomelo handle that?

using var conn = new MySqlConnection(dbConnString);
conn.Open();

using var txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);

var sql = "SET @@aurora_replica_read_consistency=\"SESSION\";";
using var cmd = conn.CreateCommand();
cmd.Transaction = txn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

sql = "DELETE FROM ApiResources WHERE ID = 1;";
sql += "SELECT ROW_COUNT();";
cmd.CommandText = sql;

var results = new List<string>();
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    results.Add(reader.GetValue(0).ToString());
}
reader.Close();

if (results.All(r => r == "1"))
{
    txn.Commit();
}
else
{
    txn.Rollback();
}

[bgrainger]

Does MySQL support multiple result sets?

Yes; after MySqlDataReader.Read() returns false, call MySqlDataReader.NextResult to check if there is an additional result set (then read it with Read()).

[eric-davis]

Above test code updated to include multiple DELETE operations. But the SELECT ROW_COUNT() statements return results of 0,-1,-1,-1,-1. Does that make any sense?

using var conn = new MySqlConnection(dbConnString);
conn.Open();

using var txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);

var sql = "SET @@aurora_replica_read_consistency=\"SESSION\";";
using var cmd = conn.CreateCommand();
cmd.Transaction = txn;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

sql = "DELETE FROM ApiResources WHERE ID = 1;";
sql += "SELECT ROW_COUNT();";
sql += "DELETE FROM ApiResources WHERE ID = 2;";
sql += "SELECT ROW_COUNT();";
sql += "DELETE FROM ApiResources WHERE ID = 3;";
sql += "SELECT ROW_COUNT();";
sql += "DELETE FROM ApiResources WHERE ID = 4;";
sql += "SELECT ROW_COUNT();";
sql += "DELETE FROM ApiResources WHERE ID = 5;";
sql += "SELECT ROW_COUNT();";
cmd.CommandText = sql;

var results = new List<string>();
using var reader = cmd.ExecuteReader();
do
{
    while (reader.Read())
    {
        results.Add(reader.GetValue(0).ToString());
    }
} while (reader.NextResult());
reader.Close();

if (results.All(r => r == "1"))
{
    txn.Commit();
}
else
{
    txn.Rollback();
}

[mguinness]

What happens when you run this code against a local MySQL instance? BTW I meant that you change the UseAffectedRows option when using ADO.NET since as you already noted this is restricted in Pomelo.

[eric-davis]

Appending UseAffectedRows=True to the DB conn string when running the ADO.NET example above doesn't appear to affect anything; same results. I don't have a local DB stood up at the moment to test with. I'll see about giving that a try just to be thorough. But I'm not 100% sure how useful that local DB test will be since all this seems to work (ROW_COUNT() calls return 1's) when executing the same SQL via Workbench against the same global Aurora replica DB instance with write forwarding enabled.

[mguinness]

I tried your code as-is against a local MySQL and I got a ROW_COUNT() of 1 in each instance. Something is different using MySqlConnector against your Aurora setup. Maybe you could do a packet capture (with Wireshark, tcpdump, etc.) when using both Workbench and ADO.NET so Bradley could see what is different. Let's move this to a new issue in the MySqlConnector repo.

[eric-davis]

That sounds good to me. Are you all going to take care of moving this to MySqlConnector? Or are you expecting me to open a new ticket over there?

[mguinness]

You can open a new issue there that references this one.

[eric-davis]

Can you please point me to some detailed instructions on how to do the packet captures you suggested on a Windows box? I'm not having much luck figuring out how to do that for MySQL traffic.