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

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(a)gmail.com&gt; 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(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/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&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 > > > > > -- > С уважением, > Мерзликин Кирилл -- --Hynek