Shibboleth and database connection leak

[Tobias Galéus]

We are having problems with our Shibboleth IDP 3.3.3 losing database
connections. While I do not think this is a bug in Shibboleth I guess it
might be a configuration error.

We are currently using HikariCP, but we've also tried commons dbcp with the
same result. When enabling logging and leak detection in Hikari it reports a
connection leak after about 5 minutes:

10:49:28.960 [HikariPool-1 housekeeper] Connection leak detection triggered
for com.mysql.jdbc.JDBC4Connection at 5f233c05 on thread qtp721748895-11, stack
trace follows
java.lang.Exception: Apparent connection leak detected
(Full stack trace available here: https://pastebin.com/LTx9x2X2 )

Of the 4 connections that I've configured Hikari to use, only 1 remains
after a few hours. While this last connection seem to be working fine, it
has happened that this connection also dies and the no more connection
attempts are made to the DB and Shibboleth (of course) stops functioning.

Does anyone have an idea on how to resolve this? I'm quite clueless here.

Pasting some related configuration from our installation here:

>From global.xml:

    <bean id="shibboleth.JPAStorageService"
class="org.opensaml.storage.impl.JPAStorageService"
       p:cleanupInterval="%{idp.storage.cleanupInterval:PT10M}"
c:factory-ref="shibboleth.JPAStorageService.EntityManagerFactory" />
    <bean id="shibboleth.JPAStorageService.EntityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="storageservice" />
    <property name="packagesToScan" value="org.opensaml.storage.impl" />
    <property name="dataSource"
ref="shibboleth.JPAStorageService.DataSource" />
    <property name="jpaVendorAdapter"
ref="shibboleth.JPAStorageService.JPAVendorAdapter" />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"
/>
    </property>
    </bean>
    <bean id="shibboleth.JPAStorageService.JPAVendorAdapter"
class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="MYSQL" />
    </bean>

    <bean id="shibboleth.JPAStorageService.DataSource"
    class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"
lazy-init="true"
    p:driverClassName="com.mysql.jdbc.Driver"
   
p:jdbcUrl="jdbc:mysql://127.0.0.1:3306/shibboleth?cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&cachePrepStmts=true&useServerPrepStmts=true"
    p:username="USERNAME"
    p:password="P at SSWORD"
    p:autoCommit="false"
    p:transactionIsolation="8"
    p:leakDetectionThreshold="300000"
    p:maximumPoolSize="4"
    p:maxLifetime="28000000"
    />

mysql> SHOW VARIABLES LIKE "wait_timeout%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

>From idp.properties:
idp.session.StorageService = shibboleth.JPAStorageService
idp.consent.StorageService = shibboleth.JPAStorageService
idp.replayCache.StorageService = shibboleth.JPAStorageService
idp.artifact.StorageService = shibboleth.JPAStorageService
idp.cas.StorageService=shibboleth.JPAStorageService

/opt/shibboleth-idp/edit-webapp/WEB-INF/lib/
-rw-r--r--. 1 jetty jetty 968670 Jun 15  2016
mysql-connector-java-5.1.35-bin.jar
-rw-r--r--. 1 jetty jetty 174167 Jun 15  2016 commons-dbcp2-2.1.1.jar
-rw-r--r--. 1 jetty jetty 111969 Jun 15  2016 commons-pool2-2.4.2.jar
-rw-r--r--. 1 jetty jetty 143188 May  3 12:14 HikariCP-3.1.0.jar





--
Sent from: http://shibboleth.1660669.n2.nabble.com/Shibboleth-Users-f1660767.html