Have you been using JIRA for more than a year, or are you a power user of JIRA? That is, have you performed huge customizations, created numerous plugins, used lot of use cases, and so on? Then it is very likely that you have come across this scenario. You want to move the values from an old custom field to a new field.
JIRA doesn't have a standard way of doing this, but you can achieve this to an extent by modifying the JIRA database. However even with SQL, there are some restrictions for doing this.
The first and most important thing to check is that both the fields are compatible. You can't move the values from a text field to a number field without extra checks and validations. If there is a value such as 1234a stored in one of the issues, it can't be stored as a number field as it is not a valid number. Another example is converting a multiline text field to a single-line text field. It is not possible, as the large value in a multiline text field will not fit into the 256 characters of a single-line text field. Similar logic applies to all the field types.
Let us see the migration of compatible types and discuss a few other scenarios in this recipe.
Let us assume you have two text fields, Field A and Field B. We need to migrate the values on every issue from Field A to Field B. Here are the steps that should be executed:
- Shut down the JIRA instance.
- Take a backup of the database. We can revert to this backup if anything goes wrong.
- Connect to your database.
- Execute the following SQL query:
Update customfieldvalue set customfield = (select id from customfield where cfname='Field B') where customfield = (select id from customfield where cfname='Field A')
The query assumes that the custom field names are unique. If you have more than one custom field with the same name, use the IDs instead.
- Commit the changes.
- Disconnect from the database.
- Start JIRA.
- Re-index JIRA by going to Administration | System | Advanced | Indexing.
That should do it! Verify your changes both on the issue and in the filters.
All that we did here was change the custom field ID in the customfieldvalue table. The other steps are standard steps for executing any SQL in JIRA.
Remember, if you have two custom fields with the same name, make sure you use the correct id instead of finding it using the name in SQL.
Now, this will work fine if both the fields are of the same type. But what if you want to move the values from one type to another? This may not always be possible, because some of the values in the customfieldvalue table may not be compatible with other custom field types.
Let us consider migrating a normal text field to a text area custom field. The value in the text area custom field is stored as a CLOB in the textvalue column in the database. But the value in a normal text field is stored as VARCHAR 2(255) in the stringvalue column. So, when you convert, you need to update the custom field ID, read the VARACHAR2(255) value from the stringvalue column, and store it in the textvalue column as a CLOB. You also need to set the no-longer-used stringvalue to null in order to free space in the database.
In this example, if you are trying the reverse order, that is, migrating from text area to text field, you should take into consideration the length of the text, and remove the extra text, as the text field can hold only up to 256 characters.
You can find the data type for the various custom fields by looking at the getDatabaseType method. For a TextField, the method looks as follows:
Protected PersistenceFieldType getDatabaseType(){
return PersistenceFieldType.TYPE_LIMITED_TEXT;
}Other available field types are TYPE_UNLIMITED_TEXT (for example, text area), TYPE_DATE (date custom field), and TYPE_DECIMAL (for example, number field).
Sometimes we just need to change the type of a custom field instead of creating a new one and then migrating the values across. Let us quickly see how to do it.
In this case, the table that needs to be updated is the CustomField table. All we need to do is to update the customfieldtypekey. Just set the new custom field type key, which will be {YOUR_ATLASSIAN_PLUGIN_KEY}:{MODULE_KEY}.
For a text field, the key is com.atlassian.jira.plugin.system.customfieldtypes:textfield.
For incompatible types, we need to consider all the aforementioned cases and update the CustomFieldValue table accordingly.
- The Retrieving custom field details from database recipe in Chapter 10, Dealing with the JIRA Database