[spring mybatis]java.sql.SQLException: An attempt by a client to checkout a Connection has timed out

在照抄张老师的秒杀系统的时候, 运行junit测试函数testQueryById, 遇到"java.sql.SQLException: An attempt by a client to checkout a Connection has timed out."问题. 折腾了两个小时解决. 总结如下.

1. 首先可以通过mysql的客户端排除SecKillDao.xml本身错误

2. 在tracestack提示的exception函数(translateExceptionIfPossible)添加断点. 然后debug as junit4.
神奇的发现, console打印了更具体的错误信息(见表2: 更具体的错误信息), 这时在百度这个错误信息就很容易知道如何解决.

即在jdbc.url后面添加&serverTimezone=UTC.

最终, 详细的jdbc.properties配置如下:

jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=serverTimezone=Asia/Shanghai jdbc.username=root jdbc.password=xxxxxx

3. 由于第二点的神奇经历, 以后调试尽量在更底层的地方加断点.

    

原始错误信息: junit失败的tracestack如下: ### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out. ### The error may exist in file [E:\server_project\seckill\target\classes\mapper\SecKillDao.xml] ### The error may involve org.seckill.dao.SeckillDao.queryById ### The error occurred while executing a query ### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out. at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) Console打印如下: 五月 06, 2018 10:49:39 下午 org.springframework.test.context.support.AbstractTestContextBootstrapper getDefaultTestExecutionListenerClassNames 信息: Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener] 五月 06, 2018 10:49:39 下午 org.springframework.test.context.support.AbstractTestContextBootstrapper getTestExecutionListeners 信息: Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@51081592, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@7f9a81e8, org.springframework.test.context.support.DependencyInjectionTestExecutionListener@9629756, org.springframework.test.context.support.DirtiesContextTestExecutionListener@62ee68d8, org.springframework.test.context.transaction.TransactionalTestExecutionListener@735b5592, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@58651fd0] 五月 06, 2018 10:49:39 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions 信息: Loading XML bean definitions from class path resource [spring/spring-dao.xml] 五月 06, 2018 10:49:39 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh 信息: Refreshing org.springframework.context.support.GenericApplicationContext@4cf777e8: startup date [Sun May 06 22:49:39 CST 2018]; root of context hierarchy 五月 06, 2018 10:49:39 下午 com.mchange.v2.log.MLog  信息: MLog clients using java 1.4+ standard logging. 五月 06, 2018 10:49:40 下午 com.mchange.v2.c3p0.C3P0Registry  信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10] 五月 06, 2018 10:49:40 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource  信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 2, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 1000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge1669veyos801hrrila|6dc17b83, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.cj.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge1669veyos801hrrila|6dc17b83, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf-8&useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 30, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ] 五月 06, 2018 10:49:40 下午 com.mchange.v2.resourcepool.BasicResourcePool  警告: Bad pool size config, start 3 < min 10. Using 10 as start.

表2: 更具体的错误信息 警告: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1385856 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (2). Last acquisition attempt exception:  java.sql.SQLException: The server time zone value '?й???????' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)