in all of my findings, every question included modification to jiraissue table as well. In my case, I only need to delete records from the two tables and I have the proper relationship and know what I'm doing. I have also tested this and validated that removing the record from both tables and refreshing the JIRA Issue (UI) would instantly show the correct result. I follow by adding the same data back (I didn't have to be concerned about the next ID in these tables since I re-inserted the same records) and the JIRA UI showed the data back in the history again. I have the need to delete some 147K records created from the interface between JIRA and ET (Enterrpise Tester). Every time someone made a change in JIRA, it triggered the issue record to be selected to get refreshed on ET and since on some projects we have one JIRA to 3 to 5 ET projects, the number of these worthless etsync messages are inserted at very rapid way and obscure the actual user history. So my questions are: * assuming I have made backups of the two tables (and I do have backup of the DB as well), can I delete the records from these two tables and NOT have to re-index or stop/start JIRA? again, my testing indicated that I did not and I'm assuming that because I'm dealing with history/logs that JIRA is NOT caching these two tables as it would all other major tables. * if the answer to the above is YES, I was planning then to write a scheduled job to delete daily the newly created records by etsync. I didn't want to write a trigger but rather Delete n-1 where n is today's date and therefore not creating any conflicts/deadlocks with any live activity that JIRA may be doing to thess tables. FYI - we have submitted support/solution requests to both Atlassian and Catch software and neither have a solution for us to stop these etsync messages to appear in the history. Please note that these are history records and not the comments where the configuration tools have the option to avoid writing comments during a sync. a sample message looks like this: ID groupid FIELDTYPE FIELD OLDVALUE OLDSTRING NEWVALUE NEWSTRING 359617 285217 jira RemoteIssueLink NULL NULL 14304 This issue links to "TEL11 (Enterprise Tester)" Thanks Anatole

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Does SQL Delete of changegroup and changeitem require a re-index?

anatole farci

in all of my findings, every question included modification to jiraissue table as well. In my case, I only need to delete records from the two tables and I have the proper relationship and know what I'm doing. I have also tested this and validated that removing the record from both tables and refreshing the JIRA Issue (UI) would instantly show the correct result. I follow by adding the same data back (I didn't have to be concerned about the next ID in these tables since I re-inserted the same records) and the JIRA UI showed the data back in the history again.

I have the need to delete some 147K records created from the interface between JIRA and ET (Enterrpise Tester). Every time someone made a change in JIRA, it triggered the issue record to be selected to get refreshed on ET and since on some projects we have one JIRA to 3 to 5 ET projects, the number of these worthless etsync messages are inserted at very rapid way and obscure the actual user history.

So my questions are:

assuming I have made backups of the two tables (and I do have backup of the DB as well), can I delete the records from these two tables and NOT have to re-index or stop/start JIRA? again, my testing indicated that I did not and I'm assuming that because I'm dealing with history/logs that JIRA is NOT caching these two tables as it would all other major tables.
if the answer to the above is YES, I was planning then to write a scheduled job to delete daily the newly created records by etsync. I didn't want to write a trigger but rather Delete n-1 where n is today's date and therefore not creating any conflicts/deadlocks with any live activity that JIRA may be doing to thess tables.

FYI - we have submitted support/solution requests to both Atlassian and Catch software and neither have a solution for us to stop these etsync messages to appear in the history. Please note that these are history records and not the comments where the configuration tools have the option to avoid writing comments during a sync.

a sample message looks like this:

ID groupid FIELDTYPE FIELD OLDVALUE OLDSTRING NEWVALUE NEWSTRING
359617 285217 jira RemoteIssueLink NULL NULL 14304 This issue links to "TEL11 (Enterprise Tester)"

Thanks

Anatole

Accepted answers

anatole farci

After testing and checking the sizes of the index folders and files before and after SQL Delete of 146K entries in test instance, including with and without stop/start, my conclusion was that it did not have any impact on the index and didn't need to have the JIRA instance to be stopped nor the index be reindexed. Last Sunday, I successfully performed the operation on the production (after backing up DB and the two tables) and everything worked as expected.

