Hi Jon,
I'm glad you have got it working with an ExceptionHandler.
I suggest entering an Enhancement for this change. The key to making
this change is to deal with transactions properly since we cannot just
do a simple reconnect when we are in a transaction.
-Tom
Jon Miller wrote:
>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)
>>>>>>>...
>>>>>>>
>>>>>>>
>>>>>>>