Intermittent Operations Screen error

[Greg]

Good morning,
When using the Operations Screen from the home menu, the message "Could not connect to the database!" is intermittently displayed instead of the expected screen of host/service issues. When the issue was first seen, we had been running 2014R2.7, and as part of troubleshooting have upgraded to 5.2.7. Database repairs have been completed both before and after the upgrade. Running a tail on /var/log/httpd/error_log, the follow error is seen repeatedly (for various client IPs):

Code: Select all

[error] [client xxx.xxx.xxx.xxx] PHP Warning:  mysql_connect(): Too many connections in /usr/local/nagiosxi/html/includes/components/opscreen/merlin.php on line 28, referer: http://172.21.0.138/nagiosxi/includes/components/opscreen/opscreen.php

Reading through previous similar threads, I modified the max connections to mysql in /etc/my.cnf, then restarted the nagios, ndo2db, and mysqld services. That appeared to resolve the issue, but only temporarily. Aside from ignoring the Operations Screen and simply using the Operations Center page instead, any resolution ideas?

Code: Select all

Nagios XI Installation Profile

System:

Nagios XI Version : 5.2.7
localhost.localdomain 2.6.32-279.5.1.el6.i686 i686
CentOS release 6.3 (Final)
Gnome is not installed
Apache Information

PHP Version: 5.3.3
Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/50.0.2661.102 Safari/537.36
Server Name: 172.21.0.138
Server Address: 172.21.0.138
Server Port: 80
Date/Time

PHP Timezone: America/Anchorage 
PHP Time: Tue, 24 May 2016 13:28:20 -0800
System Time: Tue, 24 May 2016 13:28:20 -0800
Nagios XI Data

License ends in: NUTVRS

nagios (pid 7949) is running...
NPCD running (pid 1728).
ndo2db (pid 7805) is running...
CPU Load 15: 3.54 
Total Hosts: 1300 
Total Services: 3735 
Function 'get_base_uri' returns: http://172.21.0.138/nagiosxi/
Function 'get_base_url' returns: http://172.21.0.138/nagiosxi/
Function 'get_backend_url(internal_call=false)' returns: http://172.21.0.138/nagiosxi/includes/components/profile/profile.php
Function 'get_backend_url(internal_call=true)' returns: http://localhost/nagiosxi/backend/

[rkennedy]

We may have to increase the amount of connections possible for MySQL. First off, I'd like to take a look at what the root cause is. Can you run the following, and post back the full output?

Code: Select all

mysql -u root -p'nagiosxi' -e "SHOW PROCESSLIST;"

[Greg]

Code: Select all

