Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-7025

Failed database migration due to a unique index between 1.8.17 and 2.0.8

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Critical
    • Resolution: Unsupported version
    • Affects Version/s: 1.8.17, 2.0.8
    • Fix Version/s: None
    • Component/s: Server (S)
    • Environment:
      Ubuntu 12.04, MySQL 5.5.31 (distribution package), Node based Setup (two nodes), 6 proxies

      Description

      We are trying to perform a upgrade from 1.8.17 to 2.0.8 and we experience problems while applying the database patch.

      We performed the upgrade like described in:
      https://www.zabbix.com/documentation/2.0/manual/installation/upgrade

      While executing the "patch.sql" we get the following message:

      # mysql zabbix20 < patch.sql
      ERROR 1062 (23000) at line 1119: Duplicate entry '200100000000278-200100000001803' for key 'items_applications_1'
      

      The problem is caused by the following statement:

      CREATE UNIQUE INDEX items_applications_1 ON items_applications (applicationid,itemid);
      

      I analyzed the table "items_applications":

      mysql> select applicationid,itemid, COUNT(*) as dupes from items_applications GROUP BY applicationid,itemid  HAVING dupes > 1 ORDER BY dupes DESC;
      +-----------------+-----------------+-------+
      | applicationid   | itemid          | dupes |
      +-----------------+-----------------+-------+
      | 200100000000302 | 200100000001932 |     2 |
      | 200100000000584 | 200100000003503 |     2 |
      | 200100000000690 | 200100000006170 |     2 |
      | 200100000000754 | 200100000006530 |     2 |
      .....
      .....
      185 rows in set (0.00 sec)
      
      
      mysql> select * from items_applications where applicationid=200100000000302 and itemid=200100000001932;
      
      +-----------------+-----------------+-----------------+
      | itemappid       | applicationid   | itemid          |
      +-----------------+-----------------+-----------------+
      | 100100000050485 | 200100000000302 | 200100000001932 |
      | 200200000004417 | 200100000000302 | 200100000001932 |
      +-----------------+-----------------+-----------------+
      2 rows in set (0.01 sec)
      
      

      All rows which might cause problems with the unique index created by the migration script seems to have:

      • exactly two duplicates
      • all duplicate entries are looking like the output of the second statement (they have a leading 1001 and 2002 in column "itemappid")

      I removed "DROP INDEX items_applications_1.. " and "CREATE UNIQUE INDEX items_applications_1..." from "patch.sql" and kept the existing non-unique index on "applicationid" and "itemid".
      Is it really needed to have a unique index here? What can be a solution for this conflict?

      Please contact me - i can support you in examining the situation.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              scoopex Marc Schoechlin
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: