Upgrading from 5.9.1 to 5.9.2 with an Oracle or H2 database fails to add a constraint

Still need help?

The Atlassian Community is here for you.

Ask the community

 

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

Problem

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)]

Note about Unsupported Database

The H2 Database is a file based database and is prone to corruption and data loss. Atlassian does not support the use of the H2 database with Confluence in a production environment. In the event of a failure, we recommend you restore the last good backup, and migrate to another database. Please see the Confluence Supported Platforms for more information.

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.

Diagnosis - Does this apply to you?

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

  • Oracle
  • H2 Embedded Database

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.

Cause

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

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

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
    (info) 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.

    OracleH2 Database

    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":

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

  1. Restore the Confluence Home Directory and Database to the pre-upgraded state; using any filesystem and database backups you made beforehand
    (info) 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

 


Last modified on Oct 5, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.