[keycloak-user] Idle connections are not closed

Fri Jul 14 06:56:07 EDT 2017

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