アクセス数が少ないサイトで翌朝にアクセスすると java.net.SocketException: Broken pipe が発生する
事象
昨日は使えたのに、次の日になると調子java.net.SocketException: Broken pipeが発生していました。
スタックトレースは下の通りで、 MySQLサーバとの通信でエラーが起きています。
10:10:21.580 [http-nio-8080-exec-3] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin transaction failed: ] with root cause
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method) ~[na:1.8.0_65]
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109) ~[na:1.8.0_65]
at java.net.SocketOutputStream.write(SocketOutputStream.java:153) ~[na:1.8.0_65]
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.8.0_65]
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[na:1.8.0_65]
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3626) ~[mysql-connector-java-5.1.37.jar!/:5.1.37]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2452) ~[mysql-connector-java-5.1.37.jar!/:5.1.37]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617) ~[mysql-connector-java-5.1.37.jar!/:5.1.37]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546) ~[mysql-connector-java-5.1.37.jar!/:5.1.37]
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4873) ~[mysql-connector-java-5.1.37.jar!/:5.1.37]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_65]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_65]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_65]
at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_65]
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.0.28.jar!/:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.0.28.jar!/:na]
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.0.28.jar!/:na]
at com.sun.proxy.$Proxy68.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.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:380) ~[spring-orm-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) ~[spring-tx-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427) ~[spring-tx-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276) ~[spring-tx-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:654) ~[spring-aop-4.2.3.RELEASE.jar!/:4.2.3.RELEASE]
原因
色々と調べるとMySQLがアイドルコネクションと判断すると接続を切断し、Spring Bootから検知できてないのが問題。
対応
コネクションプールのデフォルトは Tomcat JDBC Connection Poolということなので、 コネクションの活性確認を定期的に行う設定を追加します。
spring:
datasource:
url: jdbc:mysql://localhost/db_test?autoReconnect=true
username: db_user
password: xxxxxx
driverClassName: com.mysql.jdbc.Driver
validationQuery: SELECT 1
testOnBorrow: true
testWhileIdle: true
timeBetweenEvictionRunsMillis: 1800000
