SQL Error with a no admin user.

[igarcia]

Hi,
I'm deploying a new nagios instance in my company, I have a normal user for the helpdesk to see all the production host (under a contactgroup associate to the default production template).

Everything well, but when I turn on the monitoring of the bandwidth of all my switch, now the helpdesk user show a error under the service sumary:

Code: Select all

SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9 SQL: SQL Error [ndoutils] : 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 ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9

Over the nagios core page I see everything with the hdesk user:

Code: Select all

Host Status Totals
Up	Down	Unreachable	Pending
831	3	0	0
All Problems	All Types
3	834
Service Status Totals
Ok	Warning	Unknown	Critical	Pending
4715	5	4	23	0
All Problems	All Types
32	4747

The OS is:
Description: Red Hat Enterprise Linux Server release 6.7 (Santiago)
Release: 6.7
Codename: Santiago

The mysql is offload (5.7.9-enterprise-commercial)
The nagiosxi version is 5.2.3.

Thanks,
Regards

P.S., with my admin user, All work fine.

[rkennedy]

Can you post a screenshot from the normal user as reference for us to take a look at?

Can you also run the following command and post the output?

Code: Select all

php /usr/local/nagiosxi/cron/dbmaint.php

[igarcia]

This is the output:

Code: Select all

php /usr/local/nagiosxi/cron/dbmaint.php
CREATING: /usr/local/nagiosxi/var/dbmaint.lock
CLEANING ndoutils TABLE 'commenthistory'...
SQL: DELETE FROM nagios_commenthistory WHERE entry_time < FROM_UNIXTIME(1421655703)
CLEANING ndoutils TABLE 'processevents'...
SQL: DELETE FROM nagios_processevents WHERE event_time < FROM_UNIXTIME(1421655703)
CLEANING ndoutils TABLE 'externalcommands'...
SQL: DELETE FROM nagios_externalcommands WHERE entry_time < FROM_UNIXTIME(1452586903)
CLEANING ndoutils TABLE 'logentries'...
SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1445415703)
CLEANING ndoutils TABLE 'notifications'...
SQL: DELETE FROM nagios_notifications WHERE start_time < FROM_UNIXTIME(1452932503)
CLEANING ndoutils TABLE 'contactnotifications'...
SQL: DELETE FROM nagios_contactnotifications WHERE start_time < FROM_UNIXTIME(1452932503)
CLEANING ndoutils TABLE 'contactnotificationmethods'...
SQL: DELETE FROM nagios_contactnotificationmethods WHERE start_time < FROM_UNIXTIME(1452932503)
CLEANING ndoutils TABLE 'statehistory'...
SQL: DELETE FROM nagios_statehistory WHERE state_time < FROM_UNIXTIME(1390119703)
CLEANING ndoutils TABLE 'timedevents'...
SQL: DELETE FROM nagios_timedevents WHERE event_time < FROM_UNIXTIME(1453191403)
CLEANING ndoutils TABLE 'systemcommands'...
SQL: DELETE FROM nagios_systemcommands WHERE start_time < FROM_UNIXTIME(1453191403)
CLEANING ndoutils TABLE 'servicechecks'...
SQL: DELETE FROM nagios_servicechecks WHERE start_time < FROM_UNIXTIME(1453191403)
CLEANING ndoutils TABLE 'hostchecks'...
SQL: DELETE FROM nagios_hostchecks WHERE start_time < FROM_UNIXTIME(1453191403)
CLEANING ndoutils TABLE 'eventhandlers'...
SQL: DELETE FROM nagios_eventhandlers WHERE start_time < FROM_UNIXTIME(1453191403)
TIME TO OPTIMIZE
LASTOPT:  1453188001
INTERVAL: 60
NOW:      1453191703
OPTTIME:  1453191601
OPTIMIZING NDOUTILS TABLE: nagios_acknowledgements
SQL: OPTIMIZE TABLE nagios_acknowledgements
OPTIMIZING NDOUTILS TABLE: nagios_commands
SQL: OPTIMIZE TABLE nagios_commands
OPTIMIZING NDOUTILS TABLE: nagios_commenthistory
SQL: OPTIMIZE TABLE nagios_commenthistory
OPTIMIZING NDOUTILS TABLE: nagios_comments
SQL: OPTIMIZE TABLE nagios_comments
OPTIMIZING NDOUTILS TABLE: nagios_configfiles
SQL: OPTIMIZE TABLE nagios_configfiles
OPTIMIZING NDOUTILS TABLE: nagios_configfilevariables
SQL: OPTIMIZE TABLE nagios_configfilevariables
OPTIMIZING NDOUTILS TABLE: nagios_conninfo
SQL: OPTIMIZE TABLE nagios_conninfo
OPTIMIZING NDOUTILS TABLE: nagios_contact_addresses
SQL: OPTIMIZE TABLE nagios_contact_addresses
OPTIMIZING NDOUTILS TABLE: nagios_contact_notificationcommands
SQL: OPTIMIZE TABLE nagios_contact_notificationcommands
OPTIMIZING NDOUTILS TABLE: nagios_contactgroup_members
SQL: OPTIMIZE TABLE nagios_contactgroup_members
OPTIMIZING NDOUTILS TABLE: nagios_contactgroups
SQL: OPTIMIZE TABLE nagios_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_contactnotificationmethods
SQL: OPTIMIZE TABLE nagios_contactnotificationmethods
OPTIMIZING NDOUTILS TABLE: nagios_contactnotifications
SQL: OPTIMIZE TABLE nagios_contactnotifications
OPTIMIZING NDOUTILS TABLE: nagios_contacts
SQL: OPTIMIZE TABLE nagios_contacts
OPTIMIZING NDOUTILS TABLE: nagios_contactstatus
SQL: OPTIMIZE TABLE nagios_contactstatus
OPTIMIZING NDOUTILS TABLE: nagios_customvariables
SQL: OPTIMIZE TABLE nagios_customvariables
OPTIMIZING NDOUTILS TABLE: nagios_customvariablestatus
SQL: OPTIMIZE TABLE nagios_customvariablestatus
OPTIMIZING NDOUTILS TABLE: nagios_dbversion
SQL: OPTIMIZE TABLE nagios_dbversion
OPTIMIZING NDOUTILS TABLE: nagios_downtimehistory
SQL: OPTIMIZE TABLE nagios_downtimehistory
OPTIMIZING NDOUTILS TABLE: nagios_eventhandlers
SQL: OPTIMIZE TABLE nagios_eventhandlers
OPTIMIZING NDOUTILS TABLE: nagios_externalcommands
SQL: OPTIMIZE TABLE nagios_externalcommands
OPTIMIZING NDOUTILS TABLE: nagios_flappinghistory
SQL: OPTIMIZE TABLE nagios_flappinghistory
OPTIMIZING NDOUTILS TABLE: nagios_host_contactgroups
SQL: OPTIMIZE TABLE nagios_host_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_host_contacts
SQL: OPTIMIZE TABLE nagios_host_contacts
OPTIMIZING NDOUTILS TABLE: nagios_host_parenthosts
SQL: OPTIMIZE TABLE nagios_host_parenthosts
OPTIMIZING NDOUTILS TABLE: nagios_hostchecks
SQL: OPTIMIZE TABLE nagios_hostchecks
OPTIMIZING NDOUTILS TABLE: nagios_hostdependencies
SQL: OPTIMIZE TABLE nagios_hostdependencies
OPTIMIZING NDOUTILS TABLE: nagios_hostescalation_contactgroups
SQL: OPTIMIZE TABLE nagios_hostescalation_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_hostescalation_contacts
SQL: OPTIMIZE TABLE nagios_hostescalation_contacts
OPTIMIZING NDOUTILS TABLE: nagios_hostescalations
SQL: OPTIMIZE TABLE nagios_hostescalations
OPTIMIZING NDOUTILS TABLE: nagios_hostgroup_members
SQL: OPTIMIZE TABLE nagios_hostgroup_members
OPTIMIZING NDOUTILS TABLE: nagios_hostgroups
SQL: OPTIMIZE TABLE nagios_hostgroups
OPTIMIZING NDOUTILS TABLE: nagios_hosts
SQL: OPTIMIZE TABLE nagios_hosts
OPTIMIZING NDOUTILS TABLE: nagios_hoststatus
SQL: OPTIMIZE TABLE nagios_hoststatus
OPTIMIZING NDOUTILS TABLE: nagios_instances
SQL: OPTIMIZE TABLE nagios_instances
OPTIMIZING NDOUTILS TABLE: nagios_logentries
SQL: OPTIMIZE TABLE nagios_logentries
OPTIMIZING NDOUTILS TABLE: nagios_notifications
SQL: OPTIMIZE TABLE nagios_notifications
OPTIMIZING NDOUTILS TABLE: nagios_objects
SQL: OPTIMIZE TABLE nagios_objects
OPTIMIZING NDOUTILS TABLE: nagios_processevents
SQL: OPTIMIZE TABLE nagios_processevents
OPTIMIZING NDOUTILS TABLE: nagios_programstatus
SQL: OPTIMIZE TABLE nagios_programstatus
OPTIMIZING NDOUTILS TABLE: nagios_runtimevariables
SQL: OPTIMIZE TABLE nagios_runtimevariables
OPTIMIZING NDOUTILS TABLE: nagios_scheduleddowntime
SQL: OPTIMIZE TABLE nagios_scheduleddowntime
OPTIMIZING NDOUTILS TABLE: nagios_service_contactgroups
SQL: OPTIMIZE TABLE nagios_service_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_service_contacts
SQL: OPTIMIZE TABLE nagios_service_contacts
OPTIMIZING NDOUTILS TABLE: nagios_servicechecks
SQL: OPTIMIZE TABLE nagios_servicechecks
OPTIMIZING NDOUTILS TABLE: nagios_servicedependencies
SQL: OPTIMIZE TABLE nagios_servicedependencies
OPTIMIZING NDOUTILS TABLE: nagios_serviceescalation_contactgroups
SQL: OPTIMIZE TABLE nagios_serviceescalation_contactgroups
OPTIMIZING NDOUTILS TABLE: nagios_serviceescalation_contacts
SQL: OPTIMIZE TABLE nagios_serviceescalation_contacts
OPTIMIZING NDOUTILS TABLE: nagios_serviceescalations
SQL: OPTIMIZE TABLE nagios_serviceescalations
OPTIMIZING NDOUTILS TABLE: nagios_servicegroup_members
SQL: OPTIMIZE TABLE nagios_servicegroup_members
OPTIMIZING NDOUTILS TABLE: nagios_servicegroups
SQL: OPTIMIZE TABLE nagios_servicegroups
OPTIMIZING NDOUTILS TABLE: nagios_services
SQL: OPTIMIZE TABLE nagios_services
OPTIMIZING NDOUTILS TABLE: nagios_servicestatus
SQL: OPTIMIZE TABLE nagios_servicestatus
OPTIMIZING NDOUTILS TABLE: nagios_statehistory
SQL: OPTIMIZE TABLE nagios_statehistory
OPTIMIZING NDOUTILS TABLE: nagios_systemcommands
SQL: OPTIMIZE TABLE nagios_systemcommands
OPTIMIZING NDOUTILS TABLE: nagios_timedeventqueue
SQL: OPTIMIZE TABLE nagios_timedeventqueue
OPTIMIZING NDOUTILS TABLE: nagios_timedevents
SQL: OPTIMIZE TABLE nagios_timedevents
OPTIMIZING NDOUTILS TABLE: nagios_timeperiod_timeranges
SQL: OPTIMIZE TABLE nagios_timeperiod_timeranges
OPTIMIZING NDOUTILS TABLE: nagios_timeperiods
SQL: OPTIMIZE TABLE nagios_timeperiods
CLEANING nagiosxi TABLE 'commands'...
SQL: DELETE FROM xi_commands WHERE processing_time < FROM_UNIXTIME(1453162903)
CLEANING nagiosxi TABLE 'events'...
SQL: DELETE FROM xi_events WHERE processing_time < FROM_UNIXTIME(1453162903)
SQL1: SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL
SQL2: DELETE FROM xi_meta WHERE meta_id IN (SELECT xi_meta.meta_id FROM xi_meta LEFT JOIN xi_events ON xi_meta.metaobj_id=xi_events.event_id WHERE metatype_id='1' AND event_id IS NULL)
    SQL:     SQL Error [nagiosxi] :</b> You can't specify target table 'xi_meta' for update in FROM clauseCLEANING nagiosxi TABLE 'auditlog'...
SQL: DELETE FROM xi_auditlog WHERE log_time < FROM_UNIXTIME(1450599703)
OPTIMIZING NAGIOSXI TABLE: xi_auditlog
SQL: OPTIMIZE TABLE xi_auditlog
OPTIMIZING NAGIOSXI TABLE: xi_commands
SQL: OPTIMIZE TABLE xi_commands
OPTIMIZING NAGIOSXI TABLE: xi_events
SQL: OPTIMIZE TABLE xi_events
OPTIMIZING NAGIOSXI TABLE: xi_notifications
SQL: OPTIMIZE TABLE xi_notifications
OPTIMIZING NAGIOSXI TABLE: xi_meta
SQL: OPTIMIZE TABLE xi_meta
OPTIMIZING NAGIOSXI TABLE: xi_options
SQL: OPTIMIZE TABLE xi_options
OPTIMIZING NAGIOSXI TABLE: xi_sysstat
SQL: OPTIMIZE TABLE xi_sysstat
OPTIMIZING NAGIOSXI TABLE: xi_usermeta
SQL: OPTIMIZE TABLE xi_usermeta
OPTIMIZING NAGIOSXI TABLE: xi_users
SQL: OPTIMIZE TABLE xi_users
CLEANING nagiosql TABLE 'logbook'...
SQL: DELETE FROM tbl_logbook WHERE time < FROM_UNIXTIME(1453162903)
OPTIMIZING NAGIOSQL TABLE: tbl_contact
SQL: OPTIMIZE TABLE tbl_contact
OPTIMIZING NAGIOSQL TABLE: tbl_host
SQL: OPTIMIZE TABLE tbl_host
OPTIMIZING NAGIOSQL TABLE: tbl_hostgroup
SQL: OPTIMIZE TABLE tbl_hostgroup
OPTIMIZING NAGIOSQL TABLE: tbl_lnkHostgroupToHost
SQL: OPTIMIZE TABLE tbl_lnkHostgroupToHost
OPTIMIZING NAGIOSQL TABLE: tbl_lnkHostToHost
SQL: OPTIMIZE TABLE tbl_lnkHostToHost
OPTIMIZING NAGIOSQL TABLE: tbl_lnkHostToHostgroup
SQL: OPTIMIZE TABLE tbl_lnkHostToHostgroup
OPTIMIZING NAGIOSQL TABLE: tbl_lnkHostdependencyToHost_DH
SQL: OPTIMIZE TABLE tbl_lnkHostdependencyToHost_DH
OPTIMIZING NAGIOSQL TABLE: tbl_lnkHostdependencyToHost_H
SQL: OPTIMIZE TABLE tbl_lnkHostdependencyToHost_H
OPTIMIZING NAGIOSQL TABLE: tbl_lnkServiceToHost
SQL: OPTIMIZE TABLE tbl_lnkServiceToHost
OPTIMIZING NAGIOSQL TABLE: tbl_lnkServicedependencyToService_DS
SQL: OPTIMIZE TABLE tbl_lnkServicedependencyToService_DS
OPTIMIZING NAGIOSQL TABLE: tbl_lnkServicedependencyToService_S
SQL: OPTIMIZE TABLE tbl_lnkServicedependencyToService_S
OPTIMIZING NAGIOSQL TABLE: tbl_lnkServiceToHostgroup
SQL: OPTIMIZE TABLE tbl_lnkServiceToHostgroup
OPTIMIZING NAGIOSQL TABLE: tbl_lnkServiceToServicegroup
SQL: OPTIMIZE TABLE tbl_lnkServiceToServicegroup
OPTIMIZING NAGIOSQL TABLE: tbl_logbook
SQL: OPTIMIZE TABLE tbl_logbook
OPTIMIZING NAGIOSQL TABLE: tbl_service
SQL: OPTIMIZE TABLE tbl_service
OPTIMIZING NAGIOSQL TABLE: tbl_servicegroup
SQL: OPTIMIZE TABLE tbl_servicegroup
OPTIMIZING NAGIOSQL TABLE: tbl_timeperiod
SQL: OPTIMIZE TABLE tbl_timeperiod
OPTIMIZING NAGIOSQL TABLE: tbl_timedefinition
SQL: OPTIMIZE TABLE tbl_timedefinition
OPTIMIZING NAGIOSQL TABLE: tbl_user
SQL: OPTIMIZE TABLE tbl_user 

