19

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 ?

skaffman
  • 398,947
  • 96
  • 818
  • 769
ashitaka
  • 3,928
  • 7
  • 38
  • 43

3 Answers3

16

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

Community
  • 1
  • 1
David Rabinowitz
  • 29,904
  • 14
  • 93
  • 125
  • 2
    Generally, more modern pools such as HikariCP or Tomcat DBCP will use the ``Connection.isValid()`` method. There is also a *special* query that only MySQL recognizes, "/* ping \*/" (must be *exactly* that), which will cause MySQL to use a lightweight *ping* to the server. I believe the ``isValid()`` implementation should still be more efficient, as the "/* ping \*/" query must still be processed by the parser. – brettw Apr 07 '15 at 15:39
9

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.

rustyx
  • 80,671
  • 25
  • 200
  • 267
2

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).

Community
  • 1
  • 1
StatsTrade
  • 51
  • 2