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

[CBoekhuis]

We upgraded last vriday from 5.6.14 to 5.7.2 and got many issues.
The service and host not showing up was fixed after a ticket, and i managed to resolve the "nagiosim.inc.php on line 491" messages , but we keep performance issues. While i was investigating nagiosxi poor performance that occurs after applying a config change i found the following messages in de cleaner.log

----------------------------------
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
NUMFOUND: 1
KEEPING ALL ERROR CHECKPOINTS
DIR: /usr/local/nagiosxi/nom/checkpoints/nagiosxi
NUMFOUND: 10
KEEPING ALL SNAPSHOTS

Our env:
CentOS release 6.10 (Final)
Vendor=GenuineIntel Model=Intel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz
MHz=1200.000 bogomips=5199.24 lscpu:CPU=24 Little Endian
ProcessorChips=1 PhyscalCores=6 Sockets=2 Cores=6 Thrds=2
Hyperthreads =2 VirtualCPUs =24 MHz=1200 max=0 min=0

[tgriep]

Could you post or PM me your Nagios XI System Profile so we can review it?
To get your system profile. Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and upload it to the forum post or PM it to me.

[CBoekhuis]

Porfile sended in PM.

This issue maybe related to our other open thread.

[tgriep]

Thanks for the profile. Shared with the other techs.

There may be an issue with the Postgres tables and that is generating the error in the cleaner.log so can you run the following as root and post the output here?

Code: Select all

psql -V
echo "\dt+;"|psql nagiosxi nagiosxi
echo '\d xi_deploy_agents;' | psql nagiosxi nagiosxi
echo '\d xi_deploy_jobs;' | psql nagiosxi nagiosxi

Get the following file from the XI server and upload it to the post.

Code: Select all

/var/lib/pgsql/data/pg_log/postgresql-Wed.log

[CBoekhuis]

nagios1_postgresql-Wed.log

nagios1_postgresql-Wed.log

nagios1:root:/var/lib> psql -V
psql (PostgreSQL) 8.4.20
contains support for command-line editing
nagios1:root:/var/lib> echo "\dt+;"|psql nagiosxi nagiosxi
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------------+-------+----------+------------+-------------
public | xi_auditlog | table | nagiosxi | 5376 kB |
public | xi_auth_tokens | table | nagiosxi | 16 kB |
public | xi_cmp_ccm_backups | table | nagiosxi | 0 bytes |
public | xi_cmp_favorites | table | nagiosxi | 0 bytes |
public | xi_cmp_nagiosbpi_backups | table | nagiosxi | 8192 bytes |
public | xi_cmp_trapdata | table | nagiosxi | 0 bytes |
public | xi_cmp_trapdata_log | table | nagiosxi | 0 bytes |
public | xi_commands | table | nagiosxi | 72 kB |
public | xi_deploy_agents | table | nagiosxi | 0 bytes |
public | xi_deploy_jobs | table | nagiosxi | 0 bytes |
public | xi_eventqueue | table | nagiosxi | 16 kB |
public | xi_events | table | nagiosxi | 200 kB |
public | xi_incidents | table | nagiosxi | 0 bytes |
public | xi_meta | table | nagiosxi | 17 MB |
public | xi_mibs | table | nagiosxi | 8192 bytes |
public | xi_options | table | nagiosxi | 40 kB |
public | xi_sessions | table | nagiosxi | 8192 bytes |
public | xi_sysstat | table | nagiosxi | 24 kB |
public | xi_usermeta | table | nagiosxi | 224 kB |
public | xi_users | table | nagiosxi | 16 kB |
(20 rows)

