MySQL Bugs: #42549: Cannot reorganize partition with disk table

Bug #42549	Cannot reorganize partition with disk table
Submitted:	2 Feb 2009 17:01	Modified:	15 Feb 2009 13:11
Reporter:	Wen Xiong	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	ndb-6.4.1	OS:	Solaris
Assigned to:	Jonas Oreland	CPU Architecture:	Any

Description:
Disk table can not be reorganized partition with
"alter online table X reorganize parition"
after adding two ndbd nodes to the running cluster.

The error message is:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'alter online table t2 reorganize partition'

How to repeat:
1 Start a cluster with one ndb_mgmd and two ndbd and then do 
mysql>create LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE 2M ENGINE NDB;
mysql>create tablespace ts_1 add datafile 'data_1.dat' use logfile group lg_1 initial_size 32M engine ndb;
mysql>create table t1(id int not null primary key, data char(3))engine=ndb; Query 
mysql> create table t2(id int not null primary key, data char(3)) tablespace ts_1 storage disk engine=ndb;

2 Add two ndbd in config file and restart cluster, and create nodegroup for new ndbd nodes.Then do reorganize partition like:

mysql> alter online table t1 reorganize partition;
Query OK, 0 rows affected (9.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter online table t2 reorganize partition;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'alter online table t2 reorganize partition'

Also reproducible without adding nodes, only ALTER OFFLINE seems to work on disk based tables right now. SHOW WARNINGS does not provide any additional information besides repeating the error message.

ok, "found" it

the following works
create table t2 (a int primary key, b char(3) storage disk) tablespace ts1 engine=ndb;
alter table t2 reorganize partition;

i.e problem is when "storage disk" is given at table level,
then alter-code incorrectly (and implicitly) changes forgets
about the storage disk, and tries to put b in memory,
and altering from disk to memory is not supported online.

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

2816 Jonas Oreland	2009-02-12
      ndb - bug#42549
        Fix alter (online) table wrt storage disk

Pushed into 5.1.31-ndb-6.4.3 (revid:jonas@mysql.com-20090212152146-k76m16f0hekg9kpu) (version source revid:jonas@mysql.com-20090212152146-k76m16f0hekg9kpu) (merge vers: 5.1.31-ndb-6.4.3) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:jonas@mysql.com-20090212142234-pasf2g82k35ckgon) (version source revid:jonas@mysql.com-20090212142234-pasf2g82k35ckgon) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.23 (revid:jonas@mysql.com-20090212145330-nf5jrg0g2ovqnptp) (version source revid:jonas@mysql.com-20090212145330-nf5jrg0g2ovqnptp) (merge vers: 5.1.31-ndb-6.3.23) (pib:6)

documentation: prior to this patch, the "storage disk" (either per column or for table) was handled incorrectly. This affected *all* alters, not only the one
specified in bug-report originally.

<example>
create table t1 (a int primary key, b int) storage disk engine = ndb;
a) alter online table t1 add column c0 int null column_format DYNAMIC;
b) alter online table t1 add column c1 int null column_format DYNAMIC storage memory;

create table t2 (a int primary key, b int storage disk) engine = ndb;
c) alter online table t2 add column c0 int null column_format DYNAMIC;
d) alter online table t2 add column c1 int null column_format DYNAMIC storage memory;
</example>

prio to patch, all alters a-d would return 'not supported'.
after patch the following behaviour
a) not supported, motivation: table has "storage disk", the added column 'inherits' this behviour, add add column is not (yet) an online alter for cluster

b) works, the newly added column has explicitly stated storage memory
c) works, table is memory column 'inherits' this
d) works

Documented in the NDB-6.2.17, 6.3.23, and 6.4.3 changelogs as follows:

        It was not possible to add an in-memory column online to a table
        that used a table-level or column-level STORAGE DISK option. The
        same issue prevented ALTER ONLINE TABLE ... REORGANIZE PARTITION
        from working on Disk Data tables.

Also updated http://dev.mysql.com/doc/refman/5.1/en/alter-table.html and http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-online-add-node-example.html.