We've deployed several apps that run in the same tomcat container, and connect to our postgresql database through a JNDI lookup. Here's the sanitized config (server.xml):
我们已经部署了几个在同一个tomcat容器中运行的应用程序，并通过JNDI查找连接到我们的postgresql数据库。下面是经过清理的配置(server.xml):
<Resource name="MyDataSource"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://myrdsinstance.com:5432/MyDatabase"
username="my_username"
password="my_password" />
This was running fine for months, but this morning any attempt at reading the DB failed:
几个月来，这一切都很顺利，但今天早上任何阅读DB的尝试都失败了:
org.postgresql.util.PSQLException: This connection has been closed.
org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:843)
org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:804)
sun.reflect.GeneratedMethodAccessor1079.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
com.sun.proxy.$Proxy30.getAutoCommit(Unknown Source)
org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:68)
org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1309)
org.hibernate.ejb.TransactionImpl.begin(TransactionImpl.java:57)
org.springframework.orm.jpa.DefaultJpaDialect.beginTransaction(DefaultJpaDialect.java:70)
org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:59)
org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:377)
org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
The temporary fix was to restart the tomcat7 service, which I assume re-opened the closed DB connection. But now I'm investigating the root cause and hopefully a long-term fix. I found from catalina.out:
临时修复是重新启动tomcat7服务，我假设该服务重新打开了已关闭的DB连接。但现在我正在调查根本原因，希望能长期解决。我发现从catalina.out:
2015-02-09 14:15:55,260 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQL Error: 0, SQLState: 08003
From the PostgreSQL error codes doc I see that it means connection_does_not_exist.
从PostgreSQL错误代码doc中，我看到它表示connection_does_not_exist。
I've done some Googling and couldn't find much relating to this exact problem. I would think that if a connection is closed it would be handled by JDBC, Hibernate, or even GORM (for the grails apps). Am I missing something obvious here?
我用谷歌搜索了一下，找不到与这个问题相关的内容。我认为，如果一个连接被关闭，它将被JDBC、Hibernate甚至GORM(对于grails应用程序)处理。我漏掉了什么明显的东西吗?
1 个解决方案
#1
0
Examining the code for BasicDataSource in commons-dbcp, I've come to the conclusion that by specifying a validationQuery as a property of the data source we're allowing the container to verify that the connection is valid before it ever reaches our code. Of course there still is a small chance that the connection becomes invalid between when it is validated by tomcat and when it reaches our code, so this solution is not ideal. In short though:
通过在common -dbcp中检查BasicDataSource的代码，我得出的结论是，通过将validationQuery指定为数据源的属性，我们允许容器在连接到达我们的代码之前验证连接是否有效。当然，当tomcat被tomcat验证并到达我们的代码时，连接变得无效的可能性仍然很小，所以这个解决方案并不理想。简而言之:
<Resource name="MyDataSource"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://myrdsinstance.com:5432/MyDatabase"
username="my_username"
password="my_password"
validationQuery="SELECT 1" />
Hopefully there's a better solution than this, but I figured I'd post what we did for any other googlers out there.
希望有更好的解决方案，但我想我应该把我们为其他谷歌员工所做的事情公布出来。
#1
0
Examining the code for BasicDataSource in commons-dbcp, I've come to the conclusion that by specifying a validationQuery as a property of the data source we're allowing the container to verify that the connection is valid before it ever reaches our code. Of course there still is a small chance that the connection becomes invalid between when it is validated by tomcat and when it reaches our code, so this solution is not ideal. In short though:
通过在common -dbcp中检查BasicDataSource的代码，我得出的结论是，通过将validationQuery指定为数据源的属性，我们允许容器在连接到达我们的代码之前验证连接是否有效。当然，当tomcat被tomcat验证并到达我们的代码时，连接变得无效的可能性仍然很小，所以这个解决方案并不理想。简而言之:
<Resource name="MyDataSource"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://myrdsinstance.com:5432/MyDatabase"
username="my_username"
password="my_password"
validationQuery="SELECT 1" />
Hopefully there's a better solution than this, but I figured I'd post what we did for any other googlers out there.
希望有更好的解决方案，但我想我应该把我们为其他谷歌员工所做的事情公布出来。