RBPM stops responding during idle times

  • 7010371
  • 03-Jul-2012
  • 03-Jul-2012

Environment

NetIQ Identity Manager Roles Based Provisioning Module 4.0.1
JBoss 5.1
Microsoft SQL Server

Situation

When RBPM/User Application stays idle for long periods of time (like over a weekend), it would stop working and present the following error in the server.log:

INFO  [STDOUT] (Timer-5) 210330799 [Timer-5] WARN org.hibernate.util.JDBCExceptionReporter  - SQL Error: 0, SQLState: 08S01
INFO  [STDOUT] (Timer-5) 210330799 [Timer-5] ERROR org.hibernate.util.JDBCExceptionReporter  - Connection reset
 INFO  [STDOUT] (Timer-5) 210330897 [Timer-5] ERROR org.hibernate.transaction.JDBCTransaction  - JDBC rollback failed
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(Unknown Source)
    at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.java:545)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:826)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.rollback(WrappedConnection.java:499)
    at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:213)
    at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:192)
    at com.novell.soa.persist.HibernateUtil.rollback(HibernateUtil.java:237)
    at com.novell.soa.persist.HibernateDAO.rollback(HibernateDAO.java:463)
    at com.novell.soa.persist.HibernateDAO.findByExample(HibernateDAO.java:410)
    at com.novell.soa.persist.HibernateDAO.findByExample(HibernateDAO.java:378)
    at com.novell.soa.persist.HibernateDAO.findByExample(HibernateDAO.java:362)
    at com.novell.soa.af.impl.timers.PendingActivityTimerTask.restartPendingActivities(PendingActivityTimerTask.java:91)
    at com.novell.soa.af.impl.timers.PendingActivityTimerTask.run(PendingActivityTimerTask.java:51)
    at java.util.TimerThread.mainLoop(Timer.java:512)
    at java.util.TimerThread.run(Timer.java:462)

restarting JBoss will get RBPM back working until the next inactivity period.

Resolution

JBoss requires an additional parameter to validate database connections to MS SQL via JDBC before using them. The absence of that parameter could cause the issue described during long inactivity times. For MS SQL, the parameter is:
<check-valid-connection-sql>SELECT 1 FROM sysobjects</check-valid-connection-sql>
 
This parameter needs to be added to the -ds.xml file used by the web application. So if RBPM is configured to use http://ip:port/IDM , the file would be IDM-ds.xml and reside at <jboss install root>/server/IDM/deploy/IDM-ds.xml . If  RBPM is configured to use http://ip:port/IDMProv , the file would be IDM-ds.xml and reside at <jboss install root>/server/IDM/deploy/IDMProv-ds.xml .

Additional Information

Sample IDM-ds.xml file contents, with the additional parameter already in place:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
    <local-tx-datasource>
        <jndi-name>IDMUADataSource</jndi-name>
        <connection-url>jdbc:sqlserver://mylab:1234;DatabaseName=UserAppDB</connection-url>
        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
        <security-domain>EncryptDBPassword</security-domain>
        <check-valid-connection-sql>SELECT 1 FROM sysobjects</check-valid-connection-sql>
    </local-tx-datasource>
</datasources>


Feedback service temporarily unavailable. For content questions or problems, please contact Support.