What is difference between autoReconnect & autoReconnectForPools in MySql connector/J?

Question

In the configuration reference for MySql's connector J driver, a caveat emptor is issued on the use of the autoReconnect property. I followed the instructions and increased my server's wait_timeout. Since I am using DBCP (I am considering moving to c3po after reading several posts on Stackoverflow shooting down DBCP ), is it ok to use the autoReconnectForPools property ? What does it actually do when enabled under DBCP or any connection pool for that matter ?

Answer 1

autoReconnect will throw an SQLException to the client, but will try to re-establish the connection.

autoReconnectForPools will try to ping the server before each SQL execution.

I had a lot of issues with dbcp in the past, especially disconnections. Most were solved by moving to c3p0. Notice that the mysql driver has connection tester for c3p0 (com.mysql.jdbc.integration.c3p0.MysqlConnectionTester).

Also, you may want to check this out: Connection pooling options with JDBC: DBCP vs C3P0

Answer 2

MySQL's autoReconnect feature is deprecated, as it has many issues (ref: official documentation).

autoReconnectForPools has little to do with autoReconnect, it has more to do with autoCommit and reconnectAtTxEnd - when all 3 are true, it will ping the server at the end of each transaction and automatically reconnect if needed.

DBCP's connection validation is imperfect - even when testOnBorrow is set, it sometimes returns broken connections from the pool (not to mention testing a connection before every borrow is horribly inefficient).

According to this article, HikariCP seems to be a better pool implementation, as it is able to use JDBC4 isValid() API which is much faster than running a test query, and is specially designed to never return broken connections to the client application.

Answer 3

Are you sure you're using DBCP properly?

According to the short configuration notes, it's supposed to handle timeouts pretty well thanks to the default value of testOnBorrow=true (tests the connection before used, and if it fails it is dropped from the pool and we try to get a new one instead).

The only thing you need to do is to make sure you configure the validationQuery property to a non-null String, e.g. "SELECT 0" for MySQL database (here is a post about different validationQuery values per DB used).