Shibboleth and database connection leak

[Tobias Galéus]

I've investigated this a bit further. Shibboleth reports a deadlock:

2018-07-06 10:59:19,552 - INFO
[net.shibboleth.idp.authn.impl.ValidateUsernamePasswordAgainstLDAP:152] -
Profile Action ValidateUsernamePasswordAgainstLDAP: Login by 'gusgXXXXX'
succeeded|130.xxx.xx.xxx|
2018-07-06 10:59:19,642 - WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper:144] - SQL Error: 1213,
SQLState: 40001|130.xxx.xx.xxx|
2018-07-06 10:59:19,643 - ERROR
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146] - Deadlock found when
trying to get lock; try restarting transaction|130.xxx.xx.xxx|

SQL Queries during that period of time (I've masked usernames, IPs and
tickets):
https://pastebin.com/TpaWm8C8

I'm using Hikari CP and configured it to report leaks after 5 minutes. So
after 5 minutes hikari logs a leak:
11:04:19.645 [HikariPool-1 housekeeper] Connection leak detection triggered
for com.mysql.jdbc.JDBC4Connection at 44309e77 on thread qtp721748895-1747,
stack trace follows
java.lang.Exception: Apparent connection leak detected

Current configuration in global.xml:

    <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="shibboleth"
    p:password="REMOVED"
    p:autoCommit="false"
    p:transactionIsolation="TRANSACTION_SERIALIZABLE"
    p:leakDetectionThreshold="300000"
    p:maximumPoolSize="4"
    p:maxLifetime="600000"
    p:idleTimeout="300000"
    />


Is this a Shibboleth bug or a configuration error on my part? How can I
investigate this further?

Best regards,
Tobias



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