MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the "autoReconnect" parameter.
Also, you should be catching SQLExceptions in your application and dealing with them, rather than propagating them all the way until your application exits, this is just good programming practice. MySQL Connector/J will set the SQLState (see java.sql.SQLException.getSQLState() in your APIDOCS) to "08S01" when it encounters network-connectivity issues during the processing of a query. Your application code should then attempt to re-connect to MySQL at this point.
The following (simplistic) example shows what code that can handle these exceptions might look like:
public void doBusinessOp() throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// How many times do you want to retry the transaction
// (or at least _getting_ a connection)?
int retryCount = 5;
boolean transactionCompleted = false;
do {
try {
conn = getConnection(); // assume getting this from a
// javax.sql.DataSource, or the
// java.sql.DriverManager
conn.setAutoCommit(false);
//
// Okay, at this point, the 'retry-ability' of the
// transaction really depends on your application logic,
// whether or not you're using autocommit (in this case
// not), and whether you're using transacational storage
// engines
//
// For this example, we'll assume that it's _not_ safe
// to retry the entire transaction, so we set retry
// count to 0 at this point
//
// If you were using exclusively transaction-safe tables,
// or your application could recover from a connection going
// bad in the middle of an operation, then you would not
// touch 'retryCount' here, and just let the loop repeat
// until retryCount == 0.
//
retryCount = 0;
stmt = conn.createStatement();
String query = "SELECT foo FROM bar ORDER BY baz";
rs = stmt.executeQuery(query);
while (rs.next()) {
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.commit();
conn.close();
conn = null;
transactionCompleted = true;
} catch (SQLException sqlEx) {
//
// The two SQL states that are 'retry-able' are 08S01
// for a communications error, and 40001 for deadlock.
//
// Only retry if the error was due to a stale connection,
// communications problem or deadlock
//
String sqlState = sqlEx.getSQLState();
if ("08S01".equals(sqlState) || "40001".equals(sqlState)) {
retryCount--;
} else {
retryCount = 0;
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
// You'd probably want to log this . . .
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
// You'd probably want to log this as well . . .
}
}
if (conn != null) {
try {
//
// If we got here, and conn is not null, the
// transaction should be rolled back, as not
// all work has been done
try {
conn.rollback();
} finally {
conn.close();
}
} catch (SQLException sqlEx) {
//
// If we got an exception here, something
// pretty serious is going on, so we better
// pass it up the stack, rather than just
// logging it. . .
throw sqlEx;
}
}
}
} while (!transactionCompleted && (retryCount > 0));
}There really is no way to keep a connection alive indefitely, and I'm
not aware of compelling reasons to do so for many reasons. First, it
consumes resources that aren't needed if the connection is sitting idle,
and second, it increases the risk that network issues, or server
restarts will cause your application to crash.
It takes very little time (on the order of a few milliseconds) to create
a JDBC connection to MySQL, so you should set your connection pool to
only let connections stay idle for a few minutes.
Most of the high-volume applications I've seen that use Java with MySQL
don't let connections stay idle more than 10 minutes or so.
------------------------------------------------------------------------------
Mark,
Since years you recommend not to just use "autoReconnect=true" but to be
smarter. First hints are in
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-p
roperties.html and
http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html
A few questions:
1) you recommend to "deal with SQLExceptions", do you have any code-examples
how you recommend to do this - a while loop until I finally get a working
connection or a different exception?
In my setup, I use both ssl protected remote jdbc's and regular local jdbc's
and AFAICR, the exceptions I get are not necessarily identical?
You suggest this also http://lists.mysql.com/java/8119 - what would be a
generic approach to do this? I guess if I deal with the exceptions,
testOnBorrow=true no longer makes sense?
2) How do you rate the "testWhileIdle" strategy? If I do that, shouldn't I
add also "minIdle" to ensure that if an idle test fails, a new connection is
added to the pool? If this is configured nicely, do you still think it is
worthwhile to set testOnBorrow=true as long as my application doesn't have a
5*9 SLA?
Also, I use the Jakarta commons-dbcp with BasicDataSource and see that in
org.apache.commons.dbcp.datasources.InstanceKeyDataSource the
"testWhileIdle" keyword is dealt with. But who really does the testing? Is
that connector-j or dbcp?
3) The documentation also lists autoReconnectForPools. How does this fit
into the picture if I have tomcat with dbcp and JNDI resources?
http://lists.mysql.com/java/7774
Many thanks for any hints in advance!
Ralf
No comments:
Post a Comment