Mysql – Won’t start: InnoDB: Corrupted page … Tablespace was not found

crashinnodbMySQLmysql-5.7

After a server crash (Ubuntu 16.04), when I try to start Mysql (Mysql 5.7) using innodb_force_recovery=0 it doesn't start and error.log shows:

InnoDB: Checksum mismatch in datafile: ./panel_financiero_v2/kpis_analytics.ibd, Space ID:93, Flags: 33. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
InnoDB: Corrupted page [page id: space=93, page number=0] of datafile './panel_financiero_v2/kpis_analytics.ibd' could not be found in the doublewrite buffer.
InnoDB: Tablespace 93 was not found at ./panel_financiero_v2/kpis_analytics.ibd.
InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
InnoDB: Cannot continue operation.

I just can start Mysql using innodb_force_recovery=6 (I can't start Mysql using innodb_force_recovery=1 as the error message advises).
The .ibd and .frm files of the troubled table are in the right directory (no empty files); I even tried (just in case, with no real hope) to delete these two files (moving them to another directory) but it doesn't work neither.

Any way to repair or recover the table (bearing in mind that innodb_force_recovery=6 starts Mysql in read-only mode and I even can't create a new table)? Any way, at least, to start mysql normally (with innodb_force_recovery=0) even losing the information of that troubled table?

Best Answer

https://dev.mysql.com/doc/refman/5.7/en/repair-table.html says:

REPAIR TABLE works for MyISAM, ARCHIVE, and CSV tables.

In other words, REPAIR TABLE does nothing for InnoDB. InnoDB has its own automatic crash recovery that it runs on startup.

But InnoDB crash recovery only works for lost changes that were in progress at the time of the crash. It uses the redo log and the doublewrite buffer to reconstruct lost dirty pages. This process cannot reconstruct data that was corrupted at rest.

The error message actually tells you what to do:

InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.

1 (SRV_FORCE_IGNORE_CORRUPT) Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

Start the server with innodb_force_recovery=1 after moving your tablespace files back where they belong.

Then you can dump the table using mysqldump, and it should read the pages it can read, skipping corrupted pages. Then you can make a new table to import from the dump:

CREATE TABLE mytable_new LIKE mytable;
RENAME TABLE mytable TO mytable_bad, mytable_new TO mytable;

Then re-import your dumped data.

I assume you do not need help with dumping a single table and importing the dump file.

Re your comment:

It seems like you have more extensive corruption. I suggest the following steps:

Start the MySQL server with innodb_force_recovery=6
Use mysqldump to dump all of the data.
Shut down MySQL server.
Back up the contents of your datadir just in case you want to get professional help to see if they can recover more of the data. Try https://twindb.com, they are the best experts in MySQL database recovery.
Initialize a new datadir (I would use a new disk device, and take the old one out of service because it now has unknown damage).
Restore the data dump you created with mysqldump.

Related Solutions

Mysql – Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Unfortunately, Yes you have to do every database. I have a step-by-step process I wrote up back on Oct 29, 2010 in StackOverflow as well as thorough explanation why there is nothing else you can do.

You also asked

Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database ?

You cannot reclaim half. It is an all-or-nothing decision. It is good to keep in mind what lives in ibdata1 when innodb_file_per_table is disabled :

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollback Segments
- Undo Space
Table Metadata (Data Dictionary)
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary indexes)
See the Pictorial Representation of ibdata1

Still, if you want to know which InnoDB tables are in ibdata1 and which are in their own tablespace file (.ibd), here is what you do:

STEP 01 : Run this query in the MySQL client

SELECT COUNT(1) InnoDBTableCount
FROM information_schema.tables
WHERE engine='InnoDB';

STEP 02 : Collect the number of .ibd files in the OS

ls -lR | grep -c ".ibd$"

STEP 03 : Subtract those values in the OS

Here is the shell script to compute these things

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.tables WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}

If you have partitioned tables, then do this:

cd /var/lib/mysql
SQLSTMT="SELECT COUNT(1) FROM information_schema.partitions WHERE engine='InnoDB'"
IB_TOT=`mysql -uroot -p... -ANe"${SQLSTMT}"`
IB_IBD=`ls -lR | grep -c ".ibd$"`
(( IB_SYS = IB_TOT - IB_IBD ))
echo ${IB_SYS}

Here is what you get:

IB_TOT : Number of InnoDB Tables in the MySQL Instance
IB_IBD : Number of InnoDB Tables Residing Outside ibdata1 as .ibd Files
IB_SYS : Number of InnoDB Tables Residing Inside ibdata1

Mysql – How to fix `The table ‘/tmp/thesql/#sql_xxxxx’ is full`

The relevant configuration is this:

ibtmp1:12M:autoextend:max:512M

You are restricting your temporary tables (and similarly your main innodb tablespace via innodb_data_file_path) to 512M total. This includes implicit (disk) temporary tables that might be created by various ORDER BY / GROUP BY / DISTINCT operations.

MySQL reports this failure relative to your configured tmpdir, although it's actually using the ibtmp1 file.

Increase or remove the :max:<size> limit to resolve this.

Alternatively you might find a way to avoid these sort of temporary tables by inspecting the offending queries - possibly via adding an index, changing data types or changing the query itself among other strategies.

Best Answer

Related Solutions

Mysql – Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace

Mysql – How to fix `The table ‘/tmp/thesql/#sql_xxxxx’ is full`

Related Question