Page 1 of 2
SQL Error nagiosxi ndoutils MySQL server has gone away, synt
Posted: Tue Jun 30, 2020 10:40 am
by 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
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Tue Jun 30, 2020 3:49 pm
by 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
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Wed Jul 01, 2020 4:29 am
by centricrm
I have sent the requested files.
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Wed Jul 01, 2020 4:57 pm
by 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
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Thu Jul 02, 2020 3:41 am
by centricrm
Errors remain after DB repair.
I have sent the requested files.
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Thu Jul 02, 2020 5:09 pm
by 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
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Sun Jul 05, 2020 4:12 am
by 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.
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Mon Jul 06, 2020 5:40 pm
by 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.
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Tue Jul 07, 2020 10:55 am
by centricrm
I have sent the profile.
Re: SQL Error nagiosxi ndoutils MySQL server has gone away,
Posted: Wed Jul 08, 2020 3:33 pm
by 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.