How to Fix ‘Incorrect Information in File’ Error in MySQL?

Several MySQL users have reported encountering the ‘Incorrect information in file’ error while accessing the tables in the database after system restart or sudden system crash. One of the user queries is:

“I am seeing “ERROR 1033 (HY000): Incorrect information in file” error after a reboot and some other slowdown issues. MySQL keeps restarting, and I assume that means InnoDB is recovering, though I am not familiar enough with the process to know for sure.”

The error usually occurs when there is some issue with the MySQL data directory, the table, or table structure. Due to this error, users fail to access the tables. In this article, we’ll discuss the causes behind the “Incorrect information in file” error and the solutions to resolve it.

What Causes the MySQL Error 1033 (HY000): Incorrect Information in File?

Here are some common reasons that can cause this error:

• Corruption in MySQL database/tables.
• Index files (.MYI) of MyISAM tables are damaged.
• Lack of permissions to read/write the database files.
• Issues in the configuration file.

Solutions to Resolve the Incorrect Information in File Error in MySQL

Now, let’s discuss the troubleshooting methods to fix the “Incorrect information in file” error.

Method 1: Check the Configuration File

Incorrect configuration file settings can cause the MySQL error 1033 (HY000). So, make sure that the configuration file settings are properly configured. You can find the configuration (CNF) file using the below command:

$ mysql --help | grep /my.cnf

Now, open the file and check the configuration settings. Check whether the specified data directories path and settings related to InnoDB engine, like innodb_force_recovery, innodb_log_file_size3, etc. are correctly configured. Do the changes (if required) and then save the applied changes. When done, restart the MySQL Server.

Method 2: Check the Permissions

The error 1033 (HY000): Incorrect information in file can occur if you don’t have the required permissions to perform the actions. Ensure that you have appropriate permissions to read and write tables in the MySQL database. For this, you can run the SHOW GRANTS statement (see the example below) to check if you (the user) have the required roles and privileges.

SHOW GRANTS

    [FOR user_or_role

        [USING role [, role] ...]]

user_or_role: {

    user (see Section 8.2.4, “Specifying Account Names”)

  | role (see Section 8.2.5, “Specifying Role Names”.

}

If you don’t have the required permissions, then use the GRANT statement to assign them.

Method 3: Repair MySQL Tables

The error 1033 (HY000): Incorrect information in file’ error can also occur due to corruption in tables. If the tables are created in MyISAM engine, then use the REPAIR command with the use_frm option to repair them.

First, make sure you have the SELECT and INSERT privileges for the table you are trying to repair. Next, run the below command:

repair table tbl1 use_frm;

If this command fails to work, then you can try dropping and recreating the corrupt tables by using the below steps:

• First, start the MySQL Server instance.
• Backup the structure of table (tbl1.FRM), tbl1.MYD (table data), and indexes files.
• Stop the server.
• Drop the corrupted table using the below command:

DROP TABLE IF EXISTS table_name;

• Next, recreate the table using the CREATE TABLE statement as given below:

CREATE TABLE table_name (

    column1 datatype,

    column2 datatype,

    ...

);

• Restore the MYD and tbl1.MY1 files to the newly created table.
• Start the MySQL instance.

If you’re using the InnoDB storage engine, then you can restore the MySQL database using the mysqldump utility. Here’s how to do this:

• First, create an empty database to save the restored database. Here’s the command:

‘mysql > create db_name’

• Then, restore the database using the following command:

mysql -u root -p db_name < dump.sql

• It will restore all the objects of the database. You can check the restored InnoDB tables by using the below command:

‘mysql> use db_name;

mysql > show tables;’

Method 4: Use a Professional MySQL Database Repair Tool

An easy solution to repair corrupt tables in MySQL database is by using a professional MySQL database repair tool, such as Stellar Repair for MySQL. The tool can repair the corrupt MySQL database without file-size limitations. It is a powerful and user-friendly tool that can recover all the data, including tables, primary keys, and foreign keys, from the database with complete integrity and precision. It supports all MySQL Server versions.

Key Features of Stellar Repair for MySQL:

• Repairs MySQL database tables of both the storage engines – InnoDB and MyISAM
• Repairs MariaDB databases
• Resolves complex MySQL database corruption errors
• Offers preview of recoverable database objects before saving
• Supports selective recovery of database objects
• Compatible with both Windows and Linux operating systems 
• Saves repaired files in multiple formats – MySQL, SQL Script, MariaDB, HTML, CSV, and XLS

Conclusion

Above, we have explained the causes behind the ‘Incorrect Information in File’ error in MySQL and the methods to resolve the error. You can check the configuration file settings and the file permissions to resolve the error. If the error has occurred due to corruption in tables or database, then you can use a reliable MySQL database recovery software, like Stellar Repair for MySQL. It can help you to effortlessly repair corrupted/damaged tables in MySQL database with complete integrity.