We need a more robust process for refreshing prod data to lower environments

We recommend creating a test and dev instances of Jira prod data. This helps our Enterprise customers test upgrades and features, workarounds and fixes, I'd say "have you vetted this in a lower environment?" is one of the most common phrases I use.  The problem arises is we only take safety precautions in regard to the mail, we can set a flag in the JVM

-Datlassian.mail.senddisabled
-Datlassian.mail.fetchdisabled
-Datlassian.mail.popdisabled 

or we can disable it upon XML restore. But this still leaves the mail server and handler data in the DB and any admin can easily re-enable these mail servers. 

There are other concerns when refreshing data:

• DVCS syncs
• applinks 
• cache replication issues if prod and dev can communicate over 40001 and 40011
• index replication if you're doing a DB dump Jira has several bugs where it will continue to try to communicate to "offline or inactive" nodes even though they cannot reach the DB see   JRASERVER-42916 - Stale node ids should automatically be removed in Jira Data Center IN PROGRESS  for more information, this can even lead to dev indexing showing up in the admin/index section in other environments 

Suggested Action: 

Perhaps we could create a "lower environment" exporter or importer that strips the following from the export or import or a startup/setup task that can be run manually.

• DVCS data
• applinks 
• cluster node tables 
• mail server info and mail handlers

Workaround for Manual DB refreshes: 

If you're not using JIRA's XML you can do the following, if you're using an XML restore, you can perform the following after importing the data and stopping Jira.

1. Refresh the data from prod to lower environment
2. run the following before starting Jira

Index related tables for Data Center:

Truncate the following tables:

clusternode
clusternodeheartbeat
replicatedindexoperation

Mail server info

1. Delete all incoming and outgoing mail servers by truncating the following table:

mailserver

2. Remove mail handlers

delete from serviceconfig where clazz='com.atlassian.jira.service.services.mail.MailFetcherService'

Remove Applinks: 

1. Find the applink ids:

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

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, you'll also need to run these for each result from query 1.

DELETE FROM oauthspconsumer WHERE consumer_key IN (SELECT propertyvalue FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%resultsfromfirstquery%'));
-- 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.%resultsfromfirstquery%'));

DELETE FROM oauthconsumertoken WHERE token_key LIKE '%resultsfromfirstquery%';

DELETE FROM oauthconsumertoken WHERE token_key LIKE '%resultsfromfirstquery%';
DELETE FROM trustedapp WHERE application_id IN (SELECT propertyvalue FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%resultsfromfirstquery%')); 
-- 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.%resultsfromfirstquery%'));

DELETE FROM propertystring WHERE id IN (SELECT id FROM propertyentry WHERE property_key LIKE 'applinks.%resultsfromfirstquery%');

DELETE FROM propertyentry WHERE property_key LIKE 'applinks.admin.resultsfromfirstquery1%';

UPDATE propertystring SET propertyvalue = REPLACE(propertyvalue,'\n2c66970e-35f8-365f-bc65-f535d7edf1a1','') where id in (select id from propertyentry where property_key like 'applinks.global%'); 

Remove DVCS ACCOUNTS: 

1. Delete the orgs from "AO_E8B6CC_ORGANIZATION_MAPPING":

   delete from "AO_E8B6CC_ORGANIZATION_MAPPING"; 

2. Delete the repos from "AO_E8B6CC_REPOSITORY_MAPPING":

   delete from "AO_E8B6CC_REPOSITORY_MAPPING""

   This will remove all connection information from JIRA to GHE.

AFTER these steps have been done you can start Jira for the first time. 

This should remove all mail servers, dvcs account info and applinks from the instance, we also have a comprehensive guide for this here.