MySQL Bugs: #21699: DROP last DATAFILE from TABLESPACE even though there are still tables in it

Bug #21699	DROP last DATAFILE from TABLESPACE even though there are still tables in it
Submitted:	17 Aug 2006 15:41	Modified:	9 May 2007 6:20
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Disk Data	Severity:	S3 (Non-critical)
Version:	5.1.11 beta	OS:	Linux (Ubuntu 2.6.15)
Assigned to:	Guangbao Ni	CPU Architecture:	Any
Tags:	cluster, Datafile, Disk based storage, Drop, Tablespace

Description:
The last datafile in a tablespace can be dropped using an ALTER TABLESPACE statement, even though there is still an empty table using the tablespace.

It should be noted that the datafile cannot be dropped if the table still contains rows, so no dataloss is involved. 

How to repeat:
create logfile group logfile_group1
add undofile 'logfile_group1_undo1.dat'
initial_size 1M
undo_buffer_size 1M
engine ndbcluster
;
create tablespace haworld_ts1
add datafile 'haworld_data1.dat'
use logfile group logfile_group1
initial_size 1M
engine ndb
;
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 haworld_ts1
storage disk
;

alter tablespace haworld_ts1 drop datafile 'haworld_data1.dat' engine ndb;

insert 
into City (Name,CountryCode,District,Population) 
values ('The Netherlands','NL','South Holland',10);

ERROR 1114 (HY000): The table 'City' is full

Suggested fix:
Hard to say. 

1) this could be unintentional behaviour. There is still a table using it in the sense that the proper functioning of the table is denpendant upon the existence of a datafile. If this is the case, the user should not be able to drop the datafile

2) This could be intentional behaviour.

In either case, this case should receive some intention in the documentation at  http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data.html. The text now does mention that a datafile cannot be dropped if there is still a table 'using' it, but it should then be clarified that 'using' means 'stores data in it'.

Also, if it should be possible to drop the last datafile while there are still tables around, then the message "The table 'foo' is full" is not really good. It should say: "The tablespace 'tsfoo' constains no datafiles'

Updating Category to Cluster.

this is intended behaviour.
but i discussed it only with myself,
  so i'm perfectly happy to change if confronted with good argument

Jonas, thanks for you comment. 

If it's intended behaviour, I think two things should be done:

1) the error message 'table is full' should not occur when the tablespace has no associated datafiles. I mean, there is nothing to be full, so it can't be full. Rather, a message should appear that informs the user that there are no datafiles, and that datafiles should be added.

2) the documentation needs to be modified and clearly state that the datafile cannot be removed when there are non-empty in the tablespace. The docs should also clarify that this means that the datafile can be removed when the tablespace constains only empty tables, and that any subsequent insert (and probably, update) into such an empty table will fail if the datafile is removed. Also, if there is a use case for removing all datafiles as described (and I expect there to be one, as it is intended behaviour), then it would be nice to have that in the docs as well, so people can use this feature to have a better cluster user experience.

modifying docs is easy.
changing error message is possible....

So, this is either a documentation request or request to change error messgae, at least.

Valeriy, thanks for verifying. I just want to clarify that is not a matter of updating the docs *or* modifying the error message; both the docs *and* the error message should be modified.

I think that if it is intended behaviour, this would be both a request to change the error message as well as to update the docs to clarify what cases warrant dropping the last datafile. 

I mean, if it is intended behaviour, I assume that it is useful to achieve something, or to perform some administrative task. IMO, that "something" should be documented; the documentation would not only state that it is intended behaviour, but also *why* it is intended behaviour.

If it is intended behaviour, a user can attempt to insert data even if there is no datafile. In that case, the error message should tell the user what the problem is exactly, namely that there is no datafile. The current error message is misleading. There is nothing to be full, so it cannot be full.

Of course, if it is not intended behaviour after all, it is a bug and dropping the last datafile should be prevented.

Right...thinking a bit about it...
The idea long term is to add more properties for tablespaces
  that will also affect memory based tuples.

E.g which redo logfile group to use.

This has however not yet been impl. (and will not be done in 5.1)
But I still think that having tablespace wo/ datafiles
  is perfectly ok.

So, conclusion
docs can be updated today.
i'll try to fix the error message any day :-)

Jonas, just to be totally clear:

I am not complaining that you an drop the last datafile. I also think that is perfectly ok. I made the remark because there is still a table that depends on the tablespace, albeit an empty one.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22112

