Question 108010 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – .net6.0 – write forward – AWS Aurora: The MySQL server is running with the –read-only option so it cannot execute this statement

Our AWS RDS (Aurora MySQL 2.10.2) has a primary cluster and a secondary cluster.

Secondary cluster is a replica and has a reader instance. This reader instance is set to read_only false and I have setup write-forwarding from secondary to primary cluster. So, I have set the parameter group’s variable init_connect to value set @@aurora_replica_read_consistency=SESSION

When I connect to secondary cluster’s reader endpoint through MySQL Workbench then write-forwarding works i.e. if I issue query to create record in secondary cluster, it gets write-forward to primary and querying same table shows the new record.

However, the problem occurs with my .net6.0 application. If I point my .net6.0 application, which uses Pomelo.EntityFrameworkCore.MySql, to same secondary cluster’s reader endpoint in connectionstring then it does not work and it fails with this error:

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()

Since parameter group variable on secondary cluster init_connect is set to value @@aurora_replica_read_consistency=SESSION, it should technically work.

When I look for ready_only variable details on secondary cluster then I see it is set to false:

show global variables like 'read_only%';

Variable_Name   Value
read_only       OFF

Does anyone know if I am missing something here?

Update

In my .net6.0 application, if I explicitly execute set @@aurora_replica_read_consistency=SESSION before SaveChanges(), I get a different error. This concurrency error seems misleading since I’m the only one connected to secondary cluster.

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)

Sniffing MySQL traffic from application, I noticed that this SaveChanges() issues a UPDATE statement along with select ROW_COUNT();. When ROW_COUNT() executes, it returns 0 which causes above exception and then the transaction rollbacks.

Tags:

2

Answers


  1. 0

    Start by checking the value of the variable from your app, using the DbContext that fails to change the records:

    var connection = context.Database.GetDbConnection();
if (connection.State != ConnectionState.Open)
    connection.Open();

var command = connection.CreateCommand();
command.CommandText = "select @@read_only;";

var result = (long)command.ExecuteScalar();
Trace.Assert(result == 0);

    You also want to check the connection string that is used by the open connection and ensure, that it is expected.

    Comment on your results or update your question and we’ll take it from there.

    Login or Signup to reply.
  2. 0

    @soccer7’s solution solved this problem for me

    You need to implement a DbCommandInterceptor, split EF’s query, execute the query and SuppressFinalize SELECT ROW_COUNT();

    This is my solution:

    public class ForwardWritesCommandInterceptor : DbCommandInterceptor
{
    private const string UPDATE_COMMAND = "SELECT ROW_COUNT();";

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        if (ShouldSplitQuery(command.CommandText))
        {
            return SplitUpdateQueryAsync(command).Result;
        }
        return base.ReaderExecuting(command, eventData, result);
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        if (ShouldSplitQuery(command.CommandText))
        {
            return await SplitUpdateQueryAsync(command);
        }
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    private bool ShouldSplitQuery(string query) => query.Trim().EndsWith(UPDATE_COMMAND);

    /// <summary>
    /// Execute the UPDATE part of the query, then SuppressWithResult the SELECT part
    /// </summary>
    private async ValueTask<InterceptionResult<DbDataReader>> SplitUpdateQueryAsync(DbCommand command)
    {
        command.CommandText = command.CommandText.Substring(0, command.CommandText.LastIndexOf(UPDATE_COMMAND));
        await command.ExecuteNonQueryAsync();
        command.CommandText = UPDATE_COMMAND;
        return InterceptionResult<DbDataReader>.SuppressWithResult(await command.ExecuteReaderAsync());
    }
}

    Register this in services.AddDbContext:

    services.AddDbContext<FooContext>(options => 
...
    options.AddInterceptors(new ForwardWritesCommandInterceptor());
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search