Tech

nagios :MySQL server has gone away

by Phyllis · March 30, 2012

The CentOS 5.5 server running opsview server crashed this morning , and after restart , below error messages being repeatedly logged in /var/log/messages :

Mar 30 11:47:14 cuse77 ndo2db: mysql_error: ‘MySQL server has gone away’
Mar 30 11:47:14 cuse77 ndo2db: Error: Connection to MySQL database has been lost!
Mar 30 11:47:14 cuse77 ndo2db: Did not find instance_name ‘default’ – retrying
Mar 30 11:47:15 cuse77 ndo2db: Error: mysql_query() failed for ‘SELECT instance_id FROM nagios_instances WHERE instance_name=’default”
Mar 30 11:47:15 cuse77 ndo2db: mysql_error: ‘MySQL server has gone away’
Mar 30 11:47:15 cuse77 ndo2db: Error: Connection to MySQL database has been lost!
Mar 30 11:47:15 cuse77 ndo2db: Error: mysql_query() failed for ‘INSERT INTO nagios_instances SET instance_name=’default”
Mar 30 11:47:15 cuse77 ndo2db: mysql_error: ‘MySQL server has gone away’
Mar 30 11:47:15 cuse77 ndo2db: Error: Connection to MySQL database has been lost!
Mar 30 11:47:15 cuse77 ndo2db: Did not find instance_name ‘default’ – retrying

The following error messages are logged in /var/log/opsview/opsviewd.log :

[2012/03/30 11:42:18] [opsviewd] [WARN] Error running start_tunnels – retry in 5 seconds: DBI connect(‘database=opsview;host=localhost’,’opsview’,…) failed: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) at /usr/local/nagios/perl/lib/Ima/DBI.pm line 329[2012/03/30 11:42:18] [opsviewd] [WARN] Error running start_tunnels – retry in 5 seconds: DBI connect(‘database=opsview;host=localhost’,’opsview’,…) failed: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) at /usr/local/nagios/perl/lib/Ima/DBI.pm line 329

When I checked the /var/lib/mysql/ibdata1 , it’s found it’s being re-created .In fact, the original ibdata1 (size 270MB) is found to disappear after crashed!

Lucky I have a backup of this MySQL database every morning , so I could do the restore right away. After the restore , I need to restart all nagios and opsview daemon , or else the database connection error will continue to appear :

/etc/init.d/mysql stop
/etc/init.d/opsview stop
/etc/init.d/opsview-agent stop
/etc/init.d/opsview-web stop
[MySQL database restoration]
/etc/init.d/mysql start
/etc/init.d/opsview start
/etc/init.d/opsview-agent start
/etc/init.d/opsview-web start

The problem now is what exactly cause the server crash and even worse : /var/lib/mysql/ibdata1 and /var/lib/mysql/[hostname].err even disappeared after boot up ?