Purpose

Sometimes Fisheye/Crucible administrators face issues when trying to add, edit or even delete an application link and a cleanup must be performed at the database level.

In this guide you'll find a step-by-step guide on how to perform this clean-up.

Solution

For the sake of this article, let's suppose that Fisheye/Crucible has an application link to a Jira instance named MyJiraInstance.

Fisheye/Crucible version >= 4.5.1

1. Stop your Fisheye/Crucible instance.
2. Always backup Fisheye/Crucible before making any changes to the database.

3. Run the following select query in order to find out what is the ID of the application link to MyJiraInstance:

   SELECT cru_property_name FROM cru_repository_property WHERE cru_property_name LIKE 'applinks.admin.%.name' AND cru_value = 'MyJiraInstance';

   The query will print something like this:

   cru_property_name
   applinks.admin.b66307f2-7308-31bc-addf-0a17c4befecf.name

   You'll find the application link ID in between admin and name.

   In this example the application link ID is b66307f2-7308-31bc-addf-0a17c4befecf so we will be using it throughout the kb article but you must replace it with your own application link ID when running select, update and delete queries.

4. Once you have the application link ID there are basically three operations you'll need to execute in order to remove all references to it from the database:

   1. Remove all references that contain the application link ID inside the cru_oauthcons table.

   2. Remove all references that contain the application link ID inside the cru_repository_property table without updating the list of application links (applinks.global.application.ids).

   3. Update the list of application links to remove the application link ID inside the cru_repository_property table.

5. Let's start by removing all references of the application link inside the cru_oauthcons table. 

   1. First we need to find what entries need to be removed inside the cru_oauthcons table so we should run the following select query

      SELECT co.cru_oauthcons_id FROM cru_oauthcons co JOIN cru_repository_property crp ON co.cru_oauthcons_key = crp.cru_value WHERE crp.cru_property_name like 'applinks.application.b66307f2-7308-31bc-addf-0a17c4befecf.oauth.incoming.consumerkey';

      The query will print something like this:

      cru_oauthcons_id
      2

   2. Run the the following delete query to remove the reference of that application link ID:

      DELETE FROM cru_oauthcons WHERE cru_oauthcons_id = 2;

6. After that we can remove all references of that application link ID from the cru_repository_property table using the following delete query:
   

   DELETE FROM cru_repository_property WHERE cru_property_name LIKE '%b66307f2-7308-31bc-addf-0a17c4befecf%' OR cru_value LIKE '%b66307f2-7308-31bc-addf-0a17c4befecf%' AND cru_property_name != 'applinks.global.application.ids';

7. Last but not least we need to update the list of application links to remove our application link ID.

   1. We need to start by getting the list of application links using the following select query:

      SELECT cru_repo_prop_id, cru_value FROM cru_repository_property WHERE cru_property_name = 'applinks.global.application.ids';

      The query will print something like this:

      cru_repo_prop_id	cru_value
      40	#java.util.List b66307f2-7308-31bc-addf-0a17c4befecf 30850197-b1df-3ba6-9f34-27ce4908c0ce e6ddadd3-604d-3866-9741-b8d298c424f2 7e181ee3-1ba7-375c-8845-ee32920c178d

   2. We have to be very careful here because we don't want to remove all of the IDs from the list (otherwise you'd see an empty 'Application Links' page after starting up Fisheye/Crucible) so we need to run an update query to change the list of application links while excluding the application link ID that we're trying to remove. In order to do that based on the example above I'll run the following update query:

      PostgreSQL 9.6 - Example

      UPDATE cru_repository_property set cru_value = E'#java.util.List\n30850197-b1df-3ba6-9f34-27ce4908c0ce\ne6ddadd3-604d-3866-9741-b8d298c424f2\n7e181ee3-1ba7-375c-8845-ee32920c178d' WHERE cru_repo_prop_id = <REPLACE_WITH_ID_FROM_SELECT_QUERY_6a>;

      PostgreSQL 9.6

      The list of application links is always stored in multiple lines so you must a) add the letter E before the start of the string and b) add \n in the string every time there's a line break. The query might need to be adapted to work with other versions or database types.

      MySQL 5.7.5 - Example

      UPDATE cru_repository_property set cru_value = '#java.util.List\n30850197-b1df-3ba6-9f34-27ce4908c0ce\ne6ddadd3-604d-3866-9741-b8d298c424f2\n7e181ee3-1ba7-375c-8845-ee32920c178d' WHERE cru_repo_prop_id = <REPLACE_WITH_ID_FROM_SELECT_QUERY_6a>;

      MySQL 5.7.5

      The list of application links is always stored in multiple lines so you must add \n to the string every time there's a line break. The query might need to be adapted to work with other versions or database types.

8. Commit your changes (if needed) and disconnect from the database.

9. Start your Fisheye / Crucible instance.

   At this point you might want / have to delete the application link to Fisheye/Crucible inside your Jira instance's database. In order to do that you need to follow the steps inside this document: Remove an application link from Jira server using SQL.

10. Create a new application link between Fisheye/Crucible and Jira by following the Linking to another application document.

Fisheye/Crucible version <= 4.5.0

1. Shutdown Fisheye/Crucible.
2. Always backup your Fisheye/Crucible instance before making any changes to the database.

3. Run the following delete query to remove all references to your application links:

   DELETE from cru_repository_property WHERE cru_property_name LIKE '%applink%' OR cru_property_name LIKE '%oauth%';

4. Commit changes (if needed) and disconnect from the database.

5. Start your Fisheye/Crucible instance.

   At this point you might want / have to delete the application link to Fisheye/Crucible inside your Jira instance's database. In order to do that you need to follow the steps inside this document: Remove an application link from Jira server using SQL.

6. Create a new application link between Fisheye/Crucible and Jira by following the Linking to another application document.