Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » jdbc connection validation query
jdbc connection validation query [message #523390] Fri, 26 March 2010 00:56 Go to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
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 #523777 is a reply to message #523390] Mon, 29 March 2010 05:47 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
Anybody any idea how to configure the validation query in the connection pool?

Tom
Re: jdbc connection validation query [message #523836 is a reply to message #523777] Mon, 29 March 2010 10:55 Go to previous messageGo to next message
Gordon Yorke is currently offline Gordon Yorke
Messages: 77
Registered: July 2009
Member
EclipseLink has the functionality to validate the connections and the validation is active by default so I am surprised you are having an issue. If you need platform specific configuration these settings are configurable through the EclipseLink extension to the orm.xml file but there are no Persistence Unit Properties available to set these settings.

Without an eclipselink-orm.xml file will need to use a SessionCustomizer and apply the configuration directly to the DatabaseLogin. This can be obtained by calling session.getLogin().

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.
--Gordon
Re: jdbc connection validation query [message #526209 is a reply to message #523836] Fri, 09 April 2010 04:33 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
> 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.
Re: jdbc connection validation query [message #526249 is a reply to message #526209] Fri, 09 April 2010 07:09 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
We also found this for getQueryRetryAttemptCount: "EclipseLink will only attempt to reconnect when EclipseLink can determine that a communication failure occurred on a read query executed outside of a transaction."

Only outside of a transaction? Naturally we start a transaction and start writing updates.

Tom
Re: jdbc connection validation query [message #526387 is a reply to message #526249] Sat, 10 April 2010 01:38 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
> Only outside of a transaction? Naturally we start a transaction and
> start writing updates.

This really seems to be the problem: "EclipseLink will retry a read query outside of a transaction if EclipseLink can determine that a communication error occurred with the database."
So if a new transaction is acquired from the pool for writing, that transaction is not validated, fails, and is never discarded. Seems like a hole in the checking to me.

Is there any way that inside a transaction a connection can be discarded, so that we can do a manual validation test?

Tom
Re: jdbc connection validation query [message #527024 is a reply to message #526387] Tue, 13 April 2010 13:53 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
> EclipseLink will retry a read
> query outside of a transaction if EclipseLink can determine that a
> communication error occurred with the database.

Anyone any suggestions on how to validatie a JDBC connection which is use inside a transaction only? (E.g. separate sequence connection)

Tom
Re: jdbc connection validation query [message #531534 is a reply to message #527024] Wed, 05 May 2010 06:58 Go to previous message
Tom Eugelink is currently offline Tom Eugelink
Messages: 807
Registered: July 2009
Senior Member
Anyone any suggestions on how to validate a JDBC connection which is used inside a transaction only? (E.g. separate sequence connection)
Previous Topic:Communication failure
Next Topic:JPA entities returned from WebService
Goto Forum:
  


Current Time: Tue Sep 02 00:29:26 EDT 2014

Powered by FUDForum. Page generated in 0.01869 seconds