# mysql -u root -p'nagiosxi' -e "SHOW PROCESSLIST;"
+------+----------+-----------+----------+---------+------+-------+------------------+
| Id   | User     | Host      | db       | Command | Time | State | Info             |
+------+----------+-----------+----------+---------+------+-------+------------------+
|    3 | ndoutils | localhost | nagios   | Sleep   |   28 |       | NULL             |
|    4 | nagiosql | localhost | nagiosql | Sleep   |   28 |       | NULL             |
|    6 | ndoutils | localhost | nagios   | Sleep   |    6 |       | NULL             |
|    7 | nagiosql | localhost | nagiosql | Sleep   |    6 |       | NULL             |
|   11 | ndoutils | localhost | nagios   | Sleep   |    1 |       | NULL             |
|   12 | nagiosql | localhost | nagiosql | Sleep   |    1 |       | NULL             |
|   13 | ndoutils | localhost | nagios   | Sleep   |   21 |       | NULL             |
|   14 | nagiosql | localhost | nagiosql | Sleep   |   21 |       | NULL             |
|   16 | ndoutils | localhost | nagios   | Sleep   | 7101 |       | NULL             |
|   17 | ndoutils | localhost | nagios   | Sleep   |    0 |       | NULL             |
|   18 | ndoutils | localhost | nagios   | Sleep   |   10 |       | NULL             |
|   19 | nagiosql | localhost | nagiosql | Sleep   |   10 |       | NULL             |
|   20 | ndoutils | localhost | nagios   | Sleep   |    3 |       | NULL             |
|   21 | nagiosql | localhost | nagiosql | Sleep   |    3 |       | NULL             |
|   23 | ndoutils | localhost | nagios   | Sleep   |   21 |       | NULL             |
|   24 | nagiosql | localhost | nagiosql | Sleep   |   21 |       | NULL             |
|   25 | ndoutils | localhost | nagios   | Sleep   |   21 |       | NULL             |
|   26 | nagiosql | localhost | nagiosql | Sleep   |   22 |       | NULL             |
|   27 | ndoutils | localhost | nagios   | Sleep   |   17 |       | NULL             |
|   28 | nagiosql | localhost | nagiosql | Sleep   |   17 |       | NULL             |
|   30 | ndoutils | localhost | nagios   | Sleep   |   24 |       | NULL             |
|   31 | nagiosql | localhost | nagiosql | Sleep   |   24 |       | NULL             |
|   32 | ndoutils | localhost | nagios   | Sleep   |   22 |       | NULL             |
|   33 | nagiosql | localhost | nagiosql | Sleep   |   22 |       | NULL             |
|   36 | ndoutils | localhost | nagios   | Sleep   |    9 |       | NULL             |
|   37 | nagiosql | localhost | nagiosql | Sleep   |    9 |       | NULL             |
|   38 | ndoutils | localhost | nagios   | Sleep   |   12 |       | NULL             |
|   39 | nagiosql | localhost | nagiosql | Sleep   |   12 |       | NULL             |
|   40 | ndoutils | localhost | nagios   | Sleep   |   17 |       | NULL             |
|   41 | nagiosql | localhost | nagiosql | Sleep   |   17 |       | NULL             |
|   42 | ndoutils | localhost | nagios   | Sleep   |   20 |       | NULL             |
|   43 | nagiosql | localhost | nagiosql | Sleep   |   20 |       | NULL             |
|   44 | ndoutils | localhost | nagios   | Sleep   |    5 |       | NULL             |
|   45 | nagiosql | localhost | nagiosql | Sleep   |    5 |       | NULL             |
|   46 | ndoutils | localhost | nagios   | Sleep   |   25 |       | NULL             |
|   47 | nagiosql | localhost | nagiosql | Sleep   |   25 |       | NULL             |
|   48 | ndoutils | localhost | nagios   | Sleep   |   12 |       | NULL             |
|   49 | nagiosql | localhost | nagiosql | Sleep   |   13 |       | NULL             |
|   50 | ndoutils | localhost | nagios   | Sleep   |   13 |       | NULL             |
|   51 | nagiosql | localhost | nagiosql | Sleep   |   13 |       | NULL             |
|   55 | ndoutils | localhost | nagios   | Sleep   |   14 |       | NULL             |
|   56 | nagiosql | localhost | nagiosql | Sleep   |   14 |       | NULL             |
|   86 | ndoutils | localhost | nagios   | Sleep   |   23 |       | NULL             |
|   87 | nagiosql | localhost | nagiosql | Sleep   | 7063 |       | NULL             |
|  144 | ndoutils | localhost | nagios   | Sleep   |   27 |       | NULL             |
|  145 | nagiosql | localhost | nagiosql | Sleep   | 7004 |       | NULL             |
|  201 | ndoutils | localhost | nagios   | Sleep   |   12 |       | NULL             |
|  202 | nagiosql | localhost | nagiosql | Sleep   | 6940 |       | NULL             |
|  266 | ndoutils | localhost | nagios   | Sleep   |   28 |       | NULL             |
|  268 | nagiosql | localhost | nagiosql | Sleep   | 6882 |       | NULL             |
|  330 | ndoutils | localhost | nagios   | Sleep   |   13 |       | NULL             |
|  331 | nagiosql | localhost | nagiosql | Sleep   | 6825 |       | NULL             |
|  385 | ndoutils | localhost | nagios   | Sleep   |   17 |       | NULL             |
|  387 | nagiosql | localhost | nagiosql | Sleep   | 6765 |       | NULL             |
|  440 | ndoutils | localhost | nagios   | Sleep   |   21 |       | NULL             |
|  442 | nagiosql | localhost | nagiosql | Sleep   | 6705 |       | NULL             |
|  498 | ndoutils | localhost | nagios   | Sleep   |    3 |       | NULL             |
|  499 | nagiosql | localhost | nagiosql | Sleep   | 6643 |       | NULL             |
|  550 | ndoutils | localhost | nagios   | Sleep   |   34 |       | NULL             |
|  551 | nagiosql | localhost | nagiosql | Sleep   | 6585 |       | NULL             |
|  601 | ndoutils | localhost | nagios   | Sleep   |   26 |       | NULL             |
|  602 | nagiosql | localhost | nagiosql | Sleep   | 6525 |       | NULL             |
|  650 | ndoutils | localhost | nagios   | Sleep   |   24 |       | NULL             |
|  651 | nagiosql | localhost | nagiosql | Sleep   | 6464 |       | NULL             |
|  705 | ndoutils | localhost | nagios   | Sleep   |    3 |       | NULL             |
|  706 | nagiosql | localhost | nagiosql | Sleep   | 6405 |       | NULL             |
|  757 | ndoutils | localhost | nagios   | Sleep   |   14 |       | NULL             |
|  758 | nagiosql | localhost | nagiosql | Sleep   | 6345 |       | NULL             |
|  801 | ndoutils | localhost | nagios   | Sleep   |   25 |       | NULL             |
|  802 | nagiosql | localhost | nagiosql | Sleep   | 6285 |       | NULL             |
|  856 | ndoutils | localhost | nagios   | Sleep   |   13 |       | NULL             |
|  857 | nagiosql | localhost | nagiosql | Sleep   | 6225 |       | NULL             |
|  895 | ndoutils | localhost | nagios   | Sleep   |   24 |       | NULL             |
|  896 | nagiosql | localhost | nagiosql | Sleep   | 6165 |       | NULL             |
|  961 | ndoutils | localhost | nagios   | Sleep   |   15 |       | NULL             |
|  962 | nagiosql | localhost | nagiosql | Sleep   | 6105 |       | NULL             |
| 1000 | ndoutils | localhost | nagios   | Sleep   |   26 |       | NULL             |
| 1003 | nagiosql | localhost | nagiosql | Sleep   | 6045 |       | NULL             |
| 1047 | ndoutils | localhost | nagios   | Sleep   |   31 |       | NULL             |
| 1048 | nagiosql | localhost | nagiosql | Sleep   | 5986 |       | NULL             |
| 1113 | ndoutils | localhost | nagios   | Sleep   |   23 |       | NULL             |
| 1115 | nagiosql | localhost | nagiosql | Sleep   | 5924 |       | NULL             |
| 1164 | ndoutils | localhost | nagios   | Sleep   |   24 |       | NULL             |
| 1165 | nagiosql | localhost | nagiosql | Sleep   | 5863 |       | NULL             |
| 1217 | ndoutils | localhost | nagios   | Sleep   |   16 |       | NULL             |
| 1218 | nagiosql | localhost | nagiosql | Sleep   | 5805 |       | NULL             |
| 1272 | ndoutils | localhost | nagios   | Sleep   |   27 |       | NULL             |
| 1273 | nagiosql | localhost | nagiosql | Sleep   | 5745 |       | NULL             |
| 1316 | ndoutils | localhost | nagios   | Sleep   |   13 |       | NULL             |
| 1317 | nagiosql | localhost | nagiosql | Sleep   | 5685 |       | NULL             |
| 1368 | ndoutils | localhost | nagios   | Sleep   |   20 |       | NULL             |
| 1369 | nagiosql | localhost | nagiosql | Sleep   | 5626 |       | NULL             |
| 1417 | ndoutils | localhost | nagios   | Sleep   |   16 |       | NULL             |
| 1420 | nagiosql | localhost | nagiosql | Sleep   | 5563 |       | NULL             |
| 1473 | ndoutils | localhost | nagios   | Sleep   |    2 |       | NULL             |
| 1474 | nagiosql | localhost | nagiosql | Sleep   | 5503 |       | NULL             |
| 1517 | ndoutils | localhost | nagios   | Sleep   |   31 |       | NULL             |
| 1519 | nagiosql | localhost | nagiosql | Sleep   | 5445 |       | NULL             |
| 1575 | ndoutils | localhost | nagios   | Sleep   |    6 |       | NULL             |
| 1577 | nagiosql | localhost | nagiosql | Sleep   | 5385 |       | NULL             |
| 1616 | ndoutils | localhost | nagios   | Sleep   |    7 |       | NULL             |
| 1618 | nagiosql | localhost | nagiosql | Sleep   | 5324 |       | NULL             |
| 1677 | ndoutils | localhost | nagios   | Sleep   |   29 |       | NULL             |
| 1678 | nagiosql | localhost | nagiosql | Sleep   | 5263 |       | NULL             |
| 1731 | ndoutils | localhost | nagios   | Sleep   |   15 |       | NULL             |
| 1732 | nagiosql | localhost | nagiosql | Sleep   | 5205 |       | NULL             |
| 1783 | ndoutils | localhost | nagios   | Sleep   |    7 |       | NULL             |
| 1784 | nagiosql | localhost | nagiosql | Sleep   | 5146 |       | NULL             |
| 1826 | ndoutils | localhost | nagios   | Sleep   |   12 |       | NULL             |
| 1828 | nagiosql | localhost | nagiosql | Sleep   | 5085 |       | NULL             |
| 1884 | ndoutils | localhost | nagios   | Sleep   |   25 |       | NULL             |
| 1885 | nagiosql | localhost | nagiosql | Sleep   | 5025 |       | NULL             |
| 1931 | ndoutils | localhost | nagios   | Sleep   |   11 |       | NULL             |
| 1933 | nagiosql | localhost | nagiosql | Sleep   | 4963 |       | NULL             |
| 1980 | ndoutils | localhost | nagios   | Sleep   |    3 |       | NULL             |
| 1981 | nagiosql | localhost | nagiosql | Sleep   | 4904 |       | NULL             |
| 2029 | ndoutils | localhost | nagios   | Sleep   |   32 |       | NULL             |
| 2030 | nagiosql | localhost | nagiosql | Sleep   | 4845 |       | NULL             |
| 2076 | ndoutils | localhost | nagios   | Sleep   |   46 |       | NULL             |
| 2078 | nagiosql | localhost | nagiosql | Sleep   | 4785 |       | NULL             |
| 2115 | ndoutils | localhost | nagios   | Sleep   |   20 |       | NULL             |
| 2118 | nagiosql | localhost | nagiosql | Sleep   | 4725 |       | NULL             |
| 2155 | ndoutils | localhost | nagios   | Sleep   |   10 |       | NULL             |
| 2156 | nagiosql | localhost | nagiosql | Sleep   | 4664 |       | NULL             |
| 2216 | ndoutils | localhost | nagios   | Sleep   |   23 |       | NULL             |
| 2218 | nagiosql | localhost | nagiosql | Sleep   | 4602 |       | NULL             |
| 2262 | ndoutils | localhost | nagios   | Sleep   |   19 |       | NULL             |
| 2263 | nagiosql | localhost | nagiosql | Sleep   | 4545 |       | NULL             |
| 2299 | ndoutils | localhost | nagios   | Sleep   |   11 |       | NULL             |
| 2302 | nagiosql | localhost | nagiosql | Sleep   | 4485 |       | NULL             |
| 2352 | ndoutils | localhost | nagios   | Sleep   |   20 |       | NULL             |
| 2353 | nagiosql | localhost | nagiosql | Sleep   | 4426 |       | NULL             |
| 2396 | ndoutils | localhost | nagios   | Sleep   |   22 |       | NULL             |
| 2398 | nagiosql | localhost | nagiosql | Sleep   | 4364 |       | NULL             |
| 2481 | ndoutils | localhost | nagios   | Sleep   |   12 |       | NULL             |
| 2482 | nagiosql | localhost | nagiosql | Sleep   | 4245 |       | NULL             |
| 2522 | ndoutils | localhost | nagios   | Sleep   |    8 |       | NULL             |
| 2523 | nagiosql | localhost | nagiosql | Sleep   | 4184 |       | NULL             |
| 2619 | ndoutils | localhost | nagios   | Sleep   |    8 |       | NULL             |
| 2620 | nagiosql | localhost | nagiosql | Sleep   | 4064 |       | NULL             |
| 2665 | ndoutils | localhost | nagios   | Sleep   |   17 |       | NULL             |
| 2666 | nagiosql | localhost | nagiosql | Sleep   | 4003 |       | NULL             |
| 2742 | ndoutils | localhost | nagios   | Sleep   |   28 |       | NULL             |
| 2834 | ndoutils | localhost | nagios   | Sleep   |   27 |       | NULL             |
| 2994 | ndoutils | localhost | nagios   | Sleep   |    6 |       | NULL             |
| 2995 | nagiosql | localhost | nagiosql | Sleep   | 3525 |       | NULL             |
| 3043 | ndoutils | localhost | nagios   | Sleep   |   27 |       | NULL             |
| 3420 | ndoutils | localhost | nagios   | Sleep   |   41 |       | NULL             |
| 3421 | nagiosql | localhost | nagiosql | Sleep   | 2865 |       | NULL             |
| 3751 | ndoutils | localhost | nagios   | Sleep   |   27 |       | NULL             |
| 4593 | ndoutils | localhost | nagios   | Sleep   |   24 |       | NULL             |
| 5245 | root     | localhost | NULL     | Query   |    0 | NULL  | SHOW PROCESSLIST |
+------+----------+-----------+----------+---------+------+-------+------------------+

[rkennedy]

Got it.

Please run the following -

Code: Select all

mysql -uroot -pnagiosxi -e "SHOW VARIABLES like 'max_connections'";

This will show us what your max_connections is currently set to. Then, open up /etc/my.cnf (might be /etc/mysql/my.cnf depending on your OS) and look for the variable 'max_connections = x'. If you do not see it present, then add it at the bottom, equal to twice as what it's currently set to. (the above mysql command will tell you what it's currently set at)

For example -

Code: Select all

[root@localhost libexec]# mysql -uroot -pnagiosxi -e "SHOW VARIABLES like 'max_connections'";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 350   |
+-----------------+-------+

It was previously set at 350, so I will change it to 700

Code: Select all

max_connections = 700

Then, restart mysql / mariadb. Either -

Code: Select all

service mysqld restart

or

service mariadb restart

Now, run the command once again, and you should see it increased.

Code: Select all

[root@localhost libexec]# mysql -uroot -pnagiosxi -e "SHOW VARIABLES like 'max_connections'";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 700   |
+-----------------+-------+

This should resolve the intermittent issue you're seeing, since it's just related to the max MySQL connections.

[Greg]

Interesting. Checking the max connections, it was initially set at 151, with no corresponding lines in the file. I added the line max_connections = 300, and restarted the mysqld service. However, the max connections check still returns 151. The /etc/my.cnf file is below for reference. Is the max connections set somewhere else, potentially?

Code: Select all

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections = 300

[rkennedy]

Ah, I didn't have a [mysqld_safe] section on mine. Just spun up a Cent6 machine, and the issue is the placement.

Move the max_connections = 300 part to be under [mysqld] rather than [mysqld_safe] and it'll work for you. Then restart mysqld once again, and it should be showing the 300.

Code: Select all

[root@localhost ~]# mysql -uroot -pnagiosxi -e "SHOW VARIABLES like 'max_connections'";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
[root@localhost ~]# nano /etc/my.cnf                                            
[root@localhost ~]# service mysqld restart                                     
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost ~]# mysql -uroot -pnagiosxi -e "SHOW VARIABLES like 'max_connections'";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
[root@localhost ~]#

For reference, here's my.cnf -

Code: Select all

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections = 300

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[Greg]

Looks like you are spot on regarding the placement requirements. Max connections is now set at 300. I'll monitor the server logs, and if we see no further issues in the near future I'll request to have the thread closed as resolved.

[rkennedy]

Awesome! Will leave this open for now -- feel free to post back an update should something happen down the road.

[Greg]

As of yet, I have not seen any further "Could not connect to the database!" messages from the Ops Screen. However, checking the error_log this morning, I'm now seeing new errors coming in that appear to be related (below).

Code: Select all

[Thu May 26 10:13:07 2016] [error] [client xxx.xxx.xxx.xxx] PHP Warning:  pg_pconnect(): Unable to connect to PostgreSQL server: FATAL:  connection limit exceeded for non-superusers in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-postgres64.inc.php on line 682, referer: http://172.21.0.138/nagiosxi/includes/components/opscreen/opscreen.php
[Thu May 26 10:13:07 2016] [error] [client xxx.xxx.xxx.xxx] PHP Notice:  Undefined variable: result in /usr/local/nagiosxi/html/includes/db.inc.php on line 249, referer: http://172.21.0.138/nagiosxi/includes/components/opscreen/opscreen.php

[tgriep]

It could be that the Postgress database needs to be vacuumed of old transaction entries
Login as root to the XI server and run the following to do that.

Code: Select all

service nagios stop
echo "vacuum;vacuum analyze;vacuum full;"|psql nagiosxi postgres
service postgresql restart
service nagios start

Let us know if this helps.