[keycloak-user] Database connection spurious failure when adding a new client via admin-cli

[Jan Lieskovsky]

Hello Nicolò,

On Fri, Oct 25, 2019 at 9:04 AM Nicolò Di Domenico <ndido98 at gmail.com>
wrote:

> I tried to update the server configuration file by using the one directly
> bundled with Keycloak 7.0.1, but the problem still persists. Any ideas?
>
> Il giorno lun 21 ott 2019 alle ore 17:09 Nicolò Di Domenico <
> ndido98 at gmail.com> ha scritto:
>
> > Hello everyone,
> >
> >
> >
> > I’m currently struggling with an issue that appears with no regular
> > pattern whatsoever. I’m deploying Keycloak to a server via Ansible, and
> > later in the playbook I create a new client using its dedicated module.
> The
> > problem is that the first time everything’s fine, but when I call the
> > playbook again to make some modifications, it sometimes gets stuck when
> > using admin-cli to add the client: if that happens, the server returns
> with
> > a 500 error and everything stops. By looking at the logs, it looks like
> > that Keycloak for some reason loses connection to the MariaDB server, and
> > it won’t make it again until I restart Keycloak manually, because every
> > subsequent database connection will fail.
> >
> > I’ll leave down here the link to a gist with the server logs, the
> > standalone.xml configuration file, and MariaDB and its JDBC version.
> >
> > https://gist.github.com/ndido98/971e19ba2b071f34d37055d973234e7e
> >
> > Thank you in advance for your help.
>

1) Were you able to reproduce the same issue with some other DB driver /
backend, than just MariaDB / MySQL one?
2) For the case you want to debug MariaDB case more - is it possible,
calling the playbook next time succeeds if
the second call was issued relatively shortly after the first one?

Unsure, this is the real cause of the behaviour, but looks you might get
hit by the MariaDB / MySQL default waitTimeout
<https://mariadb.com/kb/en/library/migrating-from-mysql-mariadb-server-closing-client-connections-unexpectedly/>
setting issue
<https://mariadb.com/kb/en/library/migrating-from-mysql-mariadb-server-closing-client-connections-unexpectedly/>.
AFAICT defaut waitTimeout()
<https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_wait_timeout>
settings seems to be 80 hours (something more than ~3 days). After it,
non-interactive connections are closed on the MariaDB / MySQL end.

OTOH, Hibernate's c3p0.maxIdleTime
<https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html>
seems
to default to zero, so idle connections (on Hibernate end) never expire
<https://www.mchange.com/projects/c3p0/#maxIdleTime>.
(that might explain why after some time, when the idle connection was
already closed on the MariaDB end, but
is still considered as valid on Hibernate end, might result into connection
refused / closed error).

Per this thread,
<https://discourse.hibernate.org/t/hibernate-throws-cannot-do-an-operation-on-a-closed-statement-on-mariadb/558/2>
if connection pooling is used, the maxLifetime value on the Hibernate end
should be set to a lower
value than the corresponding one on the database server.

Try to increase the waitTimeout() on the MariaDB server, if it makes any
difference. If that doesn't help, I would
try to maxIdleTime datasource setting to see, if it helps.

HTH

>
> >
> >
> > Greetings,
> >
> > Nicolò
> >
>

Regards, Jan
--
Jan iankko Lieskovsky / Keycloak / RH-SSO Team




> >
> >
> >
> >
> >
> >
> >
> >
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user