Keycloak not able to connect external DB after database restart?

Question

We are using Keycloak 4.2.1.Final and we noticed some weird issue.Keycloak is using external DB [Maria DB]

How to reproduce the issue ?

1. Install and run the Keycloak.
2. Use external DB to store data and we use a mariadb.
3. Keycloak up/running,MariaDB up and running
4. Now stop Mariadb service systemctl stop mariadb and then start mariadb systemctl start mariadb and check keycloak wont work
5. Try to login to keycloak https://localhost:8666/auth and check it wont allow to login.
6. In server log it will show Connection is closed

Solution 1 -

After restarting the Mariadb its mandatory to start the Keycloak service as well service keycloak restart then Keycloak will start responding properly.

But this is not a feasible Solution ,I am looking a proper solution for this .Anyone ever face or checked this type of issue?

Answer 1

At last this issue is resolve by modifying Stanalone.xml file of Keycloak.You can find the file into this location /opt/keycloak/standalone/configuration/standalone.xml ,you have to add below lines into the file

 <validation>                   
       <check-valid-connection-sql>select 1</check-valid-connection-sql>
       <background-validation>true</background-validation>
       <background-validation-millis>15000</background-validation-millis>
    </validation>

This lines should be added inside <datasource/> tag after adding above changes <datasource/> will be look like this

<datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true"> 
     <connection-url>jdbc:mariadb://localhost:3306/DBName?autoReconnect=true</connection-url>
           <driver>mariadb</driver> 
            <security> 
             <user-name>user</user-name> 
             <password>${VAULT::datasource::default-password::1}</password>
             </security>
             <validation>                     
             <check-valid-connection-sql>select 1</check-valid-connection-sql>
             <background-validation>true</background-validation>
              <background-validation-millis>15000</background-validation-millis>
              </validation>
</datasource>

Answer 2

After adding validation check, Keycloak will keep on checking about the connectivity with the database in the background with the given interval. So even though the connection tunnel is broken, it will reconnect to database. Without the validation in datasource, it won't reconnect once the connection is broken. That's why we need to add

 <validation>                   
       <check-valid-connection-sql>select 1</check-valid-connection-sql>
       <background-validation>true</background-validation>
       <background-validation-millis>15000</background-validation-millis>
    </validation>

Hope that clarifies your doubt.