I think I have it working using a customizer. Would it be possible to make
this the default behavior though? IMHO the connection pool should handle
this transparently. I'm sure I'm not the only one that will run into this
issue. While implementing a Customizer seems simple enough it isn't as
simple as having it work out of the box and while I like TopLink a lot, I
don't want to use TopLink proprietary classes.
package edu.uchicago.at.reservations;
import java.net.SocketException;
import java.util.logging.Logger;
import oracle.toplink.essentials.exceptions.DatabaseException;
import oracle.toplink.essentials.exceptions.ExceptionHandler;
import oracle.toplink.essentials.sessions.Session;
public class SessionCustomizer implements
oracle.toplink.essentials.tools.sessionconfiguration.SessionCustomizer
{
private static Logger logger =
Logger.getLogger(SessionCustomizer.class.getName());
public SessionCustomizer() {
}
public void customize(Session session) throws Exception {
session.setExceptionHandler(new ExceptionHandler() {
public Object handleException(RuntimeException exception) {
logger.info("containsSocketException = "
+ containsSocketException(exception));
if (exception instanceof DatabaseException) {
logger.info("DatabaseException = true");
DatabaseException de = (DatabaseException)exception;
if(containsSocketException(de)) {
de.getAccessor().reestablishConnection(de.getSession());
return de.getSession().executeQuery(de.getQuery());
}
}
else {
throw exception;
}
return null;
}
});
}
private boolean containsSocketException(Throwable throwable) {
if(throwable instanceof SocketException) {
return true;
}
if(throwable.getCause() != null) {
return containsSocketException(throwable.getCause());
}
return false;
}
}
Jon
----- Original Message -----
From: "Jon Miller" <jemiller_at_uchicago.edu>
To: <persistence_at_glassfish.dev.java.net>
Sent: Friday, March 09, 2007 11:56 AM
Subject: Re: Broken connection handling
> Tom,
>
> I'm wondering if it would be possible to change the default connection
> pool behavior so that if a SocketException occurs it gets a new connection
> and tries that before failing? It would try once to recover and if that
> fails, throw an exception. I'm wondering if this seems feasible or seem
> like a good idea? Could someone do this easily? I might be able to come up
> with a patch myself if it's not that hard and if people think it's a good
> idea. I don't really know any of the internals of TopLink at present
> though.
>
> Jon
>
> ----- Original Message -----
> From: "Tom Ware" <tom.ware_at_oracle.com>
> To: <persistence_at_glassfish.dev.java.net>
> Sent: Thursday, February 15, 2007 12:57 PM
> Subject: Re: Broken connection handling
>
>
>> Hi Jon,
>>
>> There are a couple of things we recommend to TopLink Essentials users
>> that are seeing issues like this with our connection pool.
>>
>> 1. Set your minimum and maximum connections to be the same. Sometimes
>> that makes it so the connections are better shared and connections don't
>> time out as often.
>> 2. Use a TopLink Exception Handler to deal reconnect when you see issues.
>> If you go this route, you can start by looking at some documentation and
>> then let me know if you have any questions.
>> - The section entitled "Using Java" in this link in the TopLink Product
>> Documentation:
>> http://www.oracle.com/technology/products/ias/toplink/doc/10131/main/_html/sescfg007.htm#BCGGFGHD
>> - Putting the ExceptionHandler on the session can be done using a Session
>> Customizer described in the following link:
>> http://www.oracle.com/technology/products/ias/toplink/jpa/resources/toplink-jpa-extensions.html#TopLinkCustomizationValidation
>> 3. Use TopLink Essentials' capability of connecting to an External
>> Connection Pool and make use of the connection pool provided by your
>> Application Server
>>
>> -Tom
>>
>> Jon Miller wrote:
>>
>>>Can anyone confirm whether or not handling of broken connections is built
>>>into the built-in connection pool in Glassfish Persistence? I tried
>>>setting the MySQL autoReconnectForPools property to true, but, that
>>>didn't seem to help. Also, I need it to handle if the network goes down
>>>or the database server goes down and comes back up. I don't want to have
>>>to restart the application everytime a database connection gets broken.
>>>Has anyone else ran into this problem?
>>>
>>>Jon
>>>
>>>----- Original Message -----
>>>From: "Jon Miller" <jemiller_at_uchicago.edu>
>>>To: <persistence_at_glassfish.dev.java.net>
>>>Sent: Tuesday, February 13, 2007 9:47 AM
>>>Subject: Re: Broken connection handling
>>>
>>>
>>>
>>>>Thanks. I'm using JPA in SE mode with standalone Tomcat. So, I don't
>>>>think that will help me at the moment. It's good to know about though.
>>>>What I'm wondering is if Glassfish Persistence has this functionality
>>>>built into it? My guess at this point is no.
>>>>
>>>>Jon
>>>>
>>>>----- Original Message -----
>>>>From: "Jagadish Prasath Ramu" <Jagadish.Ramu_at_Sun.COM>
>>>>To: <persistence_at_glassfish.dev.java.net>
>>>>Sent: Monday, February 12, 2007 8:58 PM
>>>>Subject: Re: Broken connection handling
>>>>
>>>>
>>>>
>>>>>Hi,
>>>>>MySQL will timeout the connections that are idle for 8 hours. You can
>>>>>change your server settings according to your requirements.
>>>>>
>>>>>You can enable "table based connection-validation" for GlassFish
>>>>>Connection Pools. This will help to remove the timed-out/invalid
>>>>>connections from the pool
>>>>>
>>>>>http://docs.sun.com/app/docs/doc/819-3662/6n5s9hmts?a=view
>>>>>
>>>>>Thanks,
>>>>>-Jagadish
>>>>>
>>>>>
>>>>>On Mon, 2007-02-12 at 14:00 -0600, Jon Miller wrote:
>>>>>
>>>>>>Hi all,
>>>>>>
>>>>>>I'm just beginning to start using Glassfish Persistence (V2 B33) with
>>>>>>MySQL
>>>>>>5.0. I've noticed the following exception a few times now and my best
>>>>>>guess
>>>>>>at this point as to what it is is that MySQL is timing the connections
>>>>>>out.
>>>>>>I'm wondering if GP removes broken connections from the connection
>>>>>>pool?
>>>>>>MySQL has a "autoReconnectForPools" property that I've used in the
>>>>>>past,
>>>>>>but, I noticed in the documentation that it looks like it may be
>>>>>>deprecated
>>>>>>and removed.
>>>>>>
>>>>>>Jon
>>>>>>
>>>>>>
>>>>>>Last packet sent to the server was 5 ms ago.Error Code: 0
>>>>>>Call:SELECT Id, Name, CreationUserName, Description,
>>>>>>LastWriteUserName,
>>>>>>LastWriteTime, CreationTime FROM Item ORDER BY Name ASC
>>>>>>Query:ReadAllQuery(edu.uchicago.at.reservations.persistence.entity.Item)
>>>>>> at
>>>>>>oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
>>>>>> at
>>>>>>oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:600)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2207)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2185)
>>>>>> at
>>>>>>oracle.toplink.essentials.queryframework.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:302)
>>>>>> at
>>>>>>oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:709)
>>>>>> at
>>>>>>oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
>>>>>> at
>>>>>>oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:677)
>>>>>> at
>>>>>>oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:731)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2219)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:909)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:335)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:442)
>>>>>> at
>>>>>>edu.uchicago.at.reservations.ReservationsService.findItems(ReservationsService.java:48)
>>>>>> at
>>>>>>edu.uchicago.at.reservations.ReservationsWebService.findItems(ReservationsWebService.java:37)
>>>>>> ... 35 more
>>>>>>Caused by: com.mysql.jdbc.CommunicationsException: Communications link
>>>>>>failure due to underlying exception:
>>>>>>
>>>>>>** BEGIN NESTED EXCEPTION **
>>>>>>
>>>>>>java.net.SocketException
>>>>>>MESSAGE: Broken pipe
>>>>>>
>>>>>>STACKTRACE:
>>>>>>
>>>>>>java.net.SocketException: Broken pipe
>>>>>> at java.net.SocketOutputStream.socketWrite0(Native Method)
>>>>>> at java.net.SocketOutputStream.socketWrite(Unknown Source)
>>>>>> at java.net.SocketOutputStream.write(Unknown Source)
>>>>>> at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
>>>>>> at java.io.BufferedOutputStream.flush(Unknown Source)
>>>>>> at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)
>>>>>> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1554)
>>>>>> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
>>>>>> at com.mysql.jdbc.Connection.execSQL(Connection.java:3124)
>>>>>> at
>>>>>>com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1149)
>>>>>> at
>>>>>>com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1262)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:711)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:486)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
>>>>>> at
>>>>>>oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:600)
>>>>>> at
>>>>>>oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2207)
>>>>>>...
>>>>>>
>>
>