Jpa Connection pool settings

[Joe Manavalan]

Hi,

Is there a connection pool setting which can mitigate the below error ? The server after running for few days, stops working with this error. 

I am using  CAS 6.1 [overlay template] with  cas.authn.pac4j.oauth2  and jpa registry 

Following is my current settings for ticket registry

cas.ticket.registry.jpa.driverClass=oracle.jdbc.OracleDriver

cas.ticket.registry.jpa.dialect=org.hibernate.dialect.Oracle10gDialect

cas.ticket.registry.jpa.jpaLockingTimeout=60

cas.ticket.registry.jpa.autocommit=true

cas.ticket.registry.jpa.ddl-auto=none

cas.ticket.registry.jpa.user=

cas.ticket.registry.jpa.password=

cas.ticket.registry.jpa.url=

I was wondering if any of the following properties helps and was looking for some documentation on these

cas.ticket.registry.jpa.failFastTimeout=
cas.ticket.registry.jpa.healthQuery=SELECT 1
cas.ticket.registry.jpa.isolateInternalQueries=
cas.ticket.registry.jpa.leakThreshold=
cas.ticket.registry.jpa.batchSize=
cas.ticket.registry.jpa.jpaLockingTimeout=

cas.ticket.registry.jpa.pool.minSize=6

cas.ticket.registry.jpa.pool.maxSize=18

Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.

at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697) ~[HikariCP-3.4.1.jar:?]

at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:196) ~[HikariCP-3.4.1.jar:?]

at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:161) ~[HikariCP-3.4.1.jar:?]

at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-3.4.1.jar:?]

at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:104) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:250) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:258) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:246) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:83) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:184) ~[spring-orm-5.2.0.RELEASE.jar:5.2.0.RELEASE]

at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:402) ~[spring-orm-5.2.0.RELEASE.jar:5.2.0.RELEASE]

Thanks in advance

[Tom O'Neill]

Joe,

 

I don’t know if you removed it for security purposes but the error says that the application timed out trying to connect to the database and your settings are empty.

It looks like your database URL (and other details) may be missing.

Either way, double check your cas.ticket.registry.jpa.url and the corresponding port.

 

Good luck!

Tom

 

From: cas-...@apereo.org <cas-...@apereo.org> On Behalf Of Joe Manavalan
Sent: Monday, January 11, 2021 1:35 PM
To: CAS Community <cas-...@apereo.org>
Subject: [EXT] [cas-user] Jpa Connection pool settings

 

CAUTION: This email originated from outside of SIG. Exercise caution when opening attachments or clicking links, especially from unknown senders.

[EXT-STAMP-ADDED]

--
- Website: https://apereo.github.io/cas
- Gitter Chatroom: https://gitter.im/apereo/cas
- List Guidelines: https://goo.gl/1VRrw7
- Contributions: https://goo.gl/mh7qDG
---
You received this message because you are subscribed to the Google Groups "CAS Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+u...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/583da684-7571-4492-a7d2-bedbb5bb80bcn%40apereo.org.

[Joe Manavalan]

Hi Tom

Connection details were intentionally removed here. Everything was working continuously for more than a month before it started showing the error. Restart of the server took care of the issue. But this is the second time in few months. So was wondering any connection pool related setting can resolve this or not.