Bug #31726 Can't create tablespace without data file but can drop last datafile
Submitted: 20 Oct 2007 13:38 Modified: 20 Oct 2007 16:39
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S3 (Non-critical)
Version:mysql-5.1 OS:Linux
Assigned to: CPU Architecture:Any
Tags: 5.1.22, usability
Triage: Triaged: D3 (Medium)

[20 Oct 2007 13:38] Hartmut Holzgraefe
Description:
When creating a tablespace one needs to specify exactly 
one data file right away (the DATAFILE part of the CREATE
TABLESPACE command is  not optional).

Additional data files need to be added using ALTER
TABLESPACE ADD.

Even though a tablespace can't be created without a
data file the data file can be droped right after
tablespace creation, and even needs to be before
one can drop the tablespace, too.

See also http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html
and http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html

How to repeat:
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' engine=ndb;
Query OK, 0 rows affected (31.67 sec)

mysql> CREATE TABLESPACE ts_1 USE LOGFILE GROUP lg_1 ENGINE NDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE LOGFILE GROUP lg_1 ENGINE NDB' at line 1

mysql> CREATE TABLESPACE ts_1 ADD DATAFILE 'ts_1.dat' USE LOGFILE GROUP lg_1 ENGINE NDB;
Query OK, 0 rows affected (33.27 sec)

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)

mysql> alter tablespace ts_1 drop datafile 'ts_1.dat' engine=ndb;
Query OK, 0 rows affected (0.64 sec)

mysql> DROP TABLESPACE ts_1 ENGINE=NDB;
Query OK, 0 rows affected (0.62 sec)

Suggested fix:
IMHO it would be more consistant to allow the creation of a tablespace without a data file as

- it is possible to end up with a table space without data files anyway

- when creating a multi-file table space from a program or script it
  would be easier to generate one CREATE TABLESPACE statement and 
  then one ALTER TABLESPACE ADD DATAFILE command for each data file
  instead of having to merge the first data file into the CREATE
  statement and then create ALTER commands for the remaining files