Bitbucket Server throwing ORA-00001: unique constraint violated

Related content

  • No related content found

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

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

2017-04-20 07:11:45,248 ERROR [AtlassianEvent::thread-29] h3mp @1XNYDVUx431x291863574x0 sn8pxs 172.17.6.2 "POST /rest/api/latest/projects/PROJ/repos/therepo/pull-requests/301/merge HTTP/1.1" c.a.s.i.e.AsyncBatchingInvokersTransformer There was an exception thrown trying to dispatch event 'com.atlassian.stash.internal.pull.AnalyticsPullRequestMergedEvent[source=com.atlassian.stash.internal.pull.DefaultPullRequestService@47e40de5]' for the invoker 'SingleParameterMethodListenerInvoker{method=public void com.atlassian.stash.internal.jira.index.impl.IndexEventListener.onPullRequestMerged(com.atlassian.bitbucket.event.pull.PullRequestMergedEvent), listener=com.atlassian.stash.internal.jira.index.impl.IndexEventListener@5c074746}'
java.lang.RuntimeException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Oracle
	- version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	- minor version:1
	- major version:12
Driver:
	- name:Oracle JDBC driver
	- version:12.1.0.1.0

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BITBUCKET.SYS_C006418) violated

	at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:54) ~[atlassian-event-3.0.0.jar:na]
	at com.atlassian.stash.internal.event.AsyncBatchingInvokersTransformer$AsyncInvokerBatch.invoke(AsyncBatchingInvokersTransformer.java:109) ~[bitbucket-platform-4.10.0.jar:na]
	at com.atlassian.event.internal.AsynchronousAbleEventDispatcher$1$1.run(AsynchronousAbleEventDispatcher.java:46) [atlassian-event-3.0.0.jar:na]
	at com.atlassian.sal.core.executor.ThreadLocalDelegateRunnable.run(ThreadLocalDelegateRunnable.java:34) [sal-core-3.0.6.jar:na]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_111]
	at java.lang.Thread.run(Thread.java:745) [na:1.8.0_111]
	... 1 frame trimmed
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Oracle
	- version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	- minor version:1
	- major version:12
Driver:
	- name:Oracle JDBC driver
	- version:12.1.0.1.0

java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BITBUCKET.SYS_C006418) violated

	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:88) ~[na:na]
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:261) ~[na:na]
	...
	...
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (BITBUCKET.SYS_C006418) violated

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361) ~[ojdbc7-12.1.0.1.jar:12.1.0.1.0]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-2.4.7.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-2.4.7.jar:na]
	at net.java.ao.db.OracleDatabaseProvider.executeInsertReturningKey(OracleDatabaseProvider.java:294) ~[na:na]
	at net.java.ao.DatabaseProvider.insertReturningKey(DatabaseProvider.java:1869) ~[na:na]
	at net.java.ao.EntityManager.create(EntityManager.java:366) ~[na:na]
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:86) ~[na:na]
	... 57 common frames omitted

Diagnosis

Environment

Using Oracle 12c.

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

    SELECT con.constraint_name, con.table_name, obj.object_name AS sequence_name
FROM all_constraints AS con, all_objects AS obj
WHERE con.owner = obj.owner
AND obj.object_name LIKE CONCAT(con.table_name,'%')
AND con.table_name <> obj.object_name
AND con.constraint_name = 'SYS_C006418';

    The output will be similar to this:

    CONSTRAINT_NAMETABLE_NAMESEQUENCE_NAME
    SYS_C006418AO_777666_JIRA_INDEXAO_777666_JIRA_INDEX_ID_SEQ

    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:

    1. 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:

      COLUMN_NAME
      ID

      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.

    2. 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:

      HIGHEST_ID
      24368

    3. 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:

      SEQUENCE_NEXT_VALUE
      2781

  3. Analyzing the data collected thus far:

    1. 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.
    2. 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.
    3. 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).

Resolution

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:

DROP SEQUENCE AO_777666_JIRA_INDEX_ID_SEQ;
CREATE SEQUENCE AO_777666_JIRA_INDEX_ID_SEQ minvalue 1 maxvalue 999999999999999999999 start with 24369 increment by 1 cache 20;


Last modified on Mar 9, 2022

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.