Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,360,278
Community Members
 
Community Events
168
Community Groups

Fatal database issue when upgrading to Confluence 7.11

Edited

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.

2 comments

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.

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?

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"

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. 

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"

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

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.

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.

Comment

Log in or Sign up to comment
TAGS
Community showcase
Published in Confluence

Watch 4 Confluence apps compete for Best App Demo in September's Appy Hours

Calling all collaborators! Appy Hours on the Atlassian Community is a monthly event where 4 Partner and app vendor presenters go head-to-head with 5-minute demos for the title of Best App Demo. I...

528 views 0 12
Read article

Atlassian Community Events