We are still using the Wildfly distribution (version 15.1.1) with an Oracle database used by the cluster (two instanses of KC), and was wondering if there is an easy way to adjust the initialize_sql property or JDBC_PING set-up in a different way in order to be able to tolerate a restore of the database. We have experienced this scenario a couple of times:
- Due to some issue, the database needs to be restored
- When starting Keycloak again after the restore, a cluster cannot be created due to the outdated ping data.
- We have to manually delete the outdated ping data from the database in order to start Keycloak. We would like to avoid doing this third step.
Ideally, we want to set up JDBC_PING such that JGROUPSPING is recreated when initlaizing a cluster so that outdated ping data will never be used. Is it possible to do that in the initialize_sql property?
Our current setup for JDBC_PING:
/subsystem=jgroups/stack=tcp/protocol=JDBC_PING: add(data-source="KeycloakDS", properties=[initialize_sql="CREATE TABLE JGROUPSPING (own_addr varchar(200) NOT NULL, created timestamp NOT NULL, cluster_name varchar(200) NOT NULL, ping_data blob, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name))", insert_single_sql="INSERT INTO JGROUPSPING (own_addr, created, cluster_name, ping_data) values (?,sysdate, ?, ?)", delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?", select_all_pingdata_sql="SELECT ping_data FROM JGROUPSPING WHERE cluster_name=?"])