Bug #79330 DROP TABLESPACE fails for missing general tablespace *.ibd file
Submitted: 18 Nov 2015 14:27 Modified: 9 Mar 2016 20:20
Reporter: Daniel Price Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.0 OS:Any
Assigned to: CPU Architecture:Any

[18 Nov 2015 14:27] Daniel Price
Description:
For a missing remote general tablespace .ibd file, you cannot drop the tablespace.

How to repeat:
(1) CREATE TABLESPACE `ts1` ADD DATAFILE '/home/dtprice/test/ts1.ibd' Engine=InnoDB;

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where SPACE_TYPE='General' \G
*************************** 1. row ***************************
         SPACE: 50
          NAME: ts1
          FLAG: 2048
    ROW_FORMAT: Any
     PAGE_SIZE: 16384
 ZIP_PAGE_SIZE: 0
    SPACE_TYPE: General
 FS_BLOCK_SIZE: 0
     FILE_SIZE: 18446744073709551615
ALLOCATED_SIZE: 2
1 row in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES where SPACE='50'\G
Empty set (0.00 sec)

(2) Manually remove the remote ts1.ibd file

(3) Restart

2015-11-18T13:33:55.233398Z 0 [Note] InnoDB: 5.8.0 started; log sequence number 3063339
2015-11-18T13:33:55.241029Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2015-11-18T13:33:55.241058Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2015-11-18T13:33:55.241073Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: '/home/dtprice/test/ts1.ibd' OS error: 71
2015-11-18T13:33:55.241112Z 0 [Warning] InnoDB: Ignoring tablespace `ts1` because it could not be opened.

(4) DROP TABLESPACE ts1;

mysql> DROP TABLESPACE ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1

Suggested fix:
Allow DROP TABLESPACE in the case of a missing remote general tablespace *.ibd file.
Fix as part of WL#8619 or maybe even as part of WL#7141 (Marko)
[15 Feb 2016 14:25] Marko Mäkelä
Posted by developer:
 
I can repeat the bug in 5.7.12 with the following test:

let $MYSQLD_DATADIR= `select @@datadir`;
create tablespace t add datafile 't.ibd';
--source include/shutdown_mysqld.inc
--remove_file $MYSQLD_DATADIR/t.ibd
--source include/start_mysqld.inc
drop tablespace t;

InnoDB will refuse to drop the tablespace:

mysqltest: At line 6: query 'drop tablespace t' failed: 1529: Failed to drop TABLESPACE t

Compared to the behavior with DROP TABLE and missing files, this is a regression.

In WL#7141, this bug will be fixed differently.

I removed the tags related to renaming data files, because this bug can be repeated without involving any *.isl files.
[8 Mar 2016 22:07] Kevin Lewis
Posted by developer:
 
Commit: 1235f245200b95b0a00fb65d680a4c5fdab81508 [1235f24]
Parents: 2f5cbd1d19
Author: Kevin Lewis kevin.lewis@oracle.com
Date: March 8, 2016 at 2:20:55 PM CST
Commit Date: March 8, 2016 at 3:43:26 PM CST
Labels: origin/mysql-5.7 mysql-5.7-22232892 mysql-5.7

Bug #22232892: DROP TABLESPACE FAILS FOR MISSING GENERAL TABLESPACE *.IBD FILE

DROP TABLESPACE needs to be consistent with DROP TABLE if the IBD file is missing.
It needs to ignore the fact that the IBD file is missing and delete the dictionary metadata.

Approved by Marko in RB#11984
[9 Mar 2016 20:20] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.13, 5.8.0 release, and here's the changelog entry:

DROP TABLESPACE returned an error if the remote general tablespace data
file was missing.
[18 Jun 2016 21:34] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0