Support Article
Designer Studio query runner errors: "the connection is closed"
SA-56721
Summary
Error occurs when using Designer Studio Query Runner wizard. This occurs despite adding the correct PegaRULES:DatabaseAdministrator role to the access group and restarting the system.
Error Messages
There was a problem performing a database query: Database-General Problem encountered when getting connection for database pegadata 0 08003 This connection has been closed.
DatabaseException caused by prior exception: org.postgresql.util.PSQLException: This connection has been closed.
| SQL Code: 0 | SQL State: 08003
Steps to Reproduce
Run the Query Runner.
Root Cause
A defect or configuration issue in the operating environment.
The connection pool elements in the context.xml were insufficient to guarantee that the connection provided to the application was valid. An explicit set of connection pool parameters had to be provided for this.
Resolution
Perform the following local-change:
Insert the following attributes to the Resource element for the jdbc/PegaRULES connection.
In the context.xml, add the following:
testOnBorrow="true"
testOnConnect="true"
testOnReturn="true"
testWhileIdle="true"
validationQuery="select 1"
This causes the validation query to excute at specific points in time as follows:
testOnBorrow: If true, connections are validated before being returned from the pool. If the validation fails, the connection is destroyed and a new connection is retrieved from the pool (and validated).
testOnConnect: This indicates if objects are validated when a connection is first created. If an object fails to validate, it display an SQLException.
testOnReturn: If true, connections are validated before being returned to the pool. If the validation fails, the connection is destroyed instead of being returned to the pool.
testWhileIdle: If true, connections are validated by the idle connection evictor (if any). If the validation fails, the connection is destroyed and removed from the pool.
The validation query above is valid for Postgres.
Consult the specific database vendor for appropriate validation query to use.
For long running queries that are being 'disconnected', modify the following:
removeAbandoned="true"
removeAbandonedTimeout="some large number greater than the longest expected query"
Additionally, consider setting removeAbandoned="false"
To log abandoned connections, set
logAbandoned="true"
Published December 29, 2018 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.