Cacti (home)ForumsDocumentation
Cacti: offical forums and support
It is currently Mon Jun 26, 2017 8:49 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: Error 1064 SQL after Upgrade
PostPosted: Mon Jul 27, 2015 11:44 am 
Offline
User avatar

Joined: Wed Jan 28, 2015 2:57 pm
Posts: 22
Hi,

I have updated my installation of cacti. Cacti is now Version 0.8.8f and spine 0.8.8f. In my Cacti Log File are many entries like this:

07/27/2015 06:33:50 PM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1064', SQL:"REPLACE INTO plugin_discover_hosts (hostname, ip, hash, community, snmp_version, snmp_username, snm...

Is something wrong with the Plugin Discovery? When I disable it, no errors where logged.

And now, after some tries, the Discover tab is gone. :( :(

Attachment:
tab_disco.PNG
tab_disco.PNG [ 6.37 KiB | Viewed 3356 times ]


How can I this?

Regards!

_________________
Cacti 0.8.8f on Debian Wheezy


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Mon Jul 27, 2015 5:58 pm 
Offline
Cacti Moderator
User avatar

Joined: Sat May 08, 2004 12:44 pm
Posts: 12118
Location: USA
moving to plugin forum

mysql 1064 is error in syntax. Looks like plugin author will possibly need to update the plugin...

_________________

| Scripts: Monitor processes | RFC1213 MIB | DOCSIS Stats | Dell PowerEdge | Speedfan | APC UPS | DOCSIS CMTS | 3ware | Motorola Canopy |
| Guides: Windows Install | [HOWTO] Debug Windows NTFS permission problems |
| Tools: Windows All-in-one Installer |


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Sat Aug 08, 2015 1:00 pm 
Offline
User avatar

Joined: Wed Jan 28, 2015 2:57 pm
Posts: 22
Hi,
I have tried to contact the author but I got no reply.
Any other ideas?

Regards,

Frank

_________________
Cacti 0.8.8f on Debian Wheezy


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Tue Aug 25, 2015 3:26 pm 
Offline

Joined: Fri Jul 17, 2015 3:39 pm
Posts: 2
I am getting same error when running Discovery in cacti 8.8f on a fresh install.

CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1064', SQL:"REPLACE INTO plugin_discover_hosts (hostname, ip, hash, community, snmp_version, snmp_username, snmp_password, snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, sysName, sysLocation, sysContact, sysDescr, sysUptime, os, snmp, up, time) VALUES ('''', '10.0.1.95', '167772511', '''', ''1'', '''', '''', '''', '''', '''', '''', '''', '''', '''', '''', '0', '', 0, 1,1440532884)'

When I run "php findhosts.php -d -f" discovery finds host but will not add them to cacti - discovery

08/25/2015 08:37:03 AM - DISCOVER: Poller[0] 254 IPs Scanned, 32 IPs Responded to Ping, 4 Responded to SNMP, 0 Device Added, 0 Graphs Added to Cacti

Need to resolve this asap as I have deployments waiting :(

Regards


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Sat Sep 19, 2015 11:59 am 
Offline

Joined: Thu Jan 06, 2011 9:30 am
Posts: 43
Location: Nashville, TN
I am also having this issue on a new install with Ubuntu 15.04 and 0.8.8f.


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Fri Oct 02, 2015 10:46 am 
Offline

Joined: Fri Oct 02, 2015 10:33 am
Posts: 1
Hi all,
I managed to resolve the error (I hope), I just modified this lines in the findhosts.php removing the ' before ":

if (!$host_id) {
db_execute("REPLACE INTO plugin_discover_hosts (hostname, ip, hash, community, snmp_version, snmp_username, snmp_password, snmp_auth_$
. sql_sanitize($device['dnsname'])
. ", '$host', '"
. $device['hash'] . "', "
. sql_sanitize($device['community']) . ", "
. sql_sanitize($device['snmp_version']) . ", "
. sql_sanitize($device['snmp_username']) . ", "
. sql_sanitize($device['snmp_password']) . ", "
. sql_sanitize($device['snmp_auth_protocol']) . ", "
. sql_sanitize($device['snmp_priv_passphrase']) . ", "
. sql_sanitize($device['snmp_priv_protocol']) . ", "
. sql_sanitize($device['snmp_context']) . ", "
. sql_sanitize($device['snmp_sysName']) . ", "
. sql_sanitize($device['snmp_sysLocation']) . ", "
. sql_sanitize($device['snmp_sysContact']) . ", "
. sql_sanitize($device['snmp_sysDescr']) . ", "
. sql_sanitize($device['snmp_sysUptime']) . ", "
. sql_sanitize($device['os']) . ", "
. "1, 1,".time() . ')' );
}
.....

db_execute("REPLACE INTO plugin_discover_hosts (hostname, ip, hash, community, snmp_version, snmp_username, snmp_password, snmp_auth_protocol, snmp_p$
. sql_sanitize($device['dnsname'])
. ", '$host', '"
. $device['hash'] . "', "
. sql_sanitize($device['community']) . ", "
. sql_sanitize($device['snmp_version']) . ", "
. sql_sanitize($device['snmp_username']) . ", "
. sql_sanitize($device['snmp_password']) . ", "
. sql_sanitize($device['snmp_auth_protocol']) . ", "
. sql_sanitize($device['snmp_priv_passphrase']) . ", "
. sql_sanitize($device['snmp_priv_protocol']) . ", "
. sql_sanitize($device['snmp_context']) . ", "
. sql_sanitize($device['snmp_sysName']) . ", "
. sql_sanitize($device['snmp_sysLocation']) . ", "
. sql_sanitize($device['snmp_sysContact']) . ", "
. sql_sanitize($device['snmp_sysDescr']) . ", "
. sql_sanitize($device['snmp_sysUptime']) . ", "
. "'', 0, 1,".time() . ')' );


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Tue Oct 06, 2015 8:20 am 
Offline

Joined: Tue Oct 06, 2015 8:10 am
Posts: 1
I got it working. Cacti 0.8.8f / Discovery plugin 1.5. Platform: Centos7 on x64.

Something was seriously wrong with the usage of single quotes in the generated SQL statements by the module findhosts.php.

The code below fixes this.

Notice: It is recommended to copy the original findhosts.php before making any changes.

Lines 414 up to (including) 467 should be removed and replaced with the code snippet below. testing with:
php ./findhosts.php -d -f

/* Replacement code starts here */

if (!$host_id) {
db_execute("REPLACE INTO plugin_discover_hosts (hostname, ip, hash, community, snmp_version, snmp_username, snmp_password, snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, sysName, sysLocation, sysContact, sysDescr, sysUptime, os, snmp, up, time) VALUES (" . sql_sanitize($device['dnsname']) . ", '" . $host . "', " . $device['hash'] . ", " . sql_sanitize($device['community']) . ", " . sql_sanitize($device['snmp_version']) . ", " . sql_sanitize($device['snmp_username']) . ", " . sql_sanitize($device['snmp_password']) . ", " . sql_sanitize($device['snmp_auth_protocol']) . ", " . sql_sanitize($device['snmp_priv_passphrase']) . ", " . sql_sanitize($device['snmp_priv_protocol']) . ", " . sql_sanitize($device['snmp_context']) . ", " . sql_sanitize($device['snmp_sysName']) . ", " . sql_sanitize($device['snmp_sysLocation']) . ", " . sql_sanitize($device['snmp_sysContact']) . ", " . sql_sanitize($device['snmp_sysDescr']) . ", " . sql_sanitize($device['snmp_sysUptime']) . ", " . sql_sanitize($device['os']) . ", " . "1, 1," . time() . " );" ) ; }
}
}else if ($result) {
if ($dns != '') {
$dnsname = discover_get_dns_from_ip($host, $dns, 300);
if ($dnsname != $host && $dnsname != 'timed_out') {
$device['dnsname'] = $dnsname;
}
$device['dnsname_short'] = preg_split('/[\.]+/', strtolower($dnsname), -1, PREG_SPLIT_NO_EMPTY);
}else{
$dnsname = $host;
$device['dnsname'] = '';
$device['dnsname_short'] = '';
}
db_execute("REPLACE INTO plugin_discover_hosts (hostname, ip, hash, community, snmp_version, snmp_username, snmp_password, snmp_auth_protocol, snmp_priv_passphrase, snmp_priv_protocol, snmp_context, sysName, sysLocation, sysContact, sysDescr, sysUptime, os, snmp, up, time) VALUES ("
. sql_sanitize($device['dnsname'])
. ", '" . $host . "', "
. $device['hash'] . ", "
. sql_sanitize($device['community']) . ", "
. sql_sanitize($device['snmp_version']) . ", "
. sql_sanitize($device['snmp_username']) . ", "
. sql_sanitize($device['snmp_password']) . ", "
. sql_sanitize($device['snmp_auth_protocol']) . ", "
. sql_sanitize($device['snmp_priv_passphrase']) . ", "
. sql_sanitize($device['snmp_priv_protocol']) . ", "
. sql_sanitize($device['snmp_context']) . ", "
. sql_sanitize($device['snmp_sysName']) . ", "
. sql_sanitize($device['snmp_sysLocation']) . ", "
. sql_sanitize($device['snmp_sysContact']) . ", "
. sql_sanitize($device['snmp_sysDescr']) . ", "
. sql_sanitize($device['snmp_sysUptime']) . ", "
. "'', 0, 1,".time() . ')' );
discover_debug(" - Host $dnsname is alive but no SNMP!");
}


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Wed Oct 21, 2015 10:40 am 
Offline
User avatar

Joined: Tue Oct 09, 2012 2:51 am
Posts: 46
Location: Belgium
Same problems here, I'm hoping Jimmy will update his plugin soon..

_________________
Cacti Plugins: Acceptance, Autom8-Reportit, Autom8-Thold


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Wed Nov 18, 2015 9:29 pm 
Offline
Cacti User
User avatar

Joined: Fri Nov 04, 2005 3:37 pm
Posts: 229
Location: Ca US
I think the problem is the way the components (CentOS, php, and mysql) have changed in later revisions.

On my system (CentOS 6.7, php 5.3.3, and mysql 5.1.73), it turns out that the $_POST automatically fixes the outer quotes. So I just removed sql_sanitize() everywhere it occurred. Unfortunately, that broke embedded single quotes (and other characters) that were within a text field (e.g. ' became \' ).

What I discovered turned out to be relatively simple. I just replaced "sql_sanitize()" with "stripslashes()" wherever it occurred. For example, the following line
Code:
$save['name']                   = sql_sanitize(form_input_validate($_POST['name'], 'name', '', false, 3));
became
Code:
$save['name']                   = stripslashes(form_input_validate($_POST['name'], 'name', '', false, 3));
stripslashes is a built-in php function.

_________________
---------
The Glue Guy


Top
 Profile  
 
 Post subject: Re: Error 1064 SQL after Upgrade
PostPosted: Thu Mar 30, 2017 5:24 pm 
Offline

Joined: Wed Mar 29, 2017 9:36 am
Posts: 5
naive's code works pretty good! looks like it add anything that responds to ping? at any rate that works for me!


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Protected by Anti-Spam ACP Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group