PHPMyAdmin repair table

Quick links

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.

PHPMyAdmin repair table : Table content not getting displayed
Table content not getting displayed

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

PHPMyAdmin repair table : MySQL error log in XAMPP control panel
MySQL error log in XAMPP control panel

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
PHPMyAdmin repair table : MySQL is marked as crashed and should be repaired
MySQL is marked as crashed and should be repaired

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.

PHPMyAdmin repair table : Opening table structure
Opening table structure

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.

PHPMyAdmin repair table : PHPMyAdmin repair database
PHPMyAdmin repair database

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

PHPMyAdmin repair table : Tables repair operation success message
Tables repair operation success message

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.

PHPMyAdmin repair table : Table content not displaying
Table content not displaying

MySQL innodb crash recovery

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

PHPMyAdmin repair table : PHPMyAdmin export database
PHPMyAdmin export database

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.

PHPMyAdmin repair table : MySQL export database
MySQL export database

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.

PHPMyAdmin repair table : Drop tables operation
Drop tables operation

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.

PHPMyAdmin repair table : Drop tables confirmation, foreign keys check disabled
Drop tables confirmation, foreign keys check disabled

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.

PHPMyAdmin repair table : Importation back of the database conent
Importation back of the database conent

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

PHPMyAdmin repair table : Import confirmation
Import confirmation

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.

PHPMyAdmin repair table : Tables content displayed again
Tables content displayed again

Similar articles

Comments (0)

Leave a comment