Fatal database issue when upgrading to Confluence 7.11

[Metin Savignano]

Hi,

We were trying to test upgrading our test servers to Confluence 7.11, but the in-place upgrade fails miserably giving us 

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.MIG_ATTACHMENT ADD CONSTRAINT PUBLIC.MIG_ATTACHMENT_SITE_FK FOREIGN KEY(CLOUDID) REFERENCES PUBLIC.MIG_CLOUD_SITE(CLOUDID) NOCHECK [23505-200]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
	at org.h2.message.DbException.get(DbException.java:205)
	at org.h2.message.DbException.get(DbException.java:181)
	at org.h2.pagestore.db.PageDataIndex.add(PageDataIndex.java:125)
	at org.h2.pagestore.PageStore.addMeta(PageStore.java:1804)
	at org.h2.pagestore.db.PageBtreeIndex.<init>(PageBtreeIndex.java:65)
	at org.h2.pagestore.db.PageStoreTable.addIndex(PageStoreTable.java:183)
	at org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.java:298)
	at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:223)
	at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:78)
	at org.h2.engine.MetaRecord.execute(MetaRecord.java:60)
	at org.h2.engine.Database.open(Database.java:759)
	at org.h2.engine.Database.openDatabase(Database.java:307)
	at org.h2.engine.Database.<init>(Database.java:301)
	at org.h2.engine.Engine.openSession(Engine.java:74)
	at org.h2.engine.Engine.openSession(Engine.java:192)
	at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)
	at org.h2.engine.Engine.createSession(Engine.java:166)
	at org.h2.server.TcpServerThread.run(TcpServerThread.java:168)
	at java.base/java.lang.Thread.run(Thread.java:834)

Has anyone seen this happen? How can we fix it?

It's 100% reproducible.

[Nic Brough -Adaptavist-]

Yeah, I've seen this a couple of times.  The alterations the upgrade needs to make to the database are not handled well by h2 or Confluence, and simply fail. 

You shouldn't be trying to test an upgrade on a test system using a different database to production, you're wasting your time.  You should move your test servers to use the same type of database as production and test the upgrade against that.

[Metin Savignano]

We're using the standard Atlassian docker image to run our automated tests, pre-configuring them with our test data. This has been working well for many releases.

It would require a lot of extra work to move to another database. :-(

However, I wonder why a 

Unique index or primary key violation

would be any different on another database?

[Nic Brough -Adaptavist-]

It's probably that the database has partially or entirely failed, not that there is anything wrong with the actual call to do the update.

As h2 is not a supported database, I'm afraid there's not a lot we can do - the first reaction to an h2 failure is always going to be "try it with a supported database"

[Metin Savignano]

Solved

While updating our test infrastructure, we stumbled upon the cause of this problem and managed to solve it, so we can continue to reuse our test database with Confluence 7.11 and later. Which is useful because it allows to manage only one test database for tests on all versions.

I'd like to share the solution in case someone else finds it useful.

The same problem and solution applies to Jira 8.18 and later, by the way.

Problem cause

In Confluence 7.11 and Jira 8.18, Atlassian has updated the H2 database driver to version 1.4.200 which has a known issue with the in-place upgrade of a database that has been created using an earlier version.

Solution

The recommended and officially supported solution is to migrate the H2 database by exporting and restoring it. Note that you must use the old H2 version for the export, and the new version for the restore.

To export, using the old installation of Jira or Confluence (or at least the old h2-*.jar)!

for Jira

java -cp /opt/atlassian/jira/atlassian-jira/WEB-INF/lib/h2-*.jar org.h2.tools.Script -url jdbc:h2:file:/var/atlassian/application-data/jira/database/h2db -user sa -script ~/h2db-export.zip -options compression zip

for Confluence

java -cp /opt/atlassian/confluence/confluence/WEB-INF/lib/h2-*.jar org.h2.tools.Script -url jdbc:h2:file:/var/atlassian/application-data/confluence/database/h2db -user sa -script ~/h2db-export.zip -options compression zip

This will create a compressed export file ~/h2db-export.zip containing sql statements for the database restore. 

To restore, use the new installation (or at least the new driver version)!

for Jira

java -cp /opt/atlassian/jira/atlassian-jira/WEB-INF/lib/h2-*.jar org.h2.tools.RunScript -url jdbc:h2:file:/var/atlassian/application-data/jira/database/h2db -user sa -script ~/h2db-export.zip -options compression zip

 for Confluence

java -cp /opt/atlassian/confluence/confluence/WEB-INF/lib/h2-*.jar org.h2.tools.RunScript -url jdbc:h2:file:/var/atlassian/application-data/confluence/database/h2db -user sa -script ~/h2db-export.zip -options compression zip

After that, the database has been migrated for use with the new driver version. 

[Nic Brough -Adaptavist-]

This is not quote right.  While the technical fix is right, you say "The recommended and officially supported solution is to migrate the H2 database by exporting and restoring it."

Please do not mislead people here - H2 is not "recommended" and it is certainly not "officially supported"

[Metin Savignano]

Sorry for the misunderstanding. I was referring to the recommendation of the H2 database people, not Atlassian‘s. 

[Nic Brough -Adaptavist-]

No need to be sorry (you've not said anything misleading, if anything, an apology should land on my side because I did not explain it as well as I should) 

There is no recommendation for H2, we're all told to use a real database as the storage.  H2 is for demo/dev where we don't care if we lose it all tomorrow.

[Metin Savignano]

All fine and understood, but even though we only use it for our automated tests, we find it useful to have a way to reuse the same database with all Atlassian releases.

Of course, this doesn't mean at all that H2 could be used for production.