psql graphs > 25d 13h -- empty plot (possible fix here!)

[exvito]

Hi,

Running zabbix 1.4.2 on top of postgresql 8.1.9 on an Unbuntu Server 6.06 LTS.

I already knew I could not display graphs longer that 25 days and 13 hours... (25 days 13 hours is ok, 25 days 14 hours I get no plot -- axes + labels + background ok, however!) Whatever the item selected is.

Today I decided to further investigate and with the help of Richlv and carl^_ at #zabbix on irc.freenode.net we found something that will probably fix my case (probably for others as well!).

We traced the SQL queries in the failing case which lead us to find an SQL error of "integer out of range" from postgresql. This is the reply to one of the last queries run when we ask the GUI to change the graph period to "25d 14h":

select
        itemid,
        round(973*(mod(clock+883789,2210400))/(2210400),0) as i,
        sum(num) as count,
        avg(value_avg) as avg,
        min(value_min) as min,
        max(value_max) as max,
        max(clock) as clock
from
        trends
where
        itemid=18846 and
        clock>=1186101011 and
        clock<=1188311411
group by
        itemid,
        round(973*(mod(clock+883789,2210400))/(2210400),0);

Further tests (at the psql interactive shell) have shown us that not querying the calculated 'i' column would eliminate the error -- so it was clear where it was failing:

select
        round(973*(mod(clock+883789,2210400))/(2210400),0)
from
        trends
where
        itemid = 18846;

Would reproduce the error ! Oddly enough it did not fail for max(clock) neither min(clock)...

The fix came from casting any of the integers in the expression to an 8 byte integer because postgresql seems to consider unqualified ints as 4 byte.

So...

select
        round(973::INT8*(mod(clock+883789,2210400))/(2210400),0)
from
        trends
where
        itemid = 18846;

...WORKS ! I'll try and identify the php file where this query is and try to "brute force it" to use ::INT8. Can any of the developers please check this behaviour / suggested fix ?

Thanks in advance
--
ex vito

[exvito]

Tested OK - Now how to implement the fix ?!

I changed one line of code in include/classes/graph.inc.php, where the SQL query is generated.

At line #995 we have:

$calc_field = 'round('.$x.'*(mod(clock+'.$z.','.$p.'))/('.$p.'),0)'; /* required for 'group by' support of Oracle */

...which I changed to:

$calc_field = 'round('.$x.'::int8*(mod(clock+'.$z.','.$p.'))/('.$p.'),0)'; /* required for 'group by' support of Oracle */

This works great in my setup and I can now get graphs for >25d 13h periods (one month for example!)...

Now, this is obviously not a portable fix... Oracle, MySQL, SQLITE and others will probably not like the '::int8' I inserted.

Questions:

• How would one go about implementing a general fix ?
• What is that 'i' column for, anyway ? (it looks like a complex SQL based way of generating a sequential index... could it be generated on the PHP side, eliminating this question at all ?)

I'll keep an eye on feedback or reach me at ex #dot# vitorino #at# gmail #dot# com
Cheers,
--
exvito