How to check what database pool is used with Spring Boot?

Question

I am using Spring Boot 1.3.1 with MySQL. I am getting this in my logs:

The last packet successfully received from the server was 97,025,381 milliseconds ago.  
The last packet sent successfully to the server was 97,025,381 milliseconds ago. 
is longer than the server configured value of 'wait_timeout'. 
You should consider either expiring and/or testing connection validity before use in your application, 
increasing the server configured values for client timeouts, 
or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Full stack:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 97,025,381 milliseconds ago.  The last packet sent successfully to the server was 97,025,381 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_66]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_66]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_66]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[na:1.8.0_66]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:981) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3652) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2460) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4874) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]
        at sun.reflect.GeneratedMethodAccessor70.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_66]
        at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_66]
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.0.30.jar!/:na]
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.0.30.jar!/:na]
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.0.30.jar!/:na]
        at com.sun.proxy.$Proxy78.setAutoCommit(Unknown Source) ~[na:na]
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1471) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:61) ~[hibernate-entitymanager-4.3.11.Final.jar!/:4.3.11.Final]
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:170) ~[spring-orm-4.2.4.RELEASE.jar!/:4.2.4.RELEASE]
        at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380) ~[spring-orm-4.2.4.RELEASE.jar!/:4.2.4.RELEASE]
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) ~[spring-tx-4.2.4.RELEASE.jar!/:4.2.4.RELEASE]
        at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427) ~[spring-tx-4.2.4.RELEASE.jar!/:4.2.4.RELEASE]
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276) ~[spring-tx-4.2.4.RELEASE.jar!/:4.2.4.RELEASE]
        ... 80 common frames omitted
Caused by: java.net.SocketException: Broken pipe
        at java.net.SocketOutputStream.socketWrite0(Native Method) ~[na:1.8.0_66]
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) ~[na:1.8.0_66]
        at java.net.SocketOutputStream.write(SocketOutputStream.java:153) ~[na:1.8.0_66]
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.8.0_66]
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[na:1.8.0_66]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3634) ~[mysql-connector-java-5.1.38.jar!/:5.1.38]

According to spring boot docs, the tomcat-pool should normally be used automatically since I depend on the JPA starter:

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Is there a way to check this?

The /beans endpoint returns a bean like this:

{
"bean": "dataSource",
"scope": "singleton",
"type": "org.apache.tomcat.jdbc.pool.DataSource",
"resource": "class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceAutoConfiguration$NonEmbeddedConfiguration.class]",
"dependencies": []
}

So I think it is using the tomcat pool.

This question talks about spring.datasource.test-while-idle and spring.datasource.validation-interval. Do you need to set them to avoid the problem?

I am not sure I get the relationship between the title of your issue and the actual problem. So, yes it's using the Tomcat connection pool as you found out. — Stephane Nicoll, Jan 05 '16 at 18:00
Ok, sorry about the bad title. But why do I get the exception? Do I need to use those last 2 properties to avoid it? Any link to docs that explain those? — Wim Deblauwe, Jan 05 '16 at 18:37
It's written there, in the stack trace you posted: `at org.apache.tomcat.jdbc.pool`. — JB Nizet, Jan 05 '16 at 18:56

score 0 · Accepted Answer · answered Jan 05 '16 at 19:08

0

The spring-boot-starter-jdbc (referenced by the jpa-data as well) is depending on Tomcat pooling, so by default you are using Tomcat pooling. You can easily verify that by checking your maven dependency list. (the stacktrace you posted also confirms that).

Which means that you should be using the tomcat-pooling parameters:

testWhileIdle
validationInterval
validationQuery

answered Jan 05 '16 at 19:08

Gergely Bacso

14,243
2
44
64

Thanks for this. Just now found out this nice answer that explains the properties in more detail: http://stackoverflow.com/questions/30451470/connection-to-db-dies-after-424-in-spring-boot-jpa-hibernate – Wim Deblauwe Jan 05 '16 at 19:27

How to check what database pool is used with Spring Boot?

1 Answers1