| jdbc connection validation query [message #523390] |
Fri, 26 March 2010 00:56  |
Eclipse User |
|
|
|
We have the problem that MySQL automatically closes connections on the server side after a timeout. This results in an exception. It seems Eclipselink does not do a correct validation of the connection when retrieving an existing one from the pool.
What is the best way to configure a validation query from Java?
I do not find any setting in PersistenceUnitProperties. But there seems to be an option "validationQuery"?
Tom
|
|
|
|
|
| Re: jdbc connection validation query [message #526209 is a reply to message #523836] |
Fri, 09 April 2010 04:33  |
| Eclipse User |
|
|
|
> The pertinent API :
> public void setPingSQL(String pingSQL)
> public void setQueryRetryAttemptCount(int queryRetryAttemptCount)
> public void setDelayBetweenConnectionAttempts(int
> delayBetweenConnectionAttempts)
>
> If you continue to have difficulties then please provide some details on
> the error and data source configuration.
Ok. I'm relaying this information for another project.
First the situation again:
- MySql times out connections ON THE DATABASE SERVER SIDE (duration can be configured).
- A client that uses such a connection gets a "timed-out" SQLException.
This is the data source configuration:
Map<String, Object> lOptions = new HashMap<String, Object>();
// always use KPUTIL JDBC Driver so it will automatically log SQL to log4j
lOptions.put(PersistenceUnitProperties.JDBC_DRIVER, nl.knowledgeplaza.util.jdbc.Driver.class.getName());
lOptions.put(PersistenceUnitProperties.JDBC_URL, nl.knowledgeplaza.util.jdbc.Driver.PREFIX + jdbcDriver.getName() + "#" + jdbcUrl);
lOptions.put(PersistenceUnitProperties.JDBC_USER, jdbcUsr);
lOptions.put(PersistenceUnitProperties.JDBC_PASSWORD, jdbcPwd);
lOptions.put(PersistenceUnitProperties.TARGET_DATABASE, (databasePlatform == null ? null : databasePlatform.getName()));
lOptions.put(PersistenceUnitProperties.TARGET_SERVER, TargetServer.None);
lOptions.put(PersistenceUnitProperties.CACHE_SHARED_DEFAULT, "false"); // do not use the shared cache (otherwise refresh will not update from db)
if (log4j.isDebugEnabled()) lOptions.put(PersistenceUnitProperties.LOGGING_LEVEL, "fine");
lOptions.put(PersistenceUnitProperties.LOGGING_EXCEPTIONS, "true");
lOptions.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, " nl.knowledgeplaza.profiler.engine.dao.jpa.KPSessionCustomize r ");
EntityManagerFactory lEntityManagerFactory = Persistence.createEntityManagerFactory("kpprofiler", lOptions);
And this is the session customizer:
public class KPSessionCustomizer implements org.eclipse.persistence.config.SessionCustomizer
{
@Override
public void customize(Session session) throws Exception
{
DatabaseLogin lDBLogin = session.getLogin();
if (slf4j.isDebugEnabled()) slf4j.debug("Check is: " + lDBLogin.isConnectionHealthValidatedOnError() + "; Ping SQL: " + lDBLogin.getPingSQL() + " attempting " + lDBLogin.getQueryRetryAttemptCount() + " times, every " + lDBLogin.getDelayBetweenConnectionAttempts() + " ms");
lDBLogin.setPingSQL("SELECT 1;");
lDBLogin.setConnectionHealthValidatedOnError(true);
}
}
It is confirmed that this code is executed (the log line is visible), but it does not make a difference. Actually the defaults are almost identical to these settings.
What is interesting is that the exact behavior of ConnectionHealthValidatedOnError deviates from what is common in connection validation; it does a "validate on error", not when obtaining a connection from the pool, but after the connection throws an error.
I'm not sure if that is relevant in this context, but it is noteworthy, because normally a pool would validate a connection and demote any occuring exceptions, because they are not very relevant to the user. But now you get to see the exception in full.
Anyhow, based on what we see, we suspect two issues / bugs:
1. We have JDBC logging active and when the timed-out exception is thrown, we do NOT see the ping-sql being tried. Eclipselink also keeps reusing these timed-out exceptions and Tomcat needs to be restarted to clear them.
2. Assuming that 1. would work (the ping-sql is tried and the connection is declared unusable), then we would expect a reexecution of the failed SQL statement over a new connection. But we do not see that.
So everything seems to indicate that the validation is not working.
Tom
N.B.: An unexpected database shutdown is handled correctly.
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.08352 seconds
Home