Keycloak Database creation - keycloak-database-update.sql

, , , ,
1

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.

2

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
3

Looking for exactly the same! Thank you for sharing.

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

4

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.

5

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

6

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.