Spring Boot 2.0, H2, Unable to start app on existing H2 DB file

Question

I am in the process of migrating my application from Spring Boot 1.4.x to 2.0.0.

In our development environment we run an H2 on file db, as shown in the debug log below. I can run the app with ddl-auto: create with an in MEM db, but when trying to work against my existing H2 file, I get the following error:

14:59:21.440 [] [] [main] DEBUG org.hibernate.loader.collection.plan.CollectionLoader - Static select for collection com.lmig.cortex.domain.entities.EmrProvisioning.associatedBuckets: select associated0_.emr_provisioning_id as emr_prov1_26_0_, associated0_.bucket_name as bucket_n2_26_0_, associated0_.is_ro as is_ro3_26_0_ from emr_associated_buckets associated0_ where associated0_.emr_provisioning_id=?
14:59:21.502 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - HikariPool-1 - configuration:
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - allowPoolSuspension.............false
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - autoCommit......................true
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - catalog.........................none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionInitSql...............none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTestQuery.............none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - connectionTimeout...............30000
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSource......................none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceClassName.............none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceJNDI..................none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - dataSourceProperties............{password=<masked>}
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - driverClassName................."org.h2.Driver"
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckProperties...........{}
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - healthCheckRegistry.............none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - idleTimeout.....................600000
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailFast..........true
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - initializationFailTimeout.......1
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - isolateInternalQueries..........false
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbc4ConnectionTest.............false
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - jdbcUrl.........................jdbc:h2:file:~/API;FILE_LOCK=FS;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - leakDetectionThreshold..........0
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - maxLifetime.....................1800000
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - maximumPoolSize.................10
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - metricRegistry..................none
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - metricsTrackerFactory...........com.zaxxer.hikari.metrics.micrometer.MicrometerMetricsTrackerFactory@1da5c19d
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - minimumIdle.....................10
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - password........................<masked>
14:59:21.503 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - poolName........................"HikariPool-1"
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - readOnly........................false
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - registerMbeans..................false
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutor...............none
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - scheduledExecutorService........internal
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - schema..........................none
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - threadFactory...................internal
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - transactionIsolation............default
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - username........................"sa"
14:59:21.504 [] [] [main] DEBUG com.zaxxer.hikari.HikariConfig - validationTimeout...............5000
14:59:21.504 [] [] [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
14:59:21.530 [] [] [main] DEBUG com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to create/setup connection: Constraint "FK5FKLEKY5P9R43SW8TI8CRLYXG" already exists; SQL statement:

Here is how my config looks that fails:

datasource:
    url: jdbc:h2:file:~/API;FILE_LOCK=FS;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL
    driver-class-name: org.h2.Driver
    username: sa
    password:
    continue-on-error: true
jpa:
    database-platform: H2
    hibernate:
      ddl-auto: update
h2:
    console:
      enabled: true

Additional Info:

I can delete my h2 DB files, run the app, which generates new DB files. Everything is good and I can re-run the app without problem. I delete these new files, move the old files back. Same problem. Is there a place in these files where metadata about actions/updates that have been performed? Maybe I could tweak it?

score 1 · Accepted Answer · answered Mar 24 '18 at 00:24

1

From Spring Boot 1.4.7 to 2.0.0, H2 upgrades from 1.4.196 to 1.4.197.

It's difficult to determine which one, without having your h2 files, but one of the item in the long changelog list from 1.4.196 to 1.4.197 is the cause of your problem.

If you want to keep your data, one solution would be to export them as SQL statements (see How in H2DB get sql dump like in MySql?) and reimport them with H2 1.4.197.

answered Mar 24 '18 at 00:24

Ortomala Lokni

56,620
24
188
240

I had tried to remove my 1.4.197 dependency, and explicitly set my H2 Dependency to the older version. With no luck. I do have an SQL dump, so I will probably look to migrate it over. – abarraford Mar 25 '18 at 16:42
I was able to get around this error by exporting the OLD db file to SQL with SCRIPT TO command. Then, re-creating the db files from the sql script in an h2 terminal. Then, I pointed my app datasource url to the newly created and populated db file. The app started without the above error. But, when attempting to pull data from the DB, I now get unable to parse result set errors, seems to be around the way LocalDate and LocalDateTime where being stored previously. Hopefully this does not extend to Postgres, I will be testing furture... – abarraford Mar 26 '18 at 15:22
I am going to mark this answer correct, as it is indeed a migration issues. The above comment got my app started, pointing at the old DB. – abarraford Mar 26 '18 at 16:55

Spring Boot 2.0, H2, Unable to start app on existing H2 DB file

1 Answers1