Mysql error marked crashed should repaired

Ошибка Table ‘tablename’ is marked as crashed and should be repaired

Эта ошибка может появляться в mysql, например, после непредвиденного отключения питания у сервера, неожиданного завершения работы самого MySQL сервера или каких-либо ошибках в работе диска.

Error Number: 145
The Error returned was:
Table ‘tablename’ is marked as crashed and should be repaired

Как и следует из текста ошибки, нам необходимо просто отремонтировать поврежденную таблицу с помощью встроенных средств MySQL. В данной статье будет описано 2 способа, как это сделать.

Способ 1: с использованием PHPMyAdmin

Открываем перечень всех таблиц в PHPMyAdmin, после чего выделяем нужную и выбираем пункт «Repair table».

Способ 2: с использованием MySQL Client Software (MySQL CLI)

Сначала выбираем необходимую базу данных (в примере ниже это MYDATABASE):

А далее уже ремонтируем саму поврежденную таблицу (в примере ниже это m_rating) в выбранной базе данных, для этого нужно отправить следующую команду:

Источник

[Fix] MySQL is marked as crashed and should be repaired

MySQL is an open source RDBMS backed by Oracle. It supports all platforms, for example, UNIX, Windows, and Linux. It is mostly associated with web apps and online publishing, but it is not restricted to that, and can be used in a wide range of applications. Its working model is a client-based server.

The core of MySQL is MySQL server that handles all of the database commands. MySQL server is available as a separate program for use in a client-server networked environment. Though it is installed on one machine as a library that can be linked to separate applications, it is capable of sending data to multiple locations. It allows data to be stored on and accessed over multiple storage engines. To improve performance and durability, it also can replicate data. And you are not required to learn new commands for MYSQL; you can access the MySQL database by using standard SQL commands.

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.

About The Author

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software’s. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.

Best Selling Products

Stellar Data Recovery Professional for Windows

Stellar Data Recovery has the right Windows Recovery tool for all your data recovery

Stellar Data Recovery Professional for Mac

Stellar Data Recovery for Mac program performs safe..

Stellar Photo Recovery

A comprehensive photo recovery software to restore photos, music & video files

Stellar Repair for Video

Powerful video repair tool for repairing corrupt or damaged MOV and other video files

Источник

Fix Error “MySQL is Marked as Crashed and Should be Repaired”

Read time 4 minutes

MySQL is an open-source Relational Database Management System (RDBMS), which is written in C and C++ languages and supports multiple platforms including Windows, Linux, macOS, UnixWare, etc. It is used by various applications for data storage, online publishing, and web apps. Also, it is supported by Oracle.

It works based on the client-server model with MySQL Server as the server. You can store, access, or retrieve the data stored in the server anytime.

About the “MySQL is marked as crashed and should be repaired” Error

In this blog, we get a brief introduction to one of the common MySql errors- MySql is marked as crashed and should be repaired.

The MySQL error “MySQL is marked as crashed and should be repaired” may occur suddenly while you are accessing MySQL. The frequency of the error is more after the forced shutdowns of the MySQL Server. Whatever be the reason, you cannot access or use your MySQL tables anymore as it has been completely crashed.

Reasons for the “MySQL is marked as crashed and should be repaired” error

While using MySQL, users sometimes face issues with the pop-up message, such as “MySql table is marked as crashed and should be repaired” and this happens especially after an abrupt system shutdown. Less disk capacity is the reason behind MySQL tables being marked as crashed because due to less space, tables in the database might get damaged. Other reasons like high I/O wait on the disk, and heavy load on the server can also cause MySQL crashes.

How one can identify MySQL crashes?

One can identify this problem by certain error messages, which are as follows-

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

What is the Immediate Remedy? – Restore from Backups

If you are a professional or a common MySQL Server user, you must have taken backups of the MySQL Server database files. With a recent backup of MySQL Server, it would be quite easy to restore the whole database. But If you do not have backups for MySQL Server data, then you will have to find some other solutions on your own, which would take both effort and time. So, our suggestion is to take periodic backups of the MySQL database.

How to Fix Error “MySQL is marked as crashed and should be repaired” Without Backups?

Through myisamchk Tool

Users can find out the broken or crashed MySQL tables using the native tool provided by the MySQL application known as “myisamchk”.

Run this command on myisamchk command line.

It will list out the tables which are corrupted, and you would get to know where to make the fix.

Once you get to know the name of the tables that need repair, then you can execute the next command.

In the above command, you need to provide the table name which was found out in the first command (broken or crashed).

If the table is not repaired yet, you can try another command. Before that, just stop the web services, stop MySQLd and then run this command.

Next, restart the MySQLd and the web services and check if the error is fixed.

Even after executing these commands in myisamchk application, it might be possible that you get an incomplete recovery or no recovery at all. But we are here to help you.

What Should Be Your Next Step? – Professional Third-Party Utility

If you want to recover the corrupt MySQL Server database completely and flawlessly, then you should go for the professional third-party utility which is reliable and efficient.

Here, we introduce you to the advanced Kernel for MySQL Database Repair tool for MySQL file recovery in very little time. It is designed with the latest technology and highly efficient programs to quickly fix all the corruption issues of MySQL database files and make the data accessible to the users.

It effectively repairs all the issues of the MySQL file and makes the inaccessible data available for preview. Now, you can select the required data and then save it to either MySQL Server directly or to a batch file. Moreover, the advanced tool is compatible with all MySQL Server and Windows Operating System versions.

Final Words

MySQL database is popular among database users as it is open-source and is supported by Oracle. However, there are a few errors that make its data inaccessible. Here, we have discussed some manual solutions to fix one of the MySQL errors. And in case of an unsuccessful repair, we have suggested a professional tool at the end.

Источник

Заметки Лёвика

web программирование, администрирование и всякая всячина, которая может оказаться полезной

Table is marked as crashed and should be repaired — ошибка Mysql

Table host/tablename is marked as crashed and should be repaired

Сообщение о такой ошибке при запросе к базе данных mysql может сбить с толку начинающего (да и уже “опытного”) владельца или администратора сайта. Crash mysql. Однако, все не так уж плохо — возможно, таблица всего-навсего повреждена, и её следует восстановить.

Как восстановить таблицу mysql?

В следующих двух строках вместо %table_name% следует подставить название поврежденной таблицы.

Первым делом следует проверить таблицу (запрос “CHECK TABLE %table_name%”) .

Для восстановления таблиц в mysql существует специальный запрос “REPAIR TABLE %table_name%”, который возвращает результат своей работы в виде таблицы:

| Table | Op (операция — repair) | Msg_type (тип сообщения) | Msg_text (текст сообщения) |

Если действительно, ничего страшного не случилось, то, скорее всего, этими нехитрыми действиями Вам удастся восстановить поврежденную таблицу. Если же описанные шаги не помогли — смотрим в сторону мануалов “How to Repair MyISAM Tables”

Если Вы заметили ошибку “marked as crashed and should be repaired” — сообщите, пожалуйста администратору, владельцу или представителю службы технической поддержки сайта. Как минимум, они будут Вам благодарны.

Да минует Вас Крэш… во всех его проявлениях…
Удачи!

Метки: mysql

Опубликовано Суббота, Май 3, 2008 в 18:41 в следующих категориях: Без рубрики. Вы можете подписаться на комментарии к этому сообщению через RSS 2.0. Вы можете добавить комментарий, или trackback со своего сайта.

Автор будет признателен, если Вы поделитесь ссылкой на статью, которая Вам помогла:
BB-код (для вставки на форум)

html-код (для вставки в ЖЖ, WP, blogger и на страницы сайта)

ссылка (для отправки по почте)

Комментарии (4) к записи “Table is marked as crashed and should be repaired — ошибка Mysql”

Здравствуйте!
Вы можете статью подробнее описать, а то я не понял, где найти (запрос “CHECK TABLE table_name”) и Для восстановления таблиц “Repair Table table_name”, который возвращает результат своей работы в виде таблицы: | Table | Op (операция — repair) | Msg_type (тип сообщения) | Msg_text (текст сообщения) |.
Заранее благодарен!

Нурбол, извиняюсь за изложение.. Не думал, что это должно вызывать трудности. Допустим имеется база db1, в которой существует таблица users. Выполняем команду:
CHECK TABLE db1.users;
(или, если база db1 уже используется — просто CHECK TABLE users)

Аналогично и для REPAIR TABLE:
REPAIR TABLE db1.users

Спасибо, я сделал по вашему совету, никаких ошибок не выявлено, по моему у меня удалено некоторые таблицы, так как я нему найти именно тех таблиц, которые выявляет на главной странице моего сайта, насчет отката какие можете советы дать? Или как можно вернуть таблицы?

Источник