目的

システム停止、データベースの破損、他の特定されていない原因などの発生後の特定の状況において、Jira のアプリケーション リンク (「アプリケーション リンクの設定」を参照) が破損する可能性があります。

このような場合、回避策として破損したアプリケーションをデータベースから直接取り除きます。このナレッジベース記事では、この方法について説明します。

診断

各アプリケーション リンクはアプリケーション キーで特定されます。次のクエリを実行して、どのキーがどのアプリケーションに所属するかを特定します。

SELECT SUBSTR(a.property_key,16,36) as "Application Key", b.propertyvalue as "Application Name" FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name';

-- NOTE: For Microsoft SQLServer use the following query:
SELECT substring(a.property_key,16,36) as "Application Key", b.propertyvalue as "Application Name" FROM propertyentry a join propertystring b on a.id=b.id where a.property_key like 'applinks.admin%name';

このクエリの結果は次のようなものになります。

+--------------------------------------+--------------------+
| Application Key                      | Application Name   |
+--------------------------------------+--------------------+
| 2c66970e-35f8-365f-bc65-f535d7edf1a1 |     Confluence     |
| f365831f-1827-3ecf-a992-1dff949398b1 |      Fisheye       |
+--------------------------------------+--------------------+
2 rows in set (0.00 sec)

ソリューション

この例では、Confluence アプリケーションを取り除きます。アプリケーション キーは 2c66970e-35f8-365f-bc65-f535d7edf1a1 です。

1. Jira をシャットダウンします。

2. 診断セクションの上述の SQL を使用して取り除く必要があるアプリケーションのアプリケーション キーを特定したら、次のクエリを次の順序で実行します。

   DELETE FROM oauthspconsumer WHERE consumer_key IN (SELECT propertyvalue FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%2c66970e-35f8-365f-bc65-f535d7edf1a1%'));
   
   -- NOTE: For Microsoft SQLServer use the following query:
   DELETE FROM oauthspconsumer WHERE consumer_key IN (SELECT CONVERT(NVARCHAR(MAX), propertyvalue) FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%2c66970e-35f8-365f-bc65-f535d7edf1a1%'));

   DELETE FROM oauthconsumertoken WHERE token_key LIKE '%2c66970e-35f8-365f-bc65-f535d7edf1a1%';

   DELETE FROM trustedapp WHERE application_id IN (SELECT propertyvalue FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%2c66970e-35f8-365f-bc65-f535d7edf1a1%')); 
   
   -- NOTE: For Microsoft SQLServer use the following query:
   DELETE FROM trustedapp WHERE application_id IN (SELECT CONVERT(NVARCHAR(MAX), propertyvalue) FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%2c66970e-35f8-365f-bc65-f535d7edf1a1%')); 

   DELETE FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%2c66970e-35f8-365f-bc65-f535d7edf1a1%');

   DELETE FROM propertyentry WHERE property_key LIKE 'applinks.admin.2c66970e-35f8-365f-bc65-f535d7edf1a1%';

3. Fisheye インスタンスの場合、次のクエリを実行してデータベースのプロパティとプロジェクト リンクを取り除きます。

   DELETE FROM propertytext WHERE id in (SELECT id FROM propertyentry WHERE property_key LIKE '%ual.2c66970e-35f8-365f-bc65-f535d7edf1a1%');
   
   DELETE FROM propertyentry WHERE property_key LIKE '%ual.2c66970e-35f8-365f-bc65-f535d7edf1a1%';
   
   DELETE FROM propertyentry WHERE id in (SELECT id FROM propertystring WHERE propertyvalue LIKE '%2c66970e-35f8-365f-bc65-f535d7edf1a1%');
   
   DELETE FROM propertystring WHERE propertyvalue LIKE '%2c66970e-35f8-365f-bc65-f535d7edf1a1%';
   
   DELETE FROM propertyentry WHERE id in (SELECT id FROM propertytext WHERE propertyvalue LIKE '%2c66970e-35f8-365f-bc65-f535d7edf1a1%');
   
   DELETE FROM propertytext WHERE propertyvalue LIKE '%2c66970e-35f8-365f-bc65-f535d7edf1a1%';

4. Check if applink global configuration is stored on the propertystring or on the propertytext table.
   If the output of the SQL query below is 5, then configuration is stored on the propertystring table – this would be the most common scenario.
   If it's 6, then it's on the propertytext table – this usually happens when there are more than 7 application links.
   

   select pe.propertytype
   from propertyentry pe
   where pe.property_key like 'applinks.global.%';

   
   

5. Remove the application key of the list of applications with the below SQL – note the target table depends on the previous step.

   
   

   • propertystring
   • propertytext

   UPDATE propertystring SET propertyvalue = REPLACE(propertyvalue, E'\n<application_link_id>','') where id in (select id from propertyentry where property_key like 'applinks.global%');
   
     -- NOTE: For Microsoft SQLServer use the following query:
   UPDATE propertystring SET propertyvalue = CAST(REPLACE(CAST(propertyvalue as NVarchar(MAX)), CHAR(10)+'2c66970e-35f8-365f-bc65-f535d7edf1a1','') AS NText) where id in (select id from propertyentry where property_key like 'applinks.global%');
   
   -- NOTE: For Oracle use the following query:
   UPDATE propertystring SET propertyvalue = REPLACE(propertyvalue, chr(10) || '2c66970e-35f8-365f-bc65-f535d7edf1a1','') where id in (select id from propertyentry where property_key like 'applinks.global%');

   UPDATE propertytext SET propertyvalue = REPLACE(propertyvalue, E'\n<application_link_id>','') where id in (select id from propertyentry where property_key like 'applinks.global%');
   
     -- NOTE: For Microsoft SQLServer use the following query:
   UPDATE propertytext SET propertyvalue = CAST(REPLACE(CAST(propertyvalue as NVarchar(MAX)), CHAR(10)+'2c66970e-35f8-365f-bc65-f535d7edf1a1','') AS NText) where id in (select id from propertyentry where property_key like 'applinks.global%');
   
   -- NOTE: For Oracle use the following query:
   UPDATE propertytext SET propertyvalue = REPLACE(propertyvalue, chr(10) || '2c66970e-35f8-365f-bc65-f535d7edf1a1','') where id in (select id from propertyentry where property_key like 'applinks.global%');

   
   
   NOTE 1: In the first query above, there is a newline terminator (\n) in front of the application key, which is needed to avoid empty lines in the application links list. For more details, please see Microsoft's Specify Field and Row Terminators (SQL Server) documentation.

    NOTE 2: When using an ORACLE DATABASE, Make sure to run  "commit;" to commit all changes to the database. Oracle will show that the commands have been run but JIRA will not reflect these changes.
   NOTE 3: In the second query (for Microsoft SQLServer) we use the parameter CHAR(10)+ in front of the application key, it's because it doesn't use newline terminators to separate the application keys, instead it uses simple spaces, which are not allowed with the REPLACE function, therefore use need to replace the simple spaces by its character code.

6. Jira を起動します。

Empty lines at the application links list can cause errors such as the following in the atlassian-jira.log:

2012-05-18 11:23:17,150 Spring executor 8 ERROR      [plugin.osgi.factory.OsgiPlugin] Unable to start the Spring context for plugin com.atlassian.jira.plugin.ext.bamboo
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'gadgetListener' defined in URL [bundle://60.0:0/META-INF/spring/atlassian-plugins-components.xml]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: id must be a valid java.util.UUID string: 
    ...
org.springframework.osgi.extender.internal.dependencies.startup.DependencyWaiterApplicationContextExecutor$CompleteRefreshTask.run(DependencyWaiterApplicationContextExecutor.java:132)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.IllegalArgumentException: id must be a valid java.util.UUID string: 
    ...
Caused by: java.lang.IllegalArgumentException: Invalid UUID string: 
    at java.util.UUID.fromString(Unknown Source)
    at com.atlassian.applinks.api.ApplicationId.<init>(ApplicationId.java:34)

このようなエラーが発生した場合、次のクエリの結果を参考に、空白行がないことを確認します。

select * from propertystring where id in (select id from propertyentry where property_key like 'applinks.global%');

ご利用の DBMS に応じて上述の SQL を変更する必要がある可能性があります。詳細については社内の DBA にご相談ください。

代替ソリューション

ソリューションが役に立たない場合、「Fisheye のアプリケーション リンクを取り除けない」記事をご確認ください。

 Only applicable for Fisheye/Crucible application.