[keycloak-user] Idle connections are not closed
Hynek Mlnarik
hmlnarik at redhat.com
Fri Jul 14 07:21:28 EDT 2017
In that case I suggest you to ask Wildfly community as this does not
look like Keycloak issue. If you find a way to resolve it, I'd be
interested if you could share your findings.
Thanks
--Hynek
On Fri, Jul 14, 2017 at 1:18 PM, Кир Мерзликин <kirmerzlikin at gmail.com> wrote:
> 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
>
>
>
>
> --
> С уважением,
> Мерзликин Кирилл
--
--Hynek
More information about the keycloak-user
mailing list