Bitbucket Server throwing ORA-00001: unique constraint violated

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。



問題

The following appears in the atlassian-bitbucket-<date>.log for multiple 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

診断

環境

Using Oracle database, version was 12c in this stack trace.

診断ステップ

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

  1. Run for each problematic constraint seen in the log:

    select owner, constraint_name, constraint_type, table_name, index_owner, index_name from ALL_CONSTRAINTS where CONSTRAINT_NAME in ('SYS_C006418');

    The output will be something similar to this:

    #OWNERCONSTRAINT_NAMECONSTRAINT_TYPETABLE_NAMEINDEX_OWNERINDEX_NAME
    1BITBUCKETSYS_C006418PAO_777666_JIRA_INDEXBITBUCKETSYS_C006418
  2. Run once:

    SELECT o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type FROM all_objects o WHERE o.owner = 'BITBUCKET' AND o.object_type = 'SEQUENCE'

    Sample result:

    #TABLE_SCHEMTABLE_NAMETABLE_TYPE
    1BITBUCKETAO_777666_JIRA_INDEX_ID_SEQSEQUENCE
  3. Run for each sequence (in table_name column from the query above):

    select AO_777666_JIRA_INDEX_ID_SEQ.NEXTVAL from DUAL;

    Sample result:

    #NEXTVAL
    12781.00

    Also run once:

    select max(ID) from AO_777666_JIRA_INDEX;

    Sample result:

    #MAX(ID)
    124368.00

    Note: in this last query above, the ID column might have a different name depending on the table. For the sake of this article, the AO_777666_JIRA_INDEX table has the ID column, which is also the primary key.

  4. Run once:

    SELECT * FROM all_triggers WHERE table_name IN ('AO_777666_JIRA_INDEX');

    Sample result:


    #TRIGGER_NAMETABLE_NAME
    1AO_777666_JIRA_INDE1519288902AO_777666_JIRA_INDEX
  5. For example, when these queries were run locally, one of the sequence names was "AO_777666_JIRA_INDEX_ID_SEQ". That's the sequence used to populate the ID column in the "AO_777666_JIRA_INDEX" table. 
    The expectation is that the query "select AO_777666_JIRA_INDEX_ID_SEQ.NEXTVAL from DUAL" will return a number that is higher than the one returned by the query "select max(ID) from AO_777666_JIRA_INDEX"
    If it doesn't, that would explain why the constraint violations on the primary key are happening.

  6. The queries with "select max(ID)" might not return any value, but as long as they run without error that's fine.


  7. Summarizing the data collected:

    SEQUENCETABLEANALYSIS
    AO_777666_JIRA_INDEX_ID_SEQ.NextVal = 2781.00max(ID) from AO_777666_JIRA_INDEX = 24368.00The sequence does not have a higher ID than the table max

原因

Database corruption. For every single table where these Primary Key violations are happening, the sequence has a lower ID than the max.


ソリューション

In order to fix this problem, the best step forward would be to recreate the sequence, such that its NEXTVAL will be higher than the relevant MAX (based on the table above).

The following was used in order to recreate the sequence in the example above:

DECLARE
last_used NUMBER;
curr_seq NUMBER;
BEGIN
SELECT max(id) INTO last_used FROM BITBUCKET.AO_777666_JIRA_INDEX;
LOOP
SELECT BITBUCKET.AO_777666_JIRA_INDEX_ID_SEQ.NEXTVAL INTO curr_seq FROM dual;
IF curr_seq >= last_used THEN EXIT;
END IF;
END LOOP;
END;




SELECT * FROM all_triggers WHERE table_name IN ('AO_FB71B4_SSH_PUBLIC_KEY', 'AO_BD73C3_REPOSITORY_AUDIT', 'AO_616D7B_BRANCH_TYPE', 'AO_777666_JIRA_INDEX', 'AO_BD73C3_PROJECT_AUDIT', 'AO_6978BB_RESTRICTED_REF');
最終更新日: 2018 年 2 月 28 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.