Page 1 of 2
mysqld: Table is crashed and last (automatic?) repair
Posted: Fri Dec 11, 2020 10:16 am
by capgemtools
Hi Support,
We are getting following errors in MariaDB logs for one of our Nagios servers.
[ERROR] mysqld: Table './nagios/nagios_notifications' is marked as crashed and last (automatic?) repair failed
We tried several steps of repairing the DB,
1. with db repair scripts.
/usr/local/nagiosxi/scripts/repair_databases.sh
2. Force repair.
/usr/bin/systemctl stop mariadb.service
cd /var/lib/mysql/nagios
/usr/bin/myisamchk -r -f nagios_notifications
/usr/bin/systemctl start mariadb.service
rm -f /usr/local/nagiosxi/var/dbmaint.lock
/usr/bin/php /usr/local/nagiosxi/cron/dbmaint.php
3. truncating the table but it seems it resolves for some time but when a new alert comes in it again crashes.
mysql -h 127.0.0.1 -uroot -pnagiosxi nagios -e "truncate table nagios_notifications;"
Need you help to fix this please.
Thanks,
Sandeep
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Fri Dec 11, 2020 3:45 pm
by tgriep
If the max connections to the MYSQL database has been exceeded, it would cause the database to be corrupted again and again until it has been increased.
Follow this article to increase the Max Connections to the MYSQL database.
https://support.nagios.com/kb/article/n ... -513.html​
After the change, run a repair of the database and see if this fixed the issue on your server.
If not, open a root shell on the Nagios server and run the following commands. Get the /tmp/info.txt file and upload it to the post.
Code: Select all
mysql -u root -pnagiosxi -e "show global status like '%used_connections%'; show variables like 'max_connections';" >/tmp/info.txt
echo "SELECT table_schema as 'Database', table_name AS 'Table', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC;" |mysql -t -u root -pnagiosxi >>/tmp/info.txt
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Mon Dec 14, 2020 9:00 am
by capgemtools
info.txt
Hello Support,
I tried the increasing the MAX Connections as you suggested and then run a repairdb script. but still the same errors are visible in the mariadb logs.
I have run the two command you asked me to run. Output is attached to this reply. >>
info.txt
info.txt
Thanks,
Sandeep
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Mon Dec 14, 2020 4:35 pm
by tgriep
Run this command to repair the nagios database on the server to see if this one will work.
Code: Select all
mysqlcheck -f -r -u root -pnagiosxi --databases nagios --use-frm
If you start seeing the corruption to the database happen after running the above, what version of XI are you running on the server?
Can you post the last 300 lines of the mariadb.log file? If the file is in the default location, run this as root to get that information.
Code: Select all
tail -300 /var/log/mariadb/mariadb.log
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Mon Dec 14, 2020 5:45 pm
by capgemtools
Hi tgriep,
I ranthe command you asked me to run attached is the output. As of now there are no errors in the log. I will monitor for few time and update you if I see anything.
mysqlcheck_output.txt
mysqlcheck_output.txt
mariadb_logs.txt
mariadb_logs.txt
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Tue Dec 15, 2020 9:06 am
by capgemtools
Hi Tgriep,
The corruption errors reappeared in mariadb logs. I have attached latest mariadb logs.
We are using Nagios XI 5.6.14
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Tue Dec 15, 2020 12:28 pm
by tgriep
Can you post or PM me the full /var/log/mariadb/mariadb.log log file and a System Profile so I can view them?
Could you send in your Nagios XI System Profile so I can review it?
To get your system profile. Login to the Nagios XI GUI using a web browser.
Click the "Admin" > "System Profile" Menu
Click the "Download Profile" button
Save the profile.zip file and upload it to the post or PM it by clicking the "choose item" link at the bottom of the menu.
Also, post or PM the /usr/local/nagios/var/nagios.log file from the server.
After I receive the data and had some time to look at it, I'll get back to you with the next step in the troubleshooting process.
If the log files are large, zip them up first.
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Tue Dec 15, 2020 1:44 pm
by capgemtools
Hi Tgriep,
I have collected the details you asked. Please find them attached.
mariadb.log = mariadb_log.zip
nagios.log
profile.zip
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Wed Dec 16, 2020 10:53 am
by tgriep
Yesterday at 10:05:30 AM I see the first error in the mariadb.log but the log files in the profile do not go that far back so can you get the following files from the server for that time and post them here?
Code: Select all
/var/log/messsages
/usr/local/nagiosxi/var/dbmaint.log
Run this as root and post the output here.
Code: Select all
echo 'desc nagios_notifications;' |mysql -t -u root -pnagiosxi nagios
Thanks.
Re: mysqld: Table is crashed and last (automatic?) repair
Posted: Wed Dec 16, 2020 1:36 pm
by capgemtools
Hi Tgriep,
Please find the command output below and files attached.
[root@pngoawg001 log]# echo 'desc nagios_notifications;' |mysql -t -u root -pnagiosxi nagios
ERROR 144 (HY000) at line 1: Table './nagios/nagios_notifications' is marked as crashed and last (automatic?) repair failed
/var/log/messages = messages.zip
/usr/local/nagiosxi/var/dbmaint.log = dbmaint.txt