Issue 281 Imported Comments Never Arrived - edmozley/freeitsm GitHub Wiki

๐Ÿ” #281 Comments imported from Jira never arrived on a fresh install

GitHub #281 ยท Tickets, External issue trackers ยท fixed in 5.0.0 (#2779)

What you saw

With Import comments switched on for a Jira or Azure DevOps connection, comments written on the linked issue never appeared as notes on the ticket. Nothing on screen said why. On some older installs it worked - which made it look like a problem with the connection rather than with FreeITSM.

What was wrong

A comment from Jira has no FreeITSM author, so the import saved its note with author 0. But a note's author is checked against the analysts table (the fk_notes_analysts foreign key), and there is no analyst 0, so the database refused the note.

Every install created from database/freeitsm.sql has that check, which is every fresh install. Older installs that grew their tables through Database Verify never got it, so there the note went in - with an author of 0. That is why it "worked on some installs".

It was worse than a refused note

Before writing the note, the import records the comment in a list of comments already seen (integration_comment_map), so the same comment is never imported twice. That record went in first, and stayed when the note was refused. So each failed comment was then marked as already imported - and skipped on every poll after. Even with the database fixed, those comments would never have come in.

The fix
A note with no author is NULL ticket_notes.analyst_id now allows NULL, meaning System - the same rule history entries (ticket_audit) already follow. The foreign key is unchanged: a NULL never breaks it.
The import writes NULL integrationsApplyCommentEvent() saves the note with no author.
Both or neither The "already seen" record and the note are written in one transaction. If the note is refused for any reason, the record goes too, and the comment is tried again on the next poll.
Database Verify tidies up It relaxes the column, turns old author-0 notes into NULL, and removes "already seen" records that have no note - so a refused comment can be collected again.

Nothing that shows notes needed to change. Every place that reads a note's author already joined analysts with a LEFT JOIN, and a note with no author already showed as System (or as the tracker's name, for an imported comment). The REST API already returned "analyst": null for it.

Three other places had been written around the old rule - a 3CX call note, a note from mandatory fields, and the note when a requester closes a ticket on the portal. They behave exactly as before; only their comments, which said the column could not be NULL, were corrected. Whether automation should write real notes is #268, a separate decision.

๐Ÿ“ Files

File Change
includes/integrations/integrations.php NULL author; the map row and the note in one transaction
api/system/db_verify.php relax the column, 0 โ†’ NULL, remove stuck map rows
database/freeitsm.sql, includes/db_verify_schema.php analyst_id INT NULL
api/tickets/get_notes.php, includes/integrations/threecx.php, includes/services/mandatory_fields.php, api/self-service/close_ticket.php comments only
tests/tracker-comment-import.php new

How it was proved

tests/tracker-comment-import.php, against a throwaway database created from freeitsm.sql - so it has the foreign key, like a fresh install:

  • On the old code: 8 of 13 checks failed - the import was refused with "a foreign key constraint fails ... fk_notes_analysts", and the same comment was then "skipped as already imported" although it had never arrived. That is the bug, reproduced.
  • On the new code, before Database Verify: the note was still refused (the column was still NOT NULL) - but the second try was not skipped, proving the transaction already stops the stuck record.
  • After Database Verify: 13 of 13. The note arrives as System with the comment's text; a second import is skipped; a note refused for another reason (a ticket that does not exist) still raises the error and leaves no record behind; an old author-0 note becomes NULL; a stuck record is removed while a good one is kept.

The analyst-lifecycle and Database Verify index suites still pass.

What this means for you

  • Run Database Verify after upgrading to 5.0.0 (System โ†’ Database Verify). From then on, comments import.
  • Comments lost before the fix are still in Jira. Jira is asked only for recent changes (up to the last 24 hours), so older lost comments will not come back by themselves - but nothing in Jira was touched, and a new comment on the issue is imported as normal.

Related