Identity Portal server.log is huge in size filled with repetitive IJ000609: Attempt to return connection twice exceptions

Products

CA Identity Suite

Issue/Introduction

The following JDBC SQL exception messages repetitively happened and filling up the Identity Portal (IP) server.log. This repetitive messages increase the IP server.log size rapidly and hence filling up the disk space.

2022-03-28 15:45:21,127 INFO [stdout] (default task-133) [SIGMA APPENDER - FROM WEB] 2022-03-28 15:45:21,099 - WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: null
2022-03-28 15:45:21,130 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject] (default task-133) IJ000609: Attempt to return connection twice: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@9128e13[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@72b43356 connection handles=0 lastReturned=1648500321130 lastValidated=1648480596120 lastCheckedOut=1648499850227 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@bdb2fc8 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@401bcf38[pool=sigma-ds] xaResource=LocalXAResourceImpl@415fd2ba[connectionListener=9128e13 connectionManager=61e1cf8c warned=false currentXid=null productName=Microsoft SQL Server productVersion=13.00.5888 jndiName=java:jboss/datasources/jdbc/sigmadb] txSync=null]: java.lang.Throwable: STACKTRACE
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.returnConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:722)
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.returnConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:611)
<... cut ...>

Environment

Release : 14.4

Component : Identity Suite Virtual Appliance, external MS SQL database

Cause

We suspect the issue is due to lacking of database connection resources.

Resolution

Please do the following steps:

A. Increase max_workers_count parameter on IP database at MS SQL to 1024 if it is currently set lower than 1024.

To set max_workers_count parameter on IP database at MS SQL to 1024 you can use the following SQL statement. However, please check with DBA and consider and validate this recommendation.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max worker threads', 1024 ;
GO
RECONFIGURE;

B. Adjust database pool connection to have the following parameter in IP standalone.xml using jboss-cli

<pool>
<min-pool-size>20</min-pool-size>
<initial-pool-size>35</initial-pool-size>
<max-pool-size>100</max-pool-size>
<prefill>true</prefill>
</pool>

To utilize jboss-cli to modify IP standalone XML, first of all, you need to create JBoss/Wildfly local management account. The following KB article gives the steps in its Resolution section to create the account. Please notice though, the steps in the KB article for wildfly-idm. Please do similar steps for wildfly-portal in this case.

https://knowledge.broadcom.com/external/article?articleId=133305

Once you have the local management account, then
a. Run the following to connect (e.g. the local management account is 'jboss-admin')

        /opt/CA/wildfly-portal/bin/jboss-cli.sh --connect --user=jboss-admin

      You will be prompted to key in the password for jboss-admin account. Key in the password and you should get the following prompt when it is connected successfully
      [standalone@localhost:9991 /]

b. Run the following 4 commands to set the required pool parameter

/subsystem=datasources/data-source=sigma-ds:write-attribute(name=min-pool-size,value=20)
/subsystem=datasources/data-source=sigma-ds:write-attribute(name=initial-pool-size,value=35)
/subsystem=datasources/data-source=sigma-ds:write-attribute(name=max-pool-size,value=100)
/subsystem=datasources/data-source=sigma-ds:write-attribute(name=pool-prefill,value=true)

c. View /opt/CA/wildfly-portal/standalone/configuration/standalone.xml and make sure you have the following parameter for sigmadb pool section.

d. exit the jboss-cli connection
exit

e. restart IP
restart_ip