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

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/resources/META-INF/persistence.xml#L87 > 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 > >> 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