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

SqlSugar 沟通中
5 158

背景: 我在项目中有两张历史记录表<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

  • 补充一下, PackRepo 相关代码中 使用了 事务,像这样

    _unitOfWork.BeginTran();
    
    try
    {
    
    	var result = await _packRepo.Add(packRecords);
    	_unitOfWork.CommitTran();
    	return result;
    }
    catch
    {
    	_unitOfWork.RollbackTran();
    	throw;
    }


    但是 PrintRepo 相关代码中没有使用事务

    0 回复
  • fate sta fate sta VIP0
    1个月前

    https://www.donet5.com/Home/Doc?typeId=2366

    提供完整可以重现的DEMO ,初始化启动有没有执行codefirst更新表保证索引创建成功

    0 回复
  • @fate sta
    我自己测试过,不能稳定重现,是个偶发的异常。

    初始化codefirst 只执行了下面的代码

    Db.CodeFirst
    	.SplitTables()
    	 .InitTables(splitTypes.ToArray());


    0 回复
  • fate sta fate sta VIP0
    1个月前

    @Kevin·J·:你表索引删掉 ,执行codefirst 会不会建索引

    不存在随机逻辑

    0 回复
  • @fate sta:我把表索引删掉,然后重启项目重新执行codefirst ,会建索引

    0 回复