Keycloak 10.0.2 - doesn't start locally twice

I migrated my local installation on WIndows (using h2 database), from keycloak 9.0.0 to Keycloak 10.0.2

I did as migration guide suggest: copy /standalone folder from old to new sever, run migration script, start server - database will migrate. Server starts succesfully.

When I stop it and try to start again I get errors:

11:35:10,796 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (ServerService Thread Pool – 60) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: IJ031084: Unable to create connection

Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: “PRIMARY KEY ON “””".PAGE_INDEX"
Unique index or primary key violation: “PRIMARY KEY ON “””".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.USER_FEDERATION_CONFIG ADD CONSTRAINT PUBLIC.FK_T13HPU1J94R2EBPEKR39X5EU5 FOREIGN KEY(USER_FEDERATION_PROVIDER_ID) REFERENCES PUBLIC.USER_FEDERATION_PROVIDER(ID) NOCHECK [23505-197]

When I remove standalone, copy again from old version - it starts once, and then the same.

Came across the same problem when trying to upgrade 3.1.0.Final to 11.0.2. Found out this was caused by https://github.com/h2database/h2database/issues/1247.

As a workaround, I dumped & restored the keycloak H2 datasource file before starting the new version:

Given:

  • Old KeyCloak is installed in keycloak-3.1.0.Final
  • New KeyCloak is installed in keycloak-11.0.2
  • Running standalone setup
  • Datasource is configured with below connection URL

<connection-url> jdbc:h2:${jboss.server.data.dir}/keycloak;AUTO_SERVER=TRUE </connection-url>

Dump old sql:

java -cp keycloak-3.1.0.Final/modules/system/layers/base/com/h2database/h2/main/h2-1.3.173.jar org.h2.tools.Script -url jdbc:h2:keycloak-3.1.0.Final/standalone/data/keycloak -user sa -password MYPASS -script ~/keycloak-testbackup.sql

Restore to new H2 file (make sure file doesn’t exists prior to running the restore):

java -cp keycloak-11.0.2/modules/system/layers/base/com/h2database/h2/main/h2-1.4.197.jar org.h2.tools.RunScript -url jdbc:h2:keycloak-11.0.2/standalone/data/keycloak -user sa -password MYPASS -script ~/keycloak-testbackup.sql

Now upon starting the application server (keycloak-11.0.2) for the first time, the schema is migrated to the latest version, and remains running properly at each restart.

2 Likes