Today I updated an older app (backupmon), compiled it with latest WebDSL.
There was a running instance on the server, which was built in 2016. This version had h2-1.3.158.jar.
The newer build has h2-1.4.199.jar.

After deploying the new version, the servlet worked correctly. However, after restarting tomcat, the servlet failed to connect to the h2 file database with the following error:

ERROR JDBCExceptionReporter:234 - Connections could not be acquired from the underlying database!
...
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:
ALTER TABLE "PUBLIC"."SESSIONMANAGER_MESSAGES_SESSIONMESSAGE" ADD CONSTRAINT "PUBLIC"."FKE4D3441C7D868B21" FOREIGN KEY("_SESSIONMANAGER_ID") INDEX "PUBLIC"."FKE4D3441C7D868B21_INDEX_8" REFERENCES "PUBLIC"."_SESSIONMANAGER"("ID") NOCHECK [23505-199]

I found some posts about the same issue:
- https://github.com/h2database/h2database/issues/1073
- http://shammijayasinghe.blogspot.com/2020/01/unique-index-or-primary-key-violation.html

To fix it, I eventually needed to connect to the file db with a specific version (I think it was 1.4.196).
Web console can be started from the h2 jar:

java -cp h2-1.4.196.jar org.h2.tools.Console

Then, create a SQL dump:

• open tools > Script
• use the Source Database URL with ;mv_store=false at the end.
  E.g., jdbc:h2:~/dumps/backupmon/backup-mon.db;mv_store=false
• no username/pw; I used default target script location

This succeeded, after which I connected with h2 1.4.199 to restore the backed up db:

java -cp h2-1.4.199.jar org.h2.tools.Console

• open tools > RunScript
• create a new empty folder, and enter the location with the same name as the original db in Target database URL
  E.g., jdbc:h2:~/newfolder/backup-mon.db
• Enter source script location, no username/pw;
• Hit Run

The created h2 file database should now reside in the newly created folder. It is named backup-mon.db.mv.db in this case.

I was then able to copy the recovered database file to the server again; removed the corrupted h2 file_s_ and replaced them with the new h2 file (it was named differently than the original files, but H2 will find the new file with the same connection URL).
After restarting the servlet, the web app successfully started and had all previous data.

Submitted by Elmer van Chastelet on 21 July 2023 at 11:24