Partition Errors Even Though The Table Does Not Exist Anymore

(Doc ID 2158791.1)

Last updated on MARCH 08, 2017

Applies to:

MySQL Server - Version 5.6 and later
Information in this document applies to any platform.

Symptoms

On : 5.6 version, DDL SQL Statements

When attempting to create space, an accidental deleting of certain partitions of an InnoDB,
the following error occurs.

ERROR
-----------------------
2016-06-02 10:10:23 7f2d61d1f720 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2016-06-02 10:10:23 59302 [ERROR] InnoDB: A link file was found named './P20MONDBA/CAL_PAYMENTAUTHORIZATIONSERV#P#P20160428.isl' but the linked tablespace '/spotlightdb01/mysql/mysqldata/P20MONDBA/CAL_PAYMENTAUTHORIZATIONSERV#P#P20160428.ibd' could not be opened.
2016-06-02 10:10:23 7f2d61d1f720 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: Error: could not open single-table tablespace file ./P20MONDBA/CAL_PAYMENTAUTHORIZATIONSERV#P#P20160428.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.
160602 10:10:23 mysqld_safe mysqld from pid file /x/opt/mysql/mysqldata/spotlight.pid ended


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Delete some of the partitions from an innodb table
2. Restart server
3. DROP TABLE

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot delete the table as the .frm file is no longer available and so tablespaces cannot be removed.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms