While creating the hosts using Zabbix API, we see that host creation is failing because of the below error. It is unable to make insert into hostmacro table.
ENV:
- Zabbix 6.0.8
- OS Red Hat Enterprise Linux release 8.6 (Ootpa)
- Docker: docker-ce-20.10.21-3
- Database: Mysql Version: 8.0.25
- WebServer: Nginx Version: 1.22.0
SQL statement execution has failed "INSERT INTO hostmacro (hostmacroid,hostid,macro,value,description,type) VALUES ('1113875','37534','{$CRITICALITY}','4','Criticali ty','0')"
Below is our analysis:
1. why this insert is failing ?
a. hostmacroid is PrimaryKey in hostmacro and there is already an entry for this hostmacroid 1113875 mentioned in the query for another host in DB.
[zabbix]> select * from hostmacro where hostmacroid = 1113875;
+-------------+--------+----------+----------------------------------+-------------+------+
| hostmacroid | hostid | macro | value | description | type |
+-------------+--------+----------+----------------------------------+-------------+------+
| 1113875 | 37532 | {$SYSID} | c3fc6c92dbf111d0e229c4691396192e | SYS ID | 0 |
+-------------+--------+----------+----------------------------------+-------------+------+
This is not auto-increment id in DDL.
b. How Zabbix is getting this ID ?
Queries ids table to get the nextid.
MySQL [zabbix]> select * from ids where table_name='hostmacro';
+------------+-------------+---------+
| table_name | field_name | nextid |
+------------+-------------+---------+
| hostmacro | hostmacroid | 1113874 |
+------------+-------------+---------+
Zabbix code : https://github.com/zabbix/zabbix/blo...xdbwrap/host.c
new_hostmacroid = zbx_db_get_maxid_num("hostmacro", new_hostmacros);
zbx_db_insert_prepare(&db_insert_hmacro, "hostmacro", "hostmacroid", "hostid", "macro", "value",
"description", "type", "automatic", NULL);
zbx_db_get_maxid_num function queries ids table to get the nextid,
result = zbx_db_select("select nextid from ids where table_name='%s' and field_name='%s'",
table->table, table->recid);
Line no : 674
https://github.com/zabbix/zabbix/blo...zbxdbhigh/db.c
c. Since this nextid from ids table is way below the current hostmacroid in hostmacro table, insert is failing on hostmacro with the nextid from ids table.
Now the question we have is, why are the ids table nextid not matching with next expected hostmacroid in hostmacro table? What information do we need to find root cause and fix? Please let us know if you have any questions on this problem.
Are there any downsides to just truncating that table?
ENV:
- Zabbix 6.0.8
- OS Red Hat Enterprise Linux release 8.6 (Ootpa)
- Docker: docker-ce-20.10.21-3
- Database: Mysql Version: 8.0.25
- WebServer: Nginx Version: 1.22.0
SQL statement execution has failed "INSERT INTO hostmacro (hostmacroid,hostid,macro,value,description,type) VALUES ('1113875','37534','{$CRITICALITY}','4','Criticali ty','0')"
Below is our analysis:
1. why this insert is failing ?
a. hostmacroid is PrimaryKey in hostmacro and there is already an entry for this hostmacroid 1113875 mentioned in the query for another host in DB.
[zabbix]> select * from hostmacro where hostmacroid = 1113875;
+-------------+--------+----------+----------------------------------+-------------+------+
| hostmacroid | hostid | macro | value | description | type |
+-------------+--------+----------+----------------------------------+-------------+------+
| 1113875 | 37532 | {$SYSID} | c3fc6c92dbf111d0e229c4691396192e | SYS ID | 0 |
+-------------+--------+----------+----------------------------------+-------------+------+
This is not auto-increment id in DDL.
b. How Zabbix is getting this ID ?
Queries ids table to get the nextid.
MySQL [zabbix]> select * from ids where table_name='hostmacro';
+------------+-------------+---------+
| table_name | field_name | nextid |
+------------+-------------+---------+
| hostmacro | hostmacroid | 1113874 |
+------------+-------------+---------+
Zabbix code : https://github.com/zabbix/zabbix/blo...xdbwrap/host.c
new_hostmacroid = zbx_db_get_maxid_num("hostmacro", new_hostmacros);
zbx_db_insert_prepare(&db_insert_hmacro, "hostmacro", "hostmacroid", "hostid", "macro", "value",
"description", "type", "automatic", NULL);
zbx_db_get_maxid_num function queries ids table to get the nextid,
result = zbx_db_select("select nextid from ids where table_name='%s' and field_name='%s'",
table->table, table->recid);
Line no : 674
https://github.com/zabbix/zabbix/blo...zbxdbhigh/db.c
c. Since this nextid from ids table is way below the current hostmacroid in hostmacro table, insert is failing on hostmacro with the nextid from ids table.
Now the question we have is, why are the ids table nextid not matching with next expected hostmacroid in hostmacro table? What information do we need to find root cause and fix? Please let us know if you have any questions on this problem.
Are there any downsides to just truncating that table?