If you can post or link to your full configuration, that would help us debug.
I finally figured it out - missing reference to jgroups stack. This really needs to be added to the documentation.
I updated my original post.
Hi @anowak-ct ,
Thanks for the config template. We have the same config setup with Keycloak 18 on Quarkus and able to see the below message in startup logs but the JGROUPSPING table still returns 0 rows.
ISPN000078: Starting JGroups channel KeycloakCluster with stack jdbc-ping-tcp
Can you please confirm if the bind_addr variable from insert sql command should be jboss.bind.address?
insert_single_sql=“INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,‘${jboss.bind.address:127.0.0.1}’,NOW(), ?, ?);”
Yes, I am using the same insert_single_sql as you do. However, I am using k8s and I am not familiar with Quarkus. Do you see log messages about KC instances joining the cluster?
Hi @anowak-ct
We have tested below 3 scenarios and are looking to get bind_addr value inserted to the table.
Case 1:
Dropped JGROUPSPING table, removed the initialize & insert statements from cache.xml
Result: keycloak has created the table and also inserted field values to the default 3 columns - (own_addr, cluster_name, ping_data). Cluster members were able to join each other.
Case 2:
When intitialize statement is added with additional fields but not insert sql to the cache.xml
Result: table doesnt get updated and throws below error.
2022-09-16 20:43:59,963 DEBUG [org.jgroups.protocols.JDBC_PING] (jgroups-11,ip-172-18-140-85-44275) Removed 34f
182af-85d9-4e10-9c0b-88022cabffe4 for cluster KeycloakCluster from database
2022-09-16 20:43:59,964 ERROR [org.jgroups.protocols.JDBC_PING] (jgroups-11,ip-172-18-140-85-44275) JGRP000145:
Error updating JDBC_PING table: org.postgresql.util.PSQLException: ERROR: null value in column “bind_addr” violates not-null constraint
Detail: Failing row contains (34f182af-85d9-4e10-9c0b-88022cabffe4, null, null, KeycloakCluster, \x029c0b88022cabffe434f182af85d94e100301001669702d3137322d31382d…)
initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, bind_addr VARCHAR(200) NOT NULL, created timestamp NOT NULL, cluster_name varchar(200) NOT NULL, ping_data BYTEA, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name))"
Case 3:
When both inittialize and insert sql are added to cache.xml jgroups stack
Result: table gets updated but not able to get the value for ${bind_addr} and the variable is inserted as below.
own_addr bind_addr created cluster_name ping_data
6364e211-4d41-44a0-affa-b5c75390b6bc ${bind_addr} 2022-09-16 21:23:06.755615 KeycloakCluster 0x02affab5c75390b6bc6364e2114d4144a
insert_single_sql="INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,'${bind_addr}',NOW(), ?, ?)"
This is probably because of the null value passed by keycloak for bind_addr so looking for options to get that field value inserted.
Any suggestions are appreciated.
Hi @hxb5422 ,
Hmm, In our env bind_addr is 127.0.0.1 in the JGROUPSPING table. In our env KC is listening on http://0.0.0.0:8080
Did you try to specify a default value for the bind address? I.e. '${jboss.bind.address:127.0.0.1}' That way it should never try to insert null for bind_addr
Thanks for the suggestions!!
We are using Keycloak 19 on Quarkus framework and not the jboss so that variable doesnt apply for us. For now we have omitted bind_addr to be inserted to JGROUPSPING table and testing the cluster communication.
@anowak-ct
I’m using kc18, and I have the same problem
I changed the sql using this variable ${env.JGROUPS_DISCOVERY_EXTERNAL_IP}
... values (?,'${env.JGROUPS_DISCOVERY_EXTERNAL_IP: 127.0.0.1}',NOW()....
and the column is now populated with the IP of the first node that starts (for each record). I expected the IP of the nodes participating to the cluster. Is the coordinator nodes that writes all the records?
@hxb5422 @anowak-ct
Hi, We have the same problem. We are using Keycloak 19.0.1 on Quarkus framework with the same configuration that @anowak-ct had mentioned and facing the same problem for both bind_addr and created attributes
ERROR: column “bind_addr” of relation “jgroupsping” does not exist
ERROR: column “created” of relation “jgroupsping” does not exist
I was wondering if you have any suggestions?
Note that we are on the local system, not on docker or kubernetes.
Do the columns exist? Do a show create table jgroupsping; (mysql) or /d jgroupsping (pgsql) and see if they got created.
@xgp Sorry I had created a jgroupsping before and had conflicts with that. Thanks so much for your notice.
Now I am facing another error 
:
Uncaught exception received by Vert.x: java.lang.IllegalStateException: Result is already complete
Do you have any idea what this error is caused by?
There are a lot of things that can cause that. Why don’t you open another thread and give the full context of what is happening? Include how to reproduce the issue and your configurations.
i got the same bug also, but the cluster is kinda working, both got same db, but need to refresh the GUI
Hi Alex,
I need your help.
I’m running two keycloak containers (version-19) in docker with a common DB.
keycloak1 and postgres running in an EC2 instance
keycloak2 (another instance) running in an EC2 instance in a different region. Ports are open between these two instances and I’m using the custom stack for jdbc-ping to discover the KC instances, but its not discovered. I can confirm both the EC2 instances can talk to each other and all the containers can talk to each other.
I have also tried adding the public IP of the EC2 instances in the stack like you and I still couldnt get that working. I could not see the JGROUPSPING table created.
> <TCP external_addr="*****" />
>
>
> <jgroups>
> <stack name="jdbc-ping-tcp" extends="tcp">
> <JDBC_PING connection_driver="org.postgresql.Driver"
> connection_username="${env.KC_DB_USERNAME}"
> connection_password="${env.KC_DB_PASSWORD}"
> connection_url="${env.KC_DB_URL}"
> initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, bind_addr VARCHAR(200) NOT NULL, created timestamp NOT NULL, cluster_name varchar(200) NOT NULL, ping_data BYTEA, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name));"
> insert_single_sql="INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,'${jboss.bind.address:127.0.0.1}',NOW(), ?, ?);"
> delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
> select_all_pingdata_sql="SELECT ping_data FROM JGROUPSPING WHERE cluster_name=?;"
> info_writer_sleep_time="500"
> remove_all_data_on_view_change="true"
> stack.combine="REPLACE"
> stack.position="MPING" />
> </stack>
> </jgroups>
This is what i see, could you help me to resolve this.
keycloak-jdbc | 2022-10-26 11:39:46,526 INFO [org.infinispan.CLUSTER] (keycloak-cache-init) ISPN000088: Unable to use any JGroups configuration mechanisms provided in properties {}. Using default JGroups configuration!
keycloak-jdbc | 2022-10-26 11:39:48,652 INFO [org.jgroups.protocols.pbcast.GMS] (keycloak-cache-init) 6e3721a7dbd5-18224: no members discovered after 2008 ms: creating cluster as coordinator`Preformatted text`
Hi Alex,
do you have the ref to the jdbc ping stack in the <cache-container ...> section of your infispan config file? See my earlier post in this thread…
I don’t think having KC pods in different EC2 regions should matter as long as they can see each other.
Yes, here is my infinispan cache file. Please help me resolve this. I think the problem is the database table "JGROUPSPING" is not created. Do i need to change the configuration?
"
<?xml version="1.0" encoding="UTF-8"?>
<infinispan
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:infinispan:config:11.0 http://www.infinispan.org/schemas/infinispan-config-11.0.xsd"
xmlns="urn:infinispan:config:11.0">
<jgroups>
<stack name="jdbc-ping-tcp" extends="tcp">
<TCP external_addr="${env.JGROUPS_DISCOVERY_EXTERNAL_IP}" bind_port="7600" />
<JDBC_PING connection_driver="org.postgresql.Driver"
connection_username="${env.KC_DB_USERNAME}" connection_password="${env.KC_DB_PASSWORD}"
connection_url="${env.KC_DB_URL}"
initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, bind_addr VARCHAR(200) NOT NULL, created timestamp NOT NULL, cluster_name varchar(200) NOT NULL, ping_data BYTEA, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name));"
insert_single_sql="INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,'${env.JGROUPS_DISCOVERY_EXTERNAL_IP:127.0.0.1}',NOW(), ?, ?);"
delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
select_all_pingdata_sql="SELECT ping_data FROM JGROUPSPING WHERE cluster_name=?;"
info_writer_sleep_time="500"
remove_all_data_on_view_change="true"
stack.combine="REPLACE"
stack.position="MPING" />
</stack>
</jgroups>
<cache-container name="keycloak">
<!-- custom stack must be referenced by name in the stack attribute of the transport element -->
<transport lock-timeout="60000" stack="jdbc-ping-tcp"/>
<local-cache name="realms">
<encoding>
<key media-type="application/x-java-object"/>
<value media-type="application/x-java-object"/>
</encoding>
<memory max-count="10000"/>
</local-cache>
<local-cache name="users">
<encoding>
<key media-type="application/x-java-object"/>
<value media-type="application/x-java-object"/>
</encoding>
<memory max-count="10000"/>
</local-cache>
<distributed-cache name="sessions" owners="2">
<expiration lifespan="-1"/>
</distributed-cache>
<distributed-cache name="authenticationSessions" owners="2">
<expiration lifespan="-1"/>
</distributed-cache>
<distributed-cache name="offlineSessions" owners="2">
<expiration lifespan="-1"/>
"
Hmm, maybe your jdbc-ping-tcp stack cannot be found by infinispan …
Are you sure the custom config file is being sourced as expected? I enabled distributed caches in Dockerfile like this
ENV KC_CACHE_CONFIG_FILE=cache-ispn-jdbc-ping.xml
ADD config/cache-ispn-jdbc-ping.xml /opt/keycloak/conf/cache-ispn-jdbc-ping.xml
RUN rm -f /opt/keycloak/conf/cache-ispn.xml
Secondly, I don’t have the <TCP> element in my <stack>, just <JDCP_PING>. <TCP> or <TCPPING> does not work in k8s/ec2
I saw the exact same log line
ISPN000088: Unable to use any JGroups configuration mechanisms provided in properties {}. Using default JGroups configuration!
but that got fixed once I included the ref to the jdbc-ping-tcp section as I pointed out earlier.
I have that added in the configuration file. I have the configuration file mentioned in the docker-compose file.
<cache-container name="keycloak">
<!-- custom stack must be referenced by name in the stack attribute of the transport element -->
<transport lock-timeout="60000" stack="jdbc-ping-tcp"/>
<local-cache name="realms">
<encoding>
<key media-type="application/x-java-object"/>
<value media-type="application/x-java-object"/>
</encoding>
<memory max-count="10000"/>
</local-cache>
<local-cache name="users">
<encoding>
<key media-type="application/x-java-object"/>
<value media-type="application/x-java-object"/>
</encoding>
<memory max-count="10000"/>
</local-cache>
<distributed-cache name="sessions" owners="2">
<expiration lifespan="-1"/>
</distributed-cache>
<distributed-cache name="authenticationSessions" owners="2">
<expiration lifespan="-1"/>
keycloak:
image: 'keycloakjdbc:production'
container_name: keycloak-jdbc
environment:
- KEYCLOAK_ADMIN=admin
- KEYCLOAK_ADMIN_PASSWORD=admin
- KC_HOSTNAME_STRICT=false
- KC_HOSTNAME=docker1.stdev.se
- KC_CACHE_CONFIG_FILE=/opt/keycloak/conf/cache-ispn-jdbc-ping.xml
#- KC_HTTPS_KEY_STORE_FILE=/keycloak/server.keystore
- KC_HTTPS_KEY_STORE_PASSWORD=secret
networks:
- keycloakjdbc
volumes:
- ./cache-ispn-jdbc-ping.xml:/opt/keycloak/conf/cache-ispn-jdbc-ping.xml
- keycloakjdbc:/opt/keycloak
ports:
- "8443:8443"
command:
- start --optimized
I can see its picking the correct configuration file, this is from the container's terminal.
Changes detected in configuration. Updating the server image.
Current Mode: production
Current Configuration:
kc.cache = ispn (PersistedConfigSource)
kc.cache-config-file = /opt/keycloak/conf/cache-ispn-jdbc-ping.xml (KcEnvVarConfigSource)
kc.cache.config.file = /opt/keycloak/conf/cache-ispn-jdbc-ping.xml (KcEnvVarConfigSource)
kc.config.build-and-exit = true (SysPropConfigSource)
kc.db = postgres (KcEnvVarConfigSource)
Dear @anowak-ct ,
I have 2 Keycloak instances in 2 different systems with the addresses 192.168.139.1 and 192.168.139.128 and my Postgresql is on 192.168.139.1.
My cache-ispn-jdbc-ping.xml is mostly as same as you:
cache-ispn-jdbc-ping.xml on 192.168.139.1:
<stack name="jdbc-ping-tcp" extends="tcp">
<JDBC_PING connection_driver="org.postgresql.Driver"
connection_username="keycloak" connection_password="password"
connection_url="jdbc:postgresql://192.168.139.1:5432/keycloak"
initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, bind_addr VARCHAR(200) NOT NULL, created timestamp NOT NULL, cluster_name varchar(200) NOT NULL, ping_data BYTEA, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name));"
insert_single_sql="INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,'192.168.139.1',NOW(), ?, ?);"
delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
select_all_pingdata_sql="SELECT ping_data FROM JGROUPSPING WHERE cluster_name=?;"
info_writer_sleep_time="500"
remove_all_data_on_view_change="true"
stack.combine="REPLACE"
stack.position="MPING" />
</stack>
and similarly cache-ispn-jdbc-ping.xml on 192.168.139.128 (just a change in insert_single_sql):
insert_single_sql="INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,'192.168.139.128',NOW(), ?, ?);"
When I deploy only one server (192.168.139.1) , the JGROUPSPING table is like below:
own_addr | bind_addr | created | cluster_name | ping_data
-----------------------------------------------------------------------------------------------------
8d7bdcc3-... | 192.168.139.1 | 2022-11-08 11:06:17.221554 | ISPN | \x02bd1774fb9c1...
And when I deploy both servers, the JGROUPSPING is:
own_addr | bind_addr | created | cluster_name | ping_data
-----------------------------------------------------------------------------------------------------
8d7bdcc3-... | 192.168.139.1 | 2022-11-08 11:17:36.616217 | ISPN | \x02bd1774fb9c1...
52228e48-... | 192.168.139.1 | 2022-11-08 11:17:36.644353 | ISPN | \x029e724c237fe...
(2 rows)
I was wondering why both bind_addr are the same while I expect one to be 192.168.139.128. Plus, as you see, the created column of the first record changes after adding the second server.
Thanks in advance.