.net6.0-WRITE FORWARD-AWS Aurora：MySQL服务器正在使用--Read-Only选项运行，因此无法执行此语句

ux6nzvsh  于 2022-12-10  发布在  Mysql
关注(0)|答案(2)|浏览(157)

我们的AWS RDS(Aurora MySQL 2.10.2)有一个主群集和一个辅助群集。
辅助群集是副本，并且具有读取器示例。该读卡器示例设置为READ_ONLY FALSE，我已经设置了从辅助集群到主集群的写转发。因此，我已经将参数组的变量init_connect设置为值set @@aurora_replica_read_consistency=SESSION
当我通过MySQL工作台连接到辅助群集的读取器终结点时，写转发工作，即如果我发出查询以在辅助群集中创建记录，它将被写前写到主群集，并且查询相同的表会显示新记录。
但是，我的.net6.0应用程序出现了这个问题。如果我将我的.net6.0应用程序(它使用Pomelo.EntityFrameworkCore.MySql)指向连接字符串中的同一辅助群集的读取器终结点，则它将无法工作，并且会失败，并显示以下错误：

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

由于辅助集群init_connect上的参数组变量被设置为值@@aurora_replica_read_consistency=SESSION，因此在技术上应该可以工作。
当我在辅助群集上查找READY_ONLY变量详细信息时，我发现它设置为FALSE：

show global variables like 'read_only%';

Variable_Name   Value
read_only       OFF

有人知道我是不是漏掉了什么吗？

更新

在我的.net6.0应用程序中，如果我在SaveChanges()之前显式执行set @@aurora_replica_read_consistency=SESSION，我会得到一个不同的错误。这个并发错误似乎具有误导性，因为我是唯一连接到辅助集群的人。

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)

在从应用程序嗅探MySQL流量时，我注意到这个SaveChanges()与select ROW_COUNT();一起发出了一条UPDATE语句。当row_count()执行时，它返回0，这会导致上述异常，然后事务回滚。

mysql

2条答案

按热度按时间
oxiaedzo

oxiaedzo1#

首先检查应用程序中变量的值，使用无法更改记录的DbContext：

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

您还希望检查打开的连接使用的连接字符串，并确保它是预期的。
评论您的结果或更新您的问题，我们将从那里开始。

赞(0）分享  回复(0）举报  2022-12-10
t40tm48m

t40tm48m2#

@Soccer7的解决方案为我解决了这个问题
您需要实现DbCommandInterceptor、拆分EF的查询、执行查询和SuppressFinalizeSELECT ROW_COUNT();
这是我的解决方案：

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

在services.AddDbContext中注册：

services.AddDbContext<FooContext>(options => 
...
    options.AddInterceptors(new ForwardWritesCommandInterceptor());
);
赞(0）分享  回复(0）举报  2022-12-10
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com