How to manually remove application links from the Fisheye/Crucible database

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

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.

You can find out what is the name of your application (e.g. MyJiraInstance) by going to the Fisheye/Crucible admin > Global Settings > Application Links page. The application name will appear under the Application column.

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_idcru_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.


Last modified on Jul 22, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.