Migrate custom field values for type change through SQL query?

Jira Development Jira Server
1

Hello, I usually use CSV or ScriptRunner’s built in script for copying custom field values, but I need to do a migration for over 200K issues. ScriptRunner can do the job, but it times out and we can’t benchmark the time (we know it’s several hours) in development so our governance team has given that option a red light and I’ve been asked to see if we can do it directly through MySQL. Two questions:

  • For this number of records, is SQL this the better migration option?
  • Can someone assist me with the proper SQL so that I don’t nerf our DB?
2

Hi @741,

of course - I can’t judge if your process is the right decision. There are of course multiple options. You could go with your existing approach, but instead run the update in batches.
ScriptRunner does not timeout in my experience in Jira, usually my log statements just continue to run fine. Updates through ScriptRunner will give you the necessary consistency which is harder to guarantee with database changes.

The database is possibly the fastest way, but also the one with the most potential to mess up. I currently have no idea how the fields are structured and how they store the data as you have not provided details. Essentially, I’d expect it to work roughly like this:

  • Identify the custom field to migrate
  • Identify the stored values
  • Write the necessary conversion in SQL
  • Stop Jira
  • Backup Jira
  • Then run an update on the database
  • Start Jira
  • Full reindex

This could look roughly like this (don’t have the necessary database layout right now):

INSERT INTO <table which stores custom field values> (<list of columns)
SELECT <list of columns - including any necessary data conversions / changes to use the right custom field>
FROM <table which store custom field values>
WHERE <condition to select only the needed customfield>
1 Like
3

Thank you very much for your answer. I just tested on 100K+ tables and it is definitely the better alternative. The update for 100K records took 10 seconds vs 3.5 hours using ScriptRunner and the inability to benchmark.

I ran the following command if anyone else stumbles on this thread:

update customfieldvalue a, customfieldvalue b set a.textvalue=b.stringvalue where a.issue=b.issue and a.customfield=123 and b.customfield=456;

123 is the new field (multiline text field) and 456 is the original field (simple textfield) to be copied.

@dennis.fischer is a background index OK? Would like to skip the additional downtime with a front index. And thanks again.

4

Well, I’m not sure if an update statement is sufficient. Why? This would only copy data if both customfields contain a value. Based on your description - this might not always be the case. Hence, why I used an insert statement - assuming that you created a new custom field and no values are populated.

A background index will be fine but it will take significantly longer until the search index is uptodate. If the tradeoff is acceptable, then run a background index.

1 Like
5

Thanks. Will do a background index.

Scriptrunner times out in both tested environments but still completed the job in the background. The problem is it took 3.5 hours for the 100K+ update which is a slice of our production data.

Thanks for letting me know that there might be issues with my query. I didn’t really understand your solution but it’s a learning curve issue. Would you kindly output your SQL solution based on the details provided below?

The conversion is a varchar (255) to a longtext string. In Jira the customfield is being converted from a single-line textfield to a multi-line textfield. So there will be no truncation or conversion issues.

The custom fields have a 1:1 relationship as the new custom field has been added to every screen under the existing custom field. And the new custom field id will have a NULL value in the textvalue field. Some of the original field stringvalue records may also be NULL.
The table to be updated:

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| ID          | decimal(18,0) | NO   | PRI | NULL    |       |
| ISSUE       | decimal(18,0) | YES  | MUL | NULL    |       |
| CUSTOMFIELD | decimal(18,0) | YES  |     | NULL    |       |
| PARENTKEY   | varchar(255)  | YES  |     | NULL    |       |
| STRINGVALUE | varchar(255)  | YES  |     | NULL    |       |
| NUMBERVALUE | decimal(18,6) | YES  |     | NULL    |       |
| TEXTVALUE   | longtext      | YES  |     | NULL    |       |
| DATEVALUE   | datetime      | YES  |     | NULL    |       |
| VALUETYPE   | varchar(255)  | YES  |     | NULL    |       |
| UPDATED     | decimal(18,0) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

Expected output before SQL update:

SELECT issue, customfield, stringvalue, textvalue 
    FROM customfieldvalue 
    WHERE customfield=123 
        OR customfield=456 
    ORDER BY issue 
    LIMIT 4; 
# output:
+-------+-------------+-----------------------------+
| issue | customfield | stringvalue  | textvalue    |
+-------+-------------+-----------------------------+
| 10130 |       456   | lorem ipsum  | NULL         |
| 10130 |       123   | NULL         | NULL         |
| 12179 |       456   | si dolor est | NULL         |
| 12179 |       123   | NULL         | NULL         |
+-------+-------------+-----------------------------+

Expected output after SQL update:

SELECT issue, customfield, stringvalue, textvalue 
    FROM customfieldvalue 
    WHERE customfield=123 
        OR customfield=456 
    ORDER BY issue 
    LIMIT 4; 
# output goal:
+-------+-------------+-----------------------------+
| issue | customfield | stringvalue  | textvalue    |
+-------+-------------+-----------------------------+
| 10130 |       456   | lorem ipsum  | NULL         |
| 10130 |       123   | NULL         | lorem ipsum  |
| 12179 |       456   | si dolor est | NULL         |
| 12179 |       123   | NULL         | si dolor est |
+-------+-------------+-----------------------------+
6

Ah okay. That is essentially looking good. You might want to run a few checks before and after doing the update, e.g. count the number of custom fields with a value before and after updating. Make sure that the data is consistent. And: Make backups.

1 Like