【数据源切换后报错：Error querying database. Cause: java.sql.SQLSyntaxErrorException: Table ‘db.tb‘】

背景

工具版本：无关
技术框架：springboot的若依管理系统
业务场景：项目是运行在Orcl,mysql*2=3个数据源之上的数据同步系统

问题描述

项目本来运行得没问题，但是在切换数据源之后却出现了这样的错误：

14:44:37.219 [http-nio-6666-exec-1] INFO  o.a.c.c.C.[.[.[/] - [log,173] - Initializing Spring DispatcherServlet 'dispatcherServlet'
14:44:37.707 [http-nio-6666-exec-1] DEBUG c.r.q.m.S.selectJobById - [debug,137] - ==>  Preparing: select job_id, job_name, job_group, invoke_target, cron_expression, misfire_policy, concurrent, status, create_by, create_time, remark from sys_job where job_id = ?
14:44:37.708 [http-nio-6666-exec-1] DEBUG c.r.q.m.S.selectJobById - [debug,137] - ==> Parameters: 102(Long)
14:44:37.712 [http-nio-6666-exec-1] DEBUG c.r.q.m.S.selectJobById - [debug,137] - <==      Total: 1
手术信息===视图同步到中间库===开始
14:44:37.731 [quartzScheduler_Worker-1] INFO  c.r.f.d.DynamicDataSourceContextHolder - [setDataSourceType,26] - 切换到THREE数据源
14:44:47.956 [schedule-pool-1] DEBUG c.r.s.m.S.insertOperlog - [debug,137] - ==>  Preparing: insert into sys_oper_log(title, business_type, method, request_method, operator_type, oper_name, dept_name, oper_url, oper_ip, oper_location, oper_param, json_result, status, error_msg, cost_time, oper_time) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, sysdate())
14:44:47.956 [quartzScheduler_Worker-1] INFO  c.r.f.d.DynamicDataSourceContextHolder - [setDataSourceType,26] - 切换到SLAVE数据源
14:44:47.966 [schedule-pool-1] DEBUG c.r.s.m.S.insertOperlog - [debug,137] - ==> Parameters: 定时任务(String), 2(Integer), com.ruoyi.quartz.controller.SysJobController.run()(String), PUT(String), 1(Integer), admin(String), null, /monitor/job/run(String), 127.0.0.1(String), 内网IP(String), {"jobGroup":"DEFAULT","jobId":102,"misfirePolicy":"0","params":{}}(String), {"msg":"操作成功","code":200}(String), 0(Integer), null, 42(Long)
14:44:48.083 [schedule-pool-1] DEBUG c.r.s.m.S.insertOperlog - [debug,137] - <==    Updates: 1
14:45:07.620 [quartzScheduler_Worker-1] INFO  c.a.d.p.DruidDataSource - [init,996] - {dataSource-2} inited
14:45:07.621 [quartzScheduler_Worker-1] DEBUG c.r.t.m.V.selectAllIn - [debug,137] - ==>  Preparing: select VISIT_ID, VISIT_CODE, to_char(operation_time, 'YYYY-MM-DD HH24:MI:SS') as OPERATION_TIME, OPERATION_ID, OPERATION_CODE, OPERATION_NAME from V_SW_OPERATION
14:45:07.666 [quartzScheduler_Worker-1] DEBUG c.r.t.m.V.selectAllIn - [debug,137] - ==> Parameters: 
14:45:07.680 [quartzScheduler_Worker-1] DEBUG c.r.t.m.V.selectAllIn - [debug,137] - <==      Total: 0
14:45:15.250 [quartzScheduler_Worker-1] INFO  c.r.f.d.DynamicDataSourceContextHolder - [setDataSourceType,26] - 切换到MASTER数据源
14:45:15.292 [quartzScheduler_Worker-1] DEBUG c.r.l.m.T.selectInDepartment - [debug,137] - ==>  Preparing: select o.operation_key, o.patient_key, o.visit_id, o.visit_code, o.operation_time, o.operation_id, o.operation_code, o.operation_name, o.status, o.create_time, o.operator_code from t_operation o inner join( select patient_key from t_patient where status=1 and flag=1 ) p on p.patient_key=o.patient_key and o.status=1
14:45:15.306 [quartzScheduler_Worker-1] DEBUG c.r.l.m.T.selectInDepartment - [debug,137] - ==> Parameters: 
14:45:15.313 [quartzScheduler_Worker-1] DEBUG c.r.l.m.T.selectInDepartment - [debug,137] - <==      Total: 0
手术信息===视图同步到中间库===结束
手术信息===中间库同步到业务库===开始
14:45:38.472 [quartzScheduler_Worker-1] INFO  c.r.f.d.DynamicDataSourceContextHolder - [setDataSourceType,26] - 切换到THREE数据源
14:45:45.040 [quartzScheduler_Worker-1] DEBUG c.r.s.m.M.selectMidOperationListGroupByOperationId - [debug,137] - ==>  Preparing: select _id, visit_id, visit_code,date_format(operation_time, '%Y-%m-%d %H:%i:%s') as operation_time, operation_id, operation_code, operation_name, operator_code, operator_name, message_time, create_time, data_type, is_receive from mid_operation where is_receive=0 and message_time <= ? group by operation_id
14:45:45.043 [quartzScheduler_Worker-1] DEBUG c.r.s.m.M.selectMidOperationListGroupByOperationId - [debug,137] - ==> Parameters: 2023-08-06 14:45:38(String)
14:45:45.098 [quartzScheduler_Worker-1] ERROR c.r.q.u.AbstractQuartzJob - [execute,49] - 任务执行异常  - ：
java.lang.reflect.InvocationTargetException: null
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.ruoyi.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:61)
	at com.ruoyi.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:33)
	at com.ruoyi.quartz.util.QuartzDisallowConcurrentExecution.doExecute(QuartzDisallowConcurrentExecution.java:19)
	at com.ruoyi.quartz.util.AbstractQuartzJob.execute(AbstractQuartzJob.java:43)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
