Error
Error Code:
1805
MariaDB Error 1805: Table Column Count Mismatch
Description
This error indicates a severe inconsistency in a database table's metadata, specifically concerning its column structure. It typically occurs when the actual number of columns stored on disk does not match the expected number defined in the table's schema, often pointing to data corruption.
Error Message
Column count of %s.%s is wrong. Expected %d, found %d. The table is probably corrupted
Known Causes
4 known causesCorrupted Table Data Files
Physical damage or inconsistencies within the underlying data files (e.g., .frm, .ibd) can lead to a misinterpretation of the table's column structure.
Incomplete Schema Alteration
An interrupted or failed ALTER TABLE operation, especially one that modifies column structure, can leave the table in an inconsistent state.
Underlying Hardware or OS Issues
Faulty disk drives, memory errors, or operating system crashes can lead to data being written incorrectly or becoming unreadable.
Accidental File Manipulation
Directly modifying, moving, or replacing database files outside of MariaDB's control can introduce inconsistencies between the schema and data.
Solutions
3 solutions available1. Verify and Recreate Table Structure medium
Recreate the table with the correct column count after backing up data.
1
Identify the database and table names from the error message. For example, if the error is 'Column count of my_db.my_table is wrong. Expected 5, found 4.', then `my_db` is the database and `my_table` is the table.
2
Backup all data from the affected table. If the table is inaccessible, try using `mysqldump` with options to ignore errors or dump individual rows if possible. If not, a full database dump might be necessary.
mysqldump -u your_user -p your_database your_table > table_backup.sql
# If the table is completely inaccessible, try a full database dump:
mysqldump -u your_user -p your_database > database_backup.sql3
Get the correct `CREATE TABLE` statement for the table. You can often find this in your application's schema definition files or by examining other identical tables in your database.
4
Drop the corrupted table.
DROP TABLE your_database.your_table;5
Recreate the table using the correct `CREATE TABLE` statement.
CREATE TABLE your_database.your_table (
column1 datatype,
column2 datatype,
...
columnN datatype
);6
Restore the data from your backup into the newly created table.
mysql -u your_user -p your_database < table_backup.sql2. Repair Table with `REPAIR TABLE` easy
Attempt to repair the table using MariaDB's built-in repair utility.
1
Connect to your MariaDB server using a client like `mysql`.
mysql -u your_user -p2
Select the database containing the corrupted table.
USE your_database;3
Run the `REPAIR TABLE` command. This command attempts to fix inconsistencies in table structures and data. It's generally safe to run, but it's always wise to have a backup.
REPAIR TABLE your_table;4
Check the output of the `REPAIR TABLE` command for any reported errors. If the repair is successful, you should be able to access the table normally.
3. Check Table Format and Storage Engine medium
Ensure the table's format and storage engine are compatible and not causing issues.
1
Connect to your MariaDB server.
mysql -u your_user -p2
Select the database.
USE your_database;3
Show the table structure and storage engine. Pay attention to the `ENGINE` and `FORMAT` (if applicable for InnoDB).
SHOW CREATE TABLE your_table;4
If the table uses an older or problematic format (e.g., `MyISAM` on some older versions where it might have issues), consider converting it to a more robust engine like InnoDB. Note that this requires data backup and restoration.
-- Example for converting MyISAM to InnoDB:
ALTER TABLE your_table ENGINE=InnoDB;5
If you suspect the storage engine itself is corrupted or misconfigured, try switching to a different engine (after backup) and then back to the desired engine.