nagios1:root:/var/lib> echo '\d xi_deploy_agents;' | psql nagiosxi nagiosxi
Table "public.xi_deploy_agents"
Column | Type | Modifiers
-------------------+-----------------------------+---------------------------------------------------------------
deploy_id | integer | not null default nextval('xi_deploy_agents_id_seq'::regclass)
creator_id | integer |
deployed_date | timestamp without time zone |
last_updated_date | timestamp without time zone |
last_status_check | timestamp without time zone |
available | smallint | default 0
version | character varying(10) |
address | character varying(60) |
hostname | character varying(250) |
os | character varying(24) |
metadata | text |
Indexes:
"xi_deploy_agents_pkey" PRIMARY KEY, btree (deploy_id)

nagios1:root:/var/lib> echo '\d xi_deploy_jobs;' | psql nagiosxi nagiosxi
Table "public.xi_deploy_jobs"
Column | Type | Modifiers
----------------+-----------------------+-------------------------------------------------------------
job_id | integer | not null default nextval('xi_deploy_jobs_id_seq'::regclass)
job_name | character varying(64) |
creator_id | integer |
version | character varying(10) |
os | character varying(24) |
addresses | text |
ncpa_token | text |
username | character varying(64) |
password | text |
vault_password | text |
sudo | smallint | default 0
status | integer |
pid | integer |
metadata | text |
Indexes:
"xi_deploy_jobs_pkey" PRIMARY KEY, btree (job_id)

And from out test environment:

nagios2:root:/var/lib/mysql> psql -V
psql (PostgreSQL) 8.4.20
contains support for command-line editing
nagios2:root:/var/lib/mysql> echo "\dt+;"|psql nagiosxi nagiosxi
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------------+-------+----------+------------+-------------
public | xi_auditlog | table | nagiosxi | 88 kB |
public | xi_auth_tokens | table | nagiosxi | 0 bytes |
public | xi_cmp_ccm_backups | table | nagiosxi | 0 bytes |
public | xi_cmp_favorites | table | nagiosxi | 0 bytes |
public | xi_cmp_nagiosbpi_backups | table | nagiosxi | 0 bytes |
public | xi_cmp_trapdata | table | nagiosxi | 0 bytes |
public | xi_cmp_trapdata_log | table | nagiosxi | 0 bytes |
public | xi_commands | table | nagiosxi | 0 bytes |
public | xi_deploy_agents | table | nagiosxi | 0 bytes |
public | xi_deploy_jobs | table | nagiosxi | 0 bytes |
public | xi_eventqueue | table | nagiosxi | 16 kB |
public | xi_events | table | nagiosxi | 0 bytes |
public | xi_incidents | table | nagiosxi | 0 bytes |
public | xi_meta | table | nagiosxi | 472 kB |
public | xi_mibs | table | nagiosxi | 0 bytes |
public | xi_options | table | nagiosxi | 32 kB |
public | xi_sessions | table | nagiosxi | 0 bytes |
public | xi_sysstat | table | nagiosxi | 16 kB |
public | xi_usermeta | table | nagiosxi | 152 kB |
public | xi_users | table | nagiosxi | 8192 bytes |
(20 rows)

nagios2:root:/var/lib/mysql> echo '\d xi_deploy_agents;' | psql nagiosxi nagiosxi
Table "public.xi_deploy_agents"
Column | Type | Modifiers
-------------------+-----------------------------+---------------------------------------------------------------
deploy_id | integer | not null default nextval('xi_deploy_agents_id_seq'::regclass)
creator_id | integer |
deployed_date | timestamp without time zone |
last_updated_date | timestamp without time zone |
last_status_check | timestamp without time zone |
available | smallint | default 0
version | character varying(10) |
address | character varying(60) |
hostname | character varying(250) |
os | character varying(24) |
metadata | text |
Indexes:
"xi_deploy_agents_pkey" PRIMARY KEY, btree (deploy_id)

nagios2:root:/var/lib/mysql> echo '\d xi_deploy_jobs;' | psql nagiosxi nagiosxi
Table "public.xi_deploy_jobs"
Column | Type | Modifiers
----------------+-----------------------+-------------------------------------------------------------
job_id | integer | not null default nextval('xi_deploy_jobs_id_seq'::regclass)
job_name | character varying(64) |
creator_id | integer |
version | character varying(10) |
os | character varying(24) |
addresses | text |
ncpa_token | text |
username | character varying(64) |
password | text |
vault_password | text |
sudo | smallint | default 0
status | integer |
pid | integer |
metadata | text |
Indexes:
"xi_deploy_jobs_pkey" PRIMARY KEY, btree (job_id)

[tgriep]

Thanks for the data. I was able to recreate the error in the cleaner.log file and filed a BUG fix for it.

[CBoekhuis]

OK. Is this issue also causing the high cpu consuming mysql process when applying configuration?

[tgriep]

The bug only effects the Postgres database and not the MYSQL database.

From the profile you sent in, MYSQL is not running at a high load but the Apply Config was not probably running at that time.

First, lets increase the PHP limits on the system by following this procedure.
https://support.nagios.com/kb/article/n ... e-611.html

If the memory_limit is already at 1024M, go to 2048M.

Next, open 2 root shells on the server and run the top command on one of them to see the CPU utilization.

In the other one, run the following which is the command line version of the Apply Config.

Code: Select all

su - nagios
cd /usr/local/nagiosxi/scripts
time ./reconfigure_nagios.sh

If the load for MYSQL goes really high, post the output of the top command and the full output from the other shell running the reconfigure.

[tgriep]

One more thing, upload the /var/log/nagios/var/nagios.log file to the post after you do the above.

[CBoekhuis]

Hi,

value changed from 128M ==> 1024M

nagios1:nagios:/usr/local/nagiosxi/scripts> time ./reconfigure_nagios.sh

--- reset_config_perms.sh ------------
> Setting script permissions
> Setting CCM script permissions
> Setting special script permissions
> Setting special component script permissions
> Setting configuration file/directory permissions
> Setting perfdata directory and RRD permissions
> Setting libexec directory permissions
> Setting Nagios XI config permissions
> Setting NOM checkpoint user:group permissions
> + Setting Nagios Core corelog.newobjects user:group permissions
> + Setting CCM configuration file user:group permissions
> + Setting Recurring Downtime file user:group permissions
> + Setting BPI configuration file user:group permissions
--------------------------------------

--- ccm_import.php -------------------
> Setting import directory: /usr/local/nagios/etc/import/
> Importing config files into the CCM
No files to import
--------------------------------------

--- ccm_export.php -------------------
> Writing CCM configuration to Nagios files
Finished writing out configuraton
--------------------------------------

--------------------------------------
> Verifying configuration with Nagios Core
> Output:
Nagios Core 4.4.6
Copyright (c) 2009-present Nagios Core Development Team and Community Contributors
Copyright (c) 1999-2009 Ethan Galstad
Last Modified: 2020-04-28
License: GPL

Website: https://www.nagios.org
Reading configuration data...
Read main config file okay...
WARNING: Extinfo objects are deprecated and will be removed in future versions
Read object config files okay...

Running pre-flight check on configuration data...

Checking objects...
Warning: Service 'UX_check_yum_updates_redhat' on host 'rhel8system.boekhuis.nl' has a notification interval less than its check interval! Notifications are only re-sent after checks are made, so the effective notification interval will be that of the check interval.
Checked 6502 services.
Checked 823 hosts.
Checked 67 host groups.
Checked 105 service groups.
Checked 67 contacts.
Checked 32 contact groups.
Checked 559 commands.
Checked 65 time periods.
Checked 0 host escalations.
Checked 0 service escalations.
Checking for circular paths...
Checked 823 hosts
Checked 0 service dependencies
Checked 0 host dependencies
Checked 65 timeperiods
Checking global event handlers...
Checking obsessive compulsive processor commands...
Checking misc settings...

Total Warnings: 1
Total Errors: 0

Things look okay - No serious problems were detected during the pre-flight check
> Return Code: 0
--------------------------------------
Stopping nagios: .done.
Starting nagios: done.

real 0m7.308s
user 0m1.836s
sys 0m0.846s