Changing Account Keys in JIRA Tempo Accounts

Recently at work, we painted ourselves into a bit of a corner with the way we set up our Tempo Accounts and found the need to change our Account Keys. There is no nice mechanism provided for doing this in the Tempo UI, and there is no supported or documented mechanism for doing this through the database. After much pain and anguish scouring the interwebs and meticulous investigation of the JIRA database structure, we finally came across a working solution.

I’m documenting said solution here for posterity.

Disclaimer

This is a potentially harmful process that directly modifies the JIRA database. It is not supported by either Atlassian or Tempo. The steps provided here are only applicable to JIRA v6.4.2 backed by MySQL and Tempo Accounts v1.5.1. If not using those versions or MySQL, it is possible that database structures have changed and thus the provided queries will not work as expected.

Spoiler Alert

The queries actually end up being very simple. The real difficulty in doing this was uncovering where Tempo stores the data, the table structures, and relationships between them all. As stated, there’s no nice documented way of performing this; this post on Atlassian Answers gave me the start I needed (thank you, Bjarni Thorbjornsson).

Changing the Account Key

Tempo stores the Account data in one of JIRA’s Active Object tables: AO_C3C6E8_ACCOUNT_V1. The Account key is stored in a column in this table, so this is a simple UPDATE query:

UPDATE AO_C3C6E8_ACCOUNT_V1 Accounts
  SET Accounts.KEY = newKey
  WHERE Accounts.KEY = oldKey;

Updating Worklog Accounts

We’ve set up a Worklog Attribute in our Tempo instance to link worklogs directly to Accounts. In the database, worklogs are linked to an Account by the Account’s Key rather than an internal ID. Thus, after updating our Account Keys in the previous step, any worklogs associated with that Account now have a reference to an undefined account, so we need to mend that relationship.

Worklog Attributes are related to worklogs through JIRA’s property tables. A Worklog is related to a Property Entry, which is in turn associated to a Property Value. The Property Value’s propertystring column contains the actual data for each Worklog Attribute associated to the Worklog as one String, something like:

+--------------------------------------------------------+
| propertyvalue                                          |
+--------------------------------------------------------+
| _NetSuiteProject_=INTADMIN                             |
| _NetSuiteProject_=INTADMIN                             |
| _NetSuiteProject_=INTADMIN,_NotBillable_=_NotBillable_ |

where _NetSuiteProject_ is the Key of the Worklog Attribute Account field, and INTADMIN is an example Account Key.

All we ultimately need to do is replace the old Account Key with the new one in this String. We accomplish that with the following query:

UPDATE propertyentry pe, propertystring ps, worklog w
  SET ps.propertyvalue = REPLACE(ps.propertyvalue, oldKey, newKey)
  WHERE w.id = pe.entity_id
    AND pe.entity_name = ‘Tempo.Worklog’
    AND pe.property_key = ‘Tempo.WorklogAttributes’
    AND pe.id = ps.id
    AND ps.propertyvalue LIKE CONCAT(‘%’, accountFieldKey, ‘=’, oldKey, ‘%’);

where accountFieldKey is the Key of our Worklog Attribute Account field. You can find the accountFieldKey by looking at the Worklog Attributes Administration page (/secure/admin/TempoAttributes.jspa) and copying the Key from the appropriate field.

Consolidating into a Stored Procedure

To make this nice and reusable, we encapsulated these two queries into a stored procedure.

CREATE PROCEDURE `updateAccountKey`(IN oldKey CHAR(255), IN newKey CHAR(255))
BEGIN
  UPDATE AO_C3C6E8_ACCOUNT_V1 Accounts
    SET Accounts.KEY = newKey
    WHERE Accounts.KEY = oldKey;
  UPDATE propertyentry pe, propertystring ps, worklog w
    SET ps.propertyvalue = REPLACE(ps.propertyvalue, oldKey, newKey)
    WHERE w.id = pe.entity_id AND pe.entity_name = 'Tempo.Worklog'
      AND pe.property_key = 'Tempo.WorklogAttributes' AND pe.id = ps.id
      AND ps.propertyvalue LIKE CONCAT('%', <accountFieldKey>, '=', oldKey, '%');
END

Then we generated a simple CSV that defined the mapping of old Key to new Key. From that, I was able to quickly create an SQL file that just called our stored procedure over and over for each Key update. Importing this SQL file into the database then gave us a nice, quick mass update of our Account data and relationships.

Wrap-Up

The changes will not appear to take effect in the UI immediately. If you check the Time Tracking for any updated Account, you will see several “Unknown Account” links in your time entries. My guess is that the database needs to be reindexed or the UI is caching the data somewhere. Restarting your JIRA instance forces everything to update, so I recommend doing this after you’ve updated all your Account Keys.

Hope this helps!