Deleted issues still exist in the database

Grace Dehergne_6efhp3

Hello , 

 

We are building customized metrics dashboards (something that JIRA or off the shelf applications can't provide)  using Tableau and Snowflake (with data coming from Jira).  For that matter our IT  team populates Snowflake with Jira Tables. However, the deleted issues still exist in the database whereas in the Jira UI, you cannot see it anymore.  It makes our metrics report unreliable.  How can we know (field value or other indication) that the issue has been deleted?

Radek Dostál

First things first, you're talking about external apps, most people here don't know how they work, or what sort of connectors they are using, nor what the actual data flow is between them.

I can infer from this that they're reading data from the database, which is the first semi faulty step since there are several better ways to read the data, which coincidentally is what Jira is already doing, so now there are multiple apps reading and interpreting the data from the db in their own way. I'm not saying it can't be done nor that there never ever could be the use case for it, but I am trying to indirectly say that's a bad idea on principle.

If you claim they still exist in the database, it would be useful to us to know what specific tables you have in mind, or what makes you think that is the case. As you surely know, as you're tinkering with the db, Jira has many tables and they often have to be joined to make any sense of them.

I wouldn't be too surprised if the data was being imported incrementally and old one was not deleted/discarded in those other apps, because they surely should be deleted from Jira's db if the issue is deleted. It won't be deleted when the issue/project is archived, but we are back to using table joins with conditions.

Long story short, such questions are too generic to answer, so we will need a lot more details starting with what tables you see still contain data for deleted issues. Assuming that jiraissue table is the primary one, which stitches everything together, a simple

SELECT p.pkey || '-' || ji.issuenum as "Issue Key" FROM jiraissue ji JOIN project p ON p.id = ji.project WHERE ji.id = ?

would confirm whether such an issue exists in the database, assuming it is being shown in other apps.