SQL equivalents for Workflow Integrity Check in Jira Data Center
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
以下の項目もご覧になるといいでしょう。
症状
ワークフローの整合性チェッカーをブラウザで実行するとタイムアウトが発生し、実行できない。
原因
The instance is of a significant size and checks are not able to complete within the timeout period. We have a suggestion to fix this with a progress bar as tracked in - JRA-17593Getting issue details... STATUS .
回避策
ワークフローの SQL チェックをデータベースに対して手動で実行することで完了できます。
データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。
All queries were written and tested on PostgreSQL. Other DB products might need syntax adjustments and your setup might require database name and schema to be provided for table names.
If your database is case sensitive, and any query returns an error related to table not found, please observe if in the database the queried table name is capitalized or in lower case.
Workflow entry states are correct
This will check for any workflows that have an invalid state, as a result of - JRA-4241Getting issue details... STATUS .
SELECT jiraissue.id issue_id, jiraissue.workflow_id, os_wfentry.* FROM jiraissue JOIN os_wfentry ON jiraissue.workflow_id = os_wfentry.id WHERE os_wfentry.state IS NULL OR os_wfentry.state = 0;
これが次の例のようにレコードを返す場合、有効化するためにデータベースを更新する必要があります。
issue_id | workflow_id | id | name | initialized | state ----------+-------------+-------+------------------------------+-------------+------- 10023 | 10023 | 10023 | jdg-workflow-2-1377070570282 | | 0
To fix this example, run the following SQL - we need to update the
state
to be 1 based on theworkflow_id
. Theworkflow_id
is actuallyos_wfentry.id
UPDATE os_wfentry SET state = 1 WHERE id IN (SELECT os_wfentry.id FROM jiraissue JOIN os_wfentry ON jiraissue.workflow_id = os_wfentry.id WHERE os_wfentry.state IS NULL OR os_wfentry.state = 0);
MySQL equivalent for the above query that helped:
UPDATE os_wfentry as o1 inner join ( (SELECT o2.id FROM jiraissue as ji JOIN os_wfentry as o2 ON ji.workflow_id = o2.id WHERE o2.state IS NULL OR o2.state = 0) ) AS o3 on o1.id = o3.id SET o1.state = 1;
Jira Issues with Null Status
This will check if any issues have an invalid issue status, as from - JRA-7428Getting issue details... STATUS :
SELECT jiraissue.id, jiraissue.issuenum, jiraissue.issuestatus, jiraissue.project, jiraissue.issuetype, currentStep.step_id FROM jiraissue JOIN os_currentstep currentStep ON jiraissue.workflow_id = currentStep.entry_id WHERE jiraissue.issuestatus IS NULL;
次のように修正できます。
UPDATE jiraissue SET issuestatus = (SELECT state FROM os_wfentry WHERE id = workflow_id) WHERE issuestatus IS NULL;
Workflow current step entries
This will check if any issues have an invalid workflow id, as from - JRASERVER-4539Getting issue details... STATUS :
select concat(concat(P.pkey,'-'),I.issuenum) from jiraissue I join project P on P.id = I.project left join os_currentstep C on C.entry_id = I.workflow_id where C.id is null;
Running the query below will generate the inserts for all issues missing the valid workflow step entry:
select concat(concat('insert into os_currentstep values ((select max(id)+1 from os_currentstep),',workflow_id),',1,0,'''',now(),null,null,''open'',null)') from jiraissue where not exists (select * from os_currentstep where entry_id=workflow_id);
- すべての結果をコピーし、Insert 文を実行して不足している行を追加します。