Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-65870

JIRA project import creates duplicate entries in the label table of the SQL database

XMLWordPrintable

      Summary

      JIRA project import creates duplicate entries in the label table of the SQL database

      Environment

      JIRA 7.x

      Steps to Reproduce

      1. create a project in JIRA with at least 1 issue
      2. Give this issue a label, in this case used example label called 'special'
      3. After this label has been confirmed, go into SQL and run command: 
        select * from label where label='special';

        - This will return a single entry for this one issue with this label

      4. Now generate an XML backup
      5. Then go back to the JIRA issue, remove that label from the issue
      6. Re-run SQL query, you should have 0 results
      7. Then delete the entire project from this JIRA instance
      8. then create a new project with the same project name/key as the previously delete project
      9. Then use the project import tool to import that previously deleted project (using the XML backup you created)
      10. After this is complete, run the SQL query again: 
        select * from label where label='special';

      Expected Results

      We expect to only find the single entry in this table for that single issue with that special label

      Actual Results

      We actually find there are two entries in this table for that issue in regards to this single label. Not only that, but all labels that were imported as part of issues imported in this project import appear to have duplicate entries in the label table of the database. The ID column is incrementing and is unique, but there are multiple entries with the same issue value and the same label value that appear to be caused directly by this project import process.

      Notes

      • Note that the UI of JIRA only shows a single label for this issue. So there is not a problem that way.
      • But in cases where users are running SQL queries or other external reporting on their databases for this table, this is a problem because the project import appears to be creating duplicate data that will mess with reporting.
      • This was not the case in regards to project imports in JIRA 6.x versions. It appears that this is something new as of the 7.x line of project importing.

      Workaround

      It might be possible to use some fancy SQL query to locate and remove the duplicates, but I'm not sure the specifics of how this could be done without risking data loss/integrity issues.

            [JRASERVER-65870] JIRA project import creates duplicate entries in the label table of the SQL database

            Sabine Van Regenmortel added a comment - - edited

            We encountered exactly the same problem (Jira 8.02). Indeed, by reproducing the problem with a simple project consisting of a few issues, each label was inserted twice in PostgreSQL. The Jira UI shows it only once.
            We have thousands of labels to import very soon, so I hope there is no limit on the label ID counter in PostgreSQL? For even when deleting the double ones by sql, the counter will run high.

            1. I can also do an SQL insert on the production Jira, update the counter in the sequence_value_item table and restart.
            2. But I preferred to do this SQL insert on a test Jira and do the project import on the production Jira.

            What is safer, 1 or 2?

            Sabine Van Regenmortel added a comment - - edited We encountered exactly the same problem (Jira 8.02). Indeed, by reproducing the problem with a simple project consisting of a few issues, each label was inserted twice in PostgreSQL. The Jira UI shows it only once. We have thousands of labels to import very soon, so I hope there is no limit on the label ID counter in PostgreSQL? For even when deleting the double ones by sql, the counter will run high. 1. I can also do an SQL insert on the production Jira, update the counter in the sequence_value_item table and restart. 2. But I preferred to do this SQL insert on a test Jira and do the project import on the production Jira. What is safer, 1 or 2?

            James Chen added a comment -

            We  have observed the same issue while using project import to migrate JIRA project from another instance to another instance.

            We also notice couple of unexplained behaviors regarding to label entries in the DB table level.

            1.If someone add/update/delete the label field from the UI and save, it appears to remove any duplicate entries from the table and only keeps the unique label name.
            2. If someone to delete an issue from the UI, all associated attributes for the issue are removed from DB tables except for the label entries are still kept  in the LABEL table, where issue column values(that point to JIRAISSUE.ID) are no longer valid.

             

            Above described behaviors could potentially introduce data integrity issues in the DB level.

            James Chen added a comment - We  have observed the same issue while using project import to migrate JIRA project from another instance to another instance. We also notice couple of unexplained behaviors regarding to label entries in the DB table level. 1.If someone add/update/delete the label field from the UI and save, it appears to remove any duplicate entries from the table and only keeps the unique label name. 2. If someone to delete an issue from the UI, all associated attributes for the issue are removed from DB tables except for the label entries are still kept  in the LABEL table, where issue column values(that point to JIRAISSUE.ID) are no longer valid.   Above described behaviors could potentially introduce data integrity issues in the DB level.

              Unassigned Unassigned
              aheinzer Andy Heinzer
              Affected customers:
              3 This affects my team
              Watchers:
              6 Start watching this issue

                Created:
                Updated: