You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
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.
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"
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.
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.
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)!
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
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)!
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
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.