On Sun, 2009-07-26 at 12:54 -0400, Drew Jensen wrote:
> Drew Jensen wrote:
> > Keith Clark wrote:
> >> The server I'm connected to is set for 10 minute time out/disconnect.
> >> MySQL Query Browser must be reconnecting and just not telling the user.
> >>
> > I like that handling even more then a dialog with an OK button.
> >
> > Alright well I've been 'reading' for a bit here - in the Developers
> > Guide - and running into an interesting situation. Where I thought I
> > would be going takes me to interfaces (services, etc) marked as
> > deprecated and finding other items that look promising but they all
> > seem to be marked as UnPublished. ( Now that only means that the
> > interface is subject to change w/out notice...not that they can't be
> > used)
> >
> > So far the only thing that is becoming clear is that we are not gong
> > to be doing much of this in OO.oBasic...
> >
>
> Well - maybe this is a place to start:
> http://api.openoffice.org/docs/common/ref/com/sun/star/sdb/XDatabaseAccessListener.html
>
> Maybe not...but gotta start somewhere.
Or this from the connector manual:
1.5.3.4: I have a servlet/application that works fine for a day, and
then stops working overnight
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 (see Section 1.4.1, “Driver/Datasource Class
Names, URL Syntax and Configuration Properties for Con-
nector/J”).
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:
Example 12. Example of transaction with retry logic
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 {
53
MySQL Connector/J
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 {
54
MySQL Connector/J
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));
}
Note
Use of the autoReconnect option is not recommended because there is
no safe method of reconnecting to the MySQL
server without risking some corruption of the connection state or
database state information. Instead, you should use a con-
nection pool which will enable your application to connect to the
MySQL server using an available connection from the
pool. The autoReconnect facility is deprecated, and may be removed
in a future release.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
