問題

When upgrading Confluence to 5.9.1 from to 5.9.2, the upgrade will fail when attempting to add a constraint to the USER_RELATION table.

For Oracle databases, the following appears in the atlassian-confluence.log

2015-12-08 17:14:57,332 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table USER_RELATION add constraint u2u_relation_unique unique (SOURCEUSER, TARGETUSER, RELATIONNAME)
2015-12-08 17:14:57,333 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-02261: such unique or primary key already exists in the table

For H2 databases, the following appears in the atlassian-confluence.log

2015-12-09 15:01:54,821 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table USER_RELATION add constraint u2u_relation_unique unique (SOURCEUSER, TARGETUSER, RELATIONNAME)
2015-12-09 15:01:54,822 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute a UNIQUE constraint already exists on the set of columns in statement [alter table USER_RELATION add constraint u2u_relation_unique unique (SOURCEUSER, TARGETUSER, RELATIONNAME)]

You may also notice the following error message:

RelationService upgrade failed to prevent data loss

If you see this message, apply Workaround 2 instead of Workaround 1.

診断

This problem only occurs when the database used by Confluence is either:

• Oracle
• H2 組み込みデータベース

Additionally, the problem only occurs when specifically upgrading from 5.9.1 to 5.9.2. If you're moving from a version before 5.9.1, this article does not apply to you.

原因

This is a known bug in Confluence 5.9.2. See  CONF-40174 - Getting issue details... STATUS  for more details. The issue has been resolved in Confluence 5.9.3. In rare circumstances, the tables involved may have data in them (and cannot be dropped by the upgrade task). In this case, or if the first workaround fails, please see Workaround 2 for details. If you're unsure, please contact Atlassian Support for assistance.

Workaround 1 - if you're upgrading to Confluence 5.9.2

If you are seeing the message RelationService upgrade failed to prevent data loss, please proceed to Workaround 2 instead.

1. Restore the Confluence Home Directory and Database to the pre-upgraded state; using any filesystem and database backups you made beforehand
   You won't need to restore the installation directory, as we'll need to start the new version to re-run the upgrade process.
2. Connect to your database using your preferred database tools. If you're using a H2 Database, see Embedded H2 Database for instructions on how to connect to this database

3. Next, you must determine the names of the multicolumn constraints on each table that is involved. This process will vary depending on which database you're using. The H2 Database instructions assume you're using DBVisualiser.

   Oracle

   H2 データベース

   Execute the following SQL commands, and make note of the results: SELECT *  FROM user_cons_columns WHERE table_name = 'USER_RELATION';   SELECT * FROM user_cons_columns WHERE table_name = 'CONTENT_RELATION';   SELECT * FROM user_cons_columns WHERE table_name = 'USERCONTENT_RELATION';

   Execute the following SQL commands, and make note of the results: SELECT SQL FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'USER_RELATION';   SELECT SQL FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'CONTENT_RELATION';   SELECT SQL FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE TABLE_NAME = 'USERCONTENT_RELATION'; Note that because H2 is a flat file database, the results returned will be SQL commands. You'll need the names of those constraints, rather than the SQL to add the constraints

4. The constraints you'll need the names for:
   • On USER_RELATION: A multi column index against the columns SOURCEUSER, TARGETUSER, RELATIONNAME
   • On CONTENT_RELATION: A multi column index against the columns TARGETCONTENTID, SOURCEONTENTID, RELATIONNAME
   • On USERCONTENT_RELATION: A multi column index against the columns TARGETCONTENTID, SOURCEUSER, RELATIONNAME

5. For each constraint name, drop the constraint from the database:

   ALTER TABLE <TABLE NAME> DROP CONSTRAINT <CONSTRAINT NAME>;

   Replace TABLE NAME and CONSTRAINT NAME as appropriate

6. Start Confluence again

If this process fails, proceed to Workaround 2

Workaround 2 - if you're still having problems, or are otherwise seeing "RelationService upgrade failed to prevent data loss":

1. Restore the Confluence Home Directory and Database to the pre-upgraded state; using any filesystem and database backups you made beforehand
   You won't need to restore the installation directory, as we'll need to start the new version to re-run the upgrade process.
2. Connect to your database using your preferred database tools. If you're using a H2 Database, see Embedded H2 Database for instructions on how to connect to this database
3. Using your database tools, export the contents of the following tables as CSV files:
   • USER_RELATION
   • CONTENT_RELATION
   • USERCONTENT_RELATION

4. Next, execute the following queries to empty (but not delete) the tables: 

   TRUNCATE TABLE USER_RELATION;
   TRUNCATE TABLE CONTENT_RELATION;
   TRUNCATE TABLE USERCONTENT_RELATION;

5. Start Confluence again
6. After the upgrade process has completed, shut down Confluence and use your database tools to import the CSV data into the tables:
   • USER_RELATION
   • CONTENT_RELATION
   • USERCONTENT_RELATION
7. Start Confluence again