SQL ERROR: syntax error while applying configuration

blalande · Post by **blalande** » Fri Apr 06, 2018 7:52 am

Post by **tgriep** » Fri Apr 06, 2018 1:06 pm

When an Apply Config is ran, it logs the Apply to the Audit log so that is why you see the error at that time.
Can you post this file from the Nagios server here so we can view the settings.

Code: Select all

/usr/local/nagiosxi/html/config.inc.php

One thing that could cause the error is that the sequence numbers in the postgres table are out of sequence so it will not allow the data to bet stored in the tables.
To fix that, login to the Nagios serevr as root and run the following commands.

Code: Select all

service nagios stop
killall -9 nagios
cd /usr/local/nagiosxi/tools
./fix_postgres_sequences.sh

service postgresql restart
service nagios start

Let the system run for a 5 minutes and see if you can Apply a Config without the error.

A lot of the data in the nagiosxi database is temporary data so you may not want to restore it.
What is stored in it are user account information, dashboard settings, views that are created.

If you did not do much changes for the users, you may not want to restore the database.

blalande · Post by **blalande** » Mon Apr 09, 2018 3:33 am

Here is the config.inc.php:

Code: Select all

<?php
//
// Copyright (c) 2008-2010 Nagios Enterprises, LLC.  All rights reserved.
//
// $Id: config.inc.php 202 2010-07-13 21:22:57Z egalstad $

// base url
$cfg['base_url'] = "/nagiosxi"; // do not include http(s) or host name - this is the base from "http://localhost"

// base root directory where XI is installed
$cfg['root_dir'] = "/usr/local/nagiosxi";

// directory where scripts are installed
$cfg['script_dir'] = "/usr/local/nagiosxi/scripts";

$cfg['xidpe_dir'] = '/usr/local/nagios/var/spool/xidpe/';
$cfg['perfdata_spool'] = '/usr/local/nagios/var/spool/perfdata/';

// nom checkpoints
$cfg['nom_checkpoints_dir'] = "/usr/local/nagiosxi/nom/checkpoints/nagioscore/";

// force http/https
$cfg['use_https'] = false; // determines whether cron jobs and other scripts will force the use of HTTPS instead of HTTP

// allow for different http port for subsystem calls
$cfg['port_number'] = false;

// default server, db, connection settings
$cfg['dbtype'] = ''; // this setting is no longer used - use settings below
$cfg['dbserver'] = 'localhost'; // this setting is no longer used - use settings below

// db-specific connection information
$cfg['db_info'] = array(
    "nagiosxi" => array(
        "dbtype" => 'pgsql',
        "dbserver" => 'localhost',
        "user" => 'nagiosxi',
        "pwd" => 'n@gweb',
        "db" => 'nagiosxi',
        "dbmaint" => array( // variables affecting maintenance of db
            "max_auditlog_age" => 30, // max time (in DAYS) to keep audit log entries
            "max_commands_age" => 480, // max time (minutes) to keep commands
            "max_events_age" => 480, // max time (minutes) to keep events
            "optimize_interval" => 60, // time (in minutes) between db optimization runs
            "repair_interval" => 0, // time (in minutes) between db repair runs
        ),
    ),
    "ndoutils" => array(
        "dbtype" => 'mysql',
        "dbserver" => 'localhost',
        "user" => 'ndoutils',
        "pwd" => 'n@gweb',
        "db" => 'nagios',
        "dbmaint" => array( // variables affecting maintenance of ndoutils db

            "max_externalcommands_age" => 7, // max time (in DAYS) to keep external commands
            "max_logentries_age" => 90, // max time (in DAYS) to keep log entries
            "max_statehistory_age" => 730, // max time (in DAYS) to keep state history information
            "max_notifications_age" => 90, // max time (in DAYS) to keep notifications
            "max_timedevents_age" => 5, // max time (minutes) to keep timed events
            "max_systemcommands_age" => 5, // max time (minutes) to keep system commands
            "max_servicechecks_age" => 5, // max time (minutes) to keep service checks
            "max_hostchecks_age" => 5, // max time (minutes) to keep host checks
            "max_eventhandlers_age" => 5, // max time (minutes) to keep event handlers
            "optimize_interval" => 60, // time (in minutes) between db optimization runs
            "repair_interval" => 0, // time (in minutes) between db repair runs
        ),
    ),
    "nagiosql" => array(
        "dbtype" => 'mysql',
        "dbserver" => 'localhost',
        "user" => 'nagiosql',
        "pwd" => 'n@gweb',
        "db" => 'nagiosql',
        "dbmaint" => array( // variables affecting maintenance of db
            "max_logbook_age" => 480, // max time (minutes) to keep log book records
            "optimize_interval" => 60, // time (in minutes) between db optimization runs
            "repair_interval" => 0, // time (in minutes) between db repair runs
        ),
    ),
);