### The error may exist in file [D:\dev\code\intensive-care\critical-timing\ruoyi-system\target\classes\mapper\sync\MidOperationMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select _id, visit_id, visit_code,date_format(operation_time, '%Y-%m-%d %H:%i:%s') as operation_time, operation_id, operation_code, operation_name, operator_code, operator_name, message_time, create_time, data_type, is_receive from mid_operation               where is_receive=0 and message_time <= ?         group by operation_id
### Cause: java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy106.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at com.sun.proxy.$Proxy169.selectMidOperationListGroupByOperationId(Unknown Source)
	at com.ruoyi.sync.service.impl.MidOperationServiceImpl.selectMidOperationListGroupByOperationId(MidOperationServiceImpl.java:66)
	at com.ruoyi.sync.service.impl.MidOperationServiceImpl$$FastClassBySpringCGLIB$$1db74fd5.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
	at com.ruoyi.framework.aspectj.DataSourceAspect.around(DataSourceAspect.java:49)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
	at com.ruoyi.sync.service.impl.MidOperationServiceImpl$$EnhancerBySpringCGLIB$$64c60808.selectMidOperationListGroupByOperationId(<generated>)
	at com.ruoyi.light.service.impl.TOperationServiceImpl.getMidOperation(TOperationServiceImpl.java:144)
	at com.ruoyi.light.service.impl.TOperationServiceImpl$$FastClassBySpringCGLIB$$2d5bc984.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
	at com.ruoyi.light.service.impl.TOperationServiceImpl$$EnhancerBySpringCGLIB$$65197a75.getMidOperation(<generated>)
	at com.ruoyi.quartz.task.syncTask.operationToLight(syncTask.java:54)
	at com.ruoyi.quartz.task.syncTask.operationToMidLight(syncTask.java:35)
	... 10 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3446)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3444)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:152)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy109.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:151)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
	at com.sun.proxy.$Proxy203.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	... 52 common frames omitted
14:45:45.101 [quartzScheduler_Worker-1] DEBUG c.r.q.m.S.insertJobLog - [debug,137] - ==>  Preparing: insert into sys_job_log( job_name, job_group, invoke_target, job_message, status, exception_info, create_time )values( ?, ?, ?, ?, ?, ?, sysdate() )
14:45:45.102 [quartzScheduler_Worker-1] DEBUG c.r.q.m.S.insertJobLog - [debug,137] - ==> Parameters: 手术信息同步到中间库、业务库(String), DEFAULT(String), syncTask.operationToMidLight(String), 手术信息同步到中间库、业务库 总共耗时：67371毫秒(String), 1(String), java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.ruoyi.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:61)
	at com.ruoyi.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:33)
	at com.ruoyi.quartz.util.QuartzDisallowConcurrentExecution.doExecute(QuartzDisallowConcurrentExecution.java:19)
	at com.ruoyi.quartz.util.AbstractQuartzJob.execute(AbstractQuartzJob.java:43)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
### The error may exist in file [D:\dev\code\intensive-care\critical-timing\ruoyi-system\target\classes\mapper\sync\MidOperationMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select _id, visit_id, visit_code,date_format(operation_time, '%Y-%m-%d %H:%i:%s') as operation_time, operation_id, operation_code, operation_name, operator_code, operator_name, message_time, create_time, data_type, is_receive from mid_operation               where is_receive=0 and message_time <= ?         group by operation_id
### Cause: java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'light_icu.mid_operation' doesn't exist
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTransl(String)
14:45:45.112 [quartzScheduler_Worker-1] DEBUG c.r.q.m.S.insertJobLog - [debug,137] - <==    Updates: 1

情况分析

很显然，这是说我数据源有问题，没切过来。
但是啊，我从配置文件检查到代码注解，都没有发现哪里有漏传的情况。
而且，前一半能正常运行（这里是坑），后边一半怎么就不行了？

最终，经大神对数据源切换的反复排查，发现是报错表的配置文件的数据源enable没有写，前一半能行是因为那之前的数据为0条，没有执行后面的数据库操作，所以没报错。
bug的排查很值得好好学习。

解决方案

很简单，数据源上写上这个就好了，但是知道有个东西要写在这里很困难

            # 业务库数据源
            master:
                enabled: true