sql statements are failing

[doiggl]

Hello,
These statements are failing, see these in the cacti logfile [1].
I tried select statement on [2], it failed.
A longer cacti log listing is at [3]
Question: How can these errors be fixed ?
Thanks Glenn

I have these cacti plugins installed.
====================================
Autom8 0.35 Automate Cacti Tasks General Active Reinhard Scheck
Monitor 1.3 Device Monitoring General Active Jimmy Conner
Settings 0.5 Global Plugin Settings System Active Jimmy Conner
Hostinfo 0.2 Host Info Old PIA Active Jimmy Conner
Update 0.4 Update Checker Old PIA Active Jimmy Conner


[1] Sql statements -failing
01/11/2012 09:23:22 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!,
Error:'1054', SQL:"SELECT * FROM (SELECT host.hostname AS autom8_host, host_id,
host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT
JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON
(host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND (
host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id,
snmp_query_id, snmp_index ORDER BY autom8_host, CAST(snmp_index AS unsigned)) AS
a WHERE ( a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE
'' ) LIMIT 0,30"

01/11/2012 09:23:22 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!,
Error:'1054', SQL:"SELECT * FROM (SELECT host.hostname AS autom8_host, host_id,
host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT
JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON
(host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND (
host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id,
snmp_query_id, snmp_index ORDER BY autom8_host, CAST(snmp_index AS unsigned)) AS
a WHERE ( a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE
'' )"

[2] Sql select on first part of the statement above - it failed.
mysql> SELECT host.hostname AS autom8_host, host_id,host.disabled, host.status, snmp_query_id FROM host_snmp_cache;
ERROR 1054 (42S22): Unknown column 'host.hostname' in 'field list'


[3] Cacti log - long listing
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] get_field_names called: 1
01/11/2012 09:23:22 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT * FROM (SELECT host.hostname AS autom8_host, host_id, host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON (host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND ( host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id, snmp_query_id, snmp_index ORDER BY autom8_host, CAST(snmp_index AS unsigned)) AS a WHERE ( a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE '' ) LIMIT 0,30"
01/11/2012 09:23:22 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT * FROM (SELECT host.hostname AS autom8_host, host_id, host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON (host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND ( host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id, snmp_query_id, snmp_index ORDER BY autom8_host, CAST(snmp_index AS unsigned)) AS a WHERE ( a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE '' )"
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE ''
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:3:{i:0;a:7:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:12:"ifOperStatus";s:8:"operator";s:1:"7";s:7:"pattern";s:2:"Up";}i:1;a:7:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:4:"ifIP";s:8:"operator";s:2:"16";s:7:"pattern";s:0:"";}i:2;a:7:{s:2:"id";s:1:"3";s:7:"rule_id";s:1:"1";s:8:"sequence";s:1:"3";s:9:"operation";s:1:"1";s:5:"field";s:8:"ifHwAddr";s:8:"operator";s:2:"16";s:7:"pattern";s:0:"";}}, prefix: a.
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_data_query_sql returns: SELECT host.hostname AS autom8_host, host_id, host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON (host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND ( host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id, snmp_query_id, snmp_index
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] get_field_names called: 1
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_data_query_sql called: a:5:{s:2:"id";s:1:"1";s:4:"name";s:21:"Traffic 64 bit Server";s:13:"snmp_query_id";s:1:"1";s:13:"graph_type_id";s:2:"14";s:7:"enabled";s:2:"on";}
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_sort_order returns: ORDER BY autom8_host, CAST(snmp_index AS unsigned)
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_sort_order called: numeric/autom8_host
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] get_created_graphs called: a:5:{s:2:"id";s:1:"1";s:4:"name";s:21:"Traffic 64 bit Server";s:13:"snmp_query_id";s:1:"1";s:13:"graph_type_id";s:2:"14";s:7:"enabled";s:2:"on";}
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:23:22 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] get_field_names called: 1
01/11/2012 09:22:51 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT * FROM (SELECT host.hostname AS autom8_host, host_id, host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON (host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND ( host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id, snmp_query_id, snmp_index ORDER BY autom8_host, CAST(snmp_index AS unsigned)) AS a WHERE ( a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE '' ) LIMIT 0,30"
01/11/2012 09:22:51 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1054', SQL:"SELECT * FROM (SELECT host.hostname AS autom8_host, host_id, host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON (host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND ( host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id, snmp_query_id, snmp_index ORDER BY autom8_host, CAST(snmp_index AS unsigned)) AS a WHERE ( a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE '' )"
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE ''
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:3:{i:0;a:7:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:12:"ifOperStatus";s:8:"operator";s:1:"7";s:7:"pattern";s:2:"Up";}i:1;a:7:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:4:"ifIP";s:8:"operator";s:2:"16";s:7:"pattern";s:0:"";}i:2;a:7:{s:2:"id";s:1:"3";s:7:"rule_id";s:1:"1";s:8:"sequence";s:1:"3";s:9:"operation";s:1:"1";s:5:"field";s:8:"ifHwAddr";s:8:"operator";s:2:"16";s:7:"pattern";s:0:"";}}, prefix: a.
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_data_query_sql returns: SELECT host.hostname AS autom8_host, host_id, host.disabled, host.status, snmp_query_id, snmp_index FROM host_snmp_cache LEFT JOIN host ON (host_snmp_cache.host_id=host.id) LEFT JOIN host_template ON (host.host_template_id=host_template.id) WHERE snmp_query_id=1 AND ( host.description IS NOT NULL AND host.snmp_version >= '2') GROUP BY host_id, snmp_query_id, snmp_index
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] get_field_names called: 1
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_data_query_sql called: a:5:{s:2:"id";s:1:"1";s:4:"name";s:21:"Traffic 64 bit Server";s:13:"snmp_query_id";s:1:"1";s:13:"graph_type_id";s:2:"14";s:7:"enabled";s:2:"on";}
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_sort_order returns: ORDER BY autom8_host, CAST(snmp_index AS unsigned)
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_sort_order called: numeric/autom8_host
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] get_created_graphs called: a:5:{s:2:"id";s:1:"1";s:4:"name";s:21:"Traffic 64 bit Server";s:13:"snmp_query_id";s:1:"1";s:13:"graph_type_id";s:2:"14";s:7:"enabled";s:2:"on";}
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:22:51 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:22:49 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:49 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:49 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:22:49 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:22:43 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:43 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter returns: host.description IS NOT NULL AND host.snmp_version >= '2'
01/11/2012 09:22:43 AM - AUTOM8 TRACE: Poller[0] build_rule_item_filter called: a:2:{i:0;a:8:{s:2:"id";s:1:"1";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"1";s:9:"operation";s:1:"0";s:5:"field";s:16:"host.description";s:8:"operator";s:2:"14";s:7:"pattern";s:0:"";}i:1;a:8:{s:2:"id";s:1:"2";s:7:"rule_id";s:1:"1";s:9:"rule_type";s:1:"1";s:8:"sequence";s:1:"2";s:9:"operation";s:1:"1";s:5:"field";s:17:"host.snmp_version";s:8:"operator";s:2:"12";s:7:"pattern";s:1:"2";}}, prefix:
01/11/2012 09:22:43 AM - AUTOM8 TRACE: Poller[0] build_matching_objects_filter called rule id: 1
01/11/2012 09:21:54 AM - AUTOM8 TRACE: Poller[0] get_query_fields called
01/11/2012 09:21:54 AM - AUTOM8 TRACE: Poller[0] get_query_fields called

[gandalf]

This is AUTOM8. Apart from your findings, more failing stuff stems from

Code: Select all

a.ifOperStatus <=> 'Up' AND a.ifIP NOT LIKE '' AND a.ifHwAddr NOT LIKE
'' 

This is a "LIKE" clause without any wildcard and hence a coding error.
But please post the AUTOM8 graph rule in use: "Traffic 64 bit Server" along with all current rule items as a screenshot.
R.

[doiggl]

autom8 graph rules listing

[gandalf]

At first, please edit include/config.php and define the correct url_path. Then retry.
R.