[ZBX-7025] Failed database migration due to a unique index between 1.8.17 and 2.0.8 Created: 2013 Sep 19  Updated: 2017 Oct 17  Resolved: 2017 Oct 17

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 1.8.17, 2.0.8
Fix Version/s: None

Type: Incident report Priority: Critical
Reporter: Marc Schoechlin Assignee: Unassigned
Resolution: Unsupported version Votes: 0
Labels: dbpatches, distributed, sql, upgrade
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
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.

 Comments   
Comment by Oleksii Zagorskyi [ 2013 Sep 22 ]

ZBX-5125 can be related.

The "items_applications_1" index was mentioned there.
But looks like it was not included to fixes.

Comment by Oleksii Zagorskyi [ 2013 Sep 22 ]

But mixed node IDs for a single table row look indeed as another bug.

Comment by Marc Schoechlin [ 2013 Sep 23 ]

What can be the reason for mixed node IDs?
NOTE: We run a node based setup with two zabbix servers (1=Main Server, 2=Subordinate Server)

The following statement returns 273 rows on the main server which have mixed ids:

select itemappid,applicationid,itemid from items_applications where ((itemappid DIV 100000000000000) != (applicationid DIV 100000000000000)) OR ((applicationid DIV 100000000000000) != (itemid DIV 100000000000000));

If i remove the matched entries the patch.sql applies successfully!
Is it valid to drop these entries before running the migration script?
What is this table used for and what can be the consequences if i delete them?

Generated at Fri Apr 26 01:38:50 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.