MySQL Forums
Forum List  »  InnoDB

How to recover a table with a corrupted partition?
Posted by: harry sheng
Date: January 07, 2020 11:57AM

Environment: Redhat Linux 2.6.32-754.12.1.el6.x86_64
Server version: 5.7.20-log MySQL Community Server (GPL)

I have the following error in mysql-error.log:

2020-01-07T17:38:34.065042Z 0 [Note] InnoDB: Last MySQL binlog file position 0 201350848, file name primary_bin_log.002450
2020-01-07T17:38:35.855483Z 0 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace harry/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17 at filepath: ./harry/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17.ibd uses space ID: 186487. Cannot open filepath: ./rogers/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17.ibd which uses the same space ID.
2020-01-07T17:38:35.855528Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-01-07T17:38:35.855538Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2020-01-07T17:38:35.855562Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2020-01-07T17:38:35.855574Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `rogers/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17`. Please refer to for how to resolve the issue.
2020-01-07T17:38:35.855593Z 0 [Warning] InnoDB: Ignoring tablespace `rogers/serviceingressoctetslogrecordhourly#P#Daily_2019_12_17` because it could not be opened.

I tried restart MySQL with innodb_force_recovery from 1 to 3, MySQL started fine, but I cannot fix the problem with the table.

mysql> show tables like 'serviceingressoctetslogrecordhourly';
| Tables_in_rogers (serviceingressoctetslogrecordhourly) |
| serviceingressoctetslogrecordhourly |
1 row in set (0.02 sec)

mysql> desc serviceingressoctetslogrecordhourly;
ERROR 1030 (HY000): Got error 122 from storage engine
mysql> drop table serviceingressoctetslogrecordhourly;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Never mind the data, how do I get rid of the table from the database all together?

Options: ReplyQuote

Written By
How to recover a table with a corrupted partition?
January 07, 2020 11:57AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.