codefirst自动分表,建表时创建索引失败 返回

背景: 我在项目中有两张历史记录表<pack_box_record> 和 <print_record>,
使用到了codefirst自动分表,并配置了索引信息,2023,2024年均使用正常
数据库:mysql 8.0.28
sqlsugarcore 5.1.3.38
异常现象:跨年2025之后,某些数据查询速度越来越慢,排查发现 :
1、print_record_20250101 没有索引,但是2023、2024的分表索引均存在(没有手动创建过索引,都是orm自动生成的);
2、pack_box_record_20250101 一切正常,有索引
追溯日志:
追溯到2025/01/01的系统日志,确实发现了两笔数据库相关的异常:
异常1:
我在PackRepo.Add 中调用了
SqlSugarScope.Insertable(poList).SplitTable().ExecuteReturnSnowflakeIdListAsync();引发异常:
MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'pack_box_record_20250101' already exists
at SqlSugar.AdoProvider.ExecuteCommand(String sql, SugarParameter[] parameters)
at SqlSugar.MySqlDbMaintenance.CreateTable(String tableName, List`1 columns, Boolean isCreatePrimaryKey)
at SqlSugar.MySqlCodeFirst.NoExistLogic(EntityInfo entityInfo)
at SqlSugar.CodeFirstProvider.Execute(Type entityType)
at SqlSugar.CodeFirstProvider.InitTables(Type entityType)
at SqlSugar.CodeFirstProvider.InitTables[T]()
at SqlSugar.SplitInsertable`1.CreateTable()
at SqlSugar.SplitInsertable`1._ExecuteReturnSnowflakeIdListAsync()
at SqlSugar.SplitInsertable`1.ExecuteReturnSnowflakeIdListAsync()
at Robo.Repository.PackRepo.Add(List`1 boList) in D:\robo-print-bed\Robo.Repository\PackRepo.cs:line 45
at Robo.Service.PackBoxService.CheckSN(PackBoxReq model) in D:\robo-print-bed\Robo.Service\PackManage\PackBoxService.cs:line 152
at Robo.Webapi.Controllers.PackBoxController.CheckSN(PackBoxReq model) in D:\robo-print-bed\Robo.Webapi\Controllers\PackManage\PackBoxController.cs:line 62
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
虽然引发了异常,但是 pack_box_record_20250101表及索引均存在,一切正常
异常2:
我在 PrintRepo.Add 中调用了
SqlSugarScope.Insertable<PrintRecord>(poList).SplitTable().ExecuteReturnSnowflakeIdListAsync();引发异常:
System.InvalidOperationException: Connection must be valid and open to rollback transaction
at MySql.Data.MySqlClient.MySqlTransaction.Rollback()
at SqlSugar.AdoProvider.RollbackTran()
at SqlSugar.SplitInsertable`1.ExecuteReturnSnowflakeIdListAsync()
at Robo.Repository.PrintRepo.Add(List`1 bo) in D:\robo-print-bed\Robo.Repository\PrintRepo.cs:line 48
at Robo.Service.PrintManage.PrintLocalRFIDStrategy.PrintRequest(PrintReq req) in D:\robo-print-bed\Robo.Service\PrintManage\PrintLocalRFIDStrategy.cs:line 102
at Robo.Service.PrintManage.PrintService.PrintRequest(PrintReq req) in D:\robo-print-bed\Robo.Service\PrintManage\PrintService.cs:line 77
at Robo.Webapi.Controllers.PrintController.PrintRequest(PrintReq req) in D:\robo-print-bed\Robo.Webapi\Controllers\PrintManage\PrintController.cs:line 124
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
print_record_20250101 表创建成功,但是索引创建失败
希望帮忙分析下是什么问题
热忱回答(5)
-
Kevin·J· VIP0
1个月前补充一下, PackRepo 相关代码中 使用了 事务,像这样
_unitOfWork.BeginTran(); try { var result = await _packRepo.Add(packRecords); _unitOfWork.CommitTran(); return result; } catch { _unitOfWork.RollbackTran(); throw; }
但是 PrintRepo 相关代码中没有使用事务0 回复 -
fate sta VIP0
1个月前https://www.donet5.com/Home/Doc?typeId=2366
提供完整可以重现的DEMO ,初始化启动有没有执行codefirst更新表保证索引创建成功
0 回复 -
Kevin·J· VIP0
1个月前@fate sta:
我自己测试过,不能稳定重现,是个偶发的异常。初始化codefirst 只执行了下面的代码
Db.CodeFirst .SplitTables() .InitTables(splitTypes.ToArray());
0 回复 -
fate sta VIP0
1个月前@Kevin·J·:你表索引删掉 ,执行codefirst 会不会建索引
不存在随机逻辑
0 回复 -
Kevin·J· VIP0
1个月前@fate sta:我把表索引删掉,然后重启项目重新执行codefirst ,会建索引
0 回复