Error with duplicate values on tables "history_uint" columns "itemid", "clock", "ns".

[teab]

While performing upgrade from Zabbix 5.0 LTS to Zabbix 6.0 LTS, there is issue with actual data in table "history_uint" and new primary keys created by "history_pk_prepare.sql".

Tables "history*" are large (>500 GB), that's why before upgrade process I renamed them with suffix "_old_table" and created new empty tables with default naming and same structure. So upgrade process could be a lot faster. After successful upgrade tried to insert old data back with:

INSERT INTO history selec * from history_old_stable;

For almost all tables this approach worked, except "history_uint" - because of existing duplicate data on columns "itemid", "clock", "ns", which are used by new multicolumn primary key "history_uint_pkey".

Data cannot be inserted from old table "history_uint_old_table" into table "history_uint":

INSERT INTO history_uint SELECT * FROM history_uint_old_table;
ERROR:  duplicate key value violates unique constraint "history_uint_pkey"
DETAIL:  Key (itemid, clock, ns)=(1442, 1657527245, 687061700) already exists.

Found duplicate values with SQL:

SELECT itemid, clock, ns, COUNT(*) FROM history_uint GROUP BY itemid, clock, ns HAVING COUNT(*) > 1 ORDER BY COUNT desc;

They all come from one Zabbix item (Zabbix Agent active):

logrt[C:\app\file.log,"Query speed:"]

Issue arises sometimes when there is 100 new item values per second (it newer reaches more values - looks like limitation on Zabbix server side) from Zabbix Agent active and Zabbix server processes two of them on the same nano second.


What would You suggest as fix for this problem ?

[tim.mooney]

How many duplicate values do you have that are causing problems?

In your situation, I would probably just delete the duplicate values from the history_uint_old_table, and then re-do your INSERT INTO.

Alternately, you could update the "ns" (or clock, I suppose) to change it by 1 for each duplicate item in the old table. Hopefully that's enough to make the tuple of (itemid, clock, ns) unique for all values.

[teab]

How many duplicate values do you have that are causing problems?

In your situation, I would probably just delete the duplicate values from the history_uint_old_table, and then re-do your INSERT INTO.

Alternately, you could update the "ns" (or clock, I suppose) to change it by 1 for each duplicate item in the old table. Hopefully that's enough to make the tuple of (itemid, clock, ns) unique for all values.

Not much - varies between none at some days and around 30 at other days. Still that's enough to cause data inconsistency and issues with triggers.

Thanks tim.mooney for Your suggested solution, that might be workaround when migrating Zabbix, if history data for that item is not needed accurate. Already tried that on testing environment and for migration process it works. After migration problem still exists. There is bunch of error lines in database log files, that record won't be inserted because of duplicate value, because of primary key and there is some missing data. That will lead to data inconsistencies and possibly false trigger misfiring.

[tim.mooney]

After migration problem still exists. There is bunch of error lines in database log files, that record won't be inserted because of duplicate value, because of primary key and there is some missing data. That will lead to data inconsistencies and possibly false trigger misfiring.

You can try reporting that as a problem using the bug tracker at https://support.zabbix.com

It seems to me that when they added the primary key based upon time, they should have anticipated this possible issue and had some kind of workaround.

[teab]

You can try reporting that as a problem using the bug tracker at https://support.zabbix.com

It seems to me that when they added the primary key based upon time, they should have anticipated this possible issue and had some kind of workaround.

Thanks for advice.

Created new ticket - https://support.zabbix.com/browse/ZBX-21533 .

[cle]

Thanks for opening a bug report about that issue, however, that seems to have been closed already.

Ever since upgrading from 5.0.40something (i think) to 6.0.6 (and now 6.0.8) i'm seeing similar errors in zabbix_server.log (Alma Linux with MySQL 8.0.26, upgraded via the instructions including export and import with MySQLsh):

3050:20220909:092815.567 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '814356-1662708490-34000000' for key 'history_uint.PRIMARY'
3050:20220909:092815.578 skipped 35 duplicates
3048:20220909:092857.623 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '814320-1662708537-191000000' for key 'history_uint.PRIMARY'
3048:20220909:092857.626 skipped 1 duplicates
3051:20220909:092917.074 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '814356-1662708551-207000000' for key 'history_uint.PRIMARY'
3051:20220909:092917.078 skipped 28 duplicates
3051:20220909:092955.209 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '1079802-1662708594-915000000' for key 'history_uint.PRIMARY'
3051:20220909:092955.211 skipped 1 duplicates
3051:20220909:093017.443 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '814369-1662708612-817000000' for key 'history_uint.PRIMARY'
3051:20220909:093017.446 skipped 22 duplicates
3049:20220909:093027.705 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '1027711-1662708624-785000000' for key 'history_uint.PRIMARY'
3049:20220909:093027.718 skipped 2 duplicates

I couldn't make out any negative consequences due to these errors, so i haven't bothered much researching what may cause them (also couldn't find much anywhere especially in combination with Zabbix).

So, question: how do you go on about finding out which Trigger(s)/Item(s) cause these errors and how to fix them?

[galoxucro]

I've just upgraded from 5.0.21to 6.0.17 on Ubuntu 20.04. I'm using Percona Server for MySQL 8.0.26-17-2, and I'm getting similar error messages in zabbix_server.log:

18622:20230525:115219.652 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '77400-1685026334-272000000' for key 'history.PRIMARY'
18620:20230525:115747.231 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '236119-1685026660-322000000' for key 'history_uint.PRIMARY'
18620:20230525:115918.439 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '150150-1685026753-910000000' for key 'history_text.PRIMARY'
18620:20230525:120219.677 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '77399-1685026934-395000000' for key 'history.PRIMARY'
18621:20230525:120616.719 [Z3008] query failed due to primary key constraint: [1062] Duplicate entry '73709-1685027171-777000000' for key 'history_uint.PRIMARY'

I've already checked the history_int table consistency with 'CHECK TABLE', it's OK.

This is what I get when checking for duplicates:

mysql> SELECT itemid, clock, ns, COUNT(*) FROM history_uint GROUP BY itemid, clock, ns HAVING COUNT(*) > 1;
Empty set (3 min 13.03 sec)

cle and teab , do you have any update on this issue?

[teab]

Resolution to this was simple - ignoring duplicates as per bug report mentioned to - https://www.zabbix.com/documentation...b_primary_keys.