Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Confluence Mythbusters: Does Atlassian even use Confluence?

Hi, Confluence collaborators! As part of #Confluence-Collaboratory month, we’ve created a very special Mythsbusters segment, where we're dive into an interesting myth and uncover the truth behind i...

1,711 views 7 32
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you