Table history_uint exceeds max sequence num (Postgres) logs "[Z3005] query failed"

[rpjeff]

Zabbix version: 4.4.10
database: Postgres 12

Error messages similar to this example are appearing in /var/log/zabbix/zabbix_server.log

5941:20230807:043643.176 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  integer out of range
 [insert into history_uint (itemid,clock,ns,value) values (71802,1691383002,658107692,1),(73542,1691383002,673161042,1),(68802,1691383002,686058698,1),(71382,1691383002,693920396,156),(71562,1691383002,698893762,1),(73722,1691383002,705362660,1),(71622,1691383002,718983041,0),(73662,1691383002,883148496,200),(72222,1691383002,894125591,49200222208),(72942,1691383002,894187870,1),(71262,1691383002,897300169,6985117696),(32502,1691383002,901264602,129);
]

It's also causing problems with the history syncer

zabbix_server [5941]: ERROR [file:prediction.c,line:712] Something impossible has just happened.
5941:20230807:043641.167 === Backtrace: ===
5941:20230807:043641.167 18: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](zbx_backtrace+0x42) [0x55cfc53d16e0]
5941:20230807:043641.167 17: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x1b22f2) [0x55cfc53da2f2]
5941:20230807:043641.167 16: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x1b23aa) [0x55cfc53da3aa]
5941:20230807:043641.167 15: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](zbx_forecast+0x53e) [0x55cfc53db50c]
5941:20230807:043641.167 14: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x151097) [0x55cfc5379097]
5941:20230807:043641.167 13: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](evaluate_function+0x7c3) [0x55cfc537a201]
5941:20230807:043641.168 12: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x15fdbf) [0x55cfc5387dbf]
5941:20230807:043641.168 11: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x1606af) [0x55cfc53886af]
5941:20230807:043641.168 10: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](evaluate_expressions+0x11d) [0x55cfc5388836]
5941:20230807:043641.168 9: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x167480) [0x55cfc538f480]
5941:20230807:043641.168 8: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x16a5d8) [0x55cfc53925d8]
5941:20230807:043641.168 7: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](zbx_sync_history_cache+0x8d) [0x55cfc5392f61]
5941:20230807:043641.168 6: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](dbsyncer_thread+0x1fa) [0x55cfc52772b9]
5941:20230807:043641.168 5: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](zbx_thread_start+0x37) [0x55cfc53df163]
5941:20230807:043641.168 4: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](MAIN_ZABBIX_ENTRY+0xca6) [0x55cfc526a60d]
5941:20230807:043641.168 3: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](daemon_start+0x305) [0x55cfc53d0f49]
5941:20230807:043641.168 2: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](main+0x312) [0x55cfc5269965]
5941:20230807:043641.168 1: /lib64/libc.so.6(__libc_start_main+0xf5) [0x7ffb982eb555]
5941:20230807:043641.168 0: /usr/sbin/zabbix_server: history syncer [NODE="1"]Home[/NODE] [processed 0 values, 0 triggers in 0.000024 sec, syncing history](+0x409f9) [0x55cfc52689f9]
​

The table history_uint appears to use an int for the sequence number,

\dS history_uint
Table "public.history_uint"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+------------------------------------------
itemid | bigint | | not null |
clock | integer | | not null | 0
value | numeric(20,0) | | not null | 0::numeric
ns | integer | | not null | 0
id | integer | | not null | nextval('history_uint_id_seq'::regclass)
Indexes:
"history_uint_1" btree (itemid, clock)​

and, it's exceeded this,

Select last_value from history_uint_id_seq;
last_value
------------
2282363504
(1 row)​

We tried changing this to bigint which got rid of the "[Z3005] query failed" messages but the history syncer was still broken. We ended up having to restore from a backup as it was not possible to revert using an "ALTER TABLE history_uint ALTER COLUMN..." command.

Is there a fix for this?

Thanks in advance.

[rpjeff]

This appears to be two separate issues. I solved the sequence issue by truncating the history_unit table and resetting the sequence with

ALTER SEQUENCE history_uint_id_seq RESTART WITH 1;

I'll repost the "Something impossible has just happened." log issue as a separate message.