[keycloak-user] Idle connections are not closed

[Кир Мерзликин]

Hi Hynek,

Just did it  — for all "idle" connections the command is Sleep and the time
is increasing constantly (till 90 sec).

Regarding other changes - I don't think so. At first I only specified JDBC
URL and that was it. Then I started seeing errors in the log about the
connection being closed by server and impossibility of query execution.
Then I found Pivotal recommendations and  JBOSS documentation and added
pool, timeout and validation configs.
So now I don't see previous errors, but I see new ones - from connection
validator, that  connection is not valid and ping fails.
It's good, that idle connections are not used anymore but what I what I
want is to close idle connections on the client before they are closed by
the server.

I saw similar situation described on stackoverflow
<https://stackoverflow.com/questions/28645435/database-connections-not-closed-after-idle-timeout-in-wildfly-datasource>,
but suggested solution didn't work for me, since I didn't know how to pass
custom hibernate property (-D option didn't help).

Thanks,

On Fri, Jul 14, 2017 at 7:20 AM, Hynek Mlnarik <hmlnarik at redhat.com> wrote:

> Can you inspect commands running in expectedly idle connections in
> MySQL via SHOW FULL PROCESSLIST? Have you changed any other
> datasource- and transaction-related configuration?
>
> Thanks
>
> On Thu, Jul 13, 2017 at 11:25 PM, Кир Мерзликин <kirmerzlikin at gmail.com>
> wrote:
> > Hi Bill,
> >
> > Yes, I have read a lot of documentation articles and manuals regarding
> > datasource configuration today.
> > Basing on this material, I've created following config, which should work
> > as I want:
> >
> > - - - - - - - - -
> > <datasource jndi-name="java:jboss/datasources/KeycloakDS"
> > pool-name="KeycloakDS" enabled="true" use-java-context="true">
> >         <connection-url>jdbc:mysql://blah.cleardb.net/blah?user=
> > blah&password=blah</connection-url>
> >         <driver>mysql</driver>
> >     <pool>
> >         <min-pool-size>1</min-pool-size>
> >         <max-pool-size>10</max-pool-size>
> >     </pool>
> >     <timeout>
> >         <idle-timeout-minutes>1</idle-timeout-minutes>
> >     </timeout>
> >     <validation>
> >         <validate-on-match>true</validate-on-match>
> >         <valid-connection-checker class-name="org.jboss.jca.
> > adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
> >         <exception-sorter  class-name="org.jboss.jca.
> > adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
> >     </validation>
> > </datasource>
> > - - - - - - - - -
> >
> > But the fact is that idle connections are still able to live to 90
> seconds
> > limit, instead of 1 minute.
> >
> > My suggestions was that connection was not closed by some Keycloak code
> and
> > then returned to the connection pool, where it wasn't considered idle and
> > was not "closed" (terminated).
> >
> > Could you please share your thoughts on this?
> >
> > Thanks.
> >
> >
> > On Thu, Jul 13, 2017 at 9:14 PM, Bill Burke <bburke at redhat.com> wrote:
> >
> >> You'll have to dive into Wildfly/JBoss EAP datasource configuration
> >> documentation.  I know there are a multitude of connection pool switches
> >> you can specify.
> >>
> >>
> >> On 7/13/17 10:03 AM, Kir Merzlikin wrote:
> >> > Hi all,
> >> >
> >> > I deploy Keycloak server app to Cloud Foundry and use ClearDB service
> as
> >> > relational database for Keycloak.
> >> >
> >> > ClearDB has a restriction, that it closes all connections, that are
> idle
> >> for
> >> > 90 seconds.
> >> > To not run into the situation, when Keycloak tries to use closed
> >> connection,
> >> > I've added following datasource configuration (based on  Pivotal
> >> > recommendations
> >> > <https://discuss.pivotal.io/hc/en-us/articles/230433268-
> >> Suggested-Configuration-for-Connection-Pools-using-ClearDb>
> >> > ):
> >> >
> >> >
> >> >
> >> > But even after applying this configuration I see in the ClearDB
> >> management
> >> > console that idle connections are closed only after 90 seconds and not
> >> after
> >> > 60 seconds (1 minute) as it's specified with "idle-timeout-minutes"
> >> > parameter.
> >> >
> >> > So, have anybody of you faced similar situation? Or maybe you have
> some
> >> > ideas why these idle connections are not being closed.
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > --
> >> > View this message in context: http://keycloak-user.88327.x6.
> >> nabble.com/Idle-connections-are-not-closed-tp3948.html
> >> > Sent from the keycloak-user mailing list archive at Nabble.com.
> >> > _______________________________________________
> >> > keycloak-user mailing list
> >> > keycloak-user at lists.jboss.org
> >> > https://lists.jboss.org/mailman/listinfo/keycloak-user
> >>
> >> _______________________________________________
> >> keycloak-user mailing list
> >> keycloak-user at lists.jboss.org
> >> https://lists.jboss.org/mailman/listinfo/keycloak-user
> >>
> >
> >
> >
> > --
> > С уважением,
> > Мерзликин Кирилл
> > _______________________________________________
> > keycloak-user mailing list
> > keycloak-user at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/keycloak-user
>
>
>
> --
>
> --Hynek
>



-- 
С уважением,
Мерзликин Кирилл