Ok, so this is driving me insane!! Can't figure it out.
I've got a spring boot rest webservice which needs to run 24/7. I connects to mysql using a connection pool(using hikariCP).
Currently in dev env its only running a job once a day. So for 23+ hrs the connections are idle.
When the scheduled job runs, it is running on an idle thread for some reason and throws the below exception, even though before it Hikari clearly shows its created a new connection, within the timeout threshold.
I can't reproduce this myself after a few mins/hour of idling, it only happens after this extended period. I've tried following settings from multiple suggestions on other posts/sites, but I'm getting no where. Hoping this is something simple I have overlooked which someone can help me with.
Thanks
2018-10-05 23:00:04 DEBUG HikariPool:411 - secondary - Before cleanup stats (total=2, active=1, idle=1, waiting=0)
2018-10-05 23:00:04 DEBUG HikariPool:411 - secondary - After cleanup stats (total=2, active=1, idle=1, waiting=0)
2018-10-05 23:00:04 DEBUG HikariPool:411 - primary - Before cleanup stats (total=2, active=1, idle=1, waiting=0)
2018-10-05 23:00:04 DEBUG HikariPool:411 - primary - After cleanup stats (total=2, active=1, idle=1, waiting=0)
2018-10-05 23:00:13 DEBUG PoolBase:129 - primary - Closing connection com.mysql.jdbc.JDBC4Connection@500889cc: (connection has passed maxLifetime)
2018-10-05 23:00:13 DEBUG HikariPool:709 - primary - Added connection com.mysql.jdbc.JDBC4Connection@2afe5087
2018-10-05 23:00:21 DEBUG OrderedRequestContextFilter:114 - Bound request context to thread: org.apache.catalina.connector.RequestFacade@59f98ac5
2018-10-05 23:00:22 DEBUG HikariPool:709 - secondary - Added connection com.mysql.jdbc.JDBC4Connection@7c42a6a6
2018-10-05 23:00:22 DEBUG PoolBase:129 - secondary - Closing connection com.mysql.jdbc.JDBC4Connection@6add6002: (connection has passed maxLifetime)
2018-10-05 23:00:23 DEBUG DefaultListableBeanFactory:254 - Returning cached instance of singleton bean 'myResource'
2018-10-05 23:00:23 DEBUG DefaultListableBeanFactory:254 - Returning cached instance of singleton bean 'postExecutor'
2018-10-05 23:00:23 DEBUG DefaultListableBeanFactory:254 - Returning cached instance of singleton bean 'myMessageImpl'
2018-10-05 23:00:28 WARN ProxyConnection:153 - secondary - Connection com.mysql.jdbc.JDBC4Connection@f8c1ddd marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 44,220,394 milliseconds ago. The last packet sent successfully to the server was 44,220,395 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)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3749)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2512)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at com.jersey.myqueryservice.dao.myMessageImpl.ismyRunning(myMessageImpl.java:61)
at com.jersey.myqueryservice.dao.myMessageImpl$$FastClassBySpringCGLIB$$a8afcfbf.invoke(<generated>)
.....
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3731)
... 80 more
MySQL properties:
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.01 sec)
Properties:
primary.database.validation-query=SELECT 1
primary.database.max-active=2
primary.database.min-idle=1
primary.database.connection-timeout=14400
primary.database.idle-timeout=14400
primary.database.max-life-time=20000
secondary.database.validation-query=SELECT 1
secondary.database.max-active=5
secondary.database.min-idle=1
secondary.database.connection-timeout=14400
secondary.database.idle-timeout=14400
secondary.database.max-life-time=20000
secondary.database.url=jdbc:mysql://localhost:3306/db?autoReconnect=true
Beans:
@Bean(name = "primaryDatabaseConnection")
public Connection primaryDatabaseConnection() throws SQLException
{
return primaryDataSource().getConnection();
}
@Bean(name = "secondaryDatabaseConnection")
public Connection secondaryDatabaseConnection() throws SQLException
{
return secondaryDataSource().getConnection();
}
@Bean
public DataSource primaryDataSource() {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName("com.mysql.jdbc.Driver");
hikariConfig.setJdbcUrl(primaryUrl);
hikariConfig.setUsername(primaryUser);
hikariConfig.setPassword(primaryPassword);
hikariConfig.setMaximumPoolSize(primaryDatabaseMaxActive);
hikariConfig.setMinimumIdle(primaryDatabaseMinIdle);
hikariConfig.setConnectionTimeout(primaryDatabaseConnectionTimeout);
hikariConfig.setMaxLifetime(primaryMaxLifeTime);
hikariConfig.setIdleTimeout(primaryDatabaseIdleTimeout);
hikariConfig.setConnectionTestQuery(primaryDatabaseValidationQuery);
hikariConfig.setPoolName("primary");
HikariDataSource ds = new HikariDataSource(hikariConfig);
return ds;
}
@Bean
public DataSource secondaryDataSource() {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName("com.mysql.jdbc.Driver");
hikariConfig.setJdbcUrl(secondaryUrl);
hikariConfig.setUsername(secondaryUser);
hikariConfig.setPassword(secondaryPassword);
hikariConfig.setMaximumPoolSize(secondaryDatabaseMaxActive);
hikariConfig.setMinimumIdle(secondaryDatabaseMinIdle);
hikariConfig.setConnectionTimeout(secondaryDatabaseConnectionTimeout);
hikariConfig.setMaxLifetime(primaryMaxLifeTime);
hikariConfig.setIdleTimeout(secondaryDatabaseIdleTimeout);
hikariConfig.setConnectionTestQuery(secondaryDatabaseValidationQuery);
hikariConfig.setPoolName("secondary");
HikariDataSource ds = new HikariDataSource(hikariConfig);
return ds;
}
Repository:
@Autowired
@Qualifier("secondaryDatabaseConnection")
private Connection conn;
.
.
.
try (
PreparedStatement preparedStatement = conn.prepareStatement(IS_RUNNING_SQL)) {
.
.
.
.
