SQL Query Faild to ASSOC (maybe bug?)

[Seratio]

Hi,

i don't know if its a bug, or an issue inside the template, but when i try to use the templates from scline ( https://github.com/scline/CactiTemplate_JuniperNetworks ), i get the following sql errors:

Code: Select all

05/02/2016 06:51:15 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='Index' THEN field_value ELSE NULL END) AS 'Index', MAX(CASE WHEN field_name='jnxOperatingDescr' THEN field_value ELSE NULL END) AS 'jnxOperatingDescr', MAX(CASE WHEN field_name='temp' THEN field_value ELSE NULL END) AS 'temp' FROM host_snmp_cache WHERE host_id=98 AND snmp_query_id=35 GROUP BY host_id, snmp_query_id, snmp_index ) AS results ORDER BY CAST(Index AS unsigned) LIMIT 0,100"
05/02/2016 06:51:15 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='Index' THEN field_value ELSE NULL END) AS 'Index', MAX(CASE WHEN field_name='FirewallName' THEN field_value ELSE NULL END) AS 'FirewallName', MAX(CASE WHEN field_name='FilterName' THEN field_value ELSE NULL END) AS 'FilterName' FROM host_snmp_cache WHERE host_id=98 AND snmp_query_id=31 GROUP BY host_id, snmp_query_id, snmp_index ) AS results ORDER BY Index LIMIT 0,100"
05/02/2016 06:51:15 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='Index' THEN field_value ELSE NULL END) AS 'Index', MAX(CASE WHEN field_name='LocalIP' THEN field_value ELSE NULL END) AS 'LocalIP', MAX(CASE WHEN field_name='jnxBgpM2PeerLocalAs' THEN field_value ELSE NULL END) AS 'jnxBgpM2PeerLocalAs', MAX(CASE WHEN field_name='PeerIP' THEN field_value ELSE NULL END) AS 'PeerIP', MAX(CASE WHEN field_name='jnxBgpM2PeerRemoteAs' THEN field_value ELSE NULL END) AS 'jnxBgpM2PeerRemoteAs', MAX(CASE WHEN field_name='jnxBgpM2PeerStatus' THEN field_value ELSE NULL END) AS 'jnxBgpM2PeerStatus' FROM host_snmp_cache WHERE host_id=98 AND snmp_query_id=32 GROUP BY host_id, snmp_query_id, snmp_index ) AS results ORDER BY CAST(Index AS unsigned) LIMIT 0,100"
05/02/2016 06:51:04 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='Index' THEN field_value ELSE NULL END) AS 'Index', MAX(CASE WHEN field_name='jnxOperatingDescr' THEN field_value ELSE NULL END) AS 'jnxOperatingDescr', MAX(CASE WHEN field_name='temp' THEN field_value ELSE NULL END) AS 'temp' FROM host_snmp_cache WHERE host_id=98 AND snmp_query_id=35 GROUP BY host_id, snmp_query_id, snmp_index ) AS results ORDER BY CAST(Index AS unsigned) LIMIT 0,100"
05/02/2016 06:51:04 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='Index' THEN field_value ELSE NULL END) AS 'Index', MAX(CASE WHEN field_name='FirewallName' THEN field_value ELSE NULL END) AS 'FirewallName', MAX(CASE WHEN field_name='FilterName' THEN field_value ELSE NULL END) AS 'FilterName' FROM host_snmp_cache WHERE host_id=98 AND snmp_query_id=31 GROUP BY host_id, snmp_query_id, snmp_index ) AS results ORDER BY Index LIMIT 0,100"
05/02/2016 06:51:04 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index, MAX(CASE WHEN field_name='Index' THEN field_value ELSE NULL END) AS 'Index', MAX(CASE WHEN field_name='LocalIP' THEN field_value ELSE NULL END) AS 'LocalIP', MAX(CASE WHEN field_name='jnxBgpM2PeerLocalAs' THEN field_value ELSE NULL END) AS 'jnxBgpM2PeerLocalAs', MAX(CASE WHEN field_name='PeerIP' THEN field_value ELSE NULL END) AS 'PeerIP', MAX(CASE WHEN field_name='jnxBgpM2PeerRemoteAs' THEN field_value ELSE NULL END) AS 'jnxBgpM2PeerRemoteAs', MAX(CASE WHEN field_name='jnxBgpM2PeerStatus' THEN field_value ELSE NULL END) AS 'jnxBgpM2PeerStatus' FROM host_snmp_cache WHERE host_id=98 AND snmp_query_id=32 GROUP BY host_id, snmp_query_id, snmp_index ) AS results ORDER BY CAST(Index AS unsigned) LIMIT 0,100"

The problem here is that the word "Index" is reserved by mysql. Having the sql query formated properly, you will see the problem in the "order by" statement.

I fixed this issues with the following changes:

Code: Select all

*** cacti/graphs_new.php	2016-02-07 19:26:54.000000000 +0000
--- cacti-ps/graphs_new.php	2016-05-02 07:00:42.698033169 +0000
***************
*** 795,810 ****
  						foreach($sql_order_fields as $k) {
  							switch($xml_array['index_order_type']) {
  							case 'numeric':
! 								$sql_order = "ORDER BY CAST($k AS unsigned)";
  								break;
  							case 'alphabetic':
! 								$sql_order = "ORDER BY $k";
  								break;
  							case 'natural':
! 								$sql_order = "ORDER BY INET_ATON($k)";
  								break;
  							default:
! 								$sql_order = "ORDER BY $k";
  							}
  						}
  					}else{
--- 795,810 ----
  						foreach($sql_order_fields as $k) {
  							switch($xml_array['index_order_type']) {
  							case 'numeric':
! 								$sql_order = "ORDER BY CAST('$k' AS unsigned)";
  								break;
  							case 'alphabetic':
! 								$sql_order = "ORDER BY '$k'";
  								break;
  							case 'natural':
! 								$sql_order = "ORDER BY INET_ATON('$k')";
  								break;
  							default:
! 								$sql_order = "ORDER BY '$k'";
  							}
  						}
  					}else{

Regards
Alex

[phalek]

The alternative is to fix the snmp_queries:

from this

Code: Select all

	<index_order>Index</index_order>

	<fields>
		<Index>
			<name>Index</name>
			<method>walk</method>
			<source>index</source>
			<direction>input</direction>
		</Index>

to this:

Code: Select all

	<index_order>ifIndex</index_order>

	<fields>
		<ifIndex>
			<name>Index</name>
			<method>walk</method>
			<source>index</source>
			<direction>input</direction>
		</ifIndex>