Table is marked as crashed and should be repaired как исправить

Windows

• 21.04.2017
• 13 916
• 2
• 17
• 17
• 0

• Содержание статьи
  • Способ 1: с использованием PHPMyAdmin
  • Способ 2: с использованием MySQL Client Software (MySQL CLI)
  • Комментарии к статье ( 2 шт )
  • Добавить комментарий

Эта ошибка может появляться в 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):

mysql> USE MYDATABASE;
Database changed

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

mysql> REPAIR TABLE m_rating;

Are you facing “MySQL is marked as crashed and should be repaired” error message while accessing your MySQL database? Tried several ways to solve this issue but still not get success? If ‘YES’ then you have come to very correct place.  As in this article, you’ll learn how to fix table ‘./mysql/proc’ is marked as crashed and should be repaired error.

But before knowing the fixes to deal with such error, it’s very important to know about this issue and what causes this MySQL is marked as crashed error. So, without wasting any more time, read ahead…

“MySQL is marked as crashed and should be repaired”

The above mentioned error message may occur while you’re trying to accessing the MySQL database. The frequency of this error is more after the forced shutdown of MySQL database or because of the crash in the entire server. Now, let’s see what actually causes this issue.

What Causes MySQL Is Marked As Crashed Issue?

There are varuoius reason that causes of table mysql/proc’ is marked as crashed and should be repaired error. The first reason is behind the table crash is running out of disk space. If you got the huge amount of data in your database then ensure that you got the required disk space in advance.

Other reason behind MySQL table crashes are issuers with the operating system, hardware problems, power failures, unexpected termination of MySQL server, data corruption due to external programs.

Before proceeding towards a repair process, let have a look at how to identify a damaged or corrupted MySQL tables.

Identifying The Crashed MySQL Tables

Well, you can easily the corrupted or damaged MySQL tables via some observable errors which I have listed below:

1. Unexpected end of the file
2. Cannot find file tbl_name.MYI (Errcode: nnn)
3. Recorded file is crashed
4. Got error from table handler
5. frm is locked against change
6. Can’t find file tablethatshouldbethere.MYI
7. Got error ### from table handler.

How Long It Will Take To Repair The Crashed MySQL Table?

This is the question of most of the users for the few KB table, it just take a few seconds; for few MB table, it take few minutes; for few GB table it take hours; for huge GB tables, it take days to weeks! It even depends on the available RAM and the processor power.

Do you got the backup of the table? It might be the better option to restore the table from the backup before fixing any error. Generally, user face crash issue and the best option to overcome it to use the backup.  Unless the data is very important and you need it urgently run a weeklong repair process. But isn’t the weeklong repair process quite time consuming. Think about it. Always have a proper backup of your database.

How To Fix MySQL Table Is Marked As Crashed And Should Be Repaired” Error?

To repair the MySQL repair crashed table command line by following the given methods:

• Technique #1: Use The Myisamchk To Fix MySQL Table Is Marked As Crashed
• Technique #2: Repairing Table Using MySQL console
• Technique #3: Using PHPMyAdmin

Technique #1: Use The Myisamchk To Fix MySQL Table Is Marked As Crashed

The first method that you can to try to deal with the table ‘./MySQL/proc’ is marked as crashed and should be repaired error is to use MYISAMCHK tool.

To fix using myisamchk, you need to follow the below given steps:

Step 1: Firstly, Login to the server by using SSH command like–ssh root@example.com.

Step 2: Then, stop MySQL daemon/service through executing a MySQL stop command service directory to your MySQL database.

Step 3: Generally the directory is in /var / lib / mysql. And the command to try the below commend:

cd / var /lib / MySQL/YOUR DATABASE NAME

Step 4: At this time, execute a myisamchk command that mentioned below:

myisamchk-r table name.

Step 5: Now, start the MySQL service again via executing a MySQL command.

After completion of these steps, you can see that your MySQL table error has been fixed. If this method unable to fix the above mentioned error then you can try the next solution.

Technique #2: Repairing Table Using MySQL console

If got the shell access then connect to MySQL server and perfrom the following steps:

>use my_database;
>repair table my_crashed_table;

This step is recommended to repair crashed tables from few KBs to few GBs, which depend on the RAM and processor power.

Technique #3: Using PHPMyAdmin

Rememeber: if you haven’t got the shell access then only use this method. Usually this step is recommended for small size tables only, few KB to few MBs. And anything more than that will only be a waste of time and you’ll end up frustrated. Perform the following steps:

Log on PHPMyAdmin > choose affected database > choose affected table from right pane > from With selected menu, choose “Repair table”.

The crashed table will be repaired in short span of time, if the table size is a few KBs to a few MBS, and engine is MyISAM.

Automatic Way to Solve MySQL Table Is Marked As Crashed And Should Be Repaired” Error

If the above given solutions fails to table mysql/proc’ is marked as crashed and should be repaired then another best option left for you that is MySQL Repair Tool. Yes, it is an amazing tool, which help you repair corrupted, damaged or inaccessible data.

It not only repairs the corrypted MySQL database but also recovers data and tables structure in a healthy form. With the help of this software there is no chance of losing your important data. It supports almost every versions of MySQL such as 1.x, 2.x, 4.x, 5.x or 3.x which is accessible for all present platforms that include Windows 98/ 95/XP/200/2003 servers.

It has user friendly interface which doesn’t require any technical knowledge to fix any type of error and issues in the MySQL file. So without wasting time download this feature loaded software.

Steps To Resolve “Table For Sessions Is Marked As Crashed And Should Be Repaired” Error

Step 1: Start with Stellar Phoenix Database Repair for MySQL . Software and you can see the main interface as shown below.

Step 2: Click on ‘Select’ button & then select ‘Repair corrupt database which is stored at default location of MySQL’ option to select your database from default location and then click.

Step 3: The left pane shows the preview of database and its component in a tree like structure after completing scanning process.

Step 4: After previewing your database, to start repair process click ‘Repair’ button in file menu on the main user interface. Stellar Phoenix Database Repair for MySQL dialog box appears. Click Yes.

Step 5: After successful completion of the process, a message box is displayed as ‘repair process finished successfully’. Click ‘OK’ to close the message box.

Step 6: If you want to select the database manually then select ‘Manual selection of database to repair’ option & then click browse. Browse the folder dialog box opens. Select the folder that contains database and click ‘OK’.

To Sum Up

Well, I hope all the above given solutions will definitely work for you to repair MySQL table is marked as crashed and should be repaired issue. But, if you want the instant MySQL repair crashed table resolution, then I would like to suggest you to try the best and advanced MySQL Repair Tool to fix this issue.

If you liked this post then do not forget to share it with you friends & family members.

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:

# myisamchk -s /var/lib/mysql/*/*.MYI

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

MyISAM-table '/var/lib/mysql/dbname/table_name.MYI' is marked as crashed and should be repaired

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:

# myisamchk -r --update-state /var/lib/mysql/dbname/table_name.MYI

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 is a free-source RDBMS introduced by Oracle. It is compatible with all kinds of operating systems such as UNIX, Windows, and Linux. Besides, it is mostly connected with applications and publishing. 

Being the most compatible RDBMS platform, the interface is loaded with underlying inconsistencies. Moreover, one of them is ‘restore MySQL table is marked as crashed. Here. we will take a look at what does this inconsistency means?

What does “Mysqldump Error 1194 Table is Marked as Crashed” States?

While using MySQL, users may experience an error stating that “MySQL table is marked as crashed and should be repaired.” This error can pop up at any instance, especially after an abrupt system shutdown. If MySQL database stops working or due to the entire server system crash. While the error may seem to decline, it can be recovered. Furthermore, MySQL can fix the corrupt tables on its own. A user may require to follow the simple tactics given below.

Let’s discuss all the possible causes and relevant methods to recover corrupted MySQL database inconsistency.

Why is MySQL Table Marked as Crashed?

Less disk capacity can be the cause behind MySQL tables being marked as “crashed”. When an individual acquires less space, then some tables in the database might get damaged’’.

Often it is difficult to detect the particular tables that are severely harmed by the inconsistency. Before beginning the fixing process, let’s take a look at methods for detecting the corrupt MySQL database tables. 

Detecting MySQL Repair Crashed Tables:

A user can identify the corrupt database files and human errors in the MySQL database. An individual might be able to encounter the ‘’MySQL table is marked as crashed and should be repaired InnoDB“ error as well by the following actions.

• Cannot detect file tbl_name.MYI (Errcode: nnn)
• Unpredicted termination file
• Receiving inconsistency issues from table handler.
• Damaged Record FIle
• Cannot search file tablethatshouldbethere.MYI
• tablethatwasworking.frm is blocked from editing
• Received faulty error ### from table handler.

You can try running the inconsistency to receive extra details about the error.

How to Recover Crashed MySQL Database?

First, MySQL host offers the utility to repair crashed tables. The utility is referred to as myisamchk, and you can use it as shown below. Using these methods you can restore the MySQL table is marked as crashed.

# myisamchk -s /var/lib/mysql/*/*.MYI

Secondly, applying the above command will display the corrupt MySQL tables encrypting the below message. 

MyISAM-table ‘/var/lib/MySQL/dbname/table_name.MYI’ is marked as crashed and should be repaired

Irrespective of the result, the tool is capable of resolving the corrupt MySQL database. Mostly, the problem can be fixed by running command myisamchk. But, if the trick doesn’t work then try disconnecting the corrupt database from the internet. Additionally, the admin won’t be able to create new requests. 

myisamchk -r –update-state /var/lib/mysql/dbname/table_name.MYI

In the end, the “ upgrade mode” switch dictates MySQL to tick the referred table. Next, reboot MySQLd and internet connection, and MySQL database will fix the corrupt database. As an outcome, the inconsistency will be eliminated and your tables will swiftly be recovered.

Restore MySQL Database with REPLACE

Another way to face the ‘MySQL table is marked as crashed and should be repaired’ error is to restore the database by using the recently generated backup file.

Using a prominent backup technique is necessary to store crucial information away from strangers. Moreover, a backup technique helps you restore data when nothing seems right. Besides, exploring the crashed MySQLfiles is crucial for time handling. Hence, this is why your database must have a  home directory and crucial details. Furthermore,  installing directory and personal directories should be test return frequently by using database administration or any backup tool of user preference.

Moving on to the commercial ways to resolve the ‘’MySQL table is marked as crashed and should be repaired InnoDB’’ error.

Restore MySQL Table is Marked as Crashed Professionally:

There are higher chances that when you resolve the error using the above manual approach, the data may get corrupt eventually. Although, you can try resolving by taking a backup of MySQL credentials at a secure place. Therefore, an individual can use MySQL Database Recovery Software for this purpose.

Summing It Up

The sole purpose of this article was to acknowledge an individual about MySQL errors- how to detect them, the major causes behind them, How to resolve them. Besides, it is pretty obvious that no such distinct approach is launched yet to resolve MySQL error ‘MySQL table is marked as crashed and should be repaired InnoDB’. Therefore, one can try using the manual solution to fix the error at its best.