Hi Tom,
Yeah, I was wondering about that. i.e. what if the SocketException happens
in the middle of a query or something else rather than before it? Would the
database rollback the transaction if this happened? I guess this is why
other connection pools have a setting where you can configure a test query
to run first? Previously, I've set mine up so that it just does a SELECT '1'
FROM DUAL or something similar. If I remember correctly, from a performance
stand point, this isn't recommended, but, my apps aren't heavily used so
performance hasn't been an issue. I guess maybe my enhancement request would
be to somehow make it so that dead connections are removed from the
connection pool transparently. As far as how this is accomplished I couldn't
really tell you other than like I said I've always used a test query
previously and never had to deal with dead connections causing Exceptions
until now.
Jon
----- Original Message -----
From: "Tom Ware" <tom.ware_at_oracle.com>
To: <persistence_at_glassfish.dev.java.net>
Sent: Tuesday, March 13, 2007 8:49 AM
Subject: Re: Broken connection handling
> 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)
>>>>>>>>...
>>>>>>>>
>>>>>>>>
>