I tested with JavaDB and found that the results were the same as for MySQL.
i.e. after restarting the database server, it throws a few exceptions
(presumably one for each connection that was established before the restart)
and then it's OK. The reason I'm not using an external connection pool is
that I want my code to work from within unit tests that run outside the web
server.
Jon
----- Original Message -----
From: "Jon Miller" <jemiller_at_uchicago.edu>
To: <persistence_at_glassfish.dev.java.net>
Sent: Thursday, February 15, 2007 2:13 PM
Subject: Re: Broken connection handling
> 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)
>>>>>>...
>>>>>>
>>
>