Description:
We create a tablespace and create a disk table and didn't insert data into the table. Then we *can* drop data file in the tablespace.
If we insert data into disk table then delete all data in that table(make table empty), we *can't* drop any data file in the tablespace.
Can we drop a data file if an empty table using it?
How to repeat:
1: data file can be dropped after creating an empty disk table.
mysql> CREATE LOGFILE GROUP lg1
-> ADD UNDOFILE 'undofile.dat'
-> INITIAL_SIZE 16M
-> UNDO_BUFFER_SIZE = 1M
-> ENGINE NDB;
mysql> create tablespace ts2
-> add datafile 'datafile2_1.dat'
-> use logfile group lg1
-> initial_size 12M
-> engine ndb;
mysql> CREATE TABLE City (
-> ID int(11) NOT NULL AUTO_INCREMENT,
-> Name char(35) NOT NULL,
-> CountryCode char(3) NOT NULL,
-> District char(20) NOT NULL,
-> Population int(11) NOT NULL,
-> PRIMARY KEY (ID)
-> ) ENGINE=ndbcluster
-> tablespace ts2
-> storage disk;
mysql> alter tablespace ts2
-> drop datafile 'datafile2_1.dat'
-> engine ndb;
Query OK, 0 rows affected (0.42 sec)
mysql> drop tablespace ts2
-> engine ndb;
ERROR 1526 (HY000): Failed to drop TABLESPACE
mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------+
| Error | 1296 | Got error 768 'Cant drop filegroup, filegroup isused' from NDB |
| Error | 1526 | Failed to drop TABLESPACE |
+-------+------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
2: but we can't drop data file after we insert some data into the table. Even if we delete all the data in the table, we still can't drop that data file.
mysql> CREATE LOGFILE GROUP lg1
-> ADD UNDOFILE 'undofile.dat'
-> INITIAL_SIZE 16M
-> UNDO_BUFFER_SIZE = 1M
-> ENGINE NDB;
mysql> create tablespace ts2
-> add datafile 'datafile2_1.dat'
-> use logfile group lg1
-> initial_size 12M
-> engine ndb;
mysql> CREATE TABLE City (
-> ID int(11) NOT NULL AUTO_INCREMENT,
-> Name char(35) NOT NULL,
-> CountryCode char(3) NOT NULL,
-> District char(20) NOT NULL,
-> Population int(11) NOT NULL,
-> PRIMARY KEY (ID)
-> ) ENGINE=ndbcluster
-> tablespace ts2
-> storage disk;
mysql> insert into City value(1,"222","333","444",5);
Query OK, 1 row affected (0.01 sec)
mysql> delete from City;
mysql> alter tablespace ts2
-> drop datafile 'datafile2_1.dat'
-> engine ndb;
ERROR 1530 (HY000): Failed to alter: DROP DATAFILE
mysql> show warnings;
+-------+------+-------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------+
| Error | 1296 | Got error 770 'Cant drop file, file is used' from NDB |
| Error | 1530 | Failed to alter: DROP DATAFILE |
+-------+------+-------------------------------------------------------+
Suggested fix:
I think the two drop statement should return the same result.
Description: We create a tablespace and create a disk table and didn't insert data into the table. Then we *can* drop data file in the tablespace. If we insert data into disk table then delete all data in that table(make table empty), we *can't* drop any data file in the tablespace. Can we drop a data file if an empty table using it? How to repeat: 1: data file can be dropped after creating an empty disk table. mysql> CREATE LOGFILE GROUP lg1 -> ADD UNDOFILE 'undofile.dat' -> INITIAL_SIZE 16M -> UNDO_BUFFER_SIZE = 1M -> ENGINE NDB; mysql> create tablespace ts2 -> add datafile 'datafile2_1.dat' -> use logfile group lg1 -> initial_size 12M -> engine ndb; mysql> CREATE TABLE City ( -> ID int(11) NOT NULL AUTO_INCREMENT, -> Name char(35) NOT NULL, -> CountryCode char(3) NOT NULL, -> District char(20) NOT NULL, -> Population int(11) NOT NULL, -> PRIMARY KEY (ID) -> ) ENGINE=ndbcluster -> tablespace ts2 -> storage disk; mysql> alter tablespace ts2 -> drop datafile 'datafile2_1.dat' -> engine ndb; Query OK, 0 rows affected (0.42 sec) mysql> drop tablespace ts2 -> engine ndb; ERROR 1526 (HY000): Failed to drop TABLESPACE mysql> show warnings; +-------+------+-----------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------+ | Error | 1296 | Got error 768 'Cant drop filegroup, filegroup isused' from NDB | | Error | 1526 | Failed to drop TABLESPACE | +-------+------+-----------------------------------------------------------------+ 2 rows in set (0.00 sec) 2: but we can't drop data file after we insert some data into the table. Even if we delete all the data in the table, we still can't drop that data file. mysql> CREATE LOGFILE GROUP lg1 -> ADD UNDOFILE 'undofile.dat' -> INITIAL_SIZE 16M -> UNDO_BUFFER_SIZE = 1M -> ENGINE NDB; mysql> create tablespace ts2 -> add datafile 'datafile2_1.dat' -> use logfile group lg1 -> initial_size 12M -> engine ndb; mysql> CREATE TABLE City ( -> ID int(11) NOT NULL AUTO_INCREMENT, -> Name char(35) NOT NULL, -> CountryCode char(3) NOT NULL, -> District char(20) NOT NULL, -> Population int(11) NOT NULL, -> PRIMARY KEY (ID) -> ) ENGINE=ndbcluster -> tablespace ts2 -> storage disk; mysql> insert into City value(1,"222","333","444",5); Query OK, 1 row affected (0.01 sec) mysql> delete from City; mysql> alter tablespace ts2 -> drop datafile 'datafile2_1.dat' -> engine ndb; ERROR 1530 (HY000): Failed to alter: DROP DATAFILE mysql> show warnings; +-------+------+-------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------+ | Error | 1296 | Got error 770 'Cant drop file, file is used' from NDB | | Error | 1530 | Failed to alter: DROP DATAFILE | +-------+------+-------------------------------------------------------+ Suggested fix: I think the two drop statement should return the same result.