CODEWITHSUNDEEP
javapostgresqlglassfishdatabase-connectionconnection-pooling
Matteo
Matteo

Reputation: 14930

Glassfish, PostgreSQL and closed connections

We have an application using MySQL on Glassfish using JPA (Hibernate) which is working fine.

We then switched to PostgreSQL and almost everything is working fine. The only problem that we have, is that after some time the application stops responding and throws an exception because the connection to the DB was closed:

[#|2012-06-19T21:51:22.050+0200|SEVERE|glassfish3.1.2|javax.enterprise.system.std.com.sun.enterprise.server.logging|_ThreadID=38;_ThreadName=Thread-2;|4816089 [p: thread-pool-1; w: 21] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08006
[#|2012-06-19T21:51:22.050+0200|SEVERE|glassfish3.1.2|javax.enterprise.system.std.com.sun.enterprise.server.logging|_ThreadID=38;_ThreadName=Thread-2;|4816089 [p: thread-pool-1; w: 21] ERROR org.hibernate.util.JDBCExceptionReporter - An I/O error occured while sending to the backend.
[#|2012-06-19T21:51:22.052+0200|SEVERE|glassfish3.1.2|javax.enterprise.resource.resourceadapter.com.sun.enterprise.resource|_ThreadID=38;_ThreadName=Thread-2;|RAR5031:System Exception
javax.resource.spi.LocalTransactionException: This connection has been closed.
        at com.sun.gjc.spi.LocalTransactionImpl.rollback(LocalTransactionImpl.java:134)
        at com.sun.enterprise.resource.ConnectorXAResource.rollback(ConnectorXAResource.java:213)
        at com.sun.enterprise.transaction.JavaEETransactionImpl.rollback(JavaEETransactionImpl.java:571)
        at com.sun.enterprise.transaction.JavaEETransactionManagerSimplified.rollback(JavaEETransactionManagerSimplified.java:893)
        at com.sun.enterprise.transaction.UserTransactionImpl.rollback(UserTransactionImpl.java:234)
        at ch.ethz.id.wai.lakshmi.engine.common.TransactionHelper.rollbackTransaction(TransactionHelper.java:43)
        at ch.ethz.id.wai.lakshmi.stdcmp.tracking.StandardTracking.updateStatus(StandardTracking.java:50)
        at ch.ethz.id.wai.lakshmi.engine.ejb.LakshmiServerBean.updateTrackingStatus(LakshmiServerBean.java:992)
        at ch.ethz.id.wai.lakshmi.engine.ejb.LakshmiServerBean.executeNextWorkflowStep(LakshmiServerBean.java:771)
        at ch.ethz.id.wai.lakshmi.engine.ejb.LakshmiServerBean.executeNextWorkflowStep(LakshmiServerBean.java:683)
        at ch.ethz.id.wai.lakshmi.engine.ejb.LakshmiServerBean.continueWorkflow(LakshmiServerBean.java:602)
        at ch.ethz.id.wai.lakshmi.engine.ejb.LakshmiServerBean.processSubmitOrderRequest(LakshmiServerBean.java:487)
        at ch.ethz.id.wai.lakshmi.engine.ejb.LakshmiServerBean.onMessage(LakshmiServerBean.java:360)
        at sun.reflect.GeneratedMethodAccessor133.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1052)
        at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1124)
        at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:4180)
        at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:5368)
        at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:5348)
        at com.sun.ejb.containers.MessageBeanContainer.deliverMessage(MessageBeanContainer.java:1099)
        at com.sun.ejb.containers.MessageBeanListenerImpl.deliverMessage(MessageBeanListenerImpl.java:81)
        at com.sun.enterprise.connectors.inbound.MessageEndpointInvocationHandler.invoke(MessageEndpointInvocationHandler.java:171)
        at $Proxy264.onMessage(Unknown Source)
        at com.sun.messaging.jms.ra.OnMessageRunner.run(OnMessageRunner.java:260)
        at com.sun.enterprise.connectors.work.OneWork.doWork(OneWork.java:114)
        at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.performWork(ThreadPoolImpl.java:497)
        at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:540)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
        at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:714)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:731)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$ConnectionHandler.invoke(AbstractJdbc23PooledConnection.java:352)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$ConnectionHandler.invoke(AbstractJdbc23PooledConnection.java:352)
        at $Proxy161.rollback(Unknown Source)
        at com.sun.gjc.spi.LocalTransactionImpl.rollback(LocalTransactionImpl.java:128)
        ... 28 more

We create the connection pools in the same way for MySQL and PostgreSQL:

asadmin create-jdbc-connection-pool \
    --restype javax.sql.DataSource \
    --datasourceclassname org.postgresql.jdbc2.optional.PoolingDataSource \
    --property URL='jdbc:postgresql://127.0.0.1:5432/doi':serverName=localhost:databaseName=doi:user=doi:password=******** doi

and

asadmin create-jdbc-connection-pool \
    --restype javax.sql.DataSource \
    --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource \
    --property serverName=127.0.0.1:dataBaseName=doi:user=doi:********=doi:schema=doi doi

How can I configure the data pool to close idle connection or to keep them alive and avoid the closed connection error?

Upvotes: 3

Views: 4643

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324691

Pg connections should remain valid indefinitely so long as:

  • The DB server isn't stopped or restarted
  • The DB server's IP doesn't change
  • There's no connection-tracking or NAT router between client and server

The latter two aren't the problem since you're connecting to localhost, so you should not need any special measures to keep your connections alive.

I'm a bit suspicious that you're using org.postgresql.ds.PGSimpleDataSource . Glassfish has its own pooling, and you should probably be using the plan org.postgresql.datasource.Datasource . You quite possibly have two layers of pooling going on, with possibly confusing results. You will note the statement in the PgJDBC documentation that:

The pooling data-source implementation provided here is not the most feature-rich in the world. Among other things, connections are never closed until the pool itself is closed; there is no way to shrink the pool. As well, connections requested for users other than the default configured user are not pooled. Its error handling sometimes cannot remove a broken connection from the pool. In general it is not recommended to use the PostgreSQL™ provided connection pool. Check your application server or check out the excellent jakarta commons DBCP project.

Please switch to using org.postgresql.ds.PGSimpleDataSource and see if that resolves your issues.

Upvotes: 2

Related Questions