Can't use Firebird database. Please guide me how to implement it?

Configuring the server
1

Hi, guys, Keycloak works well while I am using H2 database. But I want to switch to using Firebird as database.

So, I have the following path D:\Keycloak\modules\system\layers\keycloak\org\firebirdsql\main, where is jaybird-3.0.9.jar and its module.xml

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.3" name="org.firebirdsql">
  <resources>
    <resource-root path="jaybird-3.0.9.jar"/>
  </resources>
  <dependencies>
    <module name="javax.api"/>
    <module name="javax.transaction.api"/>
    <module name="javax.resource.api"/>
  </dependencies>
</module>

So, I’ve changed standalone.xml:

        <datasources>
            <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
                <driver>h2</driver>
                <security>
                    <user-name>sa</user-name>
                    <password>sa</password>
                </security>
            </datasource>
            <datasource jndi-name="java:jboss/datasources/KeycloakDS" pool-name="KeycloakDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
                <connection-url>jdbc:firebirdsql://localhost:3050/D:\KeycloakDB\KeycloakDB.FDB</connection-url>
                <driver>firebirdsql</driver>
                <security>
                    <user-name>SYSDBA</user-name>
                    <password>masterkey</password>
                </security>
            </datasource>
            <drivers>
                <driver name="h2" module="com.h2database.h2">
                    <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                </driver>
                <driver name="firebirdsql" module="org.firebirdsql">
                    <driver-class>org.firebirdsql.jdbc.FBDriver</driver-class>
                </driver>
            </drivers>
        </datasources>

But this doesn’t work for me. While starting Keycloak, I get many consecutive error messages, starting with this one:

11:54:20,709 FATAL [org.keycloak.services] (ServerService Thread Pool -- 68) Error during startup: java.lang.RuntimeException: Exception invoking method [listUnrunChangeSets] on object [liquibase.Liquibase@5163cec2], using arguments [null,(),false]

The question is what am I doing wrong? Please help me with this. Thank you.

2

Hello Gen
Can you please tell me how you solved this problem?

3

Did not solve this issue yet. Now I’m considering about transition to PostgreSQL server.

4

maybe it will help you

5

You are looking at the wrong error message. Several lines below there is ‘Caused by’ statement, it is more interesting:

Caused by: liquibase.exception.ValidationFailedException: Validation Failed:
     36 changes have validation failures
          renameTable is not supported on firebird, META-INF/jpa-changelog-1.2.0.CR1.xml::1.2.0.RC1::bburke@redhat.com
          renameTable is not supported on firebird, META-INF/jpa-changelog-1.2.0.CR1.xml::1.2.0.RC1::bburke@redhat.com
          renameTable is not supported on firebird, META-INF/jpa-changelog-1.2.0.CR1.xml::1.2.0.RC1::bburke@redhat.com
          ....
          dropNotNullConstraint is not supported on firebird, META-INF/jpa-changelog-1.8.0.xml::1.8.0::mposolda@redhat.com
      ....

Now you know that

liquibase.exception.ValidationFailedException: Validation Failed:

happened because of renameTable and dropNotNullConstraint operations not supported by firebird.
Don’t worry, this can be cured. The best option of course is to add support of these features to the firebird jaybird driver, but we don’t have so much time. Instead, we continue looking at the error message trying to find out where this META-INF/jpa-changelog-1.2.0.CR1.xml file and its friends are located. The second line of the stack trace:

at org.keycloak.keycloak-model-jpa@12.0.4//org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.getLiquibaseUnrunChangeSets(LiquibaseJpaUpdaterProvider.java:285)

leads us to the

Keycloak\modules\system\layers\keycloak\org\keycloak\keycloak-model-jpa\main

directory where

keycloak-model-jpa-12.0.4.jar

lays. Use your favorite zip tool to unzip it and you can see changelog xml files in META-INF directory. If you want to use these files with firebird you must fix all steps not supported by firebird. To rename the table you must create a new table with the correct name, copy data to it and delete the old table. But if you just starting with Keycloak and don’t have an old table with data you can just create a new table with the correct name and that’s it. So you can remove renameTable step and change the step where table is created to create table with the correct name, in the same way, you can declare table fields with correct not null option in the table create statement and remove dropNotNullConstraint steps. After you do all these changes you will be happy to discover that there are more changes not supported by firebird.
It is difficult to track metadata changes through all these changelog files therefore I put all steps in one file leaving only steps that create tables, keys, etc with the correct names. I got the file that updates an empty database to version 11.0.0. jpa-changelog-up-to-11.0.0.xml

Almost forgot - there are columns named VALUE, TIMESTAMP and COUNT, these are reserved words in firebird and must be enquoted to be used as column names. There is a setting to enquote all column names

objectQuotingStrategy=“QUOTE_ALL_OBJECTS”

it must be put to changeSet element of changelog xml file.
Now you must open jpa-changelog-master.xml and replace all <include file lines with versions 11.0.0 and below with a single

<include file=“META-INF/jpa-changelog-up-to-11.0.0.xml”/>

line. jpa-changelog-master.xml
This is what is required for liquibase to have firebird database file successfully updated, but keycloak can not read realm entity from the database yet because of database table column named VALUE.
There is another option that must be set to have column names enquoted. It is in file persistence.xml located in the same META-INF directory, put the property line

<property name=“hibernate.globally_quoted_identifiers” value=“true”/>

below the

<property name=“jboss.as.jpa.managed” value=“false”/>

Sorry, no link to persistence.xml file, only 2 links in a post are allowed for new users.

Now you can zip keycloak-model-jpa-12.0.4.jar back move it to Keycloak\modules\system\layers\keycloak\org\keycloak\keycloak-model-jpa\main directory and be happy.

I’m not sure if jaybird-3.0.9 is good, I tested with jaybird-4.0.1. With older versions, I got 'something is not implemented somewhere.
When creating an empty firebird database set page size to 16384, with smaller pages some keys can not be created.

1 Like