Introduction

If you are migrating a JIRA server to a new location, and as a result, the URL of the server changes, then you will need to manually repair some data in the database to make Enterprise Tester aware of these changes.

For the purpose of this article, we will assume that from and to URL's for JIRA are:

From

To

http://jirasvr/

http://mycorp.com/jira/

 

Before you Begin

Before making any changes we suggest that you:

Updating Incidents

Incidents within the Enterprise Tester database are stored across a number of tables. The table that needs updating when changing the JIRA URL is called "IncidentDetails", and in particular, a column called "TicketKey".

You will need the assistance of your DBA to run these queries against your Enterprise Tester Database.

Database Type

 

Sql Server

UPDATE IncidentDetails
SET TicketUrl = replace(TicketUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE TicketUrl IS NOT NULL

Oracle

UPDATE INCIDENTDETAILS
SET TICKETURL = REPLACE(TICKETURL , 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE TICKETURL IS NOT NULL

MySql

UPDATE IncidentDetails
SET TicketUrl = replace(TicketUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE TicketUrl IS NOT NULL

PostgreSql

UPDATE IncidentDetails
SET TicketUrl = replace(TicketUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE TicketUrl IS NOT NULL

 

Updating Requirements

For requirements synchronized to JIRA, the JIRA information is stored in a number of  tables.  As with incidents above, it is necessary to search and replace the values of the old server name with the new server name. 

You will need the assistance of your DBA to run these queries against your Enterprise Tester Database.


SQL Server

 

ExternalSystemReferencesUPDATE ExternalSystemReferences
SET ExternalUrl= replace(ExternalUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalUrl IS NOT NULL

ExternalIconUrl

UPDATE ExternalSystemReferences
SET ExternalIconUrl= replace(ExternalIconUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalIconUrl IS NOT NULL

ExternalStatusIconUrl

 

UPDATE ExternalSystemReferences
SET ExternalStatusIconUrl= replace(ExternalStatusIconUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalStatusIconUrl IS NOT NULL

ExternalStatusIconLink

 

UPDATE ExternalSystemReferences
SET ExternalStatusIconLink= replace(ExternalStatusIconLink, 'http://jirasvr/', 'hhttp://mycorp.com/jira/')
WHERE ExternalStatusIconLink IS NOT NULL

 

Oracle

 

ExternalSystemReferencesUPDATE EXTERNALSYSTEMREFERENCES
SET EXTERNALURL = REPLACE(EXTERNALURL , 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE EXTERNALURL IS NOT NULL

ExternalIconUrl

UPDATE EXTERNALSYSTEMREFERENCES
SET EXTERNALICONURL = REPLACE(EXTERNALICONURL , 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE EXTERNALICONURL IS NOT NULL

ExternalStatusIconUrl

 

UPDATE EXTERNALSYSTEMREFERENCES
SET EXTERNALSTATUSICONURL = REPLACE(EXTERNALSTATUSICONURL , 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE EXTERNALSTATUSICONURL IS NOT NULL

ExternalStatusIconLink

 

UPDATE EXTERNALSYSTEMREFERENCES
SET EXTERNALSTATUSICONLINK = REPLACE(EXTERNALSTATUSICONLINK, 'http://jirasvr/', 'hhttp://mycorp.com/jira/')
WHERE EXTERNALSTATUSICONLINK IS NOT NULL

 

My SQL

ExternalSystemReferencesUPDATE ExternalSystemReferences
SET ExternalUrl= replace(ExternalUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalUrl IS NOT NULL

ExternalIconUrl

UPDATE ExternalSystemReferences
SET ExternalIconUrl= replace(ExternalIconUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalIconUrl IS NOT NULL

ExternalStatusIconUrl

 

UPDATE ExternalSystemReferences
SET ExternalStatusIconUrl= replace(ExternalStatusIconUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalStatusIconUrl IS NOT NULL

ExternalStatusIconLink

 

UPDATE ExternalSystemReferences
SET ExternalStatusIconLink= replace(ExternalStatusIconLink, 'http://jirasvr/', 'hhttp://mycorp.com/jira/')
WHERE ExternalStatusIconLink IS NOT NULL

 

PostgresSQL

 

ExternalSystemReferencesUPDATE ExternalSystemReferences
SET ExternalUrl= replace(ExternalUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalUrl IS NOT NULL

ExternalIconUrl

UPDATE ExternalSystemReferences
SET ExternalIconUrl= replace(ExternalIconUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalIconUrl IS NOT NULL

ExternalStatusIconUrl

 

UPDATE ExternalSystemReferences
SET ExternalStatusIconUrl= replace(ExternalStatusIconUrl, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE ExternalStatusIconUrl IS NOT NULL

ExternalStatusIconLink

 

UPDATE ExternalSystemReferences
SET ExternalStatusIconLink= replace(ExternalStatusIconLink, 'http://jirasvr/', 'hhttp://mycorp.com/jira/')
WHERE ExternalStatusIconLink IS NOT NULL

 

Log messages stored for synchronization may also contain hyperlinks. These can be replaced as follows:

Database Type

 

Sql Server

UPDATE ExternalSystemEvents
SET Message= replace(Message, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE Message IS NOT NULL

Oracle

UPDATE EXTERNALSYSTEMEVENTS
SET MESSAGE = REPLACE(MESSAGE , 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE MESSAGE IS NOT NULL

MySql

UPDATE ExternalSystemEvents
SET Message= replace(Message, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE Message IS NOT NULL

PostgreSql

UPDATE ExternalSystemEvents
SET Message= replace(Message, 'http://jirasvr/', 'http://mycorp.com/jira/')
WHERE Message IS NOT NULL

 

Completing Process

Once you have searched and replaced all the data, execute SELECT statements across the tables you have updated and confirm the URLs have been replaced correctly.

We suggest running the following statement:

 

SELECT TicketUrl FROM IncidentDetails WHERE TicketUrl IS NOT NULL

 

Next, try some of the TicketUrl values generated within your browser to confirm that you get back the expected issue (to confirm the URL is not malformed).

Once you are happy with the data updates, you can follow these steps to bring everything back online: