JDBC MySQL Connection Issue - Attempted reconnect 3 times. Giving up

Question

I have a rest service application running the Java Spring framework. The application depends on a connection to an external MySQL DB, which is connected via JDBC.

My issue is maintaining a solid connection between the rest service and the MySQL db. I have what I consider a rudimentary connection failsafe in place that looks something like:

public Connection getConnection() throws SQLException {
    if(connection == null){
         this.buildConnection();
    }
    else if(!connection.isValid(10)){ //Rebuild connection if it is no longer valid
        connection.close();
        this.buildConnection();
    }
    return connection;
}

Using this method should ensure that the connection is valid before any query is executed. My problem is that I periodically get an exception thrown when calling this method:

Could not create connection to database server. Attempted reconnect 3 times. Giving up. SQLState: 08001. ErrorCode: 0.

The things that have me super perplexed about this are:

1. This error only happens periodically. Many times the connection works just find.
2. I test this same application on my developer machine and this error never occurs.

I custom configured the MySQL DB on my own server, so I control all its config options. From this I know that this issue isn't related to the maximum number of connections allowed, or a connection timeout.

Edit - Update 1:

• This service is hosted as Cloud Service on Microsoft Azure platform.
• I accidentally set it up as an instance in Northern Europe, while the DB is located in North America - probably not related, but trying to paint the whole picture.
• Tried the advice at this link with no success. Not using thread pools, and all ResultSets and Statements/PreparedStatements are closed after.

Edit - Update 2

After some refactoring, I was able to successfully implement a HikariCP Connection Pool as outlined by @M.Deinum below. Unfortunately, the same problem persists. Everything works great on my local machine, and all Unit Tests pass, but as soon as I push it to Azure and wait more than a few minutes between requests, I get this error, when trying to grab a connection from the pool:

springHikariCP - Connection is not available, request timed out after 38268ms. SQLState: 08S01. ErrorCode: 0.

My HikariCP configuration is as follows:

//Set up connection pool
HikariConfig config = new HikariConfig();
config.setDriverClassName("com.mysql.jdbc.Driver"); 
config.setJdbcUrl("jdbc:mysql://dblocation");

//Connection pool properties
Properties prop = new Properties();
prop.setProperty("user", "Username");
prop.setProperty("password", "Password");
prop.setProperty("verifyServerCertificate", "false");
prop.setProperty("useSSL","true");
prop.setProperty("requireSSL","true");
config.setDataSourceProperties(properties);

config.setMaximumPoolSize(20);
config.setConnectionTestQuery("SELECT 1");
config.setPoolName("springHikariCP");
config.setLeakDetectionThreshold(5000); 

config.addDataSourceProperty("dataSource.cachePrepStmts", "true");
config.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
config.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

dataSource = new HikariDataSource(config);

Any help would be greatly appreciated.

Answer 1

I suggest using a proper JDBC Connection Pool like HikariCP that together with a validation query which will execute on correct intervals should give you fresh and proper connections each time.

Assuming you are using Spring and xml to configure the datasource.

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
    <property name="poolName" value="springHikariCP" />
    <property name="dataSourceClassName"       value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />
    <property name="dataSourceProperties">
        <props>
            <prop key="url">${jdbc.url}</prop>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
        </props>
    </property>
</bean>

It by default validates connections on checkout. I suggest a try out.

As you are using java bases config I suggest the following

@Bean
public DataSource dataSource() {
    HikariDataSource ds = new HikariDataSource();
    ds.setPoolName("springHikariCP");
    ds.setMaxPoolSize(20);
    ds.setLeakDetectionThreshold(5000);
    ds.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
    ds.addDataSourceProperty("url", url);
    ds.addDataSourceProperty("user", username);
    ds.addDataSourceProperty("password", password);
    ds.addDataSourceProperty("cachePrepStmts", true);
    ds.addDataSourceProperty("prepStmtCacheSize", 250);
    ds.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
    ds.addDataSourceProperty("useServerPrepStmts", true);
    ds.addDataSourceProperty("verifyServerCertificate", false);
    ds.addDataSourceProperty("useSSL", true);
    ds.addDataSourceProperty("requireSSL", true);

    return ds;
}

Answer 2

It seems to be caused by the system variable wait_timeout of MySQL.

For MySQL 5.0, 5.1, 5.5, 5.6, the default value for wait_timeout is 28800 seconds (8 hours), and the maximum value for wait_timeout:

• Linux : 31536000 seconds (365 days, one year)
• Windows : 2147483 seconds (2^31 milliseconds, 24 days 20 hours 31 min 23 seconds)

The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.

So I think you can try to use a jdbc connection to keep pinging interval some seconds, or directly using a kind of JDBC Connection Pool framework to manage jdbc connections automatically.

Hope it help. Best Regards.

Answer 3

assuming code you have now for '//Set up connection pool' is called only once, like bean creation, to initialize dataSource.

with that, your getConnection() would be just:

public Connection getConnection() throws SQLException {
   return dataSource.getConnection();
}

and make sure wait_timeout in mysql is set to minute more than maxLifetime in hikaricp which is default 30 minutes

Hope this helps.