That said, I do suggest that whoever is trying to make backend DB DML operations to follow Atlassian and Nic's suggestion. PS: Atlassian support didn't have enough knowledge to sway either direction but elected to be on the safe side. They did however felt that reindex would not be required since these are historical tables.

Again, index table usage can change from one JIRA version to another and while in my current 6.0.4 everything worked without stop/start or reindex, please do your own homework prior to any backend operation or if you are not comfortable, then follow the standard process. I won't be the one telling you that QTips are OK to clean ears but you know where I'm going with this.

All comments

Nic Brough -Adaptavist-

This is actually a really simple one. If you delete these records via SQL, then you 1. Must have Jira shutdown. 2. Must have a full, proven, backup of the database and 3. Must re-index after restarting. It does cache and index this data.

anatole farci

Hi Nic,

I'm fully aware of the 3 steps and I have done them for my other JIRA SQL changes including moving users from one user directory to another as well as updating the local groups, etc. successfully. In my other testing, I have found that SQL back-end changes did not reflect in the UI immediately and therefore required a restart and some times, it may NOT require a reindex as not every SQL update needs a reindex.

In my specific question above, since I can see my immediate modification (Deletion) in the backend from my browser, I'd like to confirm if a reindex or/and stop/start is needed.

I'm a long time developer and have done many backend changes with other tools such as RTC, ClearQuest, etc. and I would take proper measures, where needed, to make sure I don't corrupt the java cache/indices but I just have a feeling that these transactions are useless and worthless, noone will EVER search for them, therefore I don't care if the index might get confused (but I'll verify in my pre-prod if deleting the records and then searching for them would give me any strage results). Not everything is indexed and I have a feeling that perhaps I'd be safe to perform my operation succesfully.

I'll perform additional testing and will report back my results but I was hoping to hear from Atlassian or someone who has made change JUST to these two tables and if my conclusion and findings are accurate. There is a difference between doing the three steps above for a SQL change to be on the safe side vs. knowing that it may not be required for a specific case. I have found many articles but all but one included additional tables specifically jiraissue. One article actually would insert or update into the two tables using a cron job and never mention any need for reindex.

Nic Brough -Adaptavist-

You asked for confirmation. In my experience, yes, you need to be offline. I have seen jira systems damaged by direct SQL access to those tables while it is running. Which confirms that you need to do it.

anatole farci

Nic Brough -Adaptavist-

> reindex would not be required since these are historical tables.

Oh, that's wrong.

If you don't re-index, then searching the history won't be accurate.

anatole farci

Nic,

To be on the safe side, we do reindex on regular basis. But in re: to your comment, you should be able to verify it yourself that entries in the History are not indexed or searched. I just went and found an open issue assigned to me. In the History, my project manager removed a component and the history logged it in the Original Value but nothing in the New Value. So if I search for it but the issue was not displayed. I tried to look for it with advanced option and could not find a way to find it.

I'm a newbie and I'm sure there is a way to search for History entries and if you walk me thru, I would like to verify it. I'd learn something new and would appreciate it. My role have been mainly deployment and other support efforts. That said, I would also perform backend profile on my SQL Server to see if your steps forces JIRA to search the DB tables rather than the local indices.

In my testing, while the indices are in binary and the file names gets changes, I mainly relied on the size of each folder and number of files. I would think that rmoving 146K entries would have a significant change in the indices no matter how efficient the indexing might be by reusing some repeated text.

Again, I'm available to do some additional verification if you provide me the JIRA side search of History and I'll get back to you if the search was done against the tomcat index or the DB table directly.

Nic Brough -Adaptavist-

Right, dead simple.

I've just done this on a test system - made a simple change to an issue's custom field. Stopped Jira, deleted the changeitem and changegroup entries for my change and restarted Jira.

I can search for "customfield X was Y" and the issue is listed in the results. When I go into the issue and look at it the history tab, there is nothing there. So the search is broken.

Any change to the issue and it drops out of the search results, because the index is updated when issues are changed.

So, you have to re-index to get the search to work. Possibly not every case, granted, but I've just proved your assumption that you don't need to index to be wrong.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

jira-administration

index