Why Does Autoreconnect=True Not Seem to Work

Why does autoReconnect=true not seem to work?

I had the same issue and it was absolutely maddening. Here's what the docs say on the MySQL website (emphasis mine)

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications do not handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, investigate setting the MySQL server variable "wait_timeout" to some high value rather than the default of 8 hours.

In my experience, it doesn't seem like the "reconnect on the next query" functionality worked either, but I was using MySQL 4.0, which may have been the reason for that.

I ended up writing a mini-framework that catches the exceptions, checks for that specific error, and attempts to reconnect and retry the query if possible.

ETA: This link provides a bit more information, and indicates that autoReconnect will probably be removed in the future anyways.

Connection with MySql is being aborted automatically. How to configure Connector/J properly?

The text describes three solutions to prevent connection aborts:

1. Configure the connection string with autoReconnect=true. This is a property of the URL connection string, which works at the driver level. You need to change the connection string in the data source configuration.

   url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
   

2. Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.

3. Configure the connection pool to test the connection validatiy. This is done at the pool, not a the driver level. This will depend on the data source implementation that you use. But it should be configurable in the property of the data source, if you use a pooled one, e.g. c3p0.

   • Connection pooling options with JDBC: DBCP vs C3P0
   • http://snipplr.com/view/14725/c3p0-datasource-config-connectionpool/
   • http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

Additionnal comments:

• The datasource/pool can also have a timeout, which corresponds to the time an idle connection remains in the pool. To not confused with the db timeout.
• There are several way to test the validity of a connection. One common way is to have dummy test table. The pool will issue a select on the dummy test table to see if the connection is still OK.

Connection pool expires silently in Tomcat 7 but autoReconnect=true doesn't fix it

The MySQL Connector/J documentation says about autoReconnect:

If enabled the driver will throw an exception for a queries issued on
a stale or dead connection, which belong to the current transaction,
but will attempt reconnect before the next query issued on the
connection in a new transaction.

Meaning you will still get exceptions.

Connectors such as JDBI get around this by adding optional test queries on idle, borrow from pool and return to pool. Perhaps you could add something to your own JDBC connection wrapper to do the same. Alternatively, follow the documentation for autoReconnect and properly catch SQLExceptions arising from dead/stale connections.

There are further useful references on this answer using DBCP and c3p0

java c3p0: how can i configure autoreconnect=true?

Create a file c3p0.properties which must be in the root of the classpath:

# c3p0.properties
c3p0.testConnectionOnCheckout=true

For further documentation refer to this.

This post might be helpful also.

Spring Boot JPA - configuring auto reconnect

I assume that boot is configuring the DataSource for you. In this case, and since you are using MySQL, you can add the following to your application.properties up to 1.3

spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

As djxak noted in the comment, 1.4+ defines specific namespaces for the four connections pools Spring Boot supports: tomcat, hikari, dbcp, dbcp2 (dbcp is deprecated as of 1.5). You need to check which connection pool you are using and check if that feature is supported. The example above was for tomcat so you'd have to write it as follows in 1.4+:

spring.datasource.tomcat.testOnBorrow=true 
spring.datasource.tomcat.validationQuery=SELECT 1

Note that the use of autoReconnect is not recommended:

The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly.

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

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;
}

Why does Hibernate/JDBC/MySQL drop connections after a day or so?

The MySQL JDBC driver times out after 8 hours of inactivity and drops the connection.

You can set autoReconnect=true in your JDBC URL, and this causes the driver to reconnect if you try to query after it has disconnected. But this has side effects; for instance session state and transactions cannot be maintained over a new connection.

If you use autoReconnect, the JDBC connection is reestablished, but it doesn't automatically re-execute your query that got the exception. So you do need to catch SQLException in your application and retry queries.

Read http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html for more details.

---

Related Topics