No Graph, SQL Exec Failed

[allmar]

Hello 2 all!

I have cacti-0.8.6h, cacti-cactid-0.8.6g, rrdtool-1.0.49-win32 with xampp-win32-1.5.1!

I get an error in the syslog!
Poller[0] ERROR: SQL Exec Failed "update data_template_data set rrd_maximum='|query_ifSpeed|' where local_data_id=12"

and I see no graph!

but in the Associated Graph Templates
Graph Template Name Status
1) Cisco - CPU Usage Is Being Graphed (Edit)!

what ist wrong??

Or need I assistance?

allmar

[TheWitness]

First of all the error message has resulted from a Network Interface that does not have an if_speed associated with it and is not related to your CPU issue. Please place a bug in Mantis (http://bugs.cacti.net) indicating that RRD maximum values are not being automatically set for interfaces without an if_speed.

In order to discuss your CPU issue in more detail, you should run one pass of your poller in DEBUG, look for the "U" value, record the OID and then try to get it outside of Cacti using Net-SNMP. You may find that the OID does not exist for your device.

TheWtiness

[BSOD2600]

You did not follow the installation instructions very closely... you must use the cygwin build of rrdtool instead of the Win32 one for starters.

[allmar]

i have insall cygwin! (the same shit)

here my debug from cactid.exe:

C:\Programme\xampp\htdocs\cacti>cactid.exe
CACTID: Using cactid config file [cactid.conf]
CACTID: DEBUG: MySQL Query ID '1': 'SELECT value FROM settings WHERE name = 'path_webroot''
CACTID: DEBUG: MySQL Query ID '1': OK
CACTID: DEBUG: MySQL Query ID '2': 'SELECT value FROM settings WHERE name = 'path_cactilog''
CACTID: DEBUG: MySQL Query ID '2': OK
CACTID: DEBUG: The path_php_server variable is
CACTID: DEBUG: The path_cactilog variable is C:/Programme/xampp/htdocs/cacti/log/cacti.log
CACTID: DEBUG: MySQL Query ID '3': 'SELECT value FROM settings WHERE name = 'log_destination''
CACTID: DEBUG: MySQL Query ID '3': OK
CACTID: DEBUG: The log_destination variable is 2 (BOTH)
CACTID: DEBUG: MySQL Query ID '4': 'SELECT value FROM settings WHERE name = 'path_php_binary''
CACTID: DEBUG: MySQL Query ID '4': OK
CACTID: DEBUG: The path_php variable is C:/Programme/xampp/php/php.exe
CACTID: DEBUG: MySQL Query ID '5': 'SELECT value FROM settings WHERE name = 'availability_method''
CACTID: DEBUG: MySQL Query ID '5': OK
CACTID: DEBUG: The availability_method variable is 2
CACTID: DEBUG: MySQL Query ID '6': 'SELECT value FROM settings WHERE name = 'ping_recovery_count''
CACTID: DEBUG: MySQL Query ID '6': OK
CACTID: DEBUG: The ping_recovery_count variable is 3
CACTID: DEBUG: MySQL Query ID '7': 'SELECT value FROM settings WHERE name = 'ping_failure_count''
CACTID: DEBUG: MySQL Query ID '7': OK
CACTID: DEBUG: The ping_failure_count variable is 2
CACTID: DEBUG: MySQL Query ID '8': 'SELECT value FROM settings WHERE name = 'ping_method''
CACTID: DEBUG: MySQL Query ID '8': OK
CACTID: DEBUG: The ping_method variable is 2
CACTID: DEBUG: MySQL Query ID '9': 'SELECT value FROM settings WHERE name = 'ping_retries''
CACTID: DEBUG: MySQL Query ID '9': OK
CACTID: DEBUG: The ping_retries variable is 1
CACTID: DEBUG: MySQL Query ID '10': 'SELECT value FROM settings WHERE name = 'ping_timeout''
CACTID: DEBUG: MySQL Query ID '10': OK
CACTID: DEBUG: The ping_timeout variable is 400
CACTID: DEBUG: MySQL Query ID '11': 'SELECT value FROM settings WHERE name = 'log_perror''
CACTID: DEBUG: MySQL Query ID '11': OK
CACTID: DEBUG: The log_perror variable is 1
CACTID: DEBUG: MySQL Query ID '12': 'SELECT value FROM settings WHERE name = 'log_pwarn''
CACTID: DEBUG: MySQL Query ID '12': OK
CACTID: DEBUG: The log_pwarn variable is 1
CACTID: DEBUG: MySQL Query ID '13': 'SELECT value FROM settings WHERE name = 'log_pstats''
CACTID: DEBUG: MySQL Query ID '13': OK
CACTID: DEBUG: The log_pstats variable is 1
CACTID: DEBUG: MySQL Query ID '14': 'SELECT value FROM settings WHERE name = 'max_threads''
CACTID: DEBUG: MySQL Query ID '14': OK
CACTID: DEBUG: The threads variable is 1
CACTID: DEBUG: MySQL Query ID '15': 'SELECT value FROM settings WHERE name = 'poller_interval''
CACTID: DEBUG: MySQL Query ID '15': OK
CACTID: DEBUG: The polling interval is the system default
CACTID: DEBUG: MySQL Query ID '16': 'SELECT value FROM settings WHERE name = 'concurrent_processes''
CACTID: DEBUG: MySQL Query ID '16': OK
CACTID: DEBUG: The number of concurrent processes is 1
CACTID: DEBUG: MySQL Query ID '17': 'SELECT value FROM settings WHERE name = 'script_timeout''
CACTID: DEBUG: MySQL Query ID '17': OK
CACTID: DEBUG: The script timeout is 25
CACTID: DEBUG: MySQL Query ID '18': 'SELECT value FROM settings WHERE name = 'php_servers''
CACTID: DEBUG: MySQL Query ID '18': OK
CACTID: DEBUG: The number of php script servers to run is 1
CACTID: DEBUG: MySQL Query ID '19': 'SELECT action FROM poller_item WHERE action=2 LIMIT 1'
CACTID: DEBUG: MySQL Query ID '19': OK
CACTID: DEBUG: StartHost='-1', EndHost='-1', TotalPHPScripts='0'
CACTID: DEBUG: The PHP Script Server is Not Required
CACTID: DEBUG: MySQL Query ID '20': 'SELECT value FROM settings WHERE name = 'max_get_size''
CACTID: DEBUG: MySQL Query ID '20': OK
CACTID: DEBUG: The Maximum SNMP OID Get Size is 10
CACTID: CACTID: Version 0.8.6f starting
CACTID: MYSQL: Connecting to MySQL database 'cacti' on '127.0.0.1'...
CACTID: MYSQL: Connected to MySQL database 'cacti' on '127.0.0.1'...
CACTID: CACTID: Initializing Net-SNMP API
No log handling enabled - turning on stderr logging
NOTIFICATION-LOG-MIB: Module not found
NET-SNMP-AGENT-MIB: Module not found
CACTID: CACTID: Initializing PHP Script Server
CACTID: DEBUG: MySQL Query ID '21': 'SELECT id FROM host WHERE disabled='' ORDER BY id'
CACTID: DEBUG: MySQL Query ID '21': OK
CACTID: DEBUG: Initial Value of Active Threads is 0
CACTID: DEBUG: In Poller, About to Start Polling of Host
CACTID: DEBUG: Valid Thread to be Created
CACTID: MYSQL: Connecting to MySQL database 'cacti' on '127.0.0.1'...
CACTID: DEBUG: The Value of Active Threads is 1
CACTID: MYSQL: Connected to MySQL database 'cacti' on '127.0.0.1'...
CACTID: DEBUG: MySQL Query ID '22': 'SELECT action,hostname,snmp_community,snmp_
version,snmp_username,snmp_password,rrd_name,rrd_path,arg1,arg2,arg3,local_data_
id,rrd_num,snmp_port,snmp_timeout FROM poller_item WHERE host_id=0 ORDER BY arg1'
CACTID: DEBUG: MySQL Query ID '22': OK
CACTID: Host[0] DEBUG: HOST COMPLETE: About to Exit Host Polling Thread Function
CACTID: DEBUG: The Value of Active Threads is 0
CACTID: DEBUG: In Poller, About to Start Polling of Host
CACTID: DEBUG: Valid Thread to be Created
CACTID: MYSQL: Connecting to MySQL database 'cacti' on '127.0.0.1'...
CACTID: DEBUG: The Value of Active Threads is 1
CACTID: MYSQL: Connected to MySQL database 'cacti' on '127.0.0.1'...
CACTID: DEBUG: MySQL Query ID '23': 'SELECT id, hostname,snmp_community,snmp_use
rname,snmp_password,snmp_version,snmp_port,snmp_timeout,status,status_event_coun
t,status_fail_date,status_rec_date,status_last_error,min_time,max_time,cur_time,
avg_time,total_polls,failed_polls,availability FROM host WHERE id=4'
CACTID: DEBUG: MySQL Query ID '23': OK
CACTID: Host[4] SNMP Result: Host responded to SNMP
CACTID: DEBUG: MySQL Insert ID '0': 'update host set status='3',status_event_cou
nt='0', status_fail_date='0000-00-00 00:00:00',status_rec_date='0000-00-00 00:00:00',status_last_error='',min_time='5.999800',max_time='9.000060',cur_time='8.000140',avg_time='7.636331',total_polls='11',failed_polls='0',availability='100.0000' where id='4''
CACTID: DEBUG: MySQL Insert ID '0': OK
CACTID: DEBUG: MySQL Query ID '24': 'SELECT data_query_id,action,op,assert_value
,arg1 FROM poller_reindex WHERE host_id=4'
CACTID: DEBUG: MySQL Query ID '24': OK
CACTID: Host[4] RECACHE: Processing 1 items in the auto reindex cache for 'DEVICE_NAME'
CACTID: DEBUG: MySQL Insert ID '1': 'update poller_reindex set assert_value='149
4414216' where host_id='4' and data_query_id='1' and arg1='.1.3.6.1.2.1.1.3.0''
CACTID: DEBUG: MySQL Insert ID '1': OK
CACTID: DEBUG: MySQL Query ID '25': 'SELECT action,hostname,snmp_community,snmp_version,snmp_username,snmp_password,rrd_name,rrd_path,arg1,arg2,arg3,local_data_id,rrd_num,snmp_port,snmp_timeout FROM poller_item WHERE host_id=4 ORDER BY arg1'
CACTID: DEBUG: MySQL Query ID '25': OK
CACTID: Host[4] DS[21] SNMP: v1: DEVICE_NAME, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.49, value: 4236265176
CACTID: Host[4] DS[22] SNMP: v1: DEVICE_NAME, dsname: traffic_in, oid: .1.3.6.1.2.1.2.2.1.10.50, value: 2174144302
CACTID: Host[4] DS[19] SNMP: v1: DEVICE_NAME, dsname: discards_in, oid: .1.3.6.1.2.1.2.2.1.13.49, value: 0
CACTID: Host[4] DS[20] SNMP: v1: DEVICE_NAME, dsname: discards_in, oid: .1.3.6.1.2.1.2.2.1.13.50, value: 0
CACTID: Host[4] DS[19] SNMP: v1: DEVICE_NAME, dsname: errors_in, oid: .1.3.6.1.2.1.2.2.1.14.49, value: 0
CACTID: Host[4] DS[20] SNMP: v1: DEVICE_NAME, dsname: errors_in, oid: .1.3.6.1.2.1.2.2.1.14.50, value: 0
CACTID: Host[4] DS[21] SNMP: v1: DEVICE_NAME, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.49, value: 4047403191
CACTID: Host[4] DS[22] SNMP: v1: DEVICE_NAME, dsname: traffic_out, oid: .1.3.6.1.2.1.2.2.1.16.50, value: 2998922448
CACTID: Host[4] DS[19] SNMP: v1: DEVICE_NAME, dsname: discards_out, oid: .1.3.6.1.2.1.2.2.1.19.49, value: 0
CACTID: Host[4] DS[20] SNMP: v1: DEVICE_NAME, dsname: discards_out, oid: .1.3.6.1.2.1.2.2.1.19.50, value: 0
CACTID: Host[4] DS[19] SNMP: v1: DEVICE_NAME, dsname: errors_out, oid: .1.3.6.1.2.1.2.2.1.20.49, value: 0
CACTID: Host[4] DS[20] SNMP: v1: DEVICE_NAME, dsname: errors_out, oid: .1.3.6.1.2.1.2.2.1.20.50, value: 0
CACTID: Host[4] DS[18] SNMP: v1: DEVICE_NAME, dsname: 5min_cpu, oid: .1.3.6.1.4.1.9.2.1.58.0, value: 2
CACTID: DEBUG: MySQL Insert ID '2': 'INSERT INTO poller_output (local_data_id,rrd_name,time,output) VALUES (21,'traffic_in','2006-02-06 21:00:03','4236265176'),(22,'traffic_in','2006-02-06 21:00:03','2174144302'),(19,'discards_in','2006-02-06 21:00:03','0'),(20,'discards_in','2006-02-06 21:00:03','0'),(19,'errors_in','2006-02-06 21:00:03','0'),(20,'errors_in','2006-02-06 21:00:03','0'),(21,'traffic_out','2006-02-06 21:00:03','4047403191'),(22,'traffic_out','2006-02-06 21:00:03','2998922448'),(19,'discards_out','2006-02-06 21:00:03','0'),(20,'discards_out','2006-02-06 21:00:03','0'),(19,'errors_out','2006-02-06 21:00:03','0'),(20,'errors_out','2006-02-06 21:00:03','0'),(18,'5min_cpu','2006-02-06 21:00:03','2')'
CACTID: DEBUG: MySQL Insert ID '2': OK
CACTID: Host[4] DEBUG: HOST COMPLETE: About to Exit Host Polling Thread Function
CACTID: DEBUG: The Value of Active Threads is 0
CACTID: DEBUG: MySQL Insert ID '3': 'replace into settings (name,value) values ('date',NOW())'
CACTID: DEBUG: MySQL Insert ID '3': OK
CACTID: DEBUG: MySQL Insert ID '4': 'insert into poller_time (poller_id, start_time, end_time) values (0, NOW(), NOW())'
CACTID: DEBUG: MySQL Insert ID '4': OK
CACTID: DEBUG: Thread Cleanup Complete
CACTID: DEBUG: PHP Script Server Pipes Closed
CACTID: DEBUG: Allocated Variable Memory Freed
Creating directory: /C:
Failed to create the persistent directory for C:/NET-SNMP/snmp/persist/cactid.conf
Creating directory: /C:
Failed to create the persistent directory for C:/NET-SNMP/snmp/persist/cactid.conf
Creating directory: /C:
Failed to create the persistent directory for C:/NET-SNMP/snmp/persist/cactid.conf
CACTID: CACTID: Net-SNMP API Shutdown Completed
CACTID: DEBUG: MYSQL Free & Close Completed
CACTID: Time: 0.9260 s, Threads: 1, Hosts: 2

C:\Programme\xampp\htdocs\cacti>

#################

when I will open poller.php, they don't work and I see the textfile!

when i open php.exe i get this messange:
C:\Programme\xampp\php>php.exe

C:\NET-SNMP\etc\snmp/snmp.conf: line 2: Warning: Unknown token: persistentDir.
C:\NET-SNMP\etc\snmp/snmp.conf: line 3: Warning: Unknown token: tempFilePattern.

I followed attentively the installguide!

also in the apachelogfile:
ERROR: opening 'C:/Programme/xampp/htdocs/cacti/rra/DEVICENAME_traffic_in_22.rrd': No such file or directory


CACTI doesn't want work for me!

[BSOD2600]

1) installing cygwin and using the win32 version of rrdtool are very different things.

2) I suggest you try using cmd.php first and once that works, migrate over to cactid.exe

3) Delete persistentDir and tempFilePattern from snmp.conf.

4) In php.ini make sure the following is set:
register_argc_argv = On
safe_mode = Off

5) Double check you gave the proper permissions on rrdtool.exe and cmd.exe for the web user and the user account which runs the scheduled task.

[TheWitness]

One more thing, for the host that is throwing the SQL Error, please run a verbose query on the Interface Data Query and post the output.

TheWitness

[vluce]

I have the same issue. Each time I create a graph for a device I get the following error in cacti log :
02/07/2006 04:34:40 PM - CMDPHP: Poller[0] ERROR: SQL Exec Failed "update data_template_data set rrd_maximum='|query_ifSpeed|' where local_data_id=22"

No graphic is displayed.

I installed the folowing version :
- cacti 0.8.6h
- php 5.1.2
- IIS 6
- mysql 4.1.16
- rrdtools : rrdtool-cygwin-1.2.12.zip
- net-snmp : net-snmp-5.2.2-1.win32.exe

I use php poller.
I delete persistentDir and tempFilePattern from snmp.conf

See bellow the verbose query on one interface of the device :

Code: Select all

+ Running data query [1].
+ Found type = '3' [snmp query].
+ Found data query XML file at 'c:/Inetpub/wwwroot/cacti/resource/snmp_queries/interface.xml'
+ XML file parsed ok.
+ Executing SNMP walk for list of indexes @ '.1.3.6.1.2.1.2.2.1.1'
+ Located input field 'ifIndex' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.2.2.1.1'
+ Found item [ifIndex='1'] index: 1 [from value]
+ Found item [ifIndex='65539'] index: 65539 [from value]
+ Located input field 'ifOperStatus' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.2.2.1.8'
+ Found item [ifOperStatus='Up'] index: 1 [from value]
+ Found item [ifOperStatus='Up'] index: 65539 [from value]
+ Located input field 'ifDescr' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.2.2.1.2'
+ Found item [ifDescr='MS TCP Loopback interface'] index: 1 [from value]
+ Found item [ifDescr='Team R seau'] index: 65539 [from value]
+ Located input field 'ifName' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.31.1.1.1.1'
+ Located input field 'ifAlias' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.31.1.1.1.18'
+ Located input field 'ifType' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.2.2.1.3'
+ Found item [ifType='softwareLoopback(24)'] index: 1 [from value]
+ Found item [ifType='ethernetCsmacd(6)'] index: 65539 [from value]
+ Located input field 'ifSpeed' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.2.2.1.5'
+ Found item [ifSpeed='10000000'] index: 1 [from value]
+ Found item [ifSpeed='2000000000'] index: 65539 [from value]
+ Located input field 'ifHwAddr' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.2.2.1.6'
+ Found item [ifHwAddr=''] index: 1 [from value]
+ Found item [ifHwAddr='00:12:79:91:B2:64'] index: 65539 [from value]
+ Located input field 'ifIP' [walk]
+ Executing SNMP walk for data @ '.1.3.6.1.2.1.4.20.1.2'
+ Found item [ifIP='127.0.0.1'] index: 1 [from regexp oid parse]
+ Found item [ifIP='192.215.101.228'] index: 65539 [from regexp oid parse]
+ Found data query XML file at 'c:/Inetpub/wwwroot/cacti/resource/snmp_queries/interface.xml'
+ Found data query XML file at 'c:/Inetpub/wwwroot/cacti/resource/snmp_queries/interface.xml'
+ Found data query XML file at 'c:/Inetpub/wwwroot/cacti/resource/snmp_queries/interface.xml'
+ Found data query XML file at 'c:/Inetpub/wwwroot/cacti/resource/snmp_queries/interface.xml'

