Keycloak Database creation - keycloak-database-update.sql

Hi,

I have created a database using the configuration file. It specifies the file name as keycloak-database-update.sql. Is there a way I could get this file ? It would be easier for me to just execute the script rather than providing a specific application user, the rights to create objects in the database.
Other option I have is to manually generate the scripts for database creation. So I need the keycloak-database-update.sql file.

Any suggestions are really appreciated.

For anyone, who might be looking for the same and wants to execute the script manually, this might be helpful so I decided to post the solution.
In the connectionsJpa, set the below :

  1. “initializeEmpty” to “false”
  2. “migrationStrategy” to “manual”
  3. “migrationExport” to the path you want the script to be generated.
1 Like

Looking for exactly the same! Thank you for sharing.

Why is this not mentioned at all on the database upgrade information?

Well, it is not mentioned in any document but I think it is something that needs to be included. I had to try out a few things and even go through some code as I was unable to generate the sql file initially. I had to spend some time on it but I am glad the solution works for you.

Sadly, I’ve been struggling through the same issue with no resolution in site. Thanks to your excellent answer, I at least have the keycloak-database-update.sql file being generated now, but when I try to run it against MySQL, I get the following error:

SQL query:


ALTER TABLE keycloak.USER_ENTITY ADD CONSTRAINT UK_RU8TT6T700S9V50BU18WS5HA6 UNIQUE (REALM_ID, USERNAME)
MySQL said: Documentation

#1071 - Specified key was too long; max key length is 1000 bytes

And I have absolutely NO idea how to fix that one. If anyone has seen this, and found a solution, please do let me know!

Best Regards,
dg

If I change from initializeEmpt=false to true then a database initialization is attempted, but it fails with:

09:23:52,548 INFO  [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 57) Updating database.
 Using changelog META-INF/jpa-changelog-master.xml
09:23:52,809 ERROR [org.keycloak.connections.jpa.updater.liquibase.conn.DefaultLiquibaseConnectionProvider] (ServerService Thread Pool -- 57) Chang
e Set META-INF/jpa-changelog-2.5.0.xml::2.5.0-unicode-other-dbs::hmlnarik@redhat.com failed.  Error: Can't DROP 'UK_RU8TT6T700S9V50BU18WS5HA6'; check that
 column/key exists [Failed SQL: ALTER TABLE keycloak.USER_ENTITY DROP KEY UK_RU8TT6T700S9V50BU18WS5HA6]
09:23:52,809 ERROR [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool -- 57) Error has occurre
d while updating the database: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-2.5.0.xml::2.5.0-unico
de-other-dbs::hmlnarik@redhat.com:
     Reason: liquibase.exception.DatabaseException: Can't DROP 'UK_RU8TT6T700S9V50BU18WS5HA6'; check that column/key exists [Failed SQL: ALTER TABLE keycl
oak.USER_ENTITY DROP KEY UK_RU8TT6T700S9V50BU18WS5HA6]

I really am at a loss here.