‘table_name.frm is locked against change’ MySQL Error and Recovery

Each MySQL table is represented by an .frm file on disk, no matter which storage engine you use. An .frm file describes the table definition and possesses the same name as of table. The corruption of .frm file leads to table inconsistencies. It could happen due to improper locking of table, DLL file errors, system faults, disk issues, virus attack, unexpected shutdown etc. You need to use your recent data backup to restore data in such situations. However, if data backup exists in invalid state, or is not available at all, you need to use MySQL Database Recovery applications that prove to be safe tools for repairing damaged MySQL databases.

You might receive the following error message with MySQL database table(s):

".frm is locked against change"

Cause

The primary cause for the occurrence of above error message is corruption of .frm file.

Solution

In order to correct such corruption errors, you require following these steps:

- You need to first ensure about table corruption. To perform this check, use CHECK TABLE command while server is running. It follows the below syntax and is used with options, like QUICK, FAST, MEDIUM etc.:

CHECK TABLE tablename [options]

If you are using MyISAM, you can alternatively run ‘mysqlcheck’ or ‘myisamchk’ command-line utilities.

-If database check reports table corruption, you need to repair it next. Before repairing, make sure that about twice as much disk space as of affected table is available. Use REPAIR TABLE command with options, like QUICK and EXTENDED. The syntax is:

REPAIR TABLE tablename [options]

For MyISAM tables, use of ‘mysqlcheck’ or ‘myisamchk’ is also applicable.

- If above measures fail to fix table corruption, you need to delete the damaged table if valid backup exists.
- If no backup is available, or you find backup in invalid state, make use of commercial MySQL Recovery applications to repair and restore damaged MySQL tables. Such software are complete database repair products that scan corrupted databases using powerful scanning algorithms. These MySQL Database Recovery tools provide automated recovery options together with interactive interface and read-only design.

Stellar Phoenix Database Recovery For MySQL is a premier utility to repair and restore damaged MySQL tables. The software supports MySQL 5.x. It can recover database files for both InnoDB (.ibdata, .ibd and .frm) and MyISAM (.myd, .myi and .frm) database storage engines. It can restore all database objects, like tables, relations, primary key(s), and others. It can recover databases of Windows and Linux platforms and is compatible with Windows Vista, XP, 2003, and 2000.