Issue with Textfield Custom Fields Containing 'Dot' (.) during Jira Migration

Summary

During the migration process from on-premises Jira to Jira Cloud, certain Textfield custom fields that include special characters, such as 'dot', are converted into hyperlinks in the Cloud environment. This leads to data integrity issues as the links are invalid.

Cause

This behaviour is due to an existing bug identified in Jira Cloud: Custom fields of Text Field (single line) Appends Hyperlink when a Value is followed by a dot character on the New Issue View

Solution

To resolve this issue, you can append a space at the end of the custom field value before migrating. The customfieldvalue table stores the values of the custom fields, and it uses the custom field ID to map custom fields to their respective values. This adjustment can be made directly in the database. Below are the steps to follow:

• Stop Jira service

• Take backup of Jira database

   Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.

• First step is to identify the Customfield ID. You can find the custom field ID in one of two ways:

  • Via the Jira UI: Access the custom field from administration section (Settings → Issues → Customfield) and note the ID from the URL.

  • Using a Database SQL Query: Execute the following SQL query, replacing <customfield-name> with the name of the problematic custom field:

    Postgres DB

    1 SELECT id FROM customfield WHERE cfname = '<customfield-name>';

    MySQL DB

    1 SELECT id FROM customfield WHERE cfname = '<customfield-name>';

    Oracle DB

    1 SELECT id FROM customfield WHERE UPPER(cfname) = UPPER('<customfield-name>');

    MSSQL DB

    1 SELECT id FROM customfield WHERE cfname = '<customfield-name>';

• Once you have the custom field ID, you need to append a space to the values in the customfieldvalue table. You can do this by executing one of the following UPDATE queries:

  • To update the customfield values for all projects:

    Postgres DB

    1 UPDATE customfieldvalue SET STRINGVALUE = STRINGVALUE || ' ' WHERE customfield = <id> AND STRINGVALUE LIKE '%.%';

    MySQL

    1 UPDATE customfieldvalue SET STRINGVALUE = CONCAT(STRINGVALUE, ' ') WHERE customfield = <id> AND STRINGVALUE LIKE '%.%';

    Oracle DB

    1 UPDATE customfieldvalue SET STRINGVALUE = STRINGVALUE || ' ' WHERE customfield = <id> AND STRINGVALUE LIKE '%.%';

    MSSQL DB

    1 UPDATE customfieldvalue SET STRINGVALUE = STRINGVALUE + ' ' WHERE customfield = <id> AND STRINGVALUE LIKE '%.%';

    Replace <id> with the customfield id got from the above SQL query.

  • To update the custom field values for a specific project:

    Postgres DB

    1 UPDATE customfieldvalue SET STRINGVALUE = STRINGVALUE || ' ' WHERE customfield = <id> AND STRINGVALUE LIKE '%.%' AND issue IN (SELECT ji.id FROM project jp JOIN jiraissue ji ON jp.id = ji.project WHERE jp.pkey = '<Project key>');

    MySQL

    1 UPDATE customfieldvalue SET STRINGVALUE = CONCAT(STRINGVALUE, ' ') WHERE customfield = <id> AND STRINGVALUE LIKE '%.%' AND issue IN (SELECT ji.id FROM project jp JOIN jiraissue ji ON jp.id = ji.project WHERE jp.pkey = '<Project key>');

    Oracle DB

    1 UPDATE customfieldvalue SET STRINGVALUE = STRINGVALUE || ' ' WHERE customfield = <id> AND STRINGVALUE LIKE '%.%' AND issue IN (SELECT ji.id FROM project jp JOIN jiraissue ji ON jp.id = ji.project WHERE jp.pkey = '<Project key>');

    MSSQL DB

    1 UPDATE customfieldvalue SET STRINGVALUE = STRINGVALUE + ' ' WHERE customfield = <id> AND STRINGVALUE LIKE '%.%' AND issue IN (SELECT ji.id FROM project jp JOIN jiraissue ji ON jp.id = ji.project WHERE jp.pkey = '<Project key>');

    Replace <id> with the custom field ID you noted earlier and <Project key> with the key of the project you want to update.

• Start Jira service