Home » Categories » Products » Nagios XI » Troubleshooting » Common Problems

Nagios XI - SQL Error [nagiosxi] : ERROR: syntax error

Problem Description

This KB article explains how to resolve an error similar to the following that appears in the Nagios XI GUI:

SQL Error [nagiosxi] : ERROR:  syntax error at or near "localhost"
LINE 1: ...ios XI','nagiosadmin',4,'User reconfigured host \'localhost\...

 

The postgresql log file in /var/lib/pgsql/data/pg_log/ will have more detailed information:

ERROR:  syntax error at or near "localhost" at character 166
STATEMENT: INSERT INTO xi_auditlog (log_time,source,"user",type,message,ip_address,details) VALUES ('2017-09-06 14:15:29','Nagios XI','nagiosadmin',4,'User reconfigured host \'localhost\'','10.9.2.41','Array
(
[host_name] => localhost
[use] => linux-server
[alias] => localhost
[address] => 127.0.0.1
[first_notification_delay] => 0
[notifications_enabled] => 1
[register] => 1
[contacts] => nagiosadmin
)
')

 

This error occurs on the Host or Service Status Detail page > Configure tab, when you are using the re-configure a host or service option.

The problem only occurs with older installations of Nagios XI that historically used PostgreSQL for the nagiosxi database. Fresh installations of Nagios XI since 5.x do not experience this issue.

The problem only occurs with PostgreSQL 9.1 or greater.

 

 

Resolution

A simple change to the postgresql.conf file will resolve this problem.

Establish a terminal session to your Nagios XI server as root and edit the postgresql.conf file with this command:

vi /var/lib/pgsql/data/postgresql.conf

When using the vi editor, to make changes press i on the keyboard first to enter insert mode. Press Esc to exit insert mode.

 

Locate the following line:

#standard_conforming_strings =

Tip: typing /conform and pressing Enter in vi should take you directly to this section in the file.

 

Remove the # from the begging of the line and set it to off:

standard_conforming_strings = off

 

When you have finished, save the changes in vi by typing:

:wq

and press Enter.

 

You need to restart the postgresql service using one of the commands below:

 

You need to restart the service using one of the commands below:

RHEL 6 | CentOS 6 | Oracle Linux 6 | Ubuntu 14

service postgresql restart

 

RHEL 7/8 | CentOS 7/8 | Oracle Linux 7 /8| Debian | Ubuntu 16/18/20

systemctl restart postgresql.service

 

This completes the changes required for resolving the problem described above.

 

 

Further Information

The following documentation explains in detail the standard_conforming_strings option:

https://www.postgresql.org/docs/9.1/static/release-9-1.html

 

 

Final Thoughts

For any support related questions please visit the Nagios Support Forums at:

http://support.nagios.com/forum/

0 (0)
Article Rating (No Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Nagios XI - Optimizing The PHP Settings File
Viewed 5899 times since Thu, Jul 13, 2017
Nagios XI - Core 4 Load Spikes on 1.75 and 7 Hour Intervals
Viewed 1623 times since Mon, Jan 25, 2016
Nagios XI - Schedulded Backups No Longer Working
Viewed 2647 times since Fri, Jun 3, 2016
Nagios XI - Resetting The nagiosadmin Password
Viewed 20990 times since Mon, Jan 25, 2016
Nagios XI - Ajaxterm Installation Aborted
Viewed 1509 times since Tue, Jan 26, 2016
Nagios XI - Debugging Bandwidth Performance Graphs
Viewed 6213 times since Tue, Jan 27, 2015
Nagios XI - STRICT_TRANS_TABLES
Viewed 2237 times since Thu, Nov 16, 2017
Nagios XI - Notifications Not Sending In XI 5.3.0
Viewed 1765 times since Tue, Oct 4, 2016
Nagios XI - Oracle Services Critical After Nagios XI Upgrade
Viewed 1838 times since Wed, Jan 27, 2016
Nagios XI - Apply Configuration Fails - Backend login to the Core Configuration failed
Viewed 3970 times since Tue, Aug 2, 2016