SQL Error

[msw1970]

Hi

I've just installed the thold plugin and I'm getting the following message appear in the cacti log 2 or 3 times everytime the poller runs...

01/08/2008 11:30:32 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "

Any ideas why this is happening?

[tbaror]

Hi,

looking on cacti log i discover that having the same sql error

Code: Select all

 01/09/2008 10:25:18 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "
01/09/2008 10:25:17 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "
01/09/2008 10:25:16 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "

Cacti Version - 0.8.7a
Plugin Architecture - 1.4
Poller Type - CMD.php
Server Info - Windows NT 5.2
Web Server - Microsoft-IIS/6.0
PHP - 5.2.4
PHP Extensions - bcmath, calendar, com_dotnet, ctype, session, filter, ftp, hash, iconv, json, odbc, pcre, Reflection, date, libxml, standard, tokenizer, zlib, SimpleXML, dom, SPL, wddx, xml, xmlreader, xmlwriter, ISAPI, soap, gd, mysql, mysqli, snmp, sockets, xmlrpc
MySQL - 5.0.45-community-nt
RRDTool - 1.2.15
SNMP - snmpwalk [OPTIONS] AGENT [OID]

Version: 5.4.1
Web: http://www.net-snmp.org/
Email: net-snmp-coders@lists.sourceforge.net

OPTIONS:
-h, --help display this help message
-H display configuration file directives understood
-v 1|2c|3 specifies SNMP version to use
-V, --version display package version number
SNMP Version 1 or 2c specific
-c COMMUNITY set the community string
SNMP Version 3 specific
-a PROTOCOL set authentication protocol (MD5|SHA)
-A PASSPHRASE set authentication protocol pass phrase
-e ENGINE-ID set security engine ID (e.g. 800000020109840301)
-E ENGINE-ID set context engine ID (e.g. 800000020109840301)
-l LEVEL set security level (noAuthNoPriv|authNoPriv|authPriv)
-n CONTEXT set context name (e.g. bridge1)
-u USER-NAME set security name (e.g. bert)
-x PROTOCOL set privacy protocol (DES)
-X PASSPHRASE set privacy protocol pass phrase
-Z BOOTS,TIME set destination engine boots/time
General communication options
-r RETRIES set the number of retries
-t TIMEOUT set the request timeout (in seconds)
Debugging
-d dump input/output packets in hexadecimal
-D TOKEN[,...] turn on debugging output for the specified TOKENs
(ALL gives extremely verbose debugging output)
General options
-m MIB[:...] load given list of MIBs (ALL loads everything)
-M DIR[:...] look in given list of directories for MIBs
-P MIBOPTS Toggle various defaults controlling MIB parsing:
u: allow the use of underlines in MIB symbols
c: disallow the use of "--" to terminate comments
d: save the DESCRIPTIONs of the MIB objects
e: disable errors when MIB symbols conflict
w: enable warnings when MIB symbols conflict
W: enable detailed warnings when MIB symbols conflict
R: replace MIB symbols from latest module
-O OUTOPTS Toggle various defaults controlling output display:
0: print leading 0 for single-digit hex characters
a: print all strings in ascii format
b: do not break OID indexes down
e: print enums numerically
E: escape quotes in string indices
f: print full OIDs on output
n: print OIDs numerically
q: quick print for easier parsing
Q: quick print with equal-signs
s: print only last symbolic element of OID
S: print MIB module-id plus last element
t: print timeticks unparsed as numeric integers
T: print human-readable text along with hex strings
u: print OIDs using UCD-style prefix suppression
U: don't print units
v: print values only (not OID = value)
x: print all strings in hex format
X: extended index format
-I INOPTS Toggle various defaults controlling input parsing:
b: do best/regex matching to find a MIB node
h: don't apply DISPLAY-HINTs
r: do not check values for range/type legality
R: do random access to OID labels
u: top-level OIDs must have '.' prefix (UCD-style)
s SUFFIX: Append all textual OIDs with SUFFIX before parsing
S PREFIX: Prepend all textual OIDs with PREFIX before parsing
-L LOGOPTS Toggle various defaults controlling logging:
e: log to standard error
o: log to standard output
n: don't log at all
f file: log to the specified file
s facility: log to syslog (via the specified facility)

(variants)
[EON] pri: log to standard error, output or /dev/null for level 'pri' and above
[EON] p1-p2: log to standard error, output or /dev/null for levels 'p1' to 'p2'
[FS] pri token: log to file/syslog for level 'pri' and above
[FS] p1-p2 token: log to file/syslog for levels 'p1' to 'p2'
-C APPOPTS Set various application specific behaviours:
p: print the number of variables found
i: include given OID in the search range
I: don't include the given OID, even if no results are returned
c: do not check returned OIDs are increasing
t: Display wall-clock time to complete the request
Plugins

• Global Plugin Settings (settings - v0.3)
  Update Checker (update - v0.4)
  Thresholds (thold - v0.3.9)
  Network Discovery (discovery - v0.8.3)
  PHP Network Weathermap (weathermap - v0.941)
  ReportIt (ReportIt - v0.5.1)
  Real-time statistics (zond - v0.34)
  Device Tracking (mactrack - v1.1)
  Host Info (hostinfo - v0.2)
  Syslog Monitoring (syslog - v0.5.2)
  local Syslog update (Syslogupd - v0.42)

[msw1970]

