[fix] mysql is marked as crashed and should be repaired

Possible Solutions

You can repair the nagios and nagiosql databases by running the following commands in the command line as the root user:

/usr/local/nagiosxi/scripts/repairmysql.sh nagios/usr/local/nagiosxi/scripts/repairmysql.sh nagiosql/usr/local/nagiosxi/scripts/repairmysql.sh nagiosxi

Alternatively, if you are running Nagios XI 2014 onwards, you can use:

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

which will actually repair both databases (nagios and nagiosql) (and nagiosxi in XI 5.x).

If you receive an error, similar to this one:

SQL: DELETE FROM nagios_logentries WHERE logentry_time < FROM_UNIXTIME(1293570334)SQL: SQL Error  :</b> Table './nagios/nagios_logentries' is marked as crashed and last (automatic?) repair failed CLEANING ndoutils TABLE 'notifications'...    

You may need to run a force repair on the tables using commands below:

RHEL 7 | CentOS 7 | Oracle Linux 7 | Debian 9

systemctl stop mariadb.servicecd /var/lib/mysql/nagiosmyisamchk -r -f nagios_<corrupted_table>systemctl start mariadb.servicerm -f /usr/local/nagiosxi/var/dbmaint.lockphp /usr/local/nagiosxi/cron/dbmaint.php

Debian 8 | Ubuntu 16/18

systemctl stop mysql.servicecd /var/lib/mysql/nagiosmyisamchk -r -f nagios_<corrupted_table>systemctl start mysql.servicerm -f /usr/local/nagiosxi/var/dbmaint.lockphp /usr/local/nagiosxi/cron/dbmaint.php

In certain instances, it may be necessary to truncate (empty) one or more tables. The following commands provide examples of how to truncate both the nagios_logentries and nagios_notifications tables in the nagios MySQL database:

mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_logentries'mysql -u ndoutils -pn@gweb nagios -e 'TRUNCATE TABLE nagios_notifications'

Important: Running these commands will clear all entries from the affected tables. After you truncate tables, you should repeat the repair process outlined above.

Further information: Repairing The Nagios XI Database

If you have run out of disk space on your Nagios XI server, the following KB article can provide solutions to resolving this issue:

Once you’ve resolved the disk space issue you should run the repair database script again.

Crashed Tables in MySQL

While using MySQL, users may experience an error stating that “MySQL table is marked as crashed and should be repaired.” This error can appear at any time, especially after a forced shutdown of MySQL database or due to the crash of the entire server. While the error may seem daunting, it can be resolved. MySQL can repairing the crashed tables by itself. You would need to follow a few simple steps to make that happen. 

Let’s discuss the possible reasons and MySQL repair methods for this error.

The cause behind the error

Low disk space could be a reason for MySQL table being marked as “crashed”. When you have low space on your disk, some of the tables in the database may get crashed. It usually happens when you get space issues on the part of the hard disk where your database is stored.

Often it gets difficult to identify the specific tables that are affected by the error. Before starting the repair process, thus, let us quickly take a look at how to identify the corrupted MySQL tables.

Identifying crashed MySQL tables

You can identify corrupted tables through certain expected and observable errors in the queries, for example:

• Can’t find file tbl_name.MYI (Errcode: nnn)
• Unexpected end of file
• Got error from table handler
• Record file is crashed
• Can’t find file tablethatshouldbethere.MYI
• tablethatwasworking.frm is locked against change
• Got error ### from table handler.

You can run perror to get more information about the error

Recovering MySQL Crashed Tables

MySQL server provides its own tool to diagnose crashed tables. This tool is known as myisamchk, and you can use it as shown below:

Running this command will list the crashed MySQL tables, with a message as follows:

Other than diagnosis, the tool can also help you repair the damaged table. In most cases, the problem is solved by running the myisamchk command. But if it doesn’t solve the problem, try stopping your web service, so that no new requests are generated, stop the MySQLd daemon itself and run the below-given command:

The “–update-state” switch tells MySQL to mark the indicated table as “checked.” Once this has been done, restart your MySQLd and web service, and MySQL will repair the crashed table. As a result, the error will be removed, and your database will be safely restored.

Recovering database by using backup

Another way to deal with ‘MySQL table is marked as crashed and should be repaired’ error is to recover your database from a previously saved backup.

Having a solid backup strategy for your data is essential. It helps you recover the data when everything else goes wrong. Instead of searching the methods for repairing MySQL crashed tables, you can restore data instantly from a backup, and that will surely save your time. That’s why your database, install directory, and home directories should be backed up and tested regularly by using database administration or any backup tool of your choice.

Software tool for MySQL Database Repair

Stellar Repair for MySQL is a powerful and convenient tool for MySQL database repair, which can save time while doing the repair more effectively. So, it can be your default first choice.

Alternatively, you can use the software as a complementary solution to fill in for the manual method- if it doesn’t work, or if you don’t have the backup to restore your database.

It is available for a free trial download so that you can test it out for repairing your MySQL database files. The software recovers all the objects including keys, views, triggers, tables, etc., in their original format. It can repair InnoDB and MyISAM tables and allows us to save the MySQL repair database in various formats like MySQL, CSV, HTML, and XLS.

To summarize

MySQL is one of the most trusted relational database management systems. But if you need to write queries in MySQL repair table or recover tables, you need to have deep technical knowledge about it. You cannot play around with MySQL without knowing the roots of it since that will increase the chances of you losing the data permanently.

So, if you don’t have hands-on proficiency on MySQL, a safer option is to recover the corrupt database by using the backup. Next, you can use the built-in myisamchk utilityfor the database repair.

A safer, convenient, and more effective MySQL database repair solution is offered by Stellar Repair for MySQL. It’s available for the free trial which offers to scan and preview the database so that you can ascertain the capabilities really well.

mysql table is marked as crashed and last (automatic?) repair failed

http-equiv=»Content-Type» content=»text/html;charset=UTF-8″>style=»clear:both;»>

Следующее сообщение об ошибке появилось, когда MySQLDump сделал резервную копию базы данных:

Подскажите, что в таблице в базе данных есть ошибка, вы можете использоватьЭтот инструмент выполняет ремонт, шаги следующие:

1. Остановите базу данных

2. Войдите в каталог базы данных и восстановите поврежденную таблицу.

3. Перезагрузите базу данных.

Интеллектуальная рекомендация

Глава первая: Причина В большинстве анекдотов в Интернете говорится, что программисты относительно тупые, плохие слова и в основном мужчины. Я один из тысяч программистов. Обычно я не знаю, как правил…

Всегда был спрос, надеясь увидеть в реальном времени рейтинг моего сайта в Baidu Я использовал некоторые инструменты, либо медленный ответ, либо результаты не точные или в режиме реального времени Поэ…

Алгоритм обнаружения характерных точек Обнаружение угла Харриса                                 Обнаружение функции SIFT…

По просьбе пользователей сети напишите пример использования Selenium Grid для управления несколькими системами и несколькими браузерами для параллельного выполнения тестов. Поскольку у меня здесь две …

Эта проблема возникает, когда используется openrowset. Просто выполните следующий код:           http://www.cnblogs.com/wayne-ivan/archive/2008/01/07/1028759.html…

Вам также может понравиться

В проекте .net я часто сталкиваюсь с необходимостью автоматически делать скриншот кадра после загрузки видео. Вот метод использования ffmpeg для автоматического создания скриншота Сначала загрузите фа…

Ленивая загрузка не удалась, потому что @Responsebobode JSON преобразует Getroles по умолчанию, которая заканчивается пользователем, поэтому ленивая загрузка недействительна. Если вы предоставляете TO…

virtualenv установка Основное использование Создайте виртуальную среду для проекта: virtualenv venv создаст папку в текущем каталоге, содержащую исполняемые файлы Python и копию библиотеки pip, чтобы …

Java.io.fileNotfoundException: файл: \ d: \ Code \ xml-load \ target \ xx.jar! \ Xxx (имя файла, имя каталога или синтаксис громкости неверно.) 1. При использовании Spring Boot для применения к JAR не…

Недавно я попытался установить MySQL под Windows, ссылаясь на документацию в Интернете, но я также сказал, что столкнулся с некоторыми проблемами во время реальной работы. Пожалуйста, запишите здесь д…

Possible Causes

Database corruption is usually caused by power outages, running out of disk space or improper shutting down the Nagios XI server. Nagios is constantly reading from and writing to the mysql database, so killing the mysqld process (i.e. with «kill -9» or «killall mysqld») will lead to crashed tables.

Many time users would right-click on the Nagios XI VM in VMWare Workstation/ESXi/Vsphere and select «Power Off». This is wrong — it is like pulling off the power cord. The correct way for shutting down the Nagios XI server would be to issue the following command in the command line:

shutdown -h now

If the Nagios XI machine has insufficient disk space then you may see errors like this when the repair database script is run:

/usr/local/nagiosxi/scripts/repairmysql.sh: line 59: 11735 Segmentation fault (core dumped) $cmd $t --sort_buffer_size=256MTimeout error occurred trying to start MySQL Daemon.Starting mysqld: