Mysql – Zabbix MariaDB Copy item data to another item

insertmariadbMySQLselect

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?

Best Answer

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.

Related Question