MySQL Bugs: #25295: ALTER TABLE operations for NDB tables require inclusion of ENGINE=ndbcluster.

Bug #25295	ALTER TABLE operations for NDB tables require inclusion of ENGINE=ndbcluster.
Submitted:	27 Dec 2006 18:03	Modified:	6 Jul 2007 9:34
Reporter:	Matthew Montgomery	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Cluster: Disk Data	Severity:	S3 (Non-critical)
Version:	5.1.14	OS:	Linux (Linux 2.6)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	alter, cluster, disk, engine, Memory, storage

Description:
Some ALTER TABLE operations require the inclusion ENGINE=ndbcluster to complete.

This includes TABLESPACE / STORAGE changes.

There should be no need to specify an ENGINE= clause if the engine is not being changed.

How to repeat:
mysql> alter table `dt_1` TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (1.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table dt_1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dt_1  | CREATE TABLE `dt_1` (
  `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `joined` date NOT NULL,
  PRIMARY KEY (`member_id`),
  KEY `last_name` (`last_name`,`first_name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> alter table `dt_1` TABLESPACE ts_1 STORAGE DISK engine=ndbcluster;
Query OK, 0 rows affected (9.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table dt_1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dt_1  | CREATE TABLE `dt_1` (
  `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `joined` date NOT NULL,
  PRIMARY KEY (`member_id`),
  KEY `last_name` (`last_name`,`first_name`)
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

Also... 

mysql> alter table dt_1 STORAGE MEMORY;
Query OK, 0 rows affected (1.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table dt_1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dt_1  | CREATE TABLE `dt_1` (
  `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `joined` date NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`member_id`),
  KEY `last_name` (`last_name`,`first_name`)
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

Suggested fix:
Read engine info from table def.

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/19002

ChangeSet@1.2343, 2007-01-11 17:46:23+08:00, Justin.He@dev3-240.dev.cn.tlan +2 -0
  BUG#25295, ALTER TABLE operations for NDB tables require inclusion of ENGINE=ndbcluster

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/21788

ChangeSet@1.2444, 2007-03-13 18:00:59+08:00, Justin.He@dev3-240.dev.cn.tlan +1 -0
  Bug#25295, ALTER TABLE operations for NDB tables require inclusion of ENGINE=ndbcluster.
  this patch of bug25295 depends on bug25877 and bug25880

Pushed into 5.1.18-beta

mysql> ALTER TABLESPACE ts_1     ADD DATAFILE 'data_2.dat'     INITIAL_SIZE 48M;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                        |
+-------+------+------------------------------------------------------------------------------------------------+
| Error | 1466 | Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+-------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Result: "Query OK" and a warning, but no data file is not created.

This is IMO very, very wrong. The statement should be reported as successful *only* if the data file is created and associated with the tablespace.

One of two things should happen:

1. The statement should clearly *fail* with an *error* (and not a warning), since the storage engine was not specified.

2. The statement should succeed with no warnings or errors (and the data file should be created), due to the fact that tablespace ts_1 is already associated with the NDB storage engine.

This bug is not scheduled to be fixed at this time.