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
