Jira custom fields structure on database

ranarivelo

Hello everyone,

 

I am trying to retrieve all values for a list of custom fields using SQL. Some of these custom fields have not values stored on CUSTOMFIELDVALUE table. For label types, it is stored on table named LABEL. I can't find where are table associate with these types:

 

- com.atlassian.jira.toolkit:comments

- com.atlassian.jpo:jpo-custom-field-original-story-points

- com.atlassian.servicedesk.approvals-plugin:sd-approvals

- com.go2group.jira.plugin.synapse:requirementField

- com.onresolve.jira.groovy.groovyrunner:scripted-field

- com.xpandit.plugins.xray:requirement-status-custom-field

 

Thanks in advance,

 

RANARIVELO

Andy Heinzer

Hi,

I understand you are looking to better understand where custom field data for several different field types.  As you have already noticed there can be several different tables that can store different types field data in them.  This is even more common to see with the custom fields that plugins create.

I am afraid that I don't have a detailed answer that directly states the locations of each field here.  However I do have steps you can take to figure out which locations are in use by which custom fields.   Please see the guide in How to Enable Detailed SQL Logging in JIRA.  It explains how you can tune the logging in Jira to see the specific SQL statements that Jira is using to manage this data in SQL.

With the logging enabled, you can then just update a field on an issue in Jira that you want to know more about.  Then immediately turn off that SQL logging (this log will grow very large if left on, so I'd recommend only leaving it on long enough to update a field you want to know more about).

You can then open that log file in $JIRAHOME/log/atlassian-jira-sql.log

I would recommend searching specifically for phrases like UPDATE or INSERT as these are SQL commands that are used to store that data into the SQL database.  With this method you should be able to determine all the tables in use for each of these custom field types.

Please let me know the results.

Cheers,

Andy