[vluce]

I did not select the correct rrdtool version in web interface.

Now the graphic are displayed but there is no label :

[vluce]

I forgot to configure the font path in setting to c:/windows/fonts/arial.ttf

Everything is written in your doc. I apologize not to have reread your doc before posting.

[allmar]

there is only one problem with the poller! they poll not automatically, but i solved the problem with a *.bat file!

@ECHO OFF
php c:\programme\xampp\htdocs\cacti\poller.php
:END

Now I will work with cacti!

allmar

[BSOD2600]

Umm, if you followed the installation guide, you should've created a scheduled task which runs the poller every 5 minutes.

[max63]

I've also an SQL error when I want to add a new graph. The log is :

Code: Select all

 02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed "select snmp_query_graph_rrd.snmp_field_name, data_template_rrd.id as data_template_rrd_id from (snmp_query_graph_rrd,data_template_rrd) where snmp_query_graph_rrd.data_template_rrd_id=data_template_rrd.local_data_template_rrd_id and snmp_query_graph_rrd.snmp_query_graph_id= and snmp_query_graph_rrd.data_template_id=41 and data_template_rrd.local_data_id=20"
02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed "select data_input_fields.type_code, data_input_data.value from (data_input_fields,data_input_data) where data_input_fields.id=data_input_data.data_input_field_id and data_input_data.data_template_data_id= and (data_input_fields.type_code='index_type' or data_input_fields.type_code='index_value' or data_input_fields.type_code='output_type')"
02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed "select data_input_fields.type_code, data_input_data.value from (data_input_fields,data_input_data) where data_input_fields.id=data_input_data.data_input_field_id and data_input_data.data_template_data_id= and (data_input_fields.type_code='index_type' or data_input_fields.type_code='index_value' or data_input_fields.type_code='output_type')"
02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Exec Failed "replace into data_input_data (data_input_field_id,data_template_data_id,t_value,value) values (14,,'','13')"
02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Exec Failed "replace into data_input_data (data_input_field_id,data_template_data_id,t_value,value) values (13,,'','WAN (PPP/SLIP) Interface')"
02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Exec Failed "replace into data_input_data (data_input_field_id,data_template_data_id,t_value,value) values (12,,'','ifDescr')"
02/12/2006 01:18:43 AM - CMDPHP: Poller[0] ERROR: SQL Exec Failed "update data_template_data set rrd_maximum='|query_ifSpeed|' where local_data_id=22"

I've tried to find the query in the php source but no succes so I'm here to find help !

Operating System: Windows XP Pro
Webserver: Apache 2
Cacti: 0.8.6h
Cactid:0.8.6f
MySQL:5.0.18-nt
PHP: 5.1.2
RRDTool:1.2

[BSOD2600]

max63: read this http://forums.cacti.net/viewtopic.php?t=11747 then post again.

[max63]

I've re-edit my last post with the correction.

[BSOD2600]

Since the dev team has added more SQL debugging lately, it could possibly be old code which can be ignored. Does graph creation work?

Do the data queries return data which don't have functional graphs?