springboot+druid+mybatis做多数据源

甜的柠檬酸 2021-01-20 10:35:42
我是使用Druid数据源，



mysql的wait_time是28800秒，事务是使用aop手动控制的。

程序在运行期间时不时会出现这个错误，各位大佬们，这是怎么回事啊。



2021-01-19 11:13:05.230 ERROR 1965954 --- [n-cron-thread-8] com.alibaba.druid.util.JdbcUtils : close connection error

java.sql.SQLNonTransientConnectionException: Communications link failure during rollback(). Transaction resolution unknown.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:1866)
at com.mysql.cj.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:1720)
at com.mysql.cj.jdbc.ConnectionImpl.close(ConnectionImpl.java:720)
at com.alibaba.druid.util.JdbcUtils.close(JdbcUtils.java:73)
at com.alibaba.druid.pool.DruidDataSource.discardConnection(DruidDataSource.java:1340)
at com.alibaba.druid.pool.DruidDataSource.handleFatalError(DruidDataSource.java:1598)
at com.alibaba.druid.pool.DruidDataSource.handleConnectionException(DruidDataSource.java:1540)
at com.alibaba.druid.pool.DruidPooledConnection.handleException(DruidPooledConnection.java:133)
at com.alibaba.druid.pool.DruidPooledConnection.commit(DruidPooledConnection.java:751)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:329)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
at com.guardian.aspect.MultiDataSourceTransactionAspect.afterReturning(MultiDataSourceTransactionAspect.java:90)
at sun.reflect.GeneratedMethodAccessor91.invoke(Unknown Source)
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:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:626)
at org.springframework.aop.aspectj.AspectJAfterReturningAdvice.afterReturning(AspectJAfterReturningAdvice.java:66)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:56)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.guardian.synservice.impl.Ym2NbDataSynServiceImpl$$EnhancerBySpringCGLIB$$ce0fed7.synIncrementData(<generated>)
at com.guardian.job.DataSynJob.ym2nbJob(DataSynJob.java:86)
at sun.reflect.GeneratedMethodAccessor98.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

2021-01-19 11:13:05.231 ERROR 1965954 --- [n-cron-thread-8] com.alibaba.druid.pool.DruidDataSource : discard connection

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 147 milliseconds ago. The last packet sent successfully to the server was 148 milliseconds ago.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:813)
at com.alibaba.druid.pool.DruidPooledConnection.commit(DruidPooledConnection.java:749)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:329)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
at com.guardian.aspect.MultiDataSourceTransactionAspect.afterReturning(MultiDataSourceTransactionAspect.java:90)
at sun.reflect.GeneratedMethodAccessor91.invoke(Unknown Source)
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:644)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:626)
at org.springframework.aop.aspectj.AspectJAfterReturningAdvice.afterReturning(AspectJAfterReturningAdvice.java:66)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:56)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.guardian.synservice.impl.Ym2NbDataSynServiceImpl$$EnhancerBySpringCGLIB$$ce0fed7.synIncrementData(<generated>)
at com.guardian.job.DataSynJob.ym2nbJob(DataSynJob.java:86)
at sun.reflect.GeneratedMethodAccessor98.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 147 milliseconds ago. The last packet sent successfully to the server was 148 milliseconds ago.
at sun.reflect.GeneratedConstructorAccessor62.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:562)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:732)
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:671)
at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:986)
at com.mysql.cj.protocol.a.NativeProtocol.sendQueryString(NativeProtocol.java:921)
at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1165)
at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:801)
... 32 common frames omitted
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:210)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
...全文
375 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
Forevermark993 2021-01-22
  • 打赏
  • 举报
 回复
https://blog.csdn.net/qq_34322777/article/details/80833935 参考这篇大佬的吧
甜的柠檬酸 2021-01-22
  • 打赏
  • 举报
 回复
