|
Causes |
|||
| causes |
|
SQL Error After 4.0.19 Upgrade on Oracle | Closed |
|
Duplicate |
|||
Team B
[ZBX-9057] Duplicate rows in "profiles" table Created: 2014 Nov 19 Updated: 2024 Apr 10 Resolved: 2020 Mar 04 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | Frontend (F) |
| Affects Version/s: | None |
| Fix Version/s: | 4.0.19rc1, 4.4.7rc1, 5.0.0alpha3, 5.0 (plan) |
| Type: | Problem report | Priority: | Major |
| Reporter: | Oleksii Zagorskyi | Assignee: | Mārtiņš Tālbergs (Inactive) |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | database, duplicates | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||
| Team: | Team B | ||||||||||||
| Team: | Team B |
||||||||||||
| Sprint: | Sprint 59 (Dec 2019), Sprint 60 (Jan 2020), Sprint 61 (Feb 2020), Sprint 62 (Mar 2020) | ||||||||||||
| Story Points: | 1 | ||||||||||||
| Description |
|
See first comment. |
| Comments |
| Comment by Oleksii Zagorskyi [ 2014 Nov 19 ] |
|
I did not find anything related to the topic except In one production environment were noticed a bit slow SELECT queries for "profiles" table for different userid. I supposed that the table contains duplicates and they slow down SELECTS a bit. Some SQLs: mysql> select count(*) from profiles; +----------+ | count(*) | +----------+ | 41579 | +----------+ mysql> SELECT count( DISTINCT p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type ) AS unique_rows FROM profiles AS p1; +-------------+ | unique_rows | +-------------+ | 41263 | +-------------+ mysql> SELECT count(*) - count( DISTINCT p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type ) AS duplicates FROM profiles AS p1; +------------+ | duplicates | +------------+ | 327 | +------------+ So we have 327 duplicated (uneeded) rows. List of duplicate rows (which may be deleted) can be shown by: SELECT p1.* FROM profiles AS p1 INNER JOIN ( SELECT MIN( p1.profileid ) AS MinId, p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type FROM profiles AS p1 GROUP BY p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type )p2 ON p1.userid = p2.userid AND p1.idx = p2.idx AND p1.idx2 = p2.idx2 AND p1.value_id = p2.value_id AND p1.value_int = p2.value_int AND p1.value_str = p2.value_str AND p1.source = p2.source AND p1.type = p2.type AND p1.profileid != p2.MinID; this query returns also 327 rows Stat about duplictes: mysql> SELECT count(*), p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type FROM profiles AS p1 GROUP BY p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type having count(*)>1 ORDER BY count(*) DESC; +----------+--------+---------------------------+--------+----------+-----------+----------------+--------+------+ | count(*) | userid | idx | idx2 | value_id | value_int | value_str | source | type | +----------+--------+---------------------------+--------+----------+-----------+----------------+--------+------+ | 8 | 42 | web.screens.isnow | 65 | 0 | 0 | | | 2 | | 8 | 6 | web.screens.isnow | 65 | 0 | 1 | | | 2 | | 6 | 13 | web.charts.graphid | 0 | 439 | 0 | | | 1 | | 6 | 42 | web.screens.isnow | 56 | 0 | 1 | | | 2 | | 6 | 42 | web.screens.graphid | 0 | 917 | 0 | | | 1 | | 6 | 42 | web.screens.isnow | 59 | 0 | 1 | | | 2 | | 6 | 42 | web.screens.isnow | 60 | 0 | 1 | | | 2 | | 6 | 42 | web.screens.isnow | 58 | 0 | 1 | | | 2 | | 6 | 42 | web.screens.isnow | 61 | 0 | 1 | | | 2 | | 6 | 15 | web.screens.isnow | 30 | 0 | 0 | | | 2 | | 6 | 27 | web.screens.isnow | 58 | 0 | 1 | | | 2 | | 6 | 2 | web.screens.isnow | 19 | 0 | 1 | | | 2 | | 6 | 2 | web.screens.isnow | 72 | 0 | 1 | | | 2 | | 6 | 2 | web.screens.isnow | 73 | 0 | 1 | | | 2 | | 6 | 42 | web.screens.isnow | 57 | 0 | 1 | | | 2 | | 6 | 97 | web.screens.graphid | 0 | 1966 | 0 | | | 1 | | 6 | 97 | web.screens.period | 0 | 0 | 43200 | | | 2 | | 6 | 97 | web.screens.stime | 0 | 0 | 0 | 20130416211318 | | 3 | | 6 | 97 | web.screens.isnow | 0 | 0 | 0 | | | 2 | | 6 | 2 | web.screens.isnow | 59 | 0 | 0 | | | 2 | | 6 | 2 | web.screens.isnow | 51 | 0 | 0 | | | 2 | | 6 | 15 | web.screens.isnow | 14 | 0 | 0 | | | 2 | | 6 | 38 | web.screens.isnow | 32 | 0 | 0 | | | 2 | | 6 | 15 | web.screens.isnow | 51 | 0 | 1 | | | 2 | | 6 | 2 | web.screens.isnow | 70 | 0 | 0 | | | 2 | | 6 | 2 | web.screens.isnow | 65 | 0 | 1 | | | 2 | | 6 | 125 | web.screens.graphid | 0 | 1312 | 0 | | | 1 | | 6 | 2 | web.screens.isnow | 53 | 0 | 0 | | | 2 | | 6 | 76 | web.screens.graphid | 0 | 460 | 0 | | | 1 | | 5 | 8 | web.charts.graphid | 0 | 967 | 0 | | | 1 | | 5 | 2 | web.screens.isnow | 25 | 0 | 1 | | | 2 | | 5 | 117 | web.screens.graphid | 0 | 435 | 0 | | | 1 | | 5 | 2 | web.screens.isnow | 17 | 0 | 1 | | | 2 | | 4 | 28 | web.charts.graphid | 0 | 1074 | 0 | | | 1 | | 4 | 2 | web.charts.graphid | 0 | 528 | 0 | | | 1 | | 4 | 57 | web.charts.graphid | 0 | 953 | 0 | | | 1 | | 4 | 2 | web.screens.graphid | 0 | 1517 | 0 | | | 1 | | 4 | 13 | web.screens.graphid | 0 | 3034 | 0 | | | 1 | | 4 | 9 | web.screens.isnow | 32 | 0 | 1 | | | 2 | | 4 | 71 | web.screens.graphid | 0 | 1526 | 0 | | | 1 | | 4 | 2 | web.screens.isnow | 28 | 0 | 1 | | | 2 | | 4 | 9 | web.screens.isnow | 18 | 0 | 1 | | | 2 | | 4 | 6 | web.screens.isnow | 62 | 0 | 1 | | | 2 | | 4 | 6 | web.screens.graphid | 0 | 1076 | 0 | | | 1 | | 4 | 57 | web.screens.isnow | 59 | 0 | 0 | | | 2 | | 4 | 57 | web.screens.isnow | 58 | 0 | 0 | | | 2 | | 4 | 2 | web.screens.isnow | 18 | 0 | 0 | | | 2 | | 4 | 81 | web.screens.graphid | 0 | 1632 | 0 | | | 1 | | 4 | 9 | web.screens.isnow | 19 | 0 | 1 | | | 2 | | 4 | 10 | web.screens.isnow | 65 | 0 | 0 | | | 2 | | 4 | 2 | web.screens.isnow | 30 | 0 | 0 | | | 2 | | 4 | 2 | web.screens.isnow | 62 | 0 | 1 | | | 2 | | 4 | 60 | web.screens.graphid | 0 | 1027 | 0 | | | 1 | | 4 | 28 | web.screens.isnow | 56 | 0 | 1 | | | 2 | | 4 | 32 | web.screens.graphid | 0 | 777 | 0 | | | 1 | | 4 | 61 | web.screens.period | 33 | 0 | 43200 | | | 2 | | 4 | 61 | web.screens.stime | 33 | 0 | 0 | 20131127013356 | | 3 | | 4 | 61 | web.screens.isnow | 33 | 0 | 1 | | | 2 | | 4 | 15 | web.screens.isnow | 44 | 0 | 0 | | | 2 | | 4 | 2 | web.screens.httptestid | 0 | 92 | 0 | | | 1 | | 3 | 15 | web.screens.isnow | 8 | 0 | 0 | | | 2 | | 3 | 21 | web.screens.period | 8 | 0 | 3600 | | | 2 | | 3 | 21 | web.screens.stime | 8 | 0 | 0 | 20121011133908 | | 3 | | 3 | 21 | web.screens.isnow | 8 | 0 | 1 | | | 2 | | 3 | 2 | web.screens.period | 1515 | 0 | 10800 | | | 2 | | 3 | 2 | web.screens.stime | 1515 | 0 | 0 | 20140115093413 | | 3 | | 3 | 2 | web.screens.isnow | 1515 | 0 | 1 | | | 2 | | 3 | 8 | web.screens.isnow | 56 | 0 | 0 | | | 2 | | 3 | 28 | web.screens.isnow | 19 | 0 | 0 | | | 2 | | 3 | 2 | web.screens.isnow | 41 | 0 | 1 | | | 2 | | 3 | 2 | web.slides.isnow | 3 | 0 | 0 | | | 2 | | 3 | 6 | web.screens.isnow | 64 | 0 | 1 | | | 2 | | 3 | 28 | web.screens.isnow | 64 | 0 | 0 | | | 2 | | 3 | 2 | web.item.graph.period | 84396 | 0 | 86400 | | | 2 | | 3 | 2 | web.item.graph.stime | 84396 | 0 | 0 | 20131107012230 | | 3 | | 3 | 2 | web.item.graph.isnow | 84396 | 0 | 0 | | | 2 | | 3 | 9 | web.screens.isnow | 52 | 0 | 1 | | | 2 | | 2 | 15 | web.charts.graphid | 0 | 953 | 0 | | | 1 | | 2 | 32 | web.charts.graphid | 0 | 0 | 0 | | | 1 | | 2 | 45 | web.overview.view.style | 0 | 0 | 1 | | | 2 | | 2 | 45 | web.overview.type | 0 | 0 | 0 | | | 2 | | 2 | 52 | web.menu.reports.last | 0 | 0 | 0 | graphs.php | | 3 | | 2 | 15 | web.screens.timelinefixed | 0 | 0 | 0 | | | 2 | | 2 | 60 | web.charts.graphid | 0 | 940 | 0 | | | 1 | | 2 | 69 | web.charts.graphid | 0 | 1346 | 0 | | | 1 | | 2 | 70 | web.charts.graphid | 0 | 953 | 0 | | | 1 | | 2 | 15 | web.screens.isnow | 3 | 0 | 0 | | | 2 | | 2 | 9 | web.screens.isnow | 41 | 0 | 0 | | | 2 | | 2 | 9 | web.screens.graphid | 0 | 2877 | 0 | | | 1 | | 2 | 42 | web.screens.isnow | 26 | 0 | 1 | | | 2 | | 2 | 21 | web.screens.period | 3 | 0 | 3600 | | | 2 | | 2 | 21 | web.screens.stime | 3 | 0 | 0 | 20121011133918 | | 3 | | 2 | 21 | web.screens.isnow | 3 | 0 | 1 | | | 2 | | 2 | 15 | web.item.graph.period | 80901 | 0 | 3600 | | | 2 | | 2 | 15 | web.item.graph.stime | 80901 | 0 | 0 | 20121016145422 | | 3 | | 2 | 15 | web.item.graph.isnow | 80901 | 0 | 0 | | | 2 | | 2 | 42 | web.item.graph.isnow | 37676 | 0 | 0 | | | 2 | | 2 | 4 | web.screens.isnow | 3 | 0 | 1 | | | 2 | | 2 | 27 | web.screens.isnow | 26 | 0 | 1 | | | 2 | | 2 | 12 | web.screens.graphid | 0 | 3034 | 0 | | | 1 | | 2 | 2 | web.item.graph.isnow | 65929 | 0 | 1 | | | 2 | | 2 | 15 | web.item.graph.period | 103547 | 0 | 3600 | | | 2 | | 2 | 15 | web.item.graph.stime | 103547 | 0 | 0 | 20130125141950 | | 3 | | 2 | 15 | web.item.graph.isnow | 103547 | 0 | 0 | | | 2 | | 2 | 15 | web.item.graph.isnow | 39596 | 0 | 1 | | | 2 | | 2 | 9 | web.screens.isnow | 9 | 0 | 1 | | | 2 | | 2 | 13 | web.item.graph.isnow | 35389 | 0 | 1 | | | 2 | | 2 | 2 | web.item.graph.isnow | 77109 | 0 | 0 | | | 2 | | 2 | 15 | web.item.graph.period | 83691 | 0 | 259268 | | | 2 | | 2 | 15 | web.item.graph.stime | 83691 | 0 | 0 | 20130414234344 | | 3 | | 2 | 15 | web.item.graph.isnow | 83691 | 0 | 0 | | | 2 | | 2 | 15 | web.item.graph.period | 53754 | 0 | 43200 | | | 2 | | 2 | 15 | web.item.graph.stime | 53754 | 0 | 0 | 20130425043302 | | 3 | | 2 | 15 | web.item.graph.isnow | 53754 | 0 | 0 | | | 2 | | 2 | 15 | web.screens.period | 0 | 0 | 86400 | | | 2 | | 2 | 15 | web.screens.stime | 0 | 0 | 0 | 20120704110000 | | 3 | | 2 | 15 | web.screens.isnow | 0 | 0 | 0 | | | 2 | | 2 | 15 | web.item.graph.isnow | 27036 | 0 | 0 | | | 2 | | 2 | 83 | web.history.timelinefixed | 0 | 0 | 1 | | | 2 | | 2 | 2 | web.screens.isnow | 50 | 0 | 0 | | | 2 | | 2 | 83 | web.item.graph.period | 106049 | 0 | 43200 | | | 2 | | 2 | 83 | web.item.graph.stime | 106049 | 0 | 0 | 20140415215800 | | 3 | | 2 | 83 | web.item.graph.isnow | 106049 | 0 | 1 | | | 2 | | 2 | 15 | web.screens.isnow | 50 | 0 | 0 | | | 2 | +----------+--------+---------------------------+--------+----------+-----------+----------------+--------+------+ 124 rows in set (0.00 sec (what users have more records) mysql> select userid, count(*) from profiles group by userid order by count(*) desc; +--------+----------+ | userid | count(*) | +--------+----------+ | 2 | 7037 | | 83 | 5615 | | 15 | 5402 | | 70 | 1716 | | 21 | 1566 | | 9 | 1471 | | 12 | 1277 | | 101 | 1038 | | 71 | 893 | | 28 | 883 | | 42 | 799 | | 20 | 780 | | 4 | 726 | | 13 | 692 | | 57 | 654 | | 27 | 652 | | 80 | 619 | | 10 | 591 | | 117 | 588 | | 81 | 562 | | 47 | 559 | | 19 | 540 | | 23 | 464 | | 68 | 463 | | 45 | 450 | | 125 | 431 | | 124 | 414 | | 102 | 401 | | 6 | 362 | | 131 | 319 | | 60 | 318 | | 79 | 293 | | 32 | 277 | | 26 | 268 | | 97 | 225 | | 17 | 187 | | 52 | 180 | | 18 | 163 | | 78 | 158 | | 38 | 157 | | 76 | 138 | | 65 | 122 | | 14 | 111 | | 8 | 94 | | 84 | 91 | | 99 | 90 | | 127 | 86 | | 61 | 83 | | 119 | 67 | | 39 | 62 | | 128 | 62 | | 118 | 61 | | 33 | 54 | | 69 | 45 | | 11 | 43 | | 130 | 35 | | 72 | 26 | | 98 | 21 | | 25 | 18 | | 104 | 15 | | 100 | 13 | | 49 | 13 | | 56 | 12 | | 85 | 12 | | 55 | 9 | | 74 | 8 | | 75 | 5 | | 40 | 2 | | 129 | 2 | +--------+----------+ 69 rows in set (0.00 sec) userid=2 is supposedly guest Question is: p.s. I recall some discussion in the past that we have to delete unused (dropped/renamed) idx, we even deleted something as I recall, but I believe all production databases have unused idx. |
| Comment by Oleksii Zagorskyi [ 2014 Nov 19 ] |
|
Query (MySQL) to delete duplicates is: DELETE p1 FROM profiles AS p1 INNER JOIN ( SELECT MIN( p1.profileid ) AS MinId, p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type FROM profiles AS p1 GROUP BY p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type )p2 ON p1.userid = p2.userid AND p1.idx = p2.idx AND p1.idx2 = p2.idx2 AND p1.value_id = p2.value_id AND p1.value_int = p2.value_int AND p1.value_str = p2.value_str AND p1.source = p2.source AND p1.type = p2.type AND p1.profileid != p2.MinID; |
| Comment by richlv [ 2014 Nov 19 ] |
|
any old profile values must be deleted by server upgrade code. |
| Comment by Spiros Ioannou [ 2019 Feb 28 ] |
|
postgresql 11 and zabbix 3.4.15, had about 300+ duplicates out of 12K, may be the cause of "deadlock detected" after multiple "UPDATE profiles" queries in logs as stated in create table pr_temp as SELECT p1.* FROM profiles AS p1 INNER JOIN ( SELECT MIN(p1.profileid) AS MinId, p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type FROM profiles AS p1 GROUP BY p1.userid, p1.idx, p1.idx2, p1.value_id, p1.value_int, p1.value_str, p1.source, p1.type ) p2 ON p1.userid = p2.userid AND p1.idx = p2.idx AND p1.idx2 = p2.idx2 AND p1.value_id = p2.value_id AND p1.value_int = p2.value_int AND p1.value_str = p2.value_str AND p1.source = p2.source AND p1.type = p2.type AND p1.profileid != p2.MinID; then: delete from profiles where profileid in (select profileid from pr_temp); drop table pr_temp |
| Comment by Mārtiņš Tālbergs (Inactive) [ 2020 Jan 20 ] |
|
FIXED in development branch feature/ZBX-9057-4.0 |
| Comment by Mārtiņš Tālbergs (Inactive) [ 2020 Mar 03 ] |
|
Available in: |
| Comment by Mārtiņš Tālbergs (Inactive) [ 2020 Mar 03 ] |
|
To remove current duplicates from "profiles" table this query can be executed. Tested on latest versions of mariaDB, postgres and oracle.
DELETE FROM profiles WHERE profileid IN (
SELECT p1.profileid FROM profiles p1
JOIN profiles p2
ON p1.idx=p2.idx
AND p1.userid=p2.userid
AND p1.idx2=p2.idx2
AND p1.profileid < p2.profileid
GROUP BY p1.profileid
);
|