JIRA reuses old project id

[Sebastian]

Hello,

we recently moved from JIRA standalone Software 7.x to 8.5.1 dockerized (official atlassian image). Service desk is installed as plugin in Version 4.5.1. Host is running on ubuntu 18.04 and the database is mysql 5.7 (as a container aswell) with all modification necessary for JIRA mentioned in the atlassian knowledge base.

We moved the installation by creating a XML backup and re-importing it into the new container installation.

However we are facing a strange issue with the service desk. We've configured the service desk to automatically creating issues from incoming mails. This worked flawlessly in the past. But now the Service Desk start's to create "issues" based on older project id. For example currently the pcounter is set to 227. I just sent a mail, and JIRA replies to that mail with a new issue with ID 223. Also the webhook for mattermost is triggered and show's a new issue (New issue: SUPPORT-223). 

First I was thinking that JIRA probably overwrites old issues, but this doesn't happen either. The ticket is just lost. 223 in the database still belongs to the original issue.

I already tried to clear the plugin-cache and run a re-index. Sometimes it is even working and he is going to create newer issues with and ID above pcounter. All I found in the log's are these 2 messages that could probably belong to that issue:

2019-11-25 10:54:59,919 Caesium-1-2 WARN <Customer-Mail-Address> [c.a.jira.ofbiz.ConnectionPoolHealthSqlInterceptor] Dangerous use of multiple connections: taken => count=3; marks=[2-3]; pool=3/20
java.lang.AssertionError: Explicit stack trace requested

and that one:

2019-11-25 10:55:01,588 PsmqAsyncExecutors-job:thread-1 DEBUG <Customer-Mail-Address> [c.a.activeobjects.osgi.ActiveObjectsServiceFactory] getService bundle [com.atlassian.servicedesk.servicedesk-lingo-plugin]

We are really thankful  for every kind of help!

Best Regards,

Sebastian

[Andy Heinzer]

Hi Sebastian,

I understand that you have recently migrated and upgrade your Jira instance.  But that since this time, you're seeing some unexpected values in the database, specifically in regards to the pcounter field that exists in the project table.

This field is expected to correspond to the most recent issue created in that project in a sequential manner.  Given what you have told us so far, I would agree that there is some problem here in regards to the data integrity.  I would be interested to see if you can run the following SQL query to see if we can help identify which project(s) might be showing this data problem:

SELECT ji.project, count(*), p.pcounter, p.pname, p.pkey FROM jiraissue ji
JOIN project p ON p.id = ji.project
GROUP BY ji.project, p.pcounter, p.pname, p.pkey;

I know this query works in postgresql, it might need to be tweaked a bit for mysql.  This query will return 5 fields.  Normally we would expect that the count and pcounter fields in this query to be the same values.  If they are not, then it would tend to indicate there might be some data missing or corrupted here.

I'm afraid the pair of warning messages you included here are not clearly related to this problem as far as I can see.  These WARN and DEBUG messages are actually very common across Service Desk Jira sites.  What I would like to do from here would be to take a closer look at the initial XML import process when you first tried to migrate this data over to this new system.  I suspect that there might have been some error in the import that wasn't displayed to an end user in the browser, but would still be included into the logs.

Specifically I'd like to try to take a closer look at the $JIRAHOME/log/atlassian-jira.log file and any older versions of that which tend to just get appended with a .1, .2, .3, etc as the logs roll through.   Hopefully we still have logs from the time of the import.   I'm looking for some kind of ERROR level warning, perhaps a SQLException in nature that could have prevented some data from being imported during the migration/upgrade. 

If you can share the log with us here, that would be helpful.  However our Community site won't permit file uploads apart from images.  One way though would be to use a service like Dropbox, or Google drive and then share a link to that download with us here.  I'd be happy to take a closer look at the logs to see if we can find a clear indicator of the source of the problem.

Please let me know if you have any questions or concerns with this approach, as well as if you can share with us the results of the SQL query.

Thanks

Andy

[Andy Heinzer]

Actually, that SQL query is flawed, it would only work if you never deleted any issues from that project.  Instead try this one:

SELECT ji.project, max(ji.issuenum), p.pcounter, p.pname, p.pkey FROM jiraissue ji
JOIN project p ON p.id = ji.project
GROUP BY ji.project, p.pcounter, p.pname, p.pkey;

Which should be more accurate, but could still be slightly off even in a health data environment, but only if you have deleted the most recently created issue in a project.  It's looking for the most recent issuenum and grouping it on a per project basis.   I would expect the highest number in a project to correspond with the pcounter value here.

[Sebastian]

Hi Andy,

first of all: thank you for your detailed answer! The query works pretty flawlessly (max ji.issuenum and pcounter are the same currently for the service desk, both 236). I do have the feeling that the issue belongs to our webhooks. We have had a webhook that have been triggered even when it has been disabled explicitly. May I should share the support ticket number with you that you can have a look into it?

I first need to clarify which kind of logs I'm allowed to share due to the german privacy law.

Thank you!

Best Regards,

Sebastian

[Andy Heinzer]

Hi Sebastian,

I'm not sure I understand what webhooks would have to do in regards to the initial reported problem here.  I did not realize you had a support case yet.  In this case, I think it might be best to continue to work that support case, since Community doesn't really have the tools for securing your private data like our primary support portal does.

Perhaps once that support case has a resolution, we can then share that solution on this Community thread so that others who might have this same problem can find an answer more easily.

Regards,

Andy

[Noni Khutane]

Hi Everyone

What was the conclusion on this.

Regards,