How to remove Application Link from Bitbucket Data Center database

Summary

This document helps identify each Application Link that Bitbucket Data Center (formerly Atlassian Stash) has to every other application and then remove the correct one, as desired, directly from the database.

It's useful since some errors may lead to the impossibility of removing an Application Link from the UI, so it can be removed from the database.

One of the possible scenarios -> when Application link showing "Network error" and while editing the application link, we receive "consumer_key_unknown", we can follow these steps:

Solution

1. The following SQL query identifies each Application Link contained in the database. Each one has a different hash on it - this will be used to remove the correct Application Link.

   1 SELECT SUBSTR(ps.key_name,16,36) as "Application Key", ps.key_value as "Application Name" FROM plugin_setting ps WHERE lower(ps.key_name) LIKE '%applink%type%';
   If running the SELECT statement above returns an error similar to <'SUBSTR' is not a recognized built-in function name> , you may be using a DBMS with an equivalent function under a different name. For instance MS SQL Server uses SUBSTRING() instead of SUBSTR.

2. Using the Application Key corresponding to the Application Link you wish to remove, run the following two queries:

   1 2 DELETE FROM plugin_setting WHERE key_name LIKE 'applinks.%<Application Key>%'; DELETE FROM plugin_setting WHERE key_value LIKE '%<Application Key>%';

   For example, if the Application Key found by the first query is 34ba63c0-ff98-3241-b181-d0bf19f08d09 the queries will be like this:

   1 2 DELETE FROM plugin_setting WHERE key_name LIKE 'applinks.%34ba63c0-ff98-3241-b181-d0bf19f08d09%'; DELETE FROM plugin_setting WHERE key_value LIKE '%34ba63c0-ff98-3241-b181-d0bf19f08d09%';

3. The Application Key must also be removed from the list of global application ids. However, this list contains all application keys and removing the entire value may not be desired. The following UPDATE will replace just the required Application Key with a null string:

   1 2 3 4 5 6 7 UPDATE plugin_setting SET key_value = REPLACE(key_value,'\n34ba63c0-ff98-3241-b181-d0bf19f08d09','') WHERE key_name = 'applinks.global.application.ids'; -- NOTE: For Microsoft SQLServer use the following query: UPDATE plugin_setting SET key_value = CAST(REPLACE(CAST(key_value as NVarchar(MAX)), CHAR(10)+'34ba63c0-ff98-3241-b181-d0bf19f08d09','') AS NText) WHERE key_name = 'applinks.global.application.ids'; -- NOTE: For Oracle use the following query: UPDATE plugin_setting SET key_value = REPLACE(key_value, chr(10) || '34ba63c0-ff98-3241-b181-d0bf19f08d09','') WHERE key_name = 'applinks.global.application.ids';

4. Lastly, delete the following additional entries from the database:

   1 2 3 4 DELETE FROM plugin_setting WHERE key_name = 'com.atlassian.oauth.serviceprovider.ServiceProviderConsumerStore.allConsumerKeys'; DELETE FROM plugin_setting WHERE key_name = 'com.atlassian.oauth.consumer.ConsumerService.serviceNames'; DELETE FROM plugin_setting WHERE key_name = 'com.atlassian.oauth.consumer.ConsumerService.consumerService.OauthKey'; DELETE FROM plugin_setting WHERE key_name LIKE 'com.atlassian.oauth%'