Bug #31727 Ambiguous error message on DROP TABLESPACE

• Submitted: 20 Oct 2007 16:21
• Modified: 20 Oct 2007 16:39
• Reporter: Hartmut Holzgraefe
• Status: Verified
• Category: MySQL Cluster: Disk Data
• Severity: S3 (Non-critical)
• Version: mysql-5.1
• OS: Linux
• CPU Architecture: Any
• Tags: 5.1.22, usability

[20 Oct 2007 16:21] Hartmut Holzgraefe

Description:
When trying to drop a tablespace the same error message and the same warnings are returned in the following situations:

- table space not used but has data file(s)
- table space used by a table and has data file(s)
- table space used by a table but no data file

In any of these caes the error and warnings are:

mysql> DROP TABLESPACE ts_1 ENGINE=NDB;
ERROR 1527 (HY000): Failed to drop TABLESPACE
mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Error | 1296 | Got error 768 'Cant drop filegroup, filegroup is used' from NDB | 
| Error | 1527 | Failed to drop TABLESPACE                                       | 
+-------+------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

How to repeat:
CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' ENGINE=NDB;

CREATE TABLESPACE ts_1 ADD DATAFILE 'ts_1.dat' USE LOGFILE GROUP lg_1 ENGINE NDB;

DROP TABLESPACE ts_1 ENGINE=NDB;
SHOW WARNINGS;

CREATE TABLE t1 (i INT, KEY(i)) TABLESPACE ts_1 STORAGE DISK ENGINE=NDB;

DROP TABLESPACE ts_1 ENGINE=NDB;
SHOW WARNINGS;

ALTER TABLESPACE ts_1 DROP DATAFILE 'ts_1.dat' ENGINE=NDB;

DROP TABLESPACE ts_1 ENGINE=NDB;
SHOW WARNINGS;

DROP TABLE t1;

DROP TABLESPACE ts_1 ENGINE=NDB;

Suggested fix:
Report different errors for "there are still tables in the table space" and "there are still files attached to the table space" 

PS: i don't realy understand why dropping a tablespace can't drop 
    the data files with it right away if there are no tables using 
    that table space. 

    having to look up the files belonging to the table space with
  
       SELECT DISTINCT(FILE_NAME) 
         FROM INFORMATION_SCHEMA.FILES 
        WHERE FILE_TYPE='DATAFILE' 
          AND TABLESPACE_NAME='ts_1';

    first is pretty inconvenient IMHO, so i'd prefer a ON DROP
    CASCADE behavior to the current ON DROP RESTRICT style when
    it comes to tablespace data files