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

[gzaloprgm]

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



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

[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.

[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

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

[lmiltchev]

Sounds good! We will keep the thread open for the time being. Let us know if you have any further questions.

[gzaloprgm]

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




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

Many thanks, Gonzalo

[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.

[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)

[lmiltchev]

Sounds good - let us know if you have any further questions.