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

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).

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 -- С уважением, Мерзликин Кирилл