SQL Error [nagiosxi] : ERROR: syntax error at or near

[CBoekhuis]

Hi , aaply runs as usual. +/- 7-20 sec. After that the mysql process keeps running at 100-120 % for about +/- 5 minutes.

[tgriep]

See the PM I sent about the need to install ndoutils on the system to fix the slow status updates and the MYSQL load.

[CBoekhuis]

Hi,

acties are done. After apply services return whitin short time with result. Also mysql process is not spinning as far i can see.

cleaner.log still shows errors:

----------------------------------
Running callbacks:
----------------------------------
<p><pre>SQL Error [nagiosxi] : ERROR: syntax error at or near "24"
LINE 1: ..._deploy_agents WHERE last_status_check + INTERVAL 24 HOUR <=...
^</pre></p>
DIR: /usr/local/nagiosxi/nom/checkpoints/nagioscore
NUMFOUND: 10
KEEPING ALL GOOD CHECKPOINTS
DIR: /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors
ls: cannot access /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors/*.gz: No such file or directory
NUMFOUND: 0
KEEPING ALL ERROR CHECKPOINTS
DIR: /usr/local/nagiosxi/nom/checkpoints/nagiosxi
NUMFOUND: 10
KEEPING ALL SNAPSHOTS
----------------------------------
Running callbacks:
----------------------------------
<p><pre>SQL Error [nagiosxi] : ERROR: syntax error at or near "24"
LINE 1: ..._deploy_agents WHERE last_status_check + INTERVAL 24 HOUR <=...
^</pre></p>
DIR: /usr/local/nagiosxi/nom/checkpoints/nagioscore
NUMFOUND: 11
ALL:
1597953333.tar.gz
1597953333.txt
1597952804.tar.gz
1597952804.txt
1597932933.tar.gz
1597932933.txt
1597875902.tar.gz
1597875902.txt
1597865329.tar.gz
1597865329.txt
1597864778.tar.gz
1597864778.txt
1597839204.tar.gz
1597839204.txt
1597824500.tar.gz
1597824500.txt
1597822045.tar.gz
1597822045.txt
1597789442.tar.gz
1597789442.txt
1597737134.tar.gz
1597737134.txt
errors
LAST GOOD CHECKPOINT TO KEEP: /usr/local/nagiosxi/nom/checkpoints/nagioscore/1597737134.tar.gz
DELETING OLD TEXT FILES...
DELETING OLD TARBALL FILES...
DIR: /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors
ls: cannot access /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors/*.gz: No such file or directory
NUMFOUND: 0
KEEPING ALL ERROR CHECKPOINTS
DIR: /usr/local/nagiosxi/nom/checkpoints/nagiosxi
NUMFOUND: 11
ALL:
1597953333_nagiosql.sql.gz
1597952804_nagiosql.sql.gz
1597932933_nagiosql.sql.gz
1597875902_nagiosql.sql.gz
1597865329_nagiosql.sql.gz
1597864778_nagiosql.sql.gz
1597839204_nagiosql.sql.gz
1597824500_nagiosql.sql.gz
1597822045_nagiosql.sql.gz
1597789442_nagiosql.sql.gz
1597737134_nagiosql.sql.gz
LAST GOOD SNAPSHOTS TO KEEP: /usr/local/nagiosxi/nom/checkpoints/nagiosxi/1597737134_nagiosql.sql.gz
DELETING OLD SNAPSHOT FILES...
----------------------------------
Running callbacks:
----------------------------------
<p><pre>SQL Error [nagiosxi] : ERROR: syntax error at or near "24"
LINE 1: ..._deploy_agents WHERE last_status_check + INTERVAL 24 HOUR <=...
^</pre></p>

[tgriep]

This is the fix for the cleaner.php script syntax error.

Edit the /usr/local/nagiosxi/cron/cleaner.php file and on line 61, add this line

Code: Select all

global $cfg;

so it looks like this.

Code: Select all

function do_deploy_statuschecks()
{
    global $cfg;
    global $db_tables;

Then remove the following line from the file (it should be line 65)

Code: Select all

    $sql = "SELECT * FROM " . $db_tables[DB_NAGIOSXI]["deploy_agents"] . " WHERE last_status_check + INTERVAL " . intval($deploy_avail_check) . " HOUR <= NOW()";

Replace it will all of the following.

Code: Select all

    $dbtype = '';
    if (array_key_exists("dbtype", $cfg['db_info'][DB_NAGIOSXI])) {
        $dbtype = $cfg['db_info'][DB_NAGIOSXI]['dbtype'];
    }

    $interval = "INTERVAL ".intval($deploy_avail_check)." HOUR";
    if ($dbtype == 'pgsql') {
        $interval = "INTERVAL '".intval($deploy_avail_check)." hours'";
    }

    $sql = "SELECT * FROM " . $db_tables[DB_NAGIOSXI]["deploy_agents"] . " WHERE last_status_check + ".$interval." <= NOW()";

Save the settings and see if the errors are gone from the /usr/local/nagiosxi/var/cleaner.log file.

[CBoekhuis]

Changes made. Error messages are gone.

DIR: /usr/local/nagiosxi/nom/checkpoints/nagioscore
NUMFOUND: 10
KEEPING ALL GOOD CHECKPOINTS
DIR: /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors
ls: cannot access /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors/*.gz: No such file or directory
NUMFOUND: 0
KEEPING ALL ERROR CHECKPOINTS
DIR: /usr/local/nagiosxi/nom/checkpoints/nagiosxi
NUMFOUND: 10
KEEPING ALL SNAPSHOTS
----------------------------------
Running callbacks:
----------------------------------

the "cannot access" message is because there are no *.gz files but only :

nagios1:root:/usr/local/nagiosxi/var> ll -ltr /usr/local/nagiosxi/nom/checkpoints/nagioscore/errors/
total 40
-rw-r--r-- 1 nagios nagios 1257 Nov 22 2019 1574408560.txt
-rw-r--r-- 1 nagios nagios 1203 Feb 27 12:45 1582803918.txt
-rw-r--r-- 1 nagios nagios 1270 Apr 17 12:54 1587120866.txt
-rw-r--r-- 1 nagios nagios 1270 Apr 17 12:55 1587120950.txt
-rw-r--r-- 1 nagios nagios 1183 May 14 14:26 1589459214.txt
-rw-r--r-- 1 nagios nagios 1183 May 14 14:28 1589459309.txt
-rw-r--r-- 1 nagios nagios 1806 May 26 12:25 1590488711.txt
-rw-r--r-- 1 nagios nagios 1646 Jun 26 13:30 1593171042.txt
-rw-r--r-- 1 nagios nagios 1284 Jun 29 17:48 1593445682.txt
-rw-r--r-- 1 nagios nagios 1284 Jun 29 17:48 1593445721.txt

[tgriep]

Glad it fixed the SQL error on the system. The next release of XI will have the change.
The ls message can be ignored.

[CBoekhuis]

OK. Thanks for you great help.

[scottwilkerson]

OK. Thanks for you great help.

Locking thread