Cannot create new graph after upgrading to 0.8.7a

[alanso]

My cacti installation was upgraded from 0.8.6j to 0.8.7a. However, graph cannot be created afterwards, the page show,

This data query returned 0 rows, perhaps there was a problem executing this data query. You can run this data query in debug mode to get more information.

But when I run data query in debug mode, it seems to be normal,

+ Running data query [10].
+ Found type = '3' [snmp query].
+ Found data query XML file at '/var/www/html/cacti/resource/snmp_queries/net-snmp_proc.xml'
+ XML file parsed ok.
+ Executing SNMP walk for list of indexes @ '.1.3.6.1.4.1.2021.2.1.1'
+ Located input field 'prIndex' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.4.1.2021.2.1.1'
+ Found item [prIndex='1'] index: 1 [from value]
+ Found item [prIndex='2'] index: 2 [from value]
+ Located input field 'prNames' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.4.1.2021.2.1.2'
+ Found item [prNames='proftpd'] index: 1 [from value]
+ Found item [prNames='httpd'] index: 2 [from value]
+ Located input field 'prErrMessage' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.4.1.2021.2.1.101'
+ Found item [prErrMessage=''] index: 1 [from value]
+ Found item [prErrMessage=''] index: 2 [from value]
+ Located input field 'prErrFix' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.4.1.2021.2.1.102'
+ Found item [prErrFix='0'] index: 1 [from value]
+ Found item [prErrFix='0'] index: 2 [from value]
+ Located input field 'prErrFixCmd' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.4.1.2021.2.1.103'
+ Found item [prErrFixCmd=''] index: 1 [from value]
+ Found item [prErrFixCmd=''] index: 2 [from value]
+ Found data query XML file at '/var/www/html/cacti/resource/snmp_queries/net-snmp_proc.xml'
+ Found data query XML file at '/var/www/html/cacti/resource/snmp_queries/net-snmp_proc.xml'
+ Found data query XML file at '/var/www/html/cacti/resource/snmp_queries/net-snmp_proc.xml'
+ Found data query XML file at '/var/www/html/cacti/resource/snmp_queries/net-snmp_proc.xml'
+ Found data query XML file at '/var/www/html/cacti/resource/snmp_queries/net-snmp_proc.xml'

So, anything I can do to fix the problem? My Fedora Linux Core 3 run with,

net-snmp-libs-5.2.1.2-FC3.1
net-snmp-5.2.1.2-FC3.1
net-snmp-utils-5.2.1.2-FC3.1
mysql-3.23.58-16.FC3.1
mysql-server-3.23.58-16.FC3.1
php-mysql-4.3.11-2.8
php-4.3.11-2.8

Created graphs are updating as usual normally. Just I can't create new graphs with existing devices or new devices.

[gandalf]

Please post the data queries XML file
Reinhard

[alanso]

Do you mean this one?

BTW, all data queries return zero in the page creating new graphs. Not only this particular data query.

Moreover, it seems that my mysql version do not support "CAST(snmp_index AS unsigned)" in the sql statement:

mysql> SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='prErrFix' THEN field_value ELSE NULL END) AS 'prErrFix', MAX(CASE WHEN field_name='prErrFixCmd' THEN field_value ELSE NULL END) AS 'prErrFixCmd', MAX(CASE WHEN field_name='prErrMessage' THEN field_value ELSE NULL END) AS 'prErrMessage', MAX(CASE WHEN field_name='prIndex' THEN field_value ELSE NULL END) AS 'prIndex', MAX(CASE WHEN field_name='prNames' THEN field_value ELSE NULL END) AS 'prNames' FROM host_snmp_cache WHERE host_id=2 AND snmp_query_id=10 GROUP BY host_id, snmp_query_id, snmp_index ORDER BY CAST(snmp_index AS unsigned) LIMIT 0,20;
ERROR 1064: You have an error in your SQL syntax near '(snmp_index AS unsigned) LIMIT 0,20' at line 1

That's why it return zero when query in graphs_new.php.

[gandalf]

Do you mean this one?

BTW, all data queries return zero in the page creating new graphs. Not only this particular data query.

Moreover, it seems that my mysql version do not support "CAST(snmp_index AS unsigned)" in the sql statement:

mysql> SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='prErrFix' THEN field_value ELSE NULL END) AS 'prErrFix', MAX(CASE WHEN field_name='prErrFixCmd' THEN field_value ELSE NULL END) AS 'prErrFixCmd', MAX(CASE WHEN field_name='prErrMessage' THEN field_value ELSE NULL END) AS 'prErrMessage', MAX(CASE WHEN field_name='prIndex' THEN field_value ELSE NULL END) AS 'prIndex', MAX(CASE WHEN field_name='prNames' THEN field_value ELSE NULL END) AS 'prNames' FROM host_snmp_cache WHERE host_id=2 AND snmp_query_id=10 GROUP BY host_id, snmp_query_id, snmp_index ORDER BY CAST(snmp_index AS unsigned) LIMIT 0,20;
ERROR 1064: You have an error in your SQL syntax near '(snmp_index AS unsigned) LIMIT 0,20' at line 1

That's why it return zero when query in graphs_new.php.

Interesting find. Which MySQL version is this? Which OS?
Reinhard

[TheWitness]

This is an issue with 3.23 of MySQL. The "CAST" is not supported well in that version. This is why we increased the "Minimum" version to 4.x. I would like to see it 5.x soon. There are a very many performance improvements we can make with the 5.x code.

TheWitness

[dtsymbal]

Hello! Recently I started to study Cacti. I was wondering why Cacti 0.8.6k works with no problem on my test RHEL3(CentOS3) machine but Cacti 0.8.7g doesn't display list of interfaces of network devices.

It was interesting for me what makes Cacti 0.8.7 so different. I started to dig PHP code and SQL queries. I found that 0.8.7 uses CAST function for sorting in SQL query in "graphs_new.php" file and that CAST is not supported in MySQL 3.23. Later I found 3 topics on forum describing the same problem.

As I still wanted to use 0.8.7g on my CentOS3 box I changed SQL query without loss of functionaly. See below.

Open "graphs_new.php" file in main Cacti directory (/var/www/cacti in most cases) and find string containing

Code: Select all

$sql_order = "ORDER BY CAST(snmp_index AS unsigned)";

Replace with following

Code: Select all

$sql_order = "ORDER BY CONCAT(SUBSTRING('0000000000',1,10-LENGTH(snmp_index)),snmp_index)";

This query sorts correctly any 32bit unsigned integer (as original query do) and works on MySQL 3.23.

I don't think this will be very helpful for most people as RHEL3 is quite old. But at least it is interesting

[gandalf]

Thx for posting.
But we're severely relying on newer versions of PHP and MySQL now. It is strongly recommended to use version 5 of both! We will soon stop support for MySQL 4 and PHP V4. Don't ask me, how soon
R.