SQL Error nagiosxi ndoutils MySQL server has gone away, synt

[centricrm]

Hi,

We are using Nagios XI with a large number of hosts and services.
After adding a number of hosts we ran into the next issue:

When a user logs in, he gets the next SQL errors:

SQL Error [nagiosxi] : MySQL server has gone away
SQL Error [ndoutils] : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ORDER BY obj1.name1 DESC LIMIT 100000' at line 9

The strange thing is that a user with admin authorization level, does not get these errors.

When I limit the user to see only part of the total number of hosts, the error disappear.

Please advise how to solve this.

System details:
ver= 5.6.14 (5.7 not stable enough for us)
os= centos 7.7.1908 (centos 8 planned)
cpu= 8
mem= 16GB
hdd= 400GB (17% used)
hosts= 12442
services= 69781

wkr, Humphrey

[ssax]

Please run this tail command as root (and leave it running):

Code: Select all

tail -Fn0 /var/log/httpd/error_log /var/log/httpd/ssl_error_log /usr/local/nagiosxi/var/cmdsubsys.log /usr/local/nagiosxi/var/eventman.log

Then re-login to replicate the error and send us the full output of the still running tail command after you've received the error.

If you apply configuration and wait 10 minutes does it still show that?

Please send me a copy of your profile, you can download it from Admin > System Profile > Download Profile.

What is the output of these commands?

Code: Select all

rpm -qa | grep mysql
rpm -qa | grep mariadb

[centricrm]

I have sent the requested files.

[ssax]

You have crashed tables, please run these commands to fix them:

Code: Select all

cd /usr/local/nagiosxi/scripts
./repair_databases.sh

Then test, if it's still not working, please send a fresh copy of your profile.

Additionally, please send the output of these commands (as root):
- NOTE: You may need to adjust the -h 127.0.0.1, the -uroot, and -pnagiosxi in the first command if your DB is offloaded to another server and/or you've changed the root mysql password

Code: Select all

echo "SELECT table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema IN ('nagios', 'nagiosql', 'nagiosxi');" | mysql -h 127.0.0.1 -uroot -pnagiosxi --table

[centricrm]

Errors remain after DB repair.

I have sent the requested files.

[ssax]

Code: Select all

| xi_eventqueue                              |       0.03 |
| xi_events                                  |     345.92 |
| xi_meta                                    |    4756.67 |

You are hitting a bug, please run these commands to fix it and let me know if it resolves your issue:

Code: Select all

systemctl stop httpd
systemctl stop crond
systemctl stop npcd
systemctl stop nagios
systemctl stop ndo2db
pkill -9 -u nagios
pkill -9 -u apache
for i in $(ipcs -q | grep nagios |awk '{print $2}'); do ipcrm -q $i; done
rm -f /usr/local/nagiosxi/var/dbmaint.lock
rm -f /usr/local/nagiosxi/var/event_handler.lock
rm -f /usr/local/nagiosxi/scripts/reconfigure_nagios.lock
rm -f /usr/local/nagios/var/ndo2db.lock
rm -f /usr/local/nagios/var/ndo2db.pid
rm -f /usr/local/nagios/var/ndo2db.sock
rm -f /usr/local/nagios/var/ndo.sock
rm -f /us/local/nagiosxi/var/subsys/ndo2db
rm -f /var/run/nagios/nagios.lock
rm -f /var/run/nagios.lock
rm -f /usr/local/nagios/var/nagios.lock
rm -f /var/run/httpd/httpd.pid
systemctl restart mariadb
echo "truncate table xi_events; truncate table xi_meta; truncate table xi_eventqueue;" | mysql -h 127.0.0.1 -uroot -pnagiosxi nagiosxi
systemctl start ndo2db
systemctl start nagios
systemctl start npcd
systemctl start crond
systemctl restart httpd
systemctl restart snmptt

[centricrm]

Tables xi_events, xi_meta and xi_eventqueue are empty after these commands, but error is not resolved.
Still see the same error messages.

[ssax]

Please replicate the error again, once you've done that, please PM me a fresh copy of your profile, you can download it from Admin > System Profile > Download Profile button.

[centricrm]

I have sent the profile.

[ssax]

I'm looking for a new profile.zip file that you get by clicking the Download Profile button on that page, not the View System Info button.