Please help with this SQL error!!

Support forum for Nagios Core, Nagios Plugins, NCPA, NRPE, NSCA, NDOUtils and more. Engage with the community of users including those using the open source solutions.
AnaExp
Posts: 6
Joined: Thu Apr 27, 2017 3:50 am

Please help with this SQL error!!

Post by AnaExp »

Hello,

I need resolv this urgently.

I have the following tools:

CentOS Linux release 7.3.1611 (Core)
nagios core Version 4.1.1
nagiosql version 3.2.0
mysql version 5.6.35
ndoutils version 2.0.1
p4p nagios (i dont know which version is)

Some weeks ago, when I click an host in nagiosql this show me a error in red:

""" You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`value' at line 4 ""

And whebn I see in messages log, appear this error constantly:

""Apr 27 11:01:12 monitoring ndo2db: mysql_stmt_error: Duplicate entry '9212588' for key 'PRIMARY'
Apr 27 11:01:21 monitoring ndo2db: Error: mysql_stmt_execute() failed for statement 6.""


I have some host monitoring in this moment but only one host is not showing the graphics for the check host alive.

Someone could help me to fix this? :(
This host is very important for me

Thank you..
User avatar
mcapra
Posts: 3739
Joined: Thu May 05, 2016 3:54 pm

Re: Please help with this SQL error!!

Post by mcapra »

We'd have to see the full query being ran to begin to troubleshoot this. Can you try increasing the logging verbosity of ndo2db by editing ndo2db.cfg and setting the debug_level to 2? This will create an AWFUL lot of messages in the debug_file, so I wouldn't recommend running it for very long. I'd be interested in seeing specifically which queries are failing, though.

If you have a dev -> test -> prod setup, you might also try upgrading your Nagios Core version to the latest. I wouldn't do that on an unstable production machine, though.
Former Nagios employee
https://www.mcapra.com/
AnaExp
Posts: 6
Joined: Thu Apr 27, 2017 3:50 am

Re: Please help with this SQL error!!

Post by AnaExp »

Hello, Thanks for reply!!

I changed the leverl 2, was configured in -1 (Everything), but i guess that will show the same.
Ok, when i resolve the problem ill change to level 1.

The output of the debug file:

Code: Select all

[1493377263.382377] [002.0] [pid=5635] SELECT instance_id FROM nagios_instances WHERE instance_name='localhost'
[1493377263.383108] [002.0] [pid=5635] INSERT INTO nagios_conninfo SET instance_id=2, connect_time=NOW(), last_checkin_time=NOW(), bytes_processed=0, lines_processed=0, entries_processed=0, agent_name='NDOMOD', agent_version='2.1b1', disposition='REALTIME', connect_source='TCPSOCKET', connect_type='INITIAL', data_start_time=FROM_UNIXTIME(1493377263)
[1493377263.383411] [002.0] [pid=5635] SELECT object_id, objecttype_id, name1, name2 FROM nagios_objects WHERE instance_id='2'
[1493377263.385125] [002.0] [pid=5635] SELECT UNIX_TIMESTAMP(status_update_time) AS latest_time FROM nagios_programstatus WHERE instance_id=2 ORDER BY status_update_time DESC LIMIT 0,1
[1493377263.385491] [002.0] [pid=5635] SELECT UNIX_TIMESTAMP(status_update_time) AS latest_time FROM nagios_hoststatus WHERE instance_id=2 ORDER BY status_update_time DESC LIMIT 0,1
[1493377263.385756] [002.0] [pid=5635] SELECT UNIX_TIMESTAMP(status_update_time) AS latest_time FROM nagios_servicestatus WHERE instance_id=2 ORDER BY status_update_time DESC LIMIT 0,1
[1493377263.385947] [002.0] [pid=5635] SELECT UNIX_TIMESTAMP(status_update_time) AS latest_time FROM nagios_contactstatus WHERE instance_id=2 ORDER BY status_update_time DESC LIMIT 0,1
[1493377263.386222] [002.0] [pid=5635] SELECT UNIX_TIMESTAMP(queued_time) AS latest_time FROM nagios_timedeventqueue WHERE instance_id=2 ORDER BY queued_time DESC LIMIT 0,1
[1493377263.386429] [002.0] [pid=5635] SELECT UNIX_TIMESTAMP(entry_time) AS latest_time FROM nagios_comments WHERE instance_id=2 ORDER BY entry_time DESC LIMIT 0,1
It's enough?

[...]

Yes, update the nagios was one of the solutions that i was thinking about but my fear is broke the nagios more than now.
Anyway, I can do a clon of this machine and i will try this solution.

If I upgrade nagios, i have to upgrade anything else? i mean for compatibility issues.
User avatar
tgriep
Madmin
Posts: 9177
Joined: Thu Oct 30, 2014 9:02 am

Re: Please help with this SQL error!!

Post by tgriep »

There could be a corrupt entry in the MYSQL database and running a repair should fix the issue.
Run the following to repair all of the mysql databases on the system. Replace the <usename> and <password> with valid entries.

Code: Select all

mysqlcheck -f -r -u <username> -p<password> --all-databases
If that doesn't work, run the command this way to rebuild the index.

Code: Select all

mysqlcheck -f -r -u <username> -p<password> --all-databases --use_frm
Try that to see if that fixes the issue for you.
Be sure to check out our Knowledgebase for helpful articles and solutions!
AnaExp
Posts: 6
Joined: Thu Apr 27, 2017 3:50 am

Re: Please help with this SQL error!!

Post by AnaExp »

Hello!

Good and bad news.

I tried the solution that tgriep said with both commands and the error is not showing in the messages log anymore:

Code: Select all

[root@monitoring ~]# tail -f /var/log/messages
May  2 10:33:54 monitoring systemd: Started Session 1 of user root.
May  2 10:33:54 monitoring systemd: Starting Session 1 of user root.
May  2 10:33:54 monitoring systemd-logind: New session 1 of user root.
May  2 10:33:54 monitoring systemd: Started Session 2 of user root.
May  2 10:33:54 monitoring systemd-logind: New session 2 of user root.
May  2 10:33:54 monitoring systemd: Starting Session 2 of user root.
May  2 10:33:54 monitoring dbus[635]: [system] Activating service name='org.freedesktop.problems' (using servicehelper)
May  2 10:33:54 monitoring dbus-daemon: dbus[635]: [system] Activating service name='org.freedesktop.problems' (using servicehelper)
May  2 10:33:54 monitoring dbus[635]: [system] Successfully activated service 'org.freedesktop.problems'
May  2 10:33:54 monitoring dbus-daemon: dbus[635]: [system] Successfully activated service 'org.freedesktop.problems'
May  2 10:34:01 monitoring systemd: Started Session 3 of user root.
May  2 10:34:01 monitoring systemd: Starting Session 3 of user root.
May  2 10:34:14 monitoring fprintd: ** Message: No devices in use, exit
May  2 10:35:01 monitoring systemd: Started Session 4 of user root.
May  2 10:35:01 monitoring systemd: Starting Session 4 of user root.
May  2 10:36:01 monitoring systemd: Started Session 5 of user root.
May  2 10:36:01 monitoring systemd: Starting Session 5 of user root.
May  2 10:36:16 monitoring chronyd[706]: Source xxx.xxx.xx.xx replaced with xxx.xxx.xx.xx
May  2 10:37:01 monitoring systemd: Started Session 6 of user root.
May  2 10:37:01 monitoring systemd: Starting Session 6 of user root.
May  2 10:38:01 monitoring systemd: Started Session 7 of user root.
May  2 10:38:01 monitoring systemd: Starting Session 7 of user root.
But still showing error in nagiosql and without showing graphics:
Image

Image

Any idea? Seem that both are different issue :(

Thanks
User avatar
tgriep
Madmin
Posts: 9177
Joined: Thu Oct 30, 2014 9:02 am

Re: Please help with this SQL error!!

Post by tgriep »

Both the nagiosql and the pnp4nagios applications you are having issues with were not written by nagios so we can not support them fully.
You may have to go the the developers web site for more support.
Bad news is that nagiosql has been de-supported for a year or so so there may not be a fix for that issue.
The pnp4nagios web site you can go to to see about the graphing issue issue is below.
https://docs.pnp4nagios.org/
Be sure to check out our Knowledgebase for helpful articles and solutions!
AnaExp
Posts: 6
Joined: Thu Apr 27, 2017 3:50 am

Re: Please help with this SQL error!!

Post by AnaExp »

Ok i will ask there.

Anyway i discovered something:

I found the perfdata log and it says this:

Code: Select all

[root@monitoring var]# tail -f perfdata.log
2017-05-03 10:56:10 [3442] [0] RRDs::update /usr/local/pnp4nagios/var/perfdata/****/_HOST_.rrd 1493801759:97.212997:0
2017-05-03 10:56:10 [3442] [0] RRDs::update ERROR /usr/local/pnp4nagios/var/perfdata/****/_HOST_.rrd: illegal attempt to update using time 1493801759 when last update time is 1618538358 (minimum one second step)
2017-05-03 10:56:25 [3501] [0] RRDs::update /usr/local/pnp4nagios/var/perfdata/****/_HOST_.rrd 1493801775:0.636000:0
2017-05-03 10:56:25 [3501] [0] RRDs::update ERROR /usr/local/pnp4nagios/var/perfdata/***/_HOST_.rrd: illegal attempt to update using time 1493801775 when last update time is 1618538358 (minimum one second step)
That host is not the unique that are not graphing
dwhitfield
Former Nagios Staff
Posts: 4583
Joined: Wed Sep 21, 2016 10:29 am
Location: NoLo, Minneapolis, MN
Contact:

Re: Please help with this SQL error!!

Post by dwhitfield »

your hwclock, date, and grep date.timezone /etc/php.ini may not be in sync. Please post the output of those three commands.
AnaExp
Posts: 6
Joined: Thu Apr 27, 2017 3:50 am

Re: Please help with this SQL error!!

Post by AnaExp »

Hello all!

I found a solution.

Deduced that the problem was in timestamp of some _HOST_.rrd (I don't know why this happened) so i copied from another _HOST_.rrd working with the current timestamp and it works!

Now graphics are writing in the replaced database.

I dont know if is a good solution but is the solution that i found

But the error that appear in nagiosql it is not related with the above because still there:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`value' at line 4

Thanks to all
Bad news is that nagiosql has been de-supported for a year or so so there may not be a fix for that issue.
Why they stopped to support nagios? :( this no has sense
AnaExp
Posts: 6
Joined: Thu Apr 27, 2017 3:50 am

Re: Please help with this SQL error!!

Post by AnaExp »

dwhitfield wrote:your hwclock, date, and grep date.timezone /etc/php.ini may not be in sync. Please post the output of those three commands.


Ey!

All seem in the right time :( was the first things that i checked

[root@monitoring var]# hwclock
Wed 03 May 2017 05:16:53 PM CEST -0.209484 seconds
[root@monitoring var]# date
Wed May 3 17:17:03 CEST 2017
[root@monitoring var]# grep date.timezone /etc/php.ini
; http://php.net/date.timezone
date.timezone = 'Europe/Madrid'
[root@monitoring var]#

So any idea?

My machine run as a VM.


EDIT:

Searching in web i found how look the LC_TIME variable, currently got this:

[root@monitoring var]# locale | grep LC_TIME
LC_TIME="en_US.UTF-8"

Could this be a problem?
Locked