Autom8 and cacti 0.8.7i => some problem SQL Error 1064

[c3226026]

Hello Gandalf,

I used cacti 0.8.7i, with boost 5.1 and Autom8, when I "import" device on cacti I have these errors :

01/24/2012 02:40:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index FROM host_snmp_cache WHERE snmp_query_id=41 AND host_id=478 GROUP BY snmp_query_id, snmp_index) as a WHERE"

01/24/2012 02:40:09 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select field_name,field_value from host_snmp_cache where host_id= and snmp_query_id= and snmp_index=''"
01/24/2012 02:40:09 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT field_value FROM host_snmp_cache WHERE host_id= AND snmp_query_id= AND snmp_index='' AND field_name='ifHighSpeed'"

I've tryed with Autom8 0.35 and 0.36 (trunk svn version) but it's same.

Any idea to solve this ?

Thanks in advanced

[gandalf]

Please post the rules used
R.

[c3226026]

For Network, I used the initial rule Traffic 64 bits Server, I've just modify :

Rule Selection [edit: Traffic 64 bit Server Linux]

Item Sequence Operation Field Operator Pattern
Item#1 1 host_template.name contains Linux
Item#2 2 AND host.snmp_version is greater than or equal 2


Item Sequence Operation Field Operator Pattern
Item#1 1 ifOperStatus matches Up
Item#2 2 AND ifIP is not empty
Item#3 3 AND ifHwAddr is not empty
Item#4 4 AND ifIP does not contain 127.0.0.1



This error :

01/24/2012 02:40:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index FROM host_snmp_cache WHERE snmp_query_id=41 AND host_id=478 GROUP BY snmp_query_id, snmp_index) as a WHERE"

is with all rules disable but with Enable Autom8 Graph Creation in misc tab

[gandalf]

Please goto Settings for this plugin and activate highest debugging level.
Retry what was failing.
Grep for "AUTOM8" in cacti.log and post findings. I expect lots of intermediate results that should help finding the root cause
R.

[c3226026]

Sorry, I changing my answer when you write your :

This error :

01/24/2012 02:40:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index FROM host_snmp_cache WHERE snmp_query_id=41 AND host_id=478 GROUP BY snmp_query_id, snmp_index) as a WHERE"

is with all rules disable but with Enable Autom8 Graph Creation in misc tab


I give you the result tomorrow for your request, because I have'nt acces now to the server.

[Edit1]
Sorry for the later but I'm doing several test with several install).
First you can forget the error :
01/24/2012 02:40:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"SELECT * FROM (SELECT host_id, snmp_query_id, snmp_index FROM host_snmp_cache WHERE snmp_query_id=41 AND host_id=478 GROUP BY snmp_query_id, snmp_index) as a WHERE

I think it was because I've No SNMP data returned

I check for the other error with :
01/25/2012 09:30:05 AM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT field_value FROM host_snmp_cache WHERE host_id= AND snmp_query_id= AND snmp_index='' AND field_name='ifHighSpeed'
[/Edit1]

[gandalf]

is with all rules disable but with Enable Autom8 Graph Creation in misc tab

So what should this produce?
Ok, ok, I accept that it not throw SQL errors. But in fact, this setting only would produce "non-data query" graphs. And yes, I did not test such a setting ...
R.

[c3226026]

Many sorry for the later, in fact it's not direcly with autom8 import, if a stop polling during autom8 import, I've no error, but after I've those error :

01/26/2012 04:34:55 PM - SYSTEM STATS: Time:293.2276 Method:spine Processes:1 Threads:10 Hosts:19 HostsPerProcess:19 DataSources:6040 RRDsProcessed:0
01/26/2012 04:30:03 PM - SYSTEM BOOST STATS: Time:3.6700 RRDUpdates:39366
01/26/2012 04:30:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select field_name,field_value from host_snmp_cache where host_id= and snmp_query_id= and snmp_index=''"
01/26/2012 04:30:03 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT field_value FROM host_snmp_cache WHERE host_id= AND snmp_query_id= AND snmp_index='' AND field_name='ifHighSpeed'"
01/26/2012 04:30:03 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select field_name,field_value from host_snmp_cache where host_id= and snmp_query_id= and snmp_index=''"
01/26/2012 04:30:03 PM - CMDPHP: Poller[0] ERROR: SQL Cell Failed!, Error:'1064', SQL:"SELECT field_value FROM host_snmp_cache WHERE host_id= AND snmp_query_id= AND snmp_index='' AND field_name='ifHighSpeed'"
01/26/2012 04:29:59 PM - SYSTEM STATS: Time:299.5615 Method:spine Processes:1 Threads:10 Hosts:19 HostsPerProcess:19 DataSources:6040 RRDsProcessed:0
01/26/2012 04:29:59 PM - POLLER: Poller[0] Maximum runtime of 298 seconds exceeded. Exiting.



And two days ago, I've no stop polling so in my opinion these errors was during autom8 import, but no.

But as snmp_query_id, snmp_index'. is with Autom8 plugin I think there is something to root cause with it.
I'm checking spine and mysql.

[gandalf]

Some id's are missing. Either a table is corrupt (then, please run the repair table script), or the poller cache is invalid (then, please recreate the poller cache)
R.

[c3226026]

Many thanks for your help gandalf, I will check it

Complementary question, do you stop polling for "large" import with autom8 ?

[gandalf]

Complementary question, do you stop polling for "large" import with autom8 ?

No. Why?
R.

[c3226026]

It's just a question to use the better solution

[gandalf]

Well, to be honest:
In case you perform an inital run over hundreds of hosts to create thousands of graphs (I've done this already), this will consume significant amount of CPU cycles. AUTOM8 hasn't been optimized for that. This may have effects on polling.
My own experience with this is, that even mass graph generation will not cause significant dropouts. I was not able to produce such a situation on our production system running around 800 host creating several thousands of graphs in one run (selecting all hosts and applying all rules in one go). This only took some time. But polling did not show dropouts.
R.

[c3226026]

Many thanks for your answer gandalf, in my case I've actualy only 540 hosts to add, but I cannot use GUI by select all and apply autom8, because after about 20-30 to hosts added for autom8 than I've a blank page and it broken for the other hosts, so I use cli to created host (as autom8 is enable), all graphs are created by autom8 with this isue.

So my question for stop polling

[gandalf]

That might be an issue with max php run time. But the workaround to use cli is valid, of course.
R.

[c3226026]

Actually I've this : max_execution_time = 30 and max_input_time = 60, but I've same parameter on apache2/php.ini and cli/php.ini