Housekeeping broken in 1.4.2 pre

[starstream]

I think that housekeeping maybe broke in 1.4.2 pre or maybe i missed some SQL updates?

When uppgraded fron 1.4.1 -> 1.4.2 pre my zabbix_server process dies with this ->

60932:20070706:115819 Executing housekeeper
60915:20070706:115819 Query::insert into trends (clock,itemid,num,value_min,value_avg,value_max) values (1183712400,21278,1,1580337496064.000000,158033749 6064.000000,1580337496064.000000)
60915:20070706:115819 Query failed:PGRES_FATAL_ERROR:ERROR: numeric field overflow
DETAIL: A field with precision 16, scale 4 must round to an absolute value less than 10^12.

60915:20070706:115819 Query::update items set nextcheck=1183715918,prevvalue=lastvalue,lastvalue ='1580337496064',lastclock=1183715899 where itemid=21278
60915:20070706:115819 Query failed:PGRES_FATAL_ERROR:ERROR: current transaction is aborted, commands ignored until end of transaction block

60915:20070706:115819 Query::select distinct function,parameter,itemid,lastvalue from functions where itemid=21278
60915:20070706:115819 Query failed:PGRES_FATAL_ERROR:ERROR: current transaction is aborted, commands ignored until end of transaction block

60902:20070706:115819 One child process died. Exiting ...
60902:20070706:115821 ZABBIX Server stopped

Seems like zabbix want to puts values (1580337496064.000000) in the trends table that has the definition ->

  Column   |     Type      |         Modifiers          
-----------+---------------+----------------------------
 itemid    | bigint        | not null default 0::bigint
 clock     | integer       | not null default 0
 num       | integer       | not null default 0
 value_min | numeric(16,4) | not null default 0.0000
 value_avg | numeric(16,4) | not null default 0.0000
 value_max | numeric(16,4) | not null default 0.0000
Indexes:

Is it my table structure thats outdated or does the housekeeper something wrong?

Cheers!

[starstream]

I think i was a little bit to fast.

Seems like there errors still there even when downgrading to 1.4.1 but the difference is that zabbix_server do not die, it keeps running.

 62829:20070706:121638 Query::insert into trends (clock,itemid,num,value_min,value_avg,value_max) values (1183716000,21278,1,1580337496064.000000,1580337496064.000000,1580337496064.000000)
 62829:20070706:121638 Query failed:PGRES_FATAL_ERROR:ERROR:  numeric field overflow
DETAIL:  A field with precision 16, scale 4 must round to an absolute value less than 10^12.

[unilogic]

Housekeeping broken in 1.4.2 Stable [Possible Fix]

I have the same problem with 1.4.2 stable. However I think I found a fix. It takes a small schema change. By increasing the precision (total number of significant numerals in the data) of the numeric data type PostgreSQL doesn't fail the transaction and zabbix_server doesn't die. It seems the zabbix wants to use thirteen digits before the decimal point instead of 12, and have 4 numerals after the decimal point (scale or the second number in the data type).

I don't know if this will break anything (I don't think it will), and I've been testing it for all of 5 minutes now so let me know how you fare. Maybe the Zabbix guys can chime in on if this will break anything or if there is a better fix.

SQL to change as follows:

zabbix=# ALTER TABLE trends ALTER value_max TYPE numeric(17,4);
ALTER TABLE
zabbix=# ALTER TABLE trends ALTER value_min TYPE numeric(17,4);
ALTER TABLE
zabbix=# ALTER TABLE trends ALTER value_avg TYPE numeric(17,4);
ALTER TABLE

Regards,
Ben

[Alexei]

It is already in our bug tracking system, ZBX-14.

[unilogic]

I've actually ran over the numeric datatype of 17,4. I am setting them all to 19,2 now.

[mbarthelemy]

problem still occuring

Hi,

I have Zabbix 1.4.3 and still have this problem.

22101:20080104:101529 Query::insert into trends (clock,itemid,num,value_min,value_avg,value_max) values (1199437200,100100000014971,1,1570988032000.000000,1570988032000.000000,1570988032000.000000)
 22101:20080104:101529 Query failed:PGRES_FATAL_ERROR:ERROR:  numeric field overflow
DETAIL:  A field with precision 16, scale 4 must round to an absolute value less than 10^12.

I had to set fields to numeric (20.4), and after a few hours the server crashed again. Now I run with fields set to type numeric(25.8) :

zabbix=# alter table trends alter column value_min type numeric(25,8);
ALTER TABLE
zabbix=# alter table trends alter column value_avg type numeric(25,8);
ALTER TABLE
zabbix=# alter table trends alter column value_max type numeric(25,8);
ALTER TABLE

I don't know if setting decimal part to 8 is useful, but I really need my Zabbix server to work.

Maybe it would be useful to increase these fields to high sizes by default, in the database schema creation script. Moreover, it would also be useful that Server keeps running without crashing when such cases occur...

[SMB]

Running 1.4.4 on CentOS 5.1 and am having this problem on table TRENDS and HISTORY.

29404:20080311:091237 Query::insert into trends (clock,itemid,num,value_min,value_avg,value_max) values (1205240400,25487,1,86431483757915136.000000,86431 483757915136.000000,86431483757915136.000000)
29404:20080311:091237 Query failed:PGRES_FATAL_ERROR:ERROR: numeric field overflow
DETAIL: A field with precision 16, scale 4 must round to an absolute value less than 10^12.

29435:20080311:091519 Query::insert into history (clock,itemid,value) values (1205241319,25499,24883511498872832.000000)
29435:20080311:091519 Query failed:PGRES_FATAL_ERROR:ERROR: numeric field overflow
DETAIL: A field with precision 16, scale 4 must round to an absolute value less than 10^12.

Had to update the 4 fields (value, value_min, value_avg, value_max) to numeric(25,4) to get them to work. It still overflowed (20,4).