From ChatGPT - without warranty:
That specific error usually means InnoDB’s internal stats tables in the
mysql system database are damaged. You
must be careful here, because a wrong command can cause
data loss. I’ll outline the usual options from least to most destructive, and you can choose what fits your situation/backups/risk tolerance.
0. Before you touch anything
- Back up:
- Dump all non-system databases:
mysqldump -u root -p --all-databases > all-dbs-backup.sql
or at least:
mysqldump -u root -p yourdb > yourdb.sql
- Note your MySQL version
Run:
SELECT VERSION();
The commands below are for modern MySQL (5.6+ / 5.7 / 8.0) and MariaDB with InnoDB. Some details differ but the general approach is similar.
1. Try a simple check & repair (if server is running)
Open the MySQL client as root:
mysql -u root -p
Then run:
USE mysql;
CHECK TABLE innodb_table_stats;
CHECK TABLE innodb_index_stats;
- If CHECK TABLE reports it can be repaired (rare), run:
REPAIR TABLE innodb_table_stats;
REPAIR TABLE innodb_index_stats;
If that works and the errors stop appearing in the error log, you’re done.
2. Drop and recreate the InnoDB stats tables (common fix)
If they are badly corrupted, you can
drop and recreate them. These tables only store statistics; they do
not hold your actual user data, but you will lose existing index statistics and MySQL will regenerate them.
2.1. Get the official CREATE TABLE definitions
Use SHOW CREATE TABLE if they’re still readable:
SHOW CREATE TABLE mysql.innodb_table_stats\G
SHOW CREATE TABLE mysql.innodb_index_stats\G
If those fail or show garbage, you’ll need the correct schema for your MySQL version (from documentation or another clean server with the same version). Here is a commonly used definition for MySQL 5.7/8.0–style (check against your version; adjust if needed):
DROP TABLE IF EXISTS mysql.innodb_table_stats;
CREATE TABLE mysql.innodb_table_stats (
database_name varchar(64) NOT NULL,
table_name varchar(199) NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows bigint(20) unsigned NOT NULL,
clustered_index_size bigint(20) unsigned NOT NULL,
sum_of_other_index_sizes bigint(20) unsigned NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
DROP TABLE IF EXISTS mysql.innodb_index_stats;
CREATE TABLE mysql.innodb_index_stats (
database_name varchar(64) NOT NULL,
table_name varchar(199) NOT NULL,
index_name varchar(64) NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
stat_name varchar(64) NOT NULL,
stat_value bigint(20) signed NOT NULL,
sample_size bigint(20) unsigned DEFAULT NULL,
stat_description varchar(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
⚠️ Important: If your MySQL/MariaDB version has slightly different columns (e.g., collations, lengths, additional fields), you must adjust these definitions. Using the wrong schema can cause other problems. If in doubt, spin up a fresh instance with the same version and copy the definitions from there.
Once recreated, restart MySQL:
sudo systemctl restart mysql
# or
sudo service mysql restart
Check the error log for fresh messages about innodb_table_stats.
3. If server does not start because of the corruption
If MySQL refuses to start due to this error, you can temporarily disable InnoDB stats loading and fix from there.
3.1. Start with InnoDB recovery options
Edit your my.cnf (or mysqld.cnf), typically in /etc/mysql/ or /etc/my.cnf, and add under [mysqld]:
[mysqld]
innodb_force_recovery = 1
Try values from 1 up toward 6
only as needed; the higher you go, the more “read-only / dangerous” InnoDB becomes. Start with 1 and increase only if the server still won’t start.
Restart MySQL and, if it comes up:
- Connect:
mysql -u root -p
- Drop and recreate the stats tables as in section 2.
- Remove the innodb_force_recovery line from my.cnf.
- Restart MySQL normally.
4. Use mysql_upgrade / mysqlcheck (if upgrading or after a crash)
If this appeared after upgrading MySQL, or you suspect system tables are inconsistent, run:
mysql_upgrade -u root -p
or, on some systems / versions:
mysqlcheck -u root -p --all-databases --check-upgrade
mysqlcheck -u root -p --all-databases --repair
Then restart MySQL and re-check logs.
5. Worst case: rebuild the mysql system database
If
multiple system tables under mysql are corrupted and you can’t fix them individually, the nuclear option is:
- Dump all user databases (not the mysql database) if you still can.
- Stop MySQL.
- Move/rename the data directory for mysql system database and re-initialize system tables (mysqld --initialize or distro-specific scripts).
- Start MySQL with a fresh mysql database.
- Recreate users/privileges and import your dumped data.
This is a last resort and requires careful reading of the manual for your
exact version.
6. After fixing: rebuild statistics
Once the tables are recreated and MySQL is running normally:
ANALYZE TABLE yourdb.yourtable;
Or, for many tables, script ANALYZE TABLE over all of them. This repopulates innodb_table_stats and innodb_index_stats with fresh data.
Facebook
Twitter