引用 6 楼 Farmermark993 的回复:
具体事务怎么做，就看多数据源那里你到底是怎么用的，不一定非aop不可
谢谢，问题找到了，是由于触发器，导致mysql重启，因为docker安装的mysql，启动的很快，之前一直以为是程序的问题，后面发现是mysql在时不时重启。
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复 
  @Bean
        public DataSourceTransactionManager dataSourceTransactionManagerAK(@Qualifier("dataSource3") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }


        @Bean
        @Primary
        public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }

  @Bean
        public DataSourceTransactionManager dataSourceTransactionManagerYM(@Qualifier("dataSource2") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复
引用 9 楼 Farmermark993 的回复:
你的事务管理器确实有问题，一直使用的是默认的事务管理器 

for (String s : manager) {
            DataSourceTransactionManager transactionManager = applicationContext.getBean(DataSourceTransactionManager.class);
            TransactionStatus transaction = transactionManager.getTransaction(def);
            pair.push(new AbstractMap.SimpleEntry<>(transactionManager, transaction));
        }
你的思路没问题，需要在配置数据源的时候，把不同数据源配置到不同的事务管理器中 还有就是如果涉及到多个数据源可以使用spring-boot-starter-jta-atomikos来处理事务， 上面的错误应该是提交事务的时候造成的
@Override @MultiDataSourceTransactional(transactionManagers = {"dataSourceTransactionManager", "dataSourceTransactionManagerYM"}) public List<SynMessage> synIncrementGPSData(int synCount) {}
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复
引用 9 楼 Farmermark993 的回复:
你的事务管理器确实有问题，一直使用的是默认的事务管理器 

for (String s : manager) {
            DataSourceTransactionManager transactionManager = applicationContext.getBean(DataSourceTransactionManager.class);
            TransactionStatus transaction = transactionManager.getTransaction(def);
            pair.push(new AbstractMap.SimpleEntry<>(transactionManager, transaction));
        }
你的思路没问题，需要在配置数据源的时候，把不同数据源配置到不同的事务管理器中 还有就是如果涉及到多个数据源可以使用spring-boot-starter-jta-atomikos来处理事务， 上面的错误应该是提交事务的时候造成的
没有啊，这里循环的是根据注解配置来的
Forevermark993 2021-01-21
  • 打赏
  • 举报
 回复
你的事务管理器确实有问题，一直使用的是默认的事务管理器 

for (String s : manager) {
            DataSourceTransactionManager transactionManager = applicationContext.getBean(DataSourceTransactionManager.class);
            TransactionStatus transaction = transactionManager.getTransaction(def);
            pair.push(new AbstractMap.SimpleEntry<>(transactionManager, transaction));
        }
你的思路没问题，需要在配置数据源的时候，把不同数据源配置到不同的事务管理器中 还有就是如果涉及到多个数据源可以使用spring-boot-starter-jta-atomikos来处理事务， 上面的错误应该是提交事务的时候造成的
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复
引用 6 楼 Farmermark993 的回复:
具体事务怎么做，就看多数据源那里你到底是怎么用的，不一定非aop不可
目前是采用的这种方式
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复
引用 6 楼 Farmermark993 的回复:
具体事务怎么做，就看多数据源那里你到底是怎么用的，不一定非aop不可
@Aspect @Configuration public class MultiDataSourceTransactionAaspect implements ApplicationContextAware { private static final ThreadLocal<Stack<AbstractMap.SimpleEntry<DataSourceTransactionManager, TransactionStatus>>> threadLocal = new ThreadLocal<>(); private ApplicationContext applicationContext; @Override public void setApplicationContext(ApplicationContext applicationContext) throws BeansException { this.applicationContext = applicationContext; } private DefaultTransactionDefinition def = new DefaultTransactionDefinition(); public MultiDataSourceTransactionAaspect() { // 非只读模式 def.setReadOnly(false); // 事务隔离级别：采用数据库的 def.setIsolationLevel(TransactionDefinition.ISOLATION_DEFAULT); // 事务传播行为 def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); } @Pointcut("@annotation(com.guardian.annotation.MultiDataSourceTransactional)") public void pointcut() { } @Before(value = "pointcut() && @annotation(transactional)") public void before(MultiDataSourceTransactional transactional) { String[] manager = transactional.transManager(); Stack<AbstractMap.SimpleEntry<DataSourceTransactionManager, TransactionStatus>> pair = new Stack<>(); for (String s : manager) { DataSourceTransactionManager transactionManager = applicationContext.getBean(DataSourceTransactionManager.class); TransactionStatus transaction = transactionManager.getTransaction(def); pair.push(new AbstractMap.SimpleEntry<>(transactionManager, transaction)); } threadLocal.set(pair); } @AfterReturning(pointcut = "pointcut()") public void afterReturning() { Stack<AbstractMap.SimpleEntry<DataSourceTransactionManager, TransactionStatus>> simpleEntries = threadLocal.get(); while (!simpleEntries.empty()) { AbstractMap.SimpleEntry<DataSourceTransactionManager, TransactionStatus> pop = simpleEntries.pop(); System.out.println("test"); pop.getKey().commit(pop.getValue()); } threadLocal.remove(); } /** * 回滚事务 */ @AfterThrowing(value = "pointcut()") public void afterThrowing() { // ※栈顶弹出（后进先出） Stack<AbstractMap.SimpleEntry<DataSourceTransactionManager, TransactionStatus>> pairStack = threadLocal.get(); while (!pairStack.empty()) { AbstractMap.SimpleEntry<DataSourceTransactionManager, TransactionStatus> pair = pairStack.pop(); pair.getKey().rollback(pair.getValue()); } threadLocal .remove(); } }
Forevermark993 2021-01-21
  • 打赏
  • 举报
 回复
具体事务怎么做，就看多数据源那里你到底是怎么用的，不一定非aop不可
Forevermark993 2021-01-21
  • 打赏
  • 举报
 回复
只是推测你的错误，感觉是aop事务没有处理好
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复
而且这里的多数据源还是一起同时使用的，不是那种动态切换的。
甜的柠檬酸 2021-01-21
  • 打赏
  • 举报
 回复
引用 2 楼 Farmermark993 的回复:
应该是aop事务的问题，你去掉手动控制事务这块试试
这里因为是多数据源，如果这里去了的话，事务就有问题了
Forevermark993 2021-01-21
  • 打赏
  • 举报
 回复
应该是aop事务的问题，你去掉手动控制事务这块试试
甜的柠檬酸 2021-01-20
  • 打赏
  • 举报
 回复
程序一开始是能够正常启动，而且数据库什么的也能够访问
SpringBoot + mybatis-plus + druid 实现mySql与Orcl双数据源
SpringBoot + mybatis-plus + druid 实现mySql与Orcl双数据源
springboot+mvc+mybatis(通用mapper)+druid+jsp+bootstrap实现权限管理文件上传下载多数据源切换
springboot+mvc+mybatis(通用mapper)+druid+jsp+bootstrap实现权限管理文件上传下载多数据源切换
SpringBoot+ClickHouse+Vue+Druid整合实战
查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中 【本课重点】 技术栈： SpringBoot2  MybatisPlus Druid Java 8 Vue Element-UI D2-Crud-Plus   开发思路：...
Springboot+mybatis+druid多数据源配置（oracle+mybatis）
技术架构：springboot2.0 + mybatis + druid, 数据库：Oracle，Mysql 数据库分离调试，针对不同数据库情况
springboot+druid+mybatis多数据源动态切换案例
springboot+druid+mybatis多数据源动态切换，基于sql类型完成读写分离操作
Java EE

67,512

社区成员

225,881

社区内容

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器，保护我们的业务核心组件（中间件），以延续它的生命力，而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章