SQL errors "nagiosql - Duplicate entry for ..." limited user

This support forum board is for support questions relating to Nagios XI, our flagship commercial network monitoring solution.
Locked
gzaloprgm
Posts: 33
Joined: Mon Aug 06, 2018 8:46 am
Contact:

SQL errors "nagiosql - Duplicate entry for ..." limited user

Post by gzaloprgm »

Hi
A non-admin user is seeing this type of messages when they log in or enter a dashboard:

Image

I think that the table that's failing is tbl_permission (from nagiosql DB), it seems to be recalculating and reinserting the permissions of the user, even if they are already in the table. Is there any way to fix it?

I'm using Nagios XI version 5.5.10. As a side note which might not be related, the nagiosxi database is running on postgres (this is an old installation which was upgraded multiple times)

Thanks, Gonzalo
User avatar
lmiltchev
Former Nagios Staff
Posts: 13587
Joined: Mon May 23, 2011 12:15 pm

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by lmiltchev »

These errors should be fixed in Nagios XI 5.5.10 - I am surprised you are getting them.
Fixed MySQL nagiosql errors in cmdsubsys.log for regular users with limited CCM access [TPS#14045] -JO
https://www.nagios.com/downloads/nagios-xi/change-log/

Can you try rerunning the upgrade script or upgrading to 5.5.11 to see if this is going to fix the issue?

These errors could show up in some instances, when someone would have their cached permissions updating at exactly the same time as the apply config. They should not show up on 5.5.10 once the cron updates.
Be sure to check out our Knowledgebase for helpful articles and solutions!
gzaloprgm
Posts: 33
Joined: Mon Aug 06, 2018 8:46 am
Contact:

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by gzaloprgm »

I didn't mention it, but the error message disappears after refreshing the page. It seems to reappear when the limited user logs in after a configuration apply. I don't have subsystem logging enabled so I am not seing it in /usr/local/nagiosxi/var/cmdsubsys.log
lmiltchev wrote: Can you try rerunning the upgrade script or upgrading to 5.5.11 to see if this is going to fix the issue?
Sure, I've just uppgraded to 5.5.11, I'll keep you updated and tell you if we see the error again.

Thanks, Gonzalo
User avatar
lmiltchev
Former Nagios Staff
Posts: 13587
Joined: Mon May 23, 2011 12:15 pm

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by lmiltchev »

Sounds good! We will keep the thread open for the time being. Let us know if you have any further questions.
Be sure to check out our Knowledgebase for helpful articles and solutions!
gzaloprgm
Posts: 33
Joined: Mon Aug 06, 2018 8:46 am
Contact:

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by gzaloprgm »

Unfortunately, it's still happening after upgrading to 5.5.11.

Image
Image

Is there any log or DB dump you can analyse to help track down the issue?

Many thanks, Gonzalo
jomann
Development Lead
Posts: 611
Joined: Mon Apr 22, 2013 10:06 am
Location: Nagios Enterprises

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by jomann »

I have a solution for you for now to remove the errors from the screen. The errors won't hurt anything but we will stop it from happening in future versions. It seems like the apply config permissions updates have not finished by the time the cached permissions rebuild which also triggers an update to the tbl_permissions database.

Also, approximately how many limited CCM users do you have and how many hosts/services do you have? Approximately how many hosts/services per user would you estimate?

To get the messages to not show, you can edit /usr/local/nagiosxi/html/includes/components/ccm/ccm.inc.php and replace the following two lines:

For line 796 replace with:

Code: Select all

exec_sql_query(DB_NAGIOSQL, "INSERT INTO tbl_permission (user_id, object_id, type) VALUES (".$user_id.", ".$o['id'].", ".OBJECTTYPE_HOST.");", false);
For line 809 replace with:

Code: Select all

exec_sql_query(DB_NAGIOSQL, "INSERT INTO tbl_permission (user_id, object_id, type) VALUES (".$user_id.", ".$s['id'].", ".OBJECTTYPE_SERVICE.");", false);
Adding the false to those functions will stop the errors from being displayed to the screen for now until the next version.
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
gzaloprgm
Posts: 33
Joined: Mon Aug 06, 2018 8:46 am
Contact:

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by gzaloprgm »

Thanks a lot, I will patch those files.

As a reference,
How many limited CCM users do you have 24 non/admin users, just 1 with limited CCM access
and how many hosts/services do you have? ~300 / ~4000
Approximately how many hosts/services per user would you estimate? 6 hosts / 24 services (assigned to the limited user)
User avatar
lmiltchev
Former Nagios Staff
Posts: 13587
Joined: Mon May 23, 2011 12:15 pm

Re: SQL errors "nagiosql - Duplicate entry for ..." limited

Post by lmiltchev »

Sounds good - let us know if you have any further questions.
Be sure to check out our Knowledgebase for helpful articles and solutions!
Locked