Error 1064 SQL after Upgrade

[Frank42]

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.

tab_disco.PNG (6.37 KiB) Viewed 5288 times

How can I this?

Regards!

[BSOD2600]

moving to plugin forum

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

[Frank42]

Hi,
I have tried to contact the author but I got no reply.
Any other ideas?

Regards,

Frank

[jahwah]

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

[cjolly3053]

I am also having this issue on a new install with Ubuntu 15.04 and 0.8.8f.

[evicon]

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() . ')' );

[naive]

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!");
}

[Hipska]

Same problems here, I'm hoping Jimmy will update his plugin soon..

[GlueGuy]

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: Select all

$save['name']                   = sql_sanitize(form_input_validate($_POST['name'], 'name', '', false, 3));

became

Code: Select all

$save['name']                   = stripslashes(form_input_validate($_POST['name'], 'name', '', false, 3));

stripslashes is a built-in php function.

[ceenizz]

naive's code works pretty good! looks like it add anything that responds to ping? at any rate that works for me!