// db-specific table prefixes
$cfg['db_prefix'] = array(
    "ndoutils" => "nagios_", // prefix for NDOUtils tables
    "nagiosxi" => "xi_", // prefix for XI tables
    "nagiosql" => "tbl_", // prefix for NagiosQL tables
);

// component info
$cfg['component_info'] = array(
    "nagioscore" => array(
        "cgi_dir" => "/usr/local/nagios/sbin",
        "import_dir" => "/usr/local/nagios/etc/import",
        "plugin_dir" => "/usr/local/nagios/libexec",
        "cgi_config_file" => "/usr/local/nagios/etc/cgi.cfg",
        "cmd_file" => "/usr/local/nagios/var/rw/nagios.cmd",
        "log_file" => "/usr/local/nagios/var/nagios.log",
        "nom_checkpoint_interval" => 1440, // time (in minutes) between nom checkpoints
    ),
    "pnp" => array(
        "perfdata_dir" => "/usr/local/nagios/share/perfdata",
        "share_dir" => "/usr/local/nagios/share/pnp",
        "direct_url" => "/nagios/pnp",
        "username" => 'nagiosxi', // don't change this!
        "password" => 'nagiosadmin', // this gets reset when security credentials are reset after installation
    ),
    "perfdata" => array(
        "rrdtool_path" => "/usr/bin/rrdtool",
    ),
    "nagiosql" => array(
        "dir" => "/var/www/html/nagiosql",
        "direct_url" => "/nagiosql",
        "username" => 'nagiosxi', // don't change this!
        "password" => 'n@gweb', // this gets reset when security credentials are reset after installation
    ),
    "nagvis" => array(
        "share_dir" => "/usr/local/nagios/share/nagvis",
        "direct_url" => "/nagios/nagvis",
        "username" => 'nagiosadmin', // don't change this!
        "password" => 'nagiosadmin', // this gets reset when security credentials are reset after installation
    ),
);

$cfg['demo_mode'] = false; // is this in demo mode

$cfg['dashlet_refresh_multiplier'] = 1000; // milliseconds (1 second = 1000)

// REFRESH RATES FOR VARIOUS DASHLETS (IN SECONDS UNLESS THE MULTIPLIER IS CHANGED)
$cfg['dashlet_refresh_rates'] = array(
    "available_updates" => 24 * 60 * 60, // 24 hours
    "systat_eventqueuechart" => 5,
    "sysstat_monitoringstats" => 30,
    "systat_monitoringperf" => 30,
    "sysstat_monitoringproc" => 30,
    "perfdata_chart" => 60, // performance graphs
    "network_outages" => 30,
    "host_status_summary" => 60,
    "service_status_summary" => 60,
    "hostgroup_status_overview" => 60,
    "hostgroup_status_grid" => 60,
    "servicegroup_status_overview" => 60,
    "servicegroup_status_grid" => 60,
    "hostgroup_status_summary" => 60,
    "servicegroup_status_summary" => 60,
    "sysstat_componentstates" => 7,
    "sysstat_serverstats" => 5,
    "network_outages_summary" => 30,
    "network_health" => 30,
    "host_status_tac_summary" => 30,
    "service_status_tac_summary" => 30,
    "feature_status_tac_summary" => 30,
    "admin_tasks" => 60,
    "getting_started" => 60,
    "pagetop_alert_content" => 30, // not a dashlet yet, sits in page header
    "tray_alert" => 30, // sites in page footer
);


// MEMCACHED SETUP
$cfg['memcached_enable'] = false; // should we use memcached or not?
$cfg['memcached_hosts'] = array('127.0.0.1', '192.168.1.3'); // one or more memcached servers
$cfg['memcached_port'] = 11211; // default memcached port
$cfg['memcached_compress'] = false; // use true to store items compressed
$cfg['memcached_ttl'] = 10; // max number of seconds data (from SELECT statements) should be cached


// HTTP BASIC AUTHENTICATION INFO -- USED BY SUBSYSTEM
$cfg['use_basic_authentication'] = false; // is HTTP Basic authentication being used? if so, set the two variables below...
$cfg['subsystem_basic_auth_username'] = 'nagiosxi'; // subsystem credentials
$cfg['subsystem_basic_auth_password'] = 'somepassword';