ChangeSet@1.2486, 2007-03-16 18:18:03+08:00, gni@dev3-221.dev.cn.tlan +3 -0
  BUG#21699 DROP last DATAFILE from TABLESPACE even though there are still table in it. And it gives the cofusing error message.

Now it gives the following error message:
   ERROR 1296 (HY000): Got error 1602 'No datafile in tablespace' from NDBCLUSTER

patch looks good...
but...
can you also add a mysql-test-run test...

i.e create a table for a tablespace wo/ datafiles and try to perform an insert
do this wo/ creating/dropping any extra tablespaces/datafiles

/Jonas

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22231

ChangeSet@1.2486, 2007-03-19 14:33:49+08:00, gni@dev3-221.dev.cn.tlan +5 -0
  BUG#21699 DROP last DATAFILE from TABLESPACE even though there are still table in it. And it gives the cofusing error message.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22319

ChangeSet@1.2486, 2007-03-20 10:36:20+08:00, gni@dev3-221.dev.cn.tlan +5 -0
  BUG#21699 DROP last DATAFILE from TABLESPACE even though there are still table in it. And it gives the cofusing error message.

approved by Jonas.

Push into mysql-5.1-ndb-bj tree about 5.1.17 version .

Pushed into 5.1.18-beta

mysql> CREATE LOGFILE GROUP lg_1
    ->     ADD UNDOFILE 'undo_1.dat'
    ->     INITIAL_SIZE 16M
    ->     UNDO_BUFFER_SIZE 2M
    ->     ENGINE NDB;
Query OK, 0 rows affected (3.12 sec)

mysql> ALTER LOGFILE GROUP lg_1
    ->     ADD UNDOFILE 'undo_2.dat'
    ->     INITIAL_SIZE 12M
    ->     ENGINE NDB;
Query OK, 0 rows affected (1.71 sec)

mysql> CREATE TABLESPACE ts_1
    ->     ADD DATAFILE 'data_1.dat'
    ->     USE LOGFILE GROUP lg_1
    ->     INITIAL_SIZE 32M
    ->     ENGINE NDB;
Query OK, 0 rows affected (5.52 sec)

mysql>
mysql> ALTER TABLESPACE ts_1
    ->     ADD DATAFILE 'data_2.dat'
    ->     INITIAL_SIZE 48M
    ->     ENGINE NDB;
Query OK, 0 rows affected (6.24 sec)

mysql> CREATE TABLE dt_1 (
    ->     member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     last_name VARCHAR(50) NOT NULL,
    ->     first_name VARCHAR(50) NOT NULL,
    ->     dob DATE NOT NULL,
    ->     joined DATE NOT NULL,
    ->     INDEX(last_name, first_name)
    ->     )
    ->     TABLESPACE ts_1 STORAGE DISK
    ->     ENGINE NDB;
Query OK, 0 rows affected (2.47 sec)

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

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

mysql> insert into dt_1 values (null, 'smith', 'joe', '1971-06-15', '2001-05-01');
ERROR 1296 (HY000): Got error 1602 'No datafile in tablespace' from NDBCLUSTER

#  Okay, so far so good.

mysql> ALTER TABLESPACE ts_1     ADD DATAFILE 'data_1.dat'     INITIAL_SIZE 48M     ENGINE NDB;
Query OK, 0 rows affected (5.39 sec)

mysql> insert into dt_1 values (null, 'smith', 'joe', '1971-06-15', '2001-05-01');

mysql> select * from dt_1;
+-----------+-----------+------------+------------+------------+
| member_id | last_name | first_name | dob        | joined     |
+-----------+-----------+------------+------------+------------+
|         2 |    smith  | joe        | 1971-06-15 | 2001-05-01 |
+-----------+-----------+------------+------------+------------+
1 row in set (0.05 sec)

#  WTF? is there a reason why a failed insert bumped the AUTO_INCREMENT 
#  column? This doesn't happen with the equivalent MyISAM table...

mysql> alter tablespace ts_1 drop datafile 'data_1.dat' engine ndb;
ERROR 1521 (HY000): Failed to alter:  DROP DATAFILE

#  That's not a helpful error message.
#  A helpful error message would inform the user that there are still tables
#  using the data file.

1. Please clarify why and when a failed insert advances an AUTO_INCREMENT column.

2. Tell the user why the data file was not dropped - please don't make him guess!

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bug (as filed) in 5.1.18 changelog.