Postgres Errors After Upgrade

[anrstone]

I have upgraded from 1.6.6 to 1.8 on a Debian Lenny host and all is well apart from the fact that I now get a shed load of transactional failed events in the zabbix-server logs. I have enclosed an example here:

   885:20100316:181954.672 [Z3005] Query failed: [0] PGRES_FATAL_ERROR:ERROR:  current transaction is aborted, commands ignored until end of transaction block
 [select distinct i.itemid,i.key_,h.host,h.port,i.delay,i.description,i.type,h.useip,h.ip,i.history,i.lastvalue,i.prevvalue,i.hostid,i.value_type,i.delta,i.prevorgvalue,i.lastclock,i.units,i.multiplier,i.formula,i.status,i.valuemapid,h.dns,i.trends,i.lastlogsize,i.data_type,i.mtime,f.function,f.parameter,f.itemid,f.lastvalue from hosts h,items i,functions f,triggers t where f.itemid=i.itemid and h.hostid=i.hostid and f.triggerid=t.triggerid and t.status in (0) and f.itemid in (18440,18441,18442,18443,18444,18445,18468,18470,18471,18472,18473,18502,18504,18530,18531,18532,18533,18534,22162,22166,22167,22168,22169,22170,22171,22192,22193,22194,22221,22223,22250,22251,22252,22254,22276,22305,22307,22334,22336,22363,22364,22365,22392,22393,22466,22468,22495,22497,22614,22616,22643,22645,22668,22671,22679,22680,22688)]
   885:20100316:182025.904 [Z3005] Query failed: [0] 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.
 [insert into history (itemid,clock,value) values (18468,1268763618,1.040000);
insert into history (itemid,clock,value) values (18443,1268763618,50277.200000);
insert into history (itemid,clock,value) values (18444,1268763619,0.000000);
insert into history (itemid,clock,value) values (22169,1268763619,166477.800000);
insert into history (itemid,clock,value) values (22495,1268763615,0.000000);
insert into history (itemid,clock,value) values (22643,1268763615,0.266667);
insert into history (itemid,clock,value) values (18440,1268763620,955152.600000);
insert into history (itemid,clock,value) values (18445,1268763620,67765.200000);
insert into history (itemid,clock,value) values (22170,1268763620,0.000000);
insert into history (itemid,clock,value) values (18441,1268763621,264.000000);
insert into history (itemid,clock,value) values (22166,1268763621,874467.800000);
insert into history (itemid,clock,value) values (22171,1268763621,137221.200000);
insert into history (itemid,clock,value) values (18471,1268763621,8.473448);
insert into history (itemid,clock,value) values (18531,1268763621,16291201024000.000000);
insert into history (itemid,clock,value) values (22221,1268763621,95.234369);
insert into history (itemid,clock,value) values (22251,1268763621,80.754931);
insert into history (itemid,clock,value) values (22614,1268763617,0.000000);
insert into history (itemid,clock,value) values (22616,1268763617,0.000000);
insert into history (itemid,clock,value) values (22392,1268763620,0.000000);
insert into history (itemid,clock,value) values (22466,1268763616,0.016667);
insert into history (itemid,clock,value) values (22167,1268763622,0.000000);
insert into history (itemid,clock,value) values (18442,1268763622,91391.200000);
insert into history (itemid,clock,value) values (18472,1268763622,5.791313);
insert into history (itemid,clock,value) values (18502,1268763622,96.299607);
insert into history (itemid,clock,value) values (22252,1268763622,14.165209);
insert into history (itemid,clock,value) values (22276,1268763620,1.016667);
insert into history (itemid,clock,value) values (22688,1268763618,0.000000);
insert into history (itemid,clock,value) values (22679,1268763621,0.000000);
insert into history (itemid,clock,value) values (22335,1268763620,0.000000);
insert into history (itemid,clock,value) values (22334,1268763622,0.000000);
insert into history (itemid,clock,value) values (22336,1268763622,0.000000);
insert into history (itemid,clock,value) values (22193,1268763623,4.050000);
insert into history (itemid,clock,value) values (22168,1268763623,180003.200000);
insert into history (itemid,clock,value) values (18473,1268763623,6.243756);
insert into history (itemid,clock,value) values (22223,1268763623,95.234369);
insert into history (itemid,clock,value) values (22306,1268763620,0.006667);
insert into history (itemid,clock,value) values (22305,1268763621,0.016667);
insert into history (itemid,clock,value) values (22307,1268763621,0.003333);
insert into history (itemid,clock,value) values (22363,1268763622,0.083333);
insert into history (itemid,clock,value) values (22365,1268763622,0.030000);
insert into history (itemid,clock,value) values (22486,1268763622,0.000000);
insert into history (itemid,clock,value) values (22487,1268763623,0.000300);
insert into history (itemid,clock,value) values (22645,1268763620,0.240000);
insert into history (itemid,clock,value) values (22195,1268763625,2.200000);
insert into history (itemid,clock,value) values (18505,1268763625,78.886437);
insert into history (itemid,clock,value) values (22225,1268763625,99.987793);
insert into history_uint (itemid,clock,value) values (18501,1268763621,588198);
insert into history_uint (itemid,clock,value) values (18532,1268763622,34619392);
insert into history_uint (itemid,clock,value) values (22162,1268763622,1);
insert into history_uint (itemid,clock,value) values (18533,1268763623,808009728);
insert into history_uint (itemid,clock,value) values (18504,1268763624,372185759744);
insert into history_uint (itemid,clock,value) values (18534,1268763624,89374720);
insert into history_uint (itemid,clock,value) values (22254,1268763624,697249792);
insert into history_uint (itemid,clock,value) values (22224,1268763624,122865);
insert into history_uint (itemid,clock,value) values (18535,1268763625,0);
insert into history_uint (itemid,clock,value) values (22255,1268763625,14947110912);
]

Does anyone know what might the problem (i.e. what step did I forget to perform that's caused this to go awry)?

Thanks

[anrstone]

As a further update it appears that the history table has a numeric type that does not support the size of data being sent but I don't understand why that has been reduced from one version to the next - can anyone shed some light on this.

Thanks

[anrstone]

So as it obvious from the log the following insert is the problem

insert into history (itemid,clock,value) values (18531,1268763621,16291201024000.000000);

After some very crude searching around templates and hunting the DB I found that this was generated by an item in the Linux template which after some even cruder binary seaching I narrowed down to the disk space useage and useage % fields which we don't really use so have disabled...

[MartinJanda]

I had the same problem. Thanks for the solution.