$cfg['default_language'] = 'en_US'; // default language
$cfg['default_theme'] = ''; // default theme

// available languages
$cfg['languages'] = array(
    "en_US" => "English",
);


/*********   DO NOT MODIFY ANYTHING BELOW THIS LINE   **********/

$cfg['default_instance_id'] = 1; // default ndoutils instance to read from
$cfg['default_result_records'] = 100000; // max number of records to return by default

$cfg['online_help_url'] = "http://support.nagios.com/"; // comment this out to disable online help links
$cfg['feedback_url'] = "http://api.nagios.com/feedback/";
$cfg['privacy_policy_url'] = "http://www.nagios.com/legal/privacypolicy/";

//$cfg['db_version']=101;
$cfg['db_version'] = 113;
//$cfg['product_version']='2009RC1';

$cfg['subsystem_ticket'] = "12345"; // default - this gets reset...

$cfg['htaccess_file'] = "/usr/local/nagiosxi/etc/htpasswd.users";
$cfg['htpasswd_path'] = "/usr/bin/htpasswd";

$cfg['enable_analytics'] = 1;

// Secure notification %responseurl%
// setting this to 1 will force users to login in the response URL
// $cfg['secure_response_url']=1;

// Globally disable in page help system
// $cfg['disable_helpsystem']=1;

///////// keep these in order /////////

// include generic db defs
require_once(dirname(__FILE__) . '/includes/db.inc.php');

// include generic  definitions
require_once(dirname(__FILE__) . '/db/common.inc.php');
// include db-specific definitions
//require_once(dirname(__FILE__).'/db/'.$cfg['dbtype'].'.inc.php');

I ran the fix_postgres_sequences.sh script but OLD VALUES and NEW VALUES are the same, and I'm still getting the error message

Post by **tgriep** » Mon Apr 09, 2018 12:03 pm

Try following this KB article to see if it resolves the error.
https://support.nagios.com/kb/article/n ... r-754.html
Let us know how it works out.

blalande · Post by **blalande** » Tue Apr 10, 2018 3:13 am

I already went through this KB and it didn't solved my issue...

Here is the /var/lib/pgsql/data/pg_log/postgresql-Tue.log (action done: apply a configuration)

Code: Select all

ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  relation "xi_notifications" does not exist
STATEMENT:  VACUUM ANALYZE xi_notifications;
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:47:27','Nagios XI','b.lalande',16,'User authenticated
 against Nagios XI credentials','x.x.59.42')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:47:27','Nagios XI','b.lalande',32,'User submitted a c
ommand to the subsystem (ID=1100)','x.x.59.42')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:47:27','Nagios XI','b.lalande',16,'User logged in','1
0.107.59.42')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:44','Nagios CCM','b.lalande',4,'Auto-login via Nag
ios XI successful','localhost')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:51','Nagios CCM','b.lalande',4,'Config file delete
d: check_ajp_susan_node_1.cfg','localhost')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:55','Nagios XI','b.lalande',32,'User applied a new
 monitoring configuration','xx.xx.59.42')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:55','Nagios XI','b.lalande',32,'User submitted a c
ommand to the subsystem (ID=17)','xx.xx.59.42')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:56','Nagios XI','NULL',32,'cmdsubsys: User applied
 a new configuration to Nagios Core','localhost')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:56','Nagios CCM','nagiosxi',16,'nagiosxi successfu
