[keycloak-user] Idle connections are not closed
Кир Мерзликин
kirmerzlikin at gmail.com
Fri Jul 14 07:18:40 EDT 2017
Hi Hynek,
Thanks for an idea where to find a persistence.xml
But after these changes it still works as before - idle connections live
till 90 seconds :(
p.s. sorry for not mentioning it earlier - I use Keycloak 3.1.0.Final
On Fri, Jul 14, 2017 at 1:56 PM, Hynek Mlnarik <hmlnarik at redhat.com> wrote:
> On Fri, Jul 14, 2017 at 7:07 AM, Кир Мерзликин <kirmerzlikin at gmail.com>
> wrote:
> > 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, but suggested
> solution
> > didn't work for me, since I didn't know how to pass custom hibernate
> > property (-D option didn't help).
>
> Can you try adding the following line into persistence.xml [1] file
> that is present in keycloak-model-jpa.jar module?
>
> -----------
> <property name="hibernate.connection.release_mode">after_
> transaction</property>
> -----------
>
> [1] https://github.com/keycloak/keycloak/blob/master/model/
> jpa/src/main/resources/META-INF/persistence.xml#L87
>
> > 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
> >
> >
> >
> >
> > --
> > С уважением,
> > Мерзликин Кирилл
>
>
>
> --
>
> --Hynek
>
--
С уважением,
Мерзликин Кирилл
More information about the keycloak-user
mailing list