读写分离更新操作跑到从库执行导致错误 返回
a new bird 发布于2025/7/13
数据库连接配置:
"DbOptions": [
{
"Name": "yh_goods_mover",
"NodeType": "master",
"DbType": "mysql",
"ConnectionString": "Server=192.168.16.53;Database=test;User=scm_develop;Password=123456789.aaa;AllowLoadLocalInfile=true;"
},
{
"Name": "yh_goods_mover",
"NodeType": "slave",
"DbType": "mysql",
"ConnectionString": "Server=192.168.16.52;Database=test;User=scm_develop;Password=123456789.aaa;AllowLoadLocalInfile=true;"
}
]
SqlSugar注入方式:
/// <summary>
/// 主库
/// </summary>
public const string DB_MASTER = "master";
/// <summary>
/// 从库
/// </summary>
public const string DB_SLAVE = "slave";
/// <summary>
/// SqlSugar 上下文初始化
/// </summary>
/// <param name="services"></param>
public static void AddSqlSugar(this IServiceCollection services)
{
// 动态表达式 SqlFunc 支持,https://www.donet5.com/Home/Doc?typeId=2569
StaticConfig.DynamicExpressionParserType = typeof(DynamicExpressionParser);
StaticConfig.DynamicExpressionParsingConfig = new ParsingConfig
{
CustomTypeProvider = new SqlSugarTypeProvider()
};
var dbConnList = App.GetConfig<List<DbOptions>>("DbOptions");
var sugarConns = dbConnList.GroupBy(s => new { s.Name, s.DbType }).Select(g => new ConnectionConfig
{
ConfigId = g.Key.Name,
DbType = g.Key.DbType,
ConnectionString = (g.FirstOrDefault(s => s.NodeType == DB_MASTER) ?? g.FirstOrDefault())?.ConnectionString,
SlaveConnectionConfigs = g.Where(s => s.NodeType == DB_SLAVE).Select(s => new SlaveConnectionConfig
{
ConnectionString = s.ConnectionString
}).ToList()
}).ToList();
sugarConns.ForEach(SetDbConfig);
services.AddSingleton<ISqlSugarClient>(s =>
{
var logger = s.GetRequiredService<ILogger<SqlSugarScope>>();
var sqlSugar = new SqlSugarScope(sugarConns, db =>
{
sugarConns.ForEach(config =>
{
var dbProvider = db.GetConnectionScope(config.ConfigId);
SetDbAop(dbProvider, logger, config);
//SetDbDiffLog(dbProvider, logger);
});
});
return sqlSugar;
});
services.AddScoped<IUnitOfWork, UnitOfWork>();
services.AddScoped(typeof(IBaseRepository<>), typeof(BaseRepository<>)); // 仓储注册
}
/// <summary>
/// 配置连接属性
/// </summary>
/// <param name="config"></param>
public static void SetDbConfig(ConnectionConfig config)
{
var configureExternalServices = new ConfigureExternalServices
{
EntityNameService = (type, entity) => // 处理表
{
entity.IsDisabledDelete = true; // 禁止删除非 sqlsugar 创建的列
// 只处理贴了特性[SugarTable]表
if (!type.GetCustomAttributes<SugarTable>().Any())
return;
var reg = new Regex("Entity$");
if (!entity.DbTableName.Contains('_'))
entity.DbTableName = UtilMethods.ToUnderLine(reg.Replace(entity.DbTableName, "")); // 驼峰转下划线
},
EntityService = (type, column) => // 处理列
{
// 只处理贴了特性[SugarColumn]列
//if (!type.GetCustomAttributes<SugarColumn>().Any())
// return;
if (new NullabilityInfoContext().Create(type).WriteState is NullabilityState.Nullable)
column.IsNullable = true;
if (!column.IsIgnore)
{
if (!column.DbColumnName.Contains('_'))
column.DbColumnName = UtilMethods.ToUnderLine(column.DbColumnName); // 驼峰转下划线
if (type.PropertyType == typeof(string) && column.Length <= 0 && string.IsNullOrEmpty(column.DataType))
column.Length = 50;
if ((type.PropertyType == typeof(decimal) || type.PropertyType == typeof(decimal?)) && column.Length <= 0)
{
column.Length = 18;
column.DecimalDigits = 2;
}
}
}
};
config.ConfigureExternalServices = configureExternalServices;
config.InitKeyType = InitKeyType.Attribute;
config.IsAutoCloseConnection = true;
config.MoreSettings = new ConnMoreSettings
{
IsAutoDeleteQueryFilter = true, // 启用删除查询过滤器
IsAutoUpdateQueryFilter = true, // 启用更新查询过滤器
SqlServerCodeFirstNvarchar = true // 采用Nvarchar
};
}
/// <summary>
/// 配置Aop
/// </summary>
/// <param name="db"></param>
/// <param name="logger"></param>
public static void SetDbAop(SqlSugarScopeProvider db, ILogger<SqlSugarScope> logger, ConnectionConfig config)
{
// 设置超时时间
db.Ado.CommandTimeOut = 30;
// 打印SQL语句
db.Aop.OnLogExecuting = (sql, pars) =>
{
if (System.Diagnostics.Debugger.IsAttached)
{
var log = $"【SqlSugar-执行SQL:{DateTime.Now}】\r\n{UtilMethods.GetSqlString(config.DbType, sql, pars)}\r\n";
logger?.LogInformation(log);
}
};
db.Aop.OnError = ex =>
{
if (ex.Parametres == null) return;
var log = $"【SqlSugar-错误SQL:{DateTime.Now}】\r\n{UtilMethods.GetSqlString(config.DbType, ex.Sql, (SugarParameter[])ex.Parametres)}\r\n";
logger?.LogError(ex, log);
};
db.Aop.OnLogExecuted = (sql, pars) =>
{
// 执行时间超过5秒时
if (!(db.Ado.SqlExecutionTime.TotalSeconds > 5)) return;
var fileName = db.Ado.SqlStackTrace.FirstFileName; // 文件名
var fileLine = db.Ado.SqlStackTrace.FirstLine; // 行号
var firstMethodName = db.Ado.SqlStackTrace.FirstMethodName; // 方法名
var log = $"【SqlSugar-超时SQL:{DateTime.Now}】\r\n【所在文件】:{fileName}\r\n【代码行数】:{fileLine}\r\n【方法名】:{firstMethodName}\r\n" + $"【SQL语句】:{UtilMethods.GetSqlString(config.DbType, sql, pars)}";
logger.LogWarning(log);
};
// 数据审计
db.Aop.DataExecuting = (_, entityInfo) =>
{
// 新增/插入
if (entityInfo.OperationType == DataFilterType.InsertByObject)
{
dynamic entityValue = entityInfo.EntityValue;
// 若创建时间为空则赋值当前时间
if (entityInfo.PropertyName == nameof(BaseEntity.CreateTime))
{
var createTime = entityValue.CreateTime;
if (createTime == null || createTime.Equals(DateTime.MinValue))
entityInfo.SetValue(DateTime.Now);
}
// 若当前用户为空(非web线程时)
if (App.User == null) return;
if (entityInfo.PropertyName == nameof(BaseEntity.CreateId))
{
var createId = entityValue.CreateId;
if (createId == null || createId.ToString() == "" || createId.ToString() == "0")
entityInfo.SetValue(App.User?.FindFirst(ClaimConst.UserId)?.Value ?? "0");
}
else if (entityInfo.PropertyName == nameof(BaseEntity.CreateName))
{
var createName = entityValue.CreateName;
if (string.IsNullOrEmpty(createName))
entityInfo.SetValue(App.User?.FindFirst(ClaimConst.NickName)?.Value ?? "");
}
}
// 编辑/更新
else if (entityInfo.OperationType == DataFilterType.UpdateByObject)
{
dynamic entityValue = entityInfo.EntityValue;
if (entityInfo.PropertyName == nameof(BaseEntity.UpdateTime))
{
var updateTime = entityValue.UpdateTime;
if (updateTime == null || updateTime.Equals(DateTime.MinValue))
entityInfo.SetValue(DateTime.Now);
}
else if (entityInfo.PropertyName == nameof(BaseEntity.UpdateId))
{
var updateId = entityValue.UpdateId;
if (updateId == null || updateId.ToString() == "" || updateId.ToString() == "0")
entityInfo.SetValue(App.User?.FindFirst(ClaimConst.UserId)?.Value);
}
else if (entityInfo.PropertyName == nameof(BaseEntity.UpdateName))
{
var updateName = entityValue.UpdateName;
if (string.IsNullOrEmpty(updateName))
entityInfo.SetValue(App.User?.FindFirst(ClaimConst.NickName)?.Value ?? "");
}
}
};
}UnitOfWork内容:
BaseRepository内容:
调用方式:
结果在 调用更新方法时 报错:
【SqlSugar-错误SQL:07/13/2025 09:24:25】
UPDATE `goods` SET
`update_time` = '2025-07-13 09:24:25.699' WHERE ( `id` = 713889669831241733 )
$Server=192.168.16.52;Database=test;User=scm_develop;Allow Load Local Infile=True;Character Set=utf8
SqlSugar.SqlSugarException: The MySQL server is running with the --read-only option so it cannot execute this statement
热忱回答(14)
-
fate sta VIP0
2025/7/13需要提供完整可能 重现的DEMO
【DEMO可以在从库设置一个不存在的连接,如果走到不存在的连接报错说明就是从库】
,如果提供不了DEMO 【删掉OBJ和BIN打包上传】
你可以在理更新 外面套一个事务强制走主库
0 回复 -
a new bird VIP0
2025/7/13@fate sta:
似乎是 BaseREpository中获取DB的方式影响到了 Db = unitOfWork.GetDbClient().AsTenant().GetConnectionScopeWithAttr<T>();
这个是为了后面多库切换的时候可以根据实体类上的 Tenant 特性 自动切库不过现在还没有多库 所以实体类上就没有标记 Tenant特性 我将 Tenant特性加在实体类上后 现在没有发现那个错误了 我再观察观察
0 回复 -
a new bird VIP0
2025/7/13使用发现还是会出现更新从库的情况,以上是一个demo 需要使用API工具并发请求接口 偶现
$Server=192.168.16.52;Database=testDb;User=scm_develop;Allow Load Local Infile=True;Character Set=utf8 SqlSugar.SqlSugarException: The MySQL server is running with the --read-only option so it cannot execute this statement 麻烦帮忙看下是哪里有问题
@fate sta:
0 回复 -
忘有 VIP0
2025/7/22我也出现了这样的问题
0 回复 -
fate sta VIP0
2025/7/22@忘有:先加事务 强制主库。。 我这边测试一下
0 回复 -
ed VIP0
2025/7/31问题我这里也出现了,群主这个问题有没有解决
0 回复 -
fate sta VIP0
2025/7/31@ed:暂时不能重现,提供远程可以访问的DEMO
0 回复 -
fate sta VIP0
2025/7/31或者强制加事务测试
0 回复 -
fate sta VIP0
2025/7/31AOP输出Update字符串都是正确的
if (System.Diagnostics.Debugger.IsAttached&&sql.StartsWith("UPDATE")) { var log = $"【SqlSugar-执行SQL:{DateTime.Now}】\r\n \r\n {db.Ado.Connection.ConnectionString}"; logger?.LogInformation(log); }
0 回复 -
忘有 VIP0
2025/8/120 回复 -
fate sta VIP0
2025/8/12@忘有:
connectionConfig 这个对象传给ORM不能是引用类型。。必须是new出的新对象
services.AddScoped<SqlSugarClient>(provider => { // 获取配置数据库连接被替换了 var connectionConfig = provider.GetService<IOptions<ConnectionConfig>>(); var db = new SqlSugarClient(connectionConfig.Value, s => { s.Aop.OnLogExecuting = (sql, pars) => { Console.WriteLine(s.Ado.Connection.ConnectionString+sql); }; }); return db; });0 回复 -
fate sta VIP0
2025/8/12不然的话肯定出错了
0 回复 -
fate sta VIP0
2025/8/12var connectionConfig = provider.GetService<IOptions<ConnectionConfig>>();
var config= UtilMethods.CopyConfig(connectionConfig.Value); //禁止单例对象传给ORM
var db = new SqlSugarClient(config, s => {
0 回复 -
a new bird VIP0
2025/8/14@fate sta:好的,我这边改了再观察一下情况看看
0 回复
