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)
>>>>...
>>>>
>>>>