Thanks Tom. I did a little more testing. I tested with Microsoft SQL Server
2005 where I ran my unit tests against it in a web service with the database
up, then while it was down, and then again after starting it up again. This
worked fine. I then tried it with MySQL. Oddly, some of the tests succeeded
and others failed. I'm not sure if the problem is specific to MySQL or not.
I'm going to try it with JavaDB in a few minutes. Thanks for the info on the
exception handling. It would be nice if TopLink handled it internally though
(for MySQL).
On an unrelated note, I was wondering why there are different connections
for reading and writing? With the connection pools that I've used in the
past, there wasn't a distinction.
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)
>>>>>...
>>>>>
>