EXecuting

Code: Select all

tcpdump -l -s0 -w - tcp dst port 3306 | strings  |grep -B 9 -w "ORDER BY obj1.name1 DESC LIMIT 100000"

We have discovered that the following query, as many others, is malformed:

Code: Select all

​SELECT COUNT(*) as total
FROM nagios_servicestatus
LEFT JOIN nagios_objects as obj1 ON nagios_servicestatus.service_object_id=obj1.object_id
LEFT JOIN nagios_services ON nagios_servicestatus.service_object_id=nagios_services.service_object_id
LEFT JOIN nagios_hosts ON nagios_services.host_object_id=nagios_hosts.host_object_id
LEFT JOIN nagios_hoststatus ON nagios_hosts.host_object_id=nagios_hoststatus.host_object_id
WHERE TRUE AND nagios_servicestatus.current_state IN ('1','2','3') AND nagios_servicestatus.problem_has_been_acknowledged = '0' AND nagios_servicestatus.scheduled_downtime_depth = '0' AND nagios_servicestatus.instance_id = '1' AND nagios_servicestatus.service_object_id IN (  )  ORDER BY obj1.name1 DESC LIMIT 100000

Nagios XI hdesk user output:



Nagios core hdesk user output:



Nagios XI nagiosadmin user output:



If all the hosts option is activated for the hdesk user, then every thing works fine.

[igarcia]

Finally we where able to determine the exact problem. The bug appears when we reach more than a certain number of services (around 4500). To solve it we have modify the keyvalue type from TEXT (installer value, see nagiosxi/nagiosxi_db.sql) to LONGTEXT in the table xi_usermeta.

Now MySQL is able to keep higher number of services into the row. We don't know if any other table would have the same problem.

[bwallace]

Thanks for posting the solution you found - are we good to close this case? If there are any other table problems in the future, feel free to open another thread.

[igarcia]

Perfect,
You can close the case.

Thanks you!