Error
Error Code:
1805
MySQL Error 1805: Column Count Mismatch, Table Corrupted
Description
MySQL Error 1805 indicates a severe inconsistency where the database expects a different number of columns for a table than it finds in the actual table definition. This error frequently points to underlying data corruption within the table's metadata or data files. It typically occurs when MySQL attempts to access, query, or perform operations on a table whose internal structure is inconsistent or damaged.
Error Message
Column count of %s.%s is wrong. Expected %d, found %d. The table is probably corrupted
Known Causes
4 known causesAbnormal Server Termination
An unexpected shutdown of the MySQL server (e.g., power failure, system crash, or kill -9) can prevent data files from being properly flushed or synchronized, leading to metadata inconsistencies.
Underlying Hardware Failure
Faulty disk drives, failing RAID controllers, or other storage hardware issues can introduce silent data corruption into table files, including their structural definitions.
Filesystem or OS Bugs
Bugs within the operating system or the underlying filesystem can sometimes lead to data corruption, affecting the integrity of database table definitions and data.
Incorrect File Handling
Manually moving, copying, or directly editing MySQL data files outside of the server's control can corrupt them and cause metadata mismatches that trigger this error.
Solutions
4 solutions available1. Recreate Table from Dump medium
The most reliable method is to recreate the table using a recent dump.
1
Identify the database and table causing the error. The error message usually provides this information (e.g., `%s.%s`). Let's assume it's `database_name.table_name`.
2
Backup the affected database or at least the table. Even if corrupted, a backup is crucial. If you can't dump it normally, try a physical backup of the data files if possible (see advanced solution).
mysqldump -u your_user -p database_name table_name > /path/to/backup/table_backup.sql3
Drop the corrupted table. This might fail if the corruption is severe, but it's the next logical step.
DROP TABLE database_name.table_name;4
Recreate the table structure. You can often get this from `SHOW CREATE TABLE` before it was corrupted, or from your version control system if you manage schema changes there.
SHOW CREATE TABLE database_name.table_name;5
Restore the data from the backup. If the backup file only contains the table, you can restore just that table.
mysql -u your_user -p database_name < /path/to/backup/table_backup.sql2. Repair Table with `REPAIR TABLE` easy
Attempt to repair the table directly using MySQL's built-in repair utility.
1
Connect to your MySQL server using a client like `mysql`.
mysql -u your_user -p2
Select the database containing the corrupted table.
USE database_name;3
Run the `REPAIR TABLE` command. For MyISAM tables, `REPAIR TABLE table_name` is the primary command. For InnoDB, it's often better to let it recover automatically or use other methods. However, if the error explicitly states corruption, this is a good first attempt.
REPAIR TABLE table_name;4
Check the output of the `REPAIR TABLE` command. It will indicate if the repair was successful or if further action is needed.
5
If `REPAIR TABLE` fails or doesn't resolve the issue, consider `ANALYZE TABLE` or `OPTIMIZE TABLE` as they can sometimes help re-align table structures.
ANALYZE TABLE table_name;
OPTIMIZE TABLE table_name;3. Rebuild Table using `ALTER TABLE` medium
Rebuild the table by altering its structure, which can sometimes fix corruption.
1
Backup the affected table to ensure you have a recovery point.
mysqldump -u your_user -p database_name table_name > /path/to/backup/table_backup_before_alter.sql2
Attempt to rebuild the table by altering it to the same engine or a different one, and then back if desired. This forces MySQL to rewrite the table data.
ALTER TABLE database_name.table_name ENGINE=InnoDB;3
If you want to ensure it's the original engine (e.g., MyISAM), you can specify that. If the table was MyISAM, this command will rebuild it as MyISAM.
ALTER TABLE database_name.table_name ENGINE=MyISAM;4
Verify the table structure and data after the `ALTER TABLE` operation. Run a `SELECT COUNT(*)` and check a few rows.
4. Physical Data File Recovery (Advanced) advanced
If all else fails, attempt recovery from physical data files.
1
Stop the MySQL server to prevent further corruption or writes.
sudo systemctl stop mysql2
Locate the MySQL data directory. This is typically `/var/lib/mysql/` on Linux systems. You can find it in your `my.cnf` or `my.ini` configuration file under the `datadir` directive.
3
Navigate to the directory of the specific database within the data directory.
cd /var/lib/mysql/database_name4
Identify the `.frm`, `.MYD`, and `.MYI` (for MyISAM) or `.ibd` (for InnoDB) files for the corrupted table. The error message should provide the table name.
5
Copy these files to a safe, separate location for recovery attempts. Do NOT modify the original files directly.
cp table_name.* /path/to/recovery/location/6
Attempt to use MySQL's `mysqlfrm` utility to extract the table definition from the `.frm` file. This can help recreate the table structure if you've lost it.
mysqlfrm --server=user:pass@host --diagnostic /path/to/recovery/location/table_name.frm > table_schema.sql7
For InnoDB tables, if you have a full physical backup of the entire data directory, you might be able to restore that backup and then use `innodb_force_recovery` options in `my.cnf` to bring the server up and dump the data. This is a complex process and requires careful planning.
8
Once you have a potential recovery strategy (e.g., recreated table from `mysqlfrm` output and trying to import data from salvaged `.MYD` or `.ibd` files, or using `innodb_force_recovery`), restart the MySQL server and attempt to import/restore.