Re: [keycloak-user] Idle connections are not closed

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(a)redhat.com&gt; wrote: > > On Fri, Jul 14, 2017 at 7:07 AM, Кир Мерзликин <kirmerzlikin(a)gmail.com&gt; > 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/resou... > > > On Fri, Jul 14, 2017 at 7:20 AM, Hynek Mlnarik <hmlnarik(a)redhat.com&gt; > > 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(a)gmail.com&gt; > >> 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(a)redhat.com&gt; > >> > 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(a)lists.jboss.org > >> >> > https://lists.jboss.org/mailman/listinfo/keycloak-user > >> >> > >> >> _______________________________________________ > >> >> keycloak-user mailing list > >> >> keycloak-user(a)lists.jboss.org > >> >> https://lists.jboss.org/mailman/listinfo/keycloak-user > >> >> > >> > > >> > > >> > > >> > -- > >> > С уважением, > >> > Мерзликин Кирилл > >> > _______________________________________________ > >> > keycloak-user mailing list > >> > keycloak-user(a)lists.jboss.org > >> > https://lists.jboss.org/mailman/listinfo/keycloak-user > >> > >> > >> > >> -- > >> > >> --Hynek > > > > > > > > > > -- > > С уважением, > > Мерзликин Кирилл > > > > -- > > --Hynek -- С уважением, Мерзликин Кирилл