[keycloak-user] Keycloak using Sql Server on Azure as db: stops working after few days

[Pedro Igor Silva]

Maybe your server is being restarted and the connections in the pool get
stale.

You could try to change server's data source as follows:

/subsystem=datasources/data-source=KeycloakDS/:write-attribute(name=background-validation,value=true)
/subsystem=datasources/data-source=KeycloakDS/:write-attribute(name=check-valid-connection-sql,value="select
1")
/subsystem=datasources/data-source=KeycloakDS/:write-attribute(name=background-validation-millis,value=120000)


Regards.
Pedro Igor

On Mon, Jul 29, 2019 at 4:22 AM Mario Giammarco <mgiammarco at gmail.com>
wrote:

> Hello,
>
> I have installed a Keycloak as an Azure virtual machine. As database I have
> chosen the Sql Server offered as an Azure service. I have configured it
> correctly because all works. But after some days it stops working. I get
> these exceptions:
>
>
>
> 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:113)
>     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.Loader.prepareQueryStatement(Loader.java:1984)
>     at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1914)
>     at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
>     at org.hibernate.loader.Loader.doQuery(Loader.java:937)
>     at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
>     at org.hibernate.loader.Loader.doList(Loader.java:2689)
>     at org.hibernate.loader.Loader.doList(Loader.java:2672)
>     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
>     at org.hibernate.loader.Loader.list(Loader.java:2501)
>     at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504)
>     at
> org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
>     at
> org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
>     at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1507)
>     at
> org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537)
>     at
> org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
>     ... 13 more
> Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The
> connection is closed.
>     at
> com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234)
>     at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:1088)
>     at
> com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:3409)
>     at
> org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:758)
>     at
> org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:744)
>     at
> org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:459)
>     at
> org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
>     at
> org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
>
>
>
>
> It seems that, after some time, sql server refuses new connections.
> I have tried reducing max-pool-size and I have obtained that it now
> gives the problem some days before.
> I need help because the exception is not very informative.
> Thanks in advance for any hints.
> Mario
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user
>