0

My current DB:

MariaDB [zabbix]> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 10.0.28-MariaDB-2+b1 |
+----------------------+

There are some duplicated items in my Zabbix.

I have the old item which has "history" and "trend" data, but it was disabled. The new item also has data and is enabled.

I thought of copying old item history and trends to the new item but I'm not really sure how to do it safely.

I created some test items to check the update process, but It doesn't seem to go as I expected.

Old item - 30886 New Item - 32041

So, I wish to copy all data from the old item to the new item. Meaning:

  • Old item keeps its data intact.
  • New item keep its current data and receives old item's data.
  • Both items use the same tables.

"history" table excerpt:

MariaDB [zabbix]> SELECT * FROM history WHERE itemid = 30886 LIMIT 5;
+--------+------------+--------+-----------+
| itemid | clock      | value  | ns        |
+--------+------------+--------+-----------+
|  30886 | 1609965887 | 0.3270 |  59358611 |
|  30886 | 1609966187 | 0.2747 |  14863699 |
|  30886 | 1609966486 | 0.3037 | 796425573 |
|  30886 | 1609966787 | 0.2855 |   9516260 |
|  30886 | 1609967086 | 0.2866 | 903642778 |
+--------+------------+--------+-----------+

I thought of doing something like this:

INSERT INTO history (`clock`, `value`, `ns`) 
SELECT `clock`, `value`, `ns`
FROM history
WHERE itemid = 30886;

But that doesn't work. This way it won't insert the new itemid for every line and table "history" is specified twice, as target and source.

Maybe a more experienced fellow would point out some thoughts. Any ideas?

  • 1
    i don't get what you want to achieve, if you want to keeo pnly the last of 30886 you need to dekete the rest. So make a ]mre} and show the end result with mire than one id – nbk Jan 13 at 22:44
  • I'm sorry but I couldn't understand what you meant either. I've rewritten and added some highlight to my objective though. – markfree Jan 14 at 0:53
  • is itemid an auto increment or where do you get 32041? – nbk Jan 14 at 8:56
0

After some digging, I managed to successfully copy history and trends from one item to another.

The sort of syntax I used is this:

History table:

INSERT INTO history (`itemid`, `clock`, `value`, `ns`)
SELECT '<new_itemid>', `clock`, `value`, `ns`
FROM history
WHERE itemid = <old_itemid>;

Trends table:

INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`)
SELECT '<new_itemid>', `clock`, `num`, `value_min`, `value_avg`, `value_max`
FROM trends
WHERE itemid = <old_itemid>;

The secret was with INSERT INTO and the SELECT clause that added the literal itemid. The actual <new_itemid> was inserted into every new row.

The result:

MariaDB [zabbix]> INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`) SELECT '32041', `clock`, `num`, `value_min`, `value_avg`, `value_max` FROM trends WHERE itemid = 30886;
Query OK, 5227 rows affected (0.14 sec)
Records: 5227 Duplicates: 0 Warnings: 0

MariaDB [zabbix]> INSERT INTO history (`itemid`, `clock`, `value`, `ns`) SELECT '32041', `clock`, `value`, `ns` FROM history WHERE itemid = 30886;
Query OK, 2008 rows affected (0.10 sec)
Records: 2008 Duplicates: 0 Warnings: 0

I bet there's a better way, but this is all I could come up with for now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.