Purpose

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.

Diagnosis

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)

Resolution

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

1. Shutdown 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. 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. Start 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)

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

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.

 Only applicable for Fisheye/Crucible application.