Bitbucket Server throwing ORA-00001: unique constraint violated

Summary

The following appears in the atlassian-bitbucket-<date>.log for one or multiple primary key uniqueness constraints:

Environment

Using Oracle 12c.

Diagnosis

Database concepts

1. The Primary Key is a column in a table. Primary Keys need unique values, i.e., values cannot repeat in that column from that table. If a value being inserted in that column was already used, a unique constraint violation like the above will happen.

2. The Sequence is the feature responsible for creating these unique values. It increments a value and returns it.

Diagnostic Steps

Connect to the database used by Bitbucket and run the following queries:

1. Run this SELECT query below modifying just the value of constraint_name (in the last line) by the name of the constraint seen in the log file (SYS_C006418 in this example):

The output will be similar to this:

Here you can see that the constraint named SYS_C006418 that enforces Primary Key uniqueness is related to the table named AO_777666_JIRA_INDEX which, on its turn, uses the sequence named AO_777666_JIRA_INDEX_ID_SEQ in order to generate new values for its Primary Key when inserting data into it.

2. Now that you know the table name and the sequence name:

• Run this SELECT query in order to identify which column from the table AO_777666_JIRA_INDEX is the Primary Key:

  SELECT column_name FROM all_cons_columns WHERE constraint_name = ( SELECT constraint_name FROM user_constraints WHERE UPPER(table_name) = UPPER('AO_777666_JIRA_INDEX') AND CONSTRAINT_TYPE = 'P' );

  Sample result:

From the above output, the ID column is the Primary Key of the table AO_777666_JIRA_INDEX. Other tables may have other column names set as Primary Keys.

• Now that you know what column is the Primary Key, run this SELECT query in order to determine what is the highest value stored in that column (replacing id in MAX(id) accordingly, remembering that this column is appears twice in the query):

  SELECT (CASE WHEN MAX(id) IS NULL THEN 0 ELSE MAX(id) END) AS highest_id FROM AO_777666_JIRA_INDEX;

  Sample result:

• Now that you know the sequence name, run this SELECT query in order to verify what value the database is trying to store in the Primary Key column when inserting data into the AO_777666_JIRA_INDEX table:

  SELECT AO_777666_JIRA_INDEX_ID_SEQ.nextval AS sequence_next_value FROM dual;

  Sample result:

3. Analyzing the data collected thus far:

• The constraint mentioned in the logs (SYS_C006418) is related to a table named AO_777666_JIRA_INDEX which on its turn uses a sequence named AO_777666_JIRA_INDEX_ID_SEQ for generating incremental numeric values for its Primary Key column, which is named ID.

• Therefore, the correct expectation is that the number returned by the sequence AO_777666_JIRA_INDEX_ID_SEQ is higher than the highest number stored in the ID column of the AO_777666_JIRA_INDEX table.

• As determined by these SQL queries though, the value returned by the sequence is lower than the highest number already used.

Cause

Database corruption. Whenever a Primary Key uniqueness violation happens, the number being inserted (provided by the sequence) has a lower number than the highest already used in the Primary Key column of the affected table.

In this example, the number provided by the sequence (2781) is lower than the highest value stored in the ID column of the table named AO_777666_JIRA_INDEX (24368).

Solution

The best way of resolving this is by recreating the sequence making its NEXTVAL be one number greater (x + 1) than the highest value used in the Primary Key of the affected table.

Before doing that though, we strongly recommend you to:

1. Stop Bitbucket

2. Create a database backup, just to be on the safe side

For example, remembering that the highest value of the ID column from AO_777666_JIRA_INDEX table is 24368, as determined in the Diagnostic Steps section, the number 24369 (that is, x + 1) was used as starting number when recreating the sequence: