Remove an application link from Jira server using SQL

お困りですか?

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

コミュニティに質問

目的

In some specific situations, after an outage, database corruption or other non-identified causes, the JIRA Application Links (as from Configuring Application Links) may become broken.

In those cases, the workaround is remove the broken application directly through the database. This knowledge base article is a guideline on how to accomplish this.

診断

Each application link is identified by an application key. Run this query to identify which key belongs to which application:

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';


The result of this query will be something like this:

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

ソリューション

データベースの変更を行う場合は 必ず事前にバックアップを取得してください。可能な場合はテスト サーバーで変更を試すことをおすすめします。

In this example, we will remove Confluence application, application key = 2c66970e-35f8-365f-bc65-f535d7edf1a1.

Some of those queries may not return results as it will depend on the authentication type that the Application Link has been set.

  1. Jira をシャットダウンします。
  2. After identifying the application key of the application that requires removing using the above SQL in the diagnosis section, run the following queries exactly in this order:

    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. If it is a Fisheye instance, run the following queries to remove the properties and project links in database:

    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. Remove the application key of the list of applications with the below SQL:

    UPDATE propertystring SET propertyvalue = REPLACE(propertyvalue,'\n2c66970e-35f8-365f-bc65-f535d7edf1a1','') 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%');

    (info) 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.

    (info) 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.
    (info) 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.

  5. Jira を起動します。


(warning) 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)

If these errors are present, please ensure that there is not empty lines, likes in the result of this query:

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

(info) The above SQL may need to be modified, depending on your DBMS. Please consult with a DBA for further information.

Alternative Resolution

If the resolution not helping, please refer Not able to remove Fisheye Application Link article.

(info) Only applicable for Fisheye/Crucible application.

最終更新日 2019 年 9 月 25 日

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

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