Unable to create Jira Portfolio plans or Jira Software boards after restoring from database backup ( PostgreSQL and Oracle specific )

お困りですか?

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

コミュニティに質問


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



問題

Various expected product functions are not working correctly after restoring a Jira database backup using PostgreSQL or Oracle. The following error appears when attempting to create a new Portfolio plan for example:

com.atlassian.rm.jpo.env.EnvironmentAgileNotAvailableException: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: - name:PostgreSQL - version:9.4.15 - minor version:4 - major version:9 Driver: - name:PostgreSQL Native Driver - version:PostgreSQL 9.4.1212 org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_60DB71_LEXORANK_pkey" Detail: Key ("ID")=(1338) already exists.

  at com.atlassian.rm.jpo.env.rank.BaseJiraEnvironmentRankService.sort:121
  at com.atlassian.rm.jpo.core.rank.BaseKeyRankService.stackUnknown:87
  at com.atlassian.rm.jpo.core.rank.DefaultVersionDomainKeyRankService.stackUnknown:11
  at com.atlassian.rm.jpo.core.version.VersionOrderService.stackUnknownEnviromentVersions:43
  at com.atlassian.rm.jpo.core.project.ProjectService.getDeepProjects:104
  at com.atlassian.rm.jpo.core.project.ProjectService.getDeepProjectsById:81
  at com.atlassian.rm.jpo.core.issuesource.context.DefaultIssueSourceContextService.getProjectInfosForIssueSources:61
  at com.atlassian.rm.jpo.rest.service.plan.setup.projects.PlanSetupProjectsRestEndpoint.getProjects:51
  at sun.reflect.GeneratedMethodAccessor872.invoke:-1
  at sun.reflect.DelegatingMethodAccessorImpl.invoke:43
  at java.lang.reflect.Method.invoke:498

診断

環境

  • PostgreSQL or Oracle database in use

原因

The sequence tables specific to PostgreSQL or Oracle are not in sync with the actual max(id) of affected tables

ソリューション

Perform the following commands to correct this problem in the database

  • Identify affected tables with the following command:

    $ grep 'duplicate key value violates unique constraint' atlassian-jira.log* | grep 'Caused by' | sort -u
    Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_60DB71_LEXORANK_pkey"
  • Verify the affected tables ID column:

    \d "AO_60DB71_LEXORANK"
                                       Table "public.AO_60DB71_LEXORANK"
      Column   |          Type          |                             Modifiers                             
    -----------+------------------------+-------------------------------------------------------------------
     BUCKET    | integer                | default 0
     FIELD_ID  | bigint                 | not null default 0
     ID        | bigint                 | not null default nextval('"AO_60DB71_LEXORANK_ID_seq"'::regclass)
     ISSUE_ID  | bigint                 | not null default 0
     LOCK_HASH | character varying(255) | 
     LOCK_TIME | bigint                 | 
     RANK      | character varying(255) | not null
     TYPE      | integer                | not null default 0
    Indexes:
        "AO_60DB71_LEXORANK_pkey" PRIMARY KEY, btree ("ID")
  • Verify max ID:

    select max("ID") from "AO_60DB71_LEXORANK";
      max  
    -------
     10203
    (1 row)
  • Verify PostgreSQL's or Oracle's max ID from its index:

    \d "AO_60DB71_LEXORANK_ID_seq"
         Sequence "public.AO_60DB71_LEXORANK_ID_seq"
        Column     |  Type   |           Value           
    ---------------+---------+---------------------------
     sequence_name | name    | AO_60DB71_LEXORANK_ID_seq
     last_value    | bigint  | 10202
     start_value   | bigint  | 1
     increment_by  | bigint  | 1
     max_value     | bigint  | 9223372036854775807
     min_value     | bigint  | 1
     cache_value   | bigint  | 1
     log_cnt       | bigint  | 31
     is_cycled     | boolean | f
     is_called     | boolean | t
    Owned by: public."AO_60DB71_LEXORANK"."ID"
  • In this example last_value from "AO_60DB71_LEXORANK_ID_seq" does not match the maxium ID number from the relating table. Update the sequence accordingly to match:

    alter sequence "AO_60DB71_LEXORANK_ID_seq" restart 10203
  • Do this for every affected table. If the table affected is sequence_value_item see this KB for further instruction


最終更新日 2021 年 4 月 26 日

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

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