Any ideas to this one anyone??

[streaker69]

I'm not sure what this will affect. Maybe Jimmy can sound in and say if this makes a difference or not. But in setup.php in your thold directory replace line 202 with this:

Code: Select all

        $thold_items = db_fetch_assoc("select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on data_template_rrd.id = thold_data.data_id WHERE data_template_rrd.data_source_name != ''");

Thold appears to still function, but no longer gives the error in the log.

[topstuff]

Hi,
I am receiving the same error after installing the thold plugin.
However thresholds seem to be working ok.
Any thoughts?

Thanks
Sam

Install Info:
Date Tue, 05 Feb 2008 10:29:12 +1100
Cacti Version 0.8.7a
Cacti OS win32
SNMP Version net-snmp
RRDTool Version RRDTool 1.2.x

Cacti Log:

02/05/2008 10:13:14 AM - SYSTEM STATS: Time:12.5468 Method:cmd.php Processes:1 Threads:N/A Hosts:2 HostsPerProcess:2 DataSources:5 RRDsProcessed:3
02/05/2008 10:13:11 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "
02/05/2008 10:13:10 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "
02/05/2008 10:13:09 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "

[ejensen]

any resolution to this?

[goodwillwm]

I also am seeing this in my logs after installing MACTRACK

[jordan]

Hi everyone,

I had the same problem, and did something that builds on what streaker69 suggested, but is a little cleaner. I changed that line to an if statement:

Code: Select all

        if ( $rra_ids ) {
                $thold_items = db_fetch_assoc("select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data
                                                 LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id)
                                                 WHERE data_template_rrd.data_source_name != '' AND $rra_ids");
        } else {
                $thold_items = db_fetch_assoc("select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data
                                                 LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id)
                                                 WHERE data_template_rrd.data_source_name != ''");
        }

When I also added some warnings to the if block, it looks as though $rra_ids is defined about half the time, and is undefined about half of the time.

I would appreciate if anyone has a cleaner fix to this problem, as this solution is more of a hack.

[cigamit]

Already fixed in SVN.

[kasimong]

Already fixed in SVN.

what must i do to apply the patch

kasimong

[johnrembo]

several days ago it was working fine, but now svn.cacti.net responds with "404 - not found"

steaker69 and jordan - I think your pathces are wrong. Well yes, theoreticaly they do work, bu practicaly wich each poller thread thold will fetch all of it's cache and process it. So if you have 30 spine threads configured - most likely you'll got thold doing all of it's database 30 times during 5 minutes!

I think error lies within realation of thold to other plugins. If other plugin prior to thold uses poller_output plugin hook - thold will receive no rra's.

fix is simple:

add aditional plugin hook for thold at lib/poller.php line 234:

Code: Select all

api_plugin_hook_function('poller_output_thold', $rrd_update_array);

and make thold use this hook:
change plugins/thold/setup.php line 40:

Code: Select all

$plugin_hooks['poller_output_thold']['thold'] = 'thold_poller_output';

please note - this fix should be used only untill SVN will come available again.

[emanners]

Has there been any update to this? I have just upgraded my cacti and the problem still persists using newly downloaded packages.

I have used the hack mentioned above to rectify the issue though.

Cacti - Version 0.8.7d
Cacti Plugin - cacti-plugin-0.8.7d-PA-v2.4
Thold - thold-0.3.9
Settings - settings-0.5

[phakesley]

I did the change as specified but still getting the Error 1064

Cacti 0.8.7b
PA 2.1
CDM.php
Linux 2.6.22.14 Fc6
Apache 2.2.6
RRD 1.2.23
MySQL 5.0.27

Plugins:
Global Plugin Settings (settings - v0.5)
Thresholds (thold - v0.3.9)
Update Checker (update - v0.4)
Network Tools (tools - v0.3)
Realtime for Cacti (realtime - v0.32)
Device Monitoring (monitor - v0.8.2)
Host Info (hostinfo - v0.2)
Network Discovery (discovery - v0.8.4)
Report Creator (reports - v0.3)
Create Aggregate Graphs (aggregate - v0.2)
Cycle Graphs (Cycle Graphs - v0.5)
NMID SmokePing Plugin (nmidSmokeping - v0.3.9a)

[terziyski]

I am also receiving the same error with freshly installed Cacti on Windows XP box:

Cacti - Version: 0.8.7d
Spine - Version: 0.8.7c
Plugin Architecture - Version: 2.4
Large Site Performane Booster - Version: 2.4
Network Discovery - Version: 0.8.5
Host Info - Version: 0.2
PHP Network Managing - Version: 0.6.1
Device Monitoring - Version: 0.8.2
RRD File Cleaner - Version: 0.36
Global Plugin Settings - Version: 0.5
Thresholds - Version: 0.4.1
Network Tools - Version: 0.3
Update Checker - Version: 0.4

[phakesley]

I have implemented the fix mentioned above but it has not made any difference I am still rceiving the SQL Error messages:

04/21/2009 09:00:21 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1064', SQL:"select thold_data.cdef, thold_data.rra_id, thold_data.data_id, thold_data.lastread, thold_data.oldvalue, data_template_rrd.data_source_name as name, data_template_rrd.data_source_type_id from thold_data LEFT JOIN data_template_rrd on (data_template_rrd.id = thold_data.data_id) WHERE data_template_rrd.data_source_name != '' AND "