Mysql – Trying to solve “outside tablespace bounds” MySQL error, but recovering table with thesqldump fails

MySQLrecovery

I am running MySQL on a Windows 2008 server. One of the tables (> 1 million records) seems to be corrupted. The mysql error log shows this:

InnoDB: Error: trying to access page number 197105 in space 89,
InnoDB: space name dbname/tablename,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.

Based on this error message, I checked my.cnf for the relevant lines:

innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"

This is exactly where the data resides. ibdata1 is about 208 MB in size, the tablename.ibd is about 4 GB in size. I've been using this server succesfully for many months.

After searching on this site and elsewhere, I tried to perform recovery as follows:

  • Set innodb_force_recovery=1 and then up to 6.

  • Next, dump the specific table with mysqldump. I am using this
    command: mysqldump dbname tablename --user=root --password=blahblah.
    During this dump, for recovery levels 1 through 3, mysql crashes ('goes away'). At 4, it managed to dump the first 23992 records, but then it said

    Lost connection to MySQL server during query when dumping table tablename at row: 23992.

    The log has some additional lines preceding what I showed above:

    InnoDB: Failed to find tablespace for table "dbname"."tablename"
    in the cache. Attempting to load the tablespace with space id 89.
    InnoDB: Error: trying to access page number 0 in space 89,
    InnoDB: space name dbname/tablename,
    InnoDB: which is outside the tablespace bounds.

  • It seems as soon as I touch or (select) query this table, either through php, python or phpMyAdmin, mysql crashes.

Does anybody have any other suggestions, possibly based on the tablespace bounds error above?

Thanks in advance!

Best Answer

Let's elaborate the error message

InnoDB: Error: trying to access page number 197105 in space 89,

InnoDB calles an .ibd file a "table space" or just "space" in this error message. So, there is a space 89 and one of its pages refers to a non-existing page number 197105. The error message doesn't say what table corresponds to the space 89. I think some MySQL version tell that, but it's possible to find from the InnoDB dictionary.

mysql> select NAME from information_schema.INNODB_SYS_TABLESPACES WHERE SPACE=4304;
+----------------------+
| NAME                 |
+----------------------+
| sakila/film_category |
+----------------------+
1 row in set (0.01 sec)

Now, as we know what table is corrupt the question is how to fix it. InnoDB doesn't heal tablespaces (like MyISAM does with REPAIR TABLE), the only way is to drop the table space(=table) and re-create it.

In this particular case it's not necessary to start MySQL in innodb_force_recovery mode. Just start it normally and drop the table. But first, you probably need to save the data in this table.

There are two ways. First, you can choose ranges of non-corrupt primary key values and dump them into another table or a text dump. I had recovery cases like this one and wrote a script to do that. The script iterates over the PK ranges and dumps as many records as possible into another (MyISAM) table.

Second way is requires more time and skills. You will need a tool (https://github.com/twindb/undrop-for-innodb) and a blog post https://twindb.com/recover-corrupt-mysql-database/ with steps.

Then drop the corrupted table, create an empty one and load the recovered dump.

Related Question