Issue:
Have one central server for IAM. It is connected to a MSSQL database.
The logs show there were some issues related to a sql query and because of that IAM restarted.
Seems like our other server that is connected to that IAM also went down instantly.
Below is the log:
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1619) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1106) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1033) at sun.reflect.GeneratedMethodAccessor348.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:49) ... 67 more Caused by: org.hibernate.exception.GenericJDBCException: could not prepare statement at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.prepareQueryStatement(AbstractLoadPlanBasedLoader.java:241) at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:185) at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121) at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86) at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167) at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4069) at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508) at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478) at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219) at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:278) at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:121) at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89) at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1142) at org.hibernate.internal.SessionImpl.access$2600(SessionImpl.java:167) at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.doLoad(SessionImpl.java:2762) at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2741) at org.hibernate.internal.SessionImpl.get(SessionImpl.java:978) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.find(AbstractEntityManagerImpl.java:1075) ... 72 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190) at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:388) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:2166) at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757) at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743) at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) ... 91 more 2019-03-20 13:10:10,893 INFO [org.jboss.as.server] (Thread-2) WFLYSRV0220: Server shutdown has been requested via an OS signal
Resolution:
Added lines to the {{LISA_HOME}}/IdentityAccessManager/standalone/configuration/standalone.xml file:
Added the following tag in the standalone.xml, in the MSSQL datasource tag:
<validation>
<valid-connection-checker class-name=“org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker”></valid-connection-checker>
<background-validation>true</background-validation>
<check-valid-connection-sql>select getdate()</check-valid-connection-sql>
</validation>
Often the connections in the JBoss connection-pool go bad, the above validation check would create a new connection if the validation query fails to fetch the results.
After the change was made IAM service was restarted and this resolved the issue.
Also here is the change if using Oracle:
<validation>
<valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
<background-validation>true</background-validation>
<check-valid-connection-sql>select getdate()</check-valid-connection-sql>
</validation>
Additional Information:
Reference for this change was gotten from this link: https://developer.jboss.org/thread/235206
Knowledge Base Document is KB000131438.