Jod35 ⚡️

What is MariaDB/MySQL Error 1877 ER_TABLE_CORRUPT?


mysql

The ER_Table corrupt error 1877 can occur in MySQL or MariaDB, if the storage engine of the server fails to find that table, its pages, and metadata. You can face this error when trying to open the MySQL tables after a sudden system crash, if they are not in a consistent state. It occurs each time you attempt to run a query related to the referenced table, which is missing, corrupt, or contains bad data.

Some MySQL users came across this error while running the mysqldump utility or using the SELECT command. Commonly, it is seen in MySQL Server 5.5 or later versions. In this article, we will discuss the causes of the error 1877 and the solutions to fix it.

Reasons for MySQL Error 1877 ER_TABLE_CORRUPT

This error occurs when the server fails to read the InnoDB tables that it needs to open or access. It can occur due to various reasons, like: Your system’s disk, where the MySQL data folder is saved, is out of space

  • Permission issues
  • Incorrect MySQL Configuration File settings
  • Corruption in InnoDB tables, their .frm and .ibd files

Methods to Resolve the MySQL Error 1877

To resolve this error, first follow the basic workarounds mentioned below.

Workaround 1 - Check Disk Space

The MySQL error 1877 can take place when you try to open or modify a large-sized table using T-SQL queries. It often occurs if the disk on which the database is stored is out of storage space. So, check if there is enough space on the disk.

On a Windows system,

  • Go to the drive where you have saved the database, right-click on it, and then select Properties.
  • In the Properties window, check the Used and Free Space.

If the storage space is low, then free up some space by deleting unnecessary files, folders, and other data. Alternatively, you can move the files to another drive or an external drive.

You can use Storage Sense, Window’s built-in Disk Clean Up utility, or any other tool to free up drive space on Windows system.

To check disk space on a Linux system, you can use the dl command (df –h) as given below:

myServer# df -h

If the disk is full, then you can delete large files and uninstall unnecessary applications. You can also run the below command to clear the temp files:

$ sudo apt-get autoremove

Note: The above command removes unnecessary packages that were downloaded automatically with applications. So, review the list of packages you are trying to remove before confirming the removal request.

Workaround 2 - Check the Permissions

The MySQL InnoDB table error 1877 can also occur if you don’t have the required permissions to perform the action on tables. Ensure that you have appropriate permissions to update data in the tables. For this, you can run the SHOW GRANTS statement to know if you have the required roles and privileges.

Here’s how to use this statement:

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 desired permissions, then use the GRANT statement to assign them.

If the above troubleshooting methods fails, then you can check the InnoDB tables for corruption. Use Error log files to verify the information regarding certain InnoDB table errors.

To find the error log, run the following query:

SHOW VARIABLES LIKE 'log_error';

Alternatively, you can run the CHECK TABLE command in MySQL to check the database tables for corruption. This command checks the table views and their indexes. If it finds any issues, it displays an error with the table name. Before running this command, ensure you have all the necessary permissions, such as SELECT, ALTER, etc. Here’s how to run the CHECK TABLE command:

CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

If you found that the InnoDB tables are corrupted, then you can rebuild the tables using the below methods:

  1. Use ALTER TABLE Command You can use the ALTER TABLE command to rebuild the InnoDB tables. This command recreates the corrupt tables and indexes which helps in resolving minor corruption issues, including dictionary issues in InnoDB tables.

  2. Restore the Dump File using mysqldump Utility If the above command doesn’t work, then you can restore the MySQL database using the mysqldump utility. Before initiating the restore process, first verify backup in MySQL, using the validate command.

  3. Use Dump and Reload Method If the backup file is not readable then you can rebuild InnoDB tables by dumping and reloading them. Steps to perform this procedure are: First, you need to enable the Innodb_force_recovery option from the configuration file. To do so, go to the configuration file and find its [mysqld] section and then insert the below statements to enable the innodb_force_recovery.

[mysqld]
  Innodb_force_recovery=1
  service mysql restart

The default value of innodb_force_recovery is 0. However, you can easily change its value from 1- 4. If you change its value to ‘1’, it can help you start the InnoDB engine, so that you can dump the tables. Dumping tables with “innodb_force_recovery value” of 4 or higher can lead to data loss. So, it is recommended that a database backup be created first before proceeding.

  • Next, dump the table data by using the mysqldump command as given below:
$ mysqldump -u user -p database_name table_name > single_dbtable_dump.sql
  • Then, export all the databases to the dump.sql file by executing the below command:
$ mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql
  • Now, restart the MySQL Server. Use the DROP DATABASE command to drop the database and DROP TABLE command to drop the table from the database. The commands are:
DROP DATABASE database_name;
DROP TABLE table_name;
  • If it fails to drop the database, then run the below commands to delete the database manually:
$ cd /var/lib/mysql
$ rm -rf db_name
  • Next, disable the InnoDB recovery mode. Just add comment # as in the below example:
#innodb_force_recovery=...
  • Now, save changes to the configuration file and then restart your MySQL Server.

An Alternative Solution - Repair MySQL Tables using a Professional MySQL Repair Tool

Opting for a professional MySQL repair tool, like Stellar Repair for MySQL, is one of the easiest and quickest methods to repair InnoDB tables. It can easily repair multiple corrupt InnoDB tables at once or the entire database with no data loss. It can repair severely corrupted InnoDB and MyISAM tables with absolute precision, created in any of the version of MySQL Server. It can recover all the objects, including tables, foreign keys, indexes and primary keys, in less time. It also supports selective recovery of database objects and allows you to save the repaired files in multiple formats.

To Conclude

Above, we have learned the methods to resolve the MySQL error 1877. You can use the OPTIMIZE command on the tables or delete large files and uninstall unnecessary applications to free up the disk space. If corruption in InnoDB tables is the reason behind the error, then use the Innodb_force_recovery or ALTER TABLE command to repair the MySQL database. To repair the database with no data loss, you can use a professional MySQL repair tool, such as Stellar Repair for MySQL.