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