Table is not getting created in DB

[Achyuth-India]

Hi, I'm using postgres db and trying to implement JDBC_PING.cli. I followed exact steps given in README.md.

I checked JDBC_PING.cli is under discovery folder. And added the below environment variables as instructed.

• JGROUPS_DISCOVERY_EXTERNAL_IP=
• JGROUPS_DISCOVERY_PROTOCOL=JDBC_PING

When running keycloak I'm getting the below error. And when I check DB the Table itself is not got created.

  Position: 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
    at org.jgroups.protocols.JDBC_PING.delete(JDBC_PING.java:339)
    at org.jgroups.protocols.JDBC_PING.writeToDB(JDBC_PING.java:142)
    at org.jgroups.protocols.JDBC_PING.write(JDBC_PING.java:125)
    at org.jgroups.protocols.FILE_PING.findMembers(FILE_PING.java:122)
    at org.jgroups.protocols.Discovery.findMembers(Discovery.java:213)
    at org.jgroups.protocols.Discovery.down(Discovery.java:359)
    at org.jgroups.protocols.FILE_PING.down(FILE_PING.java:113)
    at org.jgroups.protocols.MERGE3$InfoSender.run(MERGE3.java:408)
    at org.jgroups.util.TimeScheduler3$Task.run(TimeScheduler3.java:324)
    at org.jgroups.util.TimeScheduler3$RecurringTask.run(TimeScheduler3.java:358)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.jboss.as.clustering.jgroups.ClassLoaderThreadFactory.lambda$newThread$0(ClassLoaderThreadFactory.java:52)
    at java.lang.Thread.run(Thread.java:748)```

Note: I'm using Keycloak version 5.0.0. Any help/fix would be really appreciated.

Thanks in Advance

[zhangliqiang]

Hi, There is a CREATE TABLE sql in the jdbc_ping.cli file of this repo, the sql works well for mysql. Maybe you have to modify the sql accordingly for postgres.

[zhangliqiang]

By the way, if you are not using docker you have to execute the cli manually.

[Achyuth-India]

Yes I'm using Docker. May be I will try modifying query for postgres and update you.

Thanks for the reply @zhangliqiang

[Achyuth-India]

@zhangliqiang I tried editing query to suit POSTGRES, but still no luck. Here is my updated query.

/subsystem=jgroups/stack=tcp/protocol=JDBC_PING/property=initialize_sql:add(value="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL PRIMARY KEY, cluster_name varchar(200) NOT NULL PRIMARY KEY, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ping_data bytea DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8")

Please let me know if I am missing something.

[liqiang-fit2cloud]

Seems that your given sql can't create table in postgres, I think you can try the sql below: https://github.com/moremagic/keycloak-jdbc-ping/blob/master/cli/jgroups.cli

[RickS-C137]

We have the same problem, BUT not on all environments. Our installation is done via Ansible, so it's the same in both environments (dev, prod). The dev cluster was setup without any problems and the JDBC_PING table has been created on the first start of the master node. In prod the table has NOT been created. The only log message regarding this is:

ERROR [org.jgroups.protocols.JDBC_PING] (ServerService Thread Pool -- 60) JGRP000138: Error reading JDBC_PING table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'keycloak-ha.JGROUPSPING' doesn't exist

        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2313)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
        at org.jgroups.protocols.JDBC_PING.readAll(JDBC_PING.java:225)
        at org.jgroups.protocols.JDBC_PING.readAll(JDBC_PING.java:197)
        at org.jgroups.protocols.FILE_PING.findMembers(FILE_PING.java:124)
        at org.jgroups.protocols.Discovery.invokeFindMembers(Discovery.java:214)
        at org.jgroups.protocols.Discovery.findMembers(Discovery.java:239)
        at org.jgroups.protocols.Discovery.down(Discovery.java:379)
        at org.jgroups.protocols.FILE_PING.down(FILE_PING.java:119)
        at org.jgroups.protocols.MERGE3.down(MERGE3.java:273)
        at org.jgroups.protocols.FD_SOCK.down(FD_SOCK.java:376)
        at org.jgroups.protocols.FD_ALL.down(FD_ALL.java:236)
        at org.jgroups.protocols.VERIFY_SUSPECT.down(VERIFY_SUSPECT.java:101)
        at org.jgroups.protocols.pbcast.NAKACK2.down(NAKACK2.java:553)
        at org.jgroups.protocols.UNICAST3.down(UNICAST3.java:570)
        at org.jgroups.protocols.pbcast.STABLE.down(STABLE.java:346)
        at org.jgroups.protocols.pbcast.ClientGmsImpl.joinInternal(ClientGmsImpl.java:72)
        at org.jgroups.protocols.pbcast.ClientGmsImpl.join(ClientGmsImpl.java:40)
        at org.jgroups.protocols.pbcast.GMS.down(GMS.java:1068)
        at org.jgroups.protocols.FORK.down(FORK.java:109)
        at org.jgroups.stack.ProtocolStack.down(ProtocolStack.java:908)
        at org.jgroups.JChannel.down(JChannel.java:668)
        at org.jgroups.JChannel._connect(JChannel.java:896)
        at org.jgroups.JChannel.connect(JChannel.java:393)
        at org.jgroups.JChannel.connect(JChannel.java:384)
        at org.jboss.as.clustering.jgroups.subsystem.ChannelServiceConfigurator.get(ChannelServiceConfigurator.java:112)
        at org.jboss.as.clustering.jgroups.subsystem.ChannelServiceConfigurator.get(ChannelServiceConfigurator.java:58)
        at org.wildfly.clustering.service.FunctionalService.start(FunctionalService.java:67)
        at org.wildfly.clustering.service.AsyncServiceConfigurator$AsyncService.lambda$start$0(AsyncServiceConfigurator.java:117)
        at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
        at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1982)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
        at java.lang.Thread.run(Thread.java:748)
        at org.jboss.threads.JBossThread.run(JBossThread.java:485)

In the standalone-ha.xml we inserted this:

<transport type="TCP" socket-binding="jgroups-tcp"/>
  <protocol type="FRAG3"/>
    <jdbc-protocol type="JDBC_PING" data-source="KeycloakDS">
      <property name="initialize_sql">
        "CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, cluster_name varchar(200) NOT NULL, updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ping_data varbinary(5000) DEFAULT NULL, PRIMARY KEY (own_addr, cluster_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin"
        </property>
      </jdbc-protocol>
  <protocol type="MERGE3"/>
  <protocol type="FD_SOCK"/>
  <protocol type="FD_ALL"/>
  <protocol type="VERIFY_SUSPECT"/>
  <protocol type="pbcast.NAKACK2"/>
  <protocol type="UNICAST3"/>
  <protocol type="pbcast.STABLE"/>
  <protocol type="pbcast.GMS"> <property name="join_timeout"> 30000 </property> <property name="print_local_addr"> true </property> <property name="print_physical_addrs"> true </property> </protocol>
</stack>

So the table should be created if it doesn't exist and, as mentioned above, it worked in our dev environment.

[liqiang-fit2cloud]

@RickS-C137 Not sure why it doesn't work, maybe you can create the table manually to check whether the sql and database are good.

[egibert]

For anyone having the same problem, I fixed it by using the CLI files from this repo: https://github.com/ivangfr/keycloak-clustered

[gchamon]

I was having problems and found out the CREATE TABLE statement for the JGROUPSPING table doesn't really work on mysql 5.6, because of variable size contraints, but works fine with 5.7

[liqiang-fit2cloud]

I was having problems and found out the CREATE TABLE statement for the JGROUPSPING table doesn't really work on mysql 5.6, because of variable size contraints, but works fine with 5.7

We haven't tried with mysql 5.6, you can modify the cli file accordingly.

[gchamon]

I was having problems and found out the CREATE TABLE statement for the JGROUPSPING table doesn't really work on mysql 5.6, because of variable size contraints, but works fine with 5.7

We haven't tried with mysql 5.6, you can modify the cli file accordingly.

I was implementing on AWS and using mysql 5.6 because that is the version used in other products. Just wanted to register this experience here, maybe others are using this version of mysql and could benefit