Custom field context projects were missed

Question

HI Community,

I tried to add one project in the context, in one of the field unfortunately I missed to press the ctrl button while selecting the projects so I lost all'of them.

Is there a way to see or get back those missing projects?

Thanks in advance.

Regards,

Ganesh

Ganesh_6lvvfv · Answer

@Radek Dostál  Much appreciated for your response on this issue.

Coming to the solution which you have suggested , will defiantly try this in our database, Lets see how it works. Thanks.

Please support us in future as well. thanks.

Radek Dostál · Answer

Ha, this is what I had in mind.

Please note I am no dba and this is tested with Postgres (may be different in mysql etc.):

# SELECT DISTINCT p.pname,p.pkey FROM project p JOIN jiraissue i ON p.id = i.project JOIN customfieldvalue cfv ON cfv.issue = i.id WHERE cfv.customfield = 10407 AND cfv.stringvalue IN (SELECT CAST(id AS text) FROM customfieldoption WHERE customfieldconfig = 10611);
       pname        | pkey
--------------------+------
 My project name123 | MP
(1 row)

Explanation:

cfv.customfield = 10407

-- this is the id of the custom field I tested with

customfieldconfig = 10611

-- this is the CONTEXT id I tested with

So it requires that you know both ids to give this a try. Yes yes it's an ugly bunch of joins but it works, at least it seems to.

As usual - always always backup your database and do not trust what people paste, it may have unforeseen consequences. Frankly as this is just a select I don't see how it could backfire, but hey, important to put that disclaimer in each sql comment :)

Radek Dostál · Answer

Unless you have a backup which you could restore somewhere else and then re-create it, then it's indeed lost.

However if the field is option-based, you might still be able to salvage this with some sql sorcery..

Each CustomFieldValue refers to IssueId, each Issue with IssueId contains ProjectKey

Each CustomFieldValue which is option-based refers to that OptionId

Each OptionId refers to FieldConfig (="context"); so if you were able to collect all customfieldvalues for that context, from that get to the list of projects, you might be able to salvage it.

However, Jira also allows invalid options so this would only really work if you have not created invalid option values on issues in the past (for instance, because somebody switched context improperly). Sadly I have no such script at hand, but it just occurred to me as that it might be one of the ways without a backup.