-
Incident report
-
Resolution: Unsupported version
-
Critical
-
None
-
1.8.17, 2.0.8
-
Ubuntu 12.04, MySQL 5.5.31 (distribution package), Node based Setup (two nodes), 6 proxies
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.