An online order history data field refresh operation, 3 tables with more than 1 million data. Since the synchronization update is too slow for about 20 minutes or more, the different method is adopted.
The local library refresh is okay, but the following exception occurs when the online library is reached:
### Error updating database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
at sun.reflect.GeneratedMethodAccessor380.invoke(Unknown Source)
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:433)
... 24 more
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:338)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:84)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
at sun.reflect.GeneratedMethodAccessor381.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy521.update(Unknown Source)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
... 28 more
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30005ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:669)
at com.zaxxer.hikari.pool.HikariPool.getConnection (HikariPool.java: 183 )
at com.zaxxer.hikari.pool.HikariPool.getConnection (HikariPool.java: 148 )
at com.zaxxer.hikari.HikariDataSource.getConnection (HikariDataSource.java: 128 )
at com.zaxxer.hikari.HikariDataSource$$FastClassBySpringCGLIB$$eeb1ae86.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:136)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:124)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at com.zaxxer.hikari.HikariDataSource$$EnhancerBySpringCGLIB$$f68c05a.getConnection(<generated>)
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78)
Two, the solution
Through the exception, it can be found that the database connection cannot be obtained. It is guessed that the number of connections is insufficient. Therefore, modify the HikariPool connection pool configuration to solve the problem.
before fixing:
spring:
datasource:
hikari:
connection-test-query: SELECT 1 FROM DUAL
connection-timeout: 30000
maximum-pool-size: 20
max-lifetime: 1800000
minimum-idle: 5
connection-init-sql: SET NAMES utf8mb4
After modification:
spring: datasource: hikari: connection-test-query: SELECT 1 FROM DUAL connection-timeout: 600000 maximum-pool-size: 500 max-lifetime: 1800000 minimum-idle: 20 validation-timeout: 3000 idle-timeout: 60000 connection-init-sql: SET NAMES utf8mb4
Read More:
- [Solved] Caused by: java.sql.SQLException: Access denied for user ‘root‘@‘hadoop102‘ (using password: YES)
- java.sql.SQLException: Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space
- Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
- [Solved] error {dataSource-1} init error java.sql.SQLException: com.mysql.cj.jdbc.Driver
- [Solved] MYSQL 5.7 gruop by eRROR: this is incompatible with sql_mode=only_full_group_by
- MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by
- [Solved] public key is not available client side (option serverRsaPublicKeyFile not set)
- [Solved] java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corres
- [Solved] Druid connection pooling Error: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
- IDEA maven Config MYSQL Connection Error: Could not create connection to database server.
- [Solved] MYSQL Start Project Error: this is incompatible with sql_mode=only_full_group_by
- Bulk Update Error: #Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax； check the m
- Android connection to cloud MySQL error: java.lang.NoClassDefFoundError Failed resolution of LjavasqlSQLType
- Cause: java.sql.SQLException: ORA-01407: Unable to update (“MESIRJ”.”TMMIC01″.”RDEL”) is NULL
- Caused by: com.ibatis.common.xml.NodeletException: Error parsing XML. Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 281; The content of element type “sqlMapConfig” is incomplete
- SQL Server Group sort de duplication row_ number() over ( PARTITION BY t1.col_ 2 ORDER BY 1 )
- Database mirroring connection error 4’An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)
- [MySQL] The principle of group by aggregation function and the reason for aggregation limitation SELECT list is not in GROUP BY clause and contains nonaggregated column
- [Solved] com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
- [Solved] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate