[Fixed] MySQL Incorrect Information in File Error

Several MySQL users have reported about receiving the 'Incorrect information in file error' when restarting the MySQL server after it crashes due to a power outage or other reasons. However, the error may also occur due to a problem with the ‘my.cnf’ configuration file. The complete error message appears as:

ERROR 1033 (HY000): Incorrect information in file: '.*.frm'

Methods to Fix the MySQL ‘Incorrect Information in File’ Error

The following are five methods to repair corrupted tables and fix the ‘Incorrect information in file’ MySQL error.

Note:If the storage engine of the corrupt table is MyISAM, use methods 1 and 2 to repair the table. If the table is using the InnoDB storage engine, skip to method 4.

Method 1 – Run the REPAIR Command with use_frm

To fix corrupt MyISAM tables, run the following command:

Here, tbl1 is the name of the table that needs to be repaired. Replace it with the name of your table you want to fix.

Method 2 – Drop and Recreate the Corrupt Table

To do so, follow these steps:

• Stop the MySQL server instance
• Backup tbl1.FRM (table structure), tbl1.MYD (table data), and tbl1.MYI (indexes) files.
• Drop tbl1 table
• Start the MySQL server
• Use CREATE TABLE statement to recreate the table (i.e., tbl1 in our case)
• Stop the server once again
• Copy theMYDandtbl1.MYIfiles into the database directory (datadir)
• Restart the MySQL instance

If this doesn’t help resolve the issue or there’s a lot of missing data in the table, skip to the following method.

Method 3 – Restore from Backup

If the above repair methods don’t work for you, restore the MySQL database from the most recent backup. Restoring a backup is the only option when it comes to recovering data from a corrupt table running on the InnoDB storage engine.

If you don’t have a backup, using a professionalMySQL repair toolmay help.

Method 4 – Correct the Configuration File

有时,MySQL服务器可能从wro开始ng configuration file (my.ini for Windows and my.cnf for Linux OS) resulting in error 1033. Such a situation happens when a script – that automatically adapts to changes in the my.cnf file when the data files are downloaded – fails to start. The size of an InnoDB ibdata1 file might not match with the downloaded file.

For instance, the my.cnf file contains the ibdata1 file of size 2GB but the size of the second data file is 4GB.

innodb_data_file_path=ibdata1:4GB;ibdata2:50M:autoextend

To resolve the issue, shut down your server and correct the above line in the configuration file to:

innodb_data_file_path=ibdata1:4GB;ibdata2:50M:autoextend

Save my.cnf file and the tables will be restored to a working state.

Method 5 – Use a MySQL Repair Tool

恒星修复MySQL软件是专用的to help MySQL users repair their corrupt tables for MyISAM and InnoDB storage engines. You can download the software’s demo version to verify if the recoverable data is correct or not.

The software supports repairing database tables of MySQL 8.0.26 and earlier versions on both Windows and Linux systems. It restores the original table properties, primary keys, views, triggers, etc.

Steps to Repair a MySQL Database Table Using Stellar Repair for MySQL

• Once you have downloaded and installed the software, launch it.
• Read the instruction and click OK to proceed.

• Browse and select the MySQL data folder containing the database you want to repair, then click OK.

• Select the database that has the corrupt table and click Repair.

• Click OK when presented with a ‘Repair Complete’ message box.

• A preview of all the repaired tables and their recoverable data is displayed.

• Select all or specific items you want to restore and click Save on the File menu.

• Choose to save the repaired database to a new MySQL database or other file formats like CSV, HTML, or XLS. Press the Save button.
• Select the location to save the database and click Save once again.

The repaired database and all the restored tables will be saved in the selected location.

FAQs

Q. As suggested in method 4 above, I changed the line in my.cnf to 80MB that helped restore the InnoDB tables. Can you suggest how can I make the ibdata1 size return to its actual size of 100MB?

A. Mark the first data file as autoextending to return the file to its actual size of 100MB.