lly logged into Nagios CCM','localhost')
ERROR:  syntax error at or near "user" at character 42
STATEMENT:  INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('2018-04-10 07:53:57','Nagios CCM','nagiosxi',16,'nagiosxi successfu
lly logged into Nagios CCM','localhost')

Regarding "relation "xi_notifications" does not exist" error, I don't know if there's a link or not but notifications are disabled in the moitoring engine.
Here are informations you requested in a similar post related to this error:

Code: Select all

[root@va153avr pg_log]# echo "\dt+;"|psql nagiosxi nagiosxi
                          List of relations
 Schema |     Name     | Type  |  Owner   |    Size    | Description
--------+--------------+-------+----------+------------+-------------
 public | xi_auditlog  | table | nagiosxi | 0 bytes    |
 public | xi_commands  | table | nagiosxi | 0 bytes    |
 public | xi_events    | table | nagiosxi | 48 kB      |
 public | xi_incidents | table | nagiosxi | 0 bytes    |
 public | xi_meta      | table | nagiosxi | 408 kB     |
 public | xi_options   | table | nagiosxi | 8192 bytes |
 public | xi_sysstat   | table | nagiosxi | 16 kB      |
 public | xi_usermeta  | table | nagiosxi | 96 kB      |
 public | xi_users     | table | nagiosxi | 8192 bytes |
(9 rows)

Post by **tgriep** » Tue Apr 10, 2018 2:22 pm

The "xi_notifications" does not exist" message, you can ignore, it is an known issue as that table does not exist in the database.

Can you post your /var/lib/pgsql/data/postgresql.conf file and run the following as root and post the output so I can see what version of Postgress that is installed on the server?

Code: Select all

psql -V

blalande · Post by **blalande** » Tue Apr 10, 2018 3:48 pm

Code: Select all

psql (PostgreSQL) 8.4.20

postgresql.conf

Post by **tgriep** » Wed Apr 11, 2018 9:42 am

Can you run the following commands on the Nagios server and post the output?

Code: Select all

echo 'SHOW standard_conforming_strings ;' |psql nagiosxi nagiosxi
echo 'select * from xi_auditlog;' |psql nagiosxi nagiosxi
echo 'select * from xi_auditlog_auditlog_id_seq;' |psql nagiosxi nagiosxi
grep INSERT /usr/local/nagiosxi/html/includes/utils-auditlog.inc.php

When you went back to using the Postgres database, did you do a restore to the Postgres database or did you only changed the config.inc.php file to point back to the Postgress database.

blalande · Post by **blalande** » Wed Apr 11, 2018 10:39 am

Code: Select all

[root@va153avr ~]# echo 'SHOW standard_conforming_strings ;' |psql nagiosxi nagiosxi
 standard_conforming_strings
-----------------------------
 off
(1 row)

[root@va153avr ~]# echo 'select * from xi_auditlog;' |psql nagiosxi nagiosxi
 auditlog_id | log_time | source | user | type | message | ip_address
-------------+----------+--------+------+------+---------+------------
(0 rows)

[root@va153avr ~]# echo 'select * from xi_auditlog_auditlog_id_seq;' |psql nagiosxi nagiosxi
        sequence_name        | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-----------------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 xi_auditlog_auditlog_id_seq |      26615 |           1 |            1 | 9223372036854775807 |         1 |           1 |      26 | f         | t
(1 row)

[root@va153avr ~]# grep INSERT /usr/local/nagiosxi/html/includes/utils-auditlog.inc.php
    $sql = "INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('" . escape_sql_param($t, DB_NAGIOSXI) . "','" . escape_sql_param($source, DB_NAGIOSXI) . "','" . escape_sql_param($user, DB_NAGIOSXI) . "'," . escape_sql_param($type, DB_NAGIOSXI) . ",'" . escape_sql_param($message, DB_NAGIOSXI) . "','" . escape_sql_param($ipaddress, DB_NAGIOSXI) . "')";
[root@va153avr ~]#

I have just pointed back to the local postgres DB

Post by **tgriep** » Wed Apr 11, 2018 4:36 pm

Is the server still runing XI 2014R2.7 ?

On my system, the SQL INSERT is formatted differently than your system.
Try this, edit the /usr/local/nagiosxi/html/includes/utils-auditlog.inc.php file and find this line and change it from

Code: Select all

$sql = "INSERT INTO xi_auditlog (log_time,source,user,type,message,ip_address) VALUES ('" . escape_sql_param($t, DB_NAGIOSXI) . "','" . escape_sql_param($source, DB_NAGIOSXI) . "','" . escape_sql_param($user, DB_NAGIOSXI) . "'," . escape_sql_param($type, DB_NAGIOSXI) . ",'" . escape_sql_param($message, DB_NAGIOSXI) . "','" . escape_sql_param($ipaddress, DB_NAGIOSXI) . "')";

to

Code: Select all

$sql = "INSERT INTO xi_auditlog (log_time,source,\"user\",type,message,ip_address) VALUES ('" . escape_sql_param($t, DB_NAGIOSXI) . "','" . escape_sql_param($source, DB_NAGIOSXI) . "','" . escape_sql_param($user, DB_NAGIOSXI) . "'," . escape_sql_param($type, DB_NAGIOSXI) . ",'" . escape_sql_param($message, DB_NAGIOSXI) . "','" . escape_sql_param($ipaddress, DB_NAGIOSXI) . "')";

Save the file out and see if the error is gone when the Apply Config is ran.

Nagios Support Forum

SQL ERROR: syntax error while applying configuration

SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration

Re: SQL ERROR: syntax error while applying configuration