PHPMyAdmin repair table

MySQL repair crashed table

After an operation on the  MySQL database‌  with PHPMyAdmin, for example complicated search queries that didn't bring any result and crashed, it might happen that the database isn't accessible anymore.

At this point, when trying to open a table, no content is displayed. The error behind it is most likely MySQL table is marked as crashed and should be repaired.

The good news is, it can most likely be solved in PHPMyAdmin client pretty fast and without any data loss. Fast depending mostly on the database size.

The first step is to check MySQL logs, the mysql_error.log file - in XAMPP, MySQL error log is accessible via the control panel, in MySQL > Logs > mysql_error.log

MySQL table is marked as crashed and should be repaired

Error might look as the following, showing that table is marked as crashed :

Table 'slow_log' is marked as crashed and should be repaired
1 client is using or hasn't closed the table properly

MySQL is marked as crashed and should be repaired

The solution is different depending on the database storage engine used, InnoDB or MyISAM. See below the solution for both of them, which is different.

Executing a PHPMyAdmin repair table for MyISAM would most likely work out using the PHPMyAdmin repair crashed table option in the graphical user interface.

The way how to repair InnoDB table in MySQL would be to export, delete, and import the database again.

However, both can be achieved with PHPMyAdmin, and will solve the table is marked as crashed and should be repaired issue.

MyISAMchk repair

First solution, working for MySQL MyISAM, is pretty easy, thanks to myisamchk tool. Using command line, this program can check, repair and optimize tables.

For our case, in PHPMyAdmin, we will call it to repair the tables, without having to run any command line.

With MyISAM tables, go to the table structure of the specified database.

There, select the tables to repair by checking the corresponding boxes, and simply select the Repair option to start a MySQL repair MyISAM table.

This might take a bit of time, depending on the database size and the exact issues that will be found by the MyISAM tables repair program.

For tables of only a few megabytes, it should be achieved within a minute.

After getting the myisamchk repair table success message, the MySQL table marked as crashed should be accessible again.

MySQL repair table InnoDB

When tables are not getting displayed anymore, and the trick for MyISAM tables isn't working (repair the tables from the tables structure screen), with PHPMyAdmin repair crashed table on InnoDB can be done via another trick - exporting, deleting and importing them back.

MySQL innodb crash recovery

In PHPMyAdmin, visit the Export menu, and there simply select Go to export the tables :

Select a place where to save the file on local computer, it will be used to repair database tables with PHPMyAdmin.

This is also how to use PHPMyAdmin to export tables, and make a backup, or also export them to another database to make a copy for example.

In the tables structure, select all the tables, and select the Drop operation. This will delete all database data. Therefore, at first, make sure that the full database has been properly backedup, and that the file is accessible and readable.

How to repair corrupt InnoDB table in MySQL

A confirmation will be requested - it is important there to uncheck the Enable foreign key checks option, otherwise tables with constraints won't be deleted, and the operation will have to be ran again until all table are deleted.

MySQL crash recovery

Once the database is empty, open the Import menu, and browse for the file previously exported, and click Go to import back the whole database in the MySQL database.

Tables are imported and recreated in PHPMyAdmin InnoDB, including all content, which should be confirmed by the interface :

And tables content is now displayed again, MySQL repair database worked for PHPMyAdmin InnoDB.

Make sure all data is there, and check if your related applications are working again as expected.

Comments (0)

Leave a comment