atom feed16 messages in net.java.dev.glassfish.persistenceRe: Broken connection handling
FromSent OnAttachments
Jon MillerFeb 12, 2007 12:00 pm 
Marina VatkinaFeb 12, 2007 3:28 pm 
Jon MillerFeb 12, 2007 4:24 pm 
Markus FuchsFeb 12, 2007 4:48 pm 
Jagadish Prasath RamuFeb 12, 2007 6:58 pm 
Jon MillerFeb 13, 2007 7:47 am 
Jon MillerFeb 14, 2007 10:57 am 
Tom WareFeb 15, 2007 10:56 am 
Jon MillerFeb 15, 2007 12:13 pm 
Tom WareFeb 15, 2007 1:15 pm 
Jon MillerFeb 15, 2007 1:18 pm 
Jon MillerMar 9, 2007 9:55 am 
Jon MillerMar 9, 2007 2:31 pm 
Jon MillerMar 12, 2007 9:54 am 
Tom WareMar 13, 2007 6:49 am 
Jon MillerMar 13, 2007 12:22 pm 
Subject:Re: Broken connection handling
From:Tom Ware (tom.@oracle.com)
Date:Mar 13, 2007 6:49:01 am
List:net.java.dev.glassfish.persistence

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; } }

----- Original Message ----- From: "Jon Miller" <jemi@uchicago.edu> To: <pers@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.

----- Original Message ----- From: "Tom Ware" <tom.@oracle.com> To: <pers@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

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?

----- Original Message ----- From: "Jon Miller" <jemi@uchicago.edu> To: <pers@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.

----- Original Message ----- From: "Jagadish Prasath Ramu" <Jaga@Sun.COM> To: <pers@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) ...