MySQL Bugs: #55618: Inconsistent conversion of col. storage format after DROP INDEX from DD tables.

Bug #55618	Inconsistent conversion of col. storage format after DROP INDEX from DD tables.
Submitted:	28 Jul 2010 22:42	Modified:	2 Dec 2010 7:25
Reporter:	Matthew Montgomery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Disk Data	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-7.1	OS:	Any
Assigned to:	Jonas Oreland	CPU Architecture:	Any
Tags:	ndb-7.1.6 bzr

Description:
When dropping an index from a column in a table with default STORAGE DISK, NDB will sometimes move the column from MEMORY to DISK and sometimes not.

1) Dropping individual column index will not result in change to the column STORAGE
2) Dropping multi-column index will cause storage to change from MEMORY to DISK
3) Dropping PRIMARY KEY moves the old PK column(s) to DISK. 

If it's a HASH vs ORDERED index doesn't matter.

How to repeat:
In config.ini add

[NDBD DEFAULT]
...
SharedGlobalMemory=512M
InitialLogFileGroup=undo.log:500M
InitialTablespace=extent_size=1M; data1.dat:2500M

# -- Create Table with PK + single column (b) ordered index

mysql> create table t1 (a int unsigned primary key, b varchar(20), c varchar(20), INDEX `b` (b)) ENGINE=NDB STORAGE DISK; 
Query OK, 0 rows affected (1.49 sec)

mysql> \q
Bye
matt@silo:~/mysql/sandbox/7.1.6$ ndb_desc -d test t1; 
-- t1 --
[...]
-- Attributes -- 
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
c Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK

-- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
b(b) - OrderedIndex
PRIMARY(a) - OrderedIndex

mysql> alter table t1 drop index `b`; 
Query OK, 0 rows affected (1.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \q
Bye
matt@silo:~/mysql/sandbox/7.1.6$ ndb_desc -d test t1; 
-- t1 --
[...]
-- Attributes -- 
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
c Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK

-- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
PRIMARY(a) - OrderedIndex

NDBT_ProgramExit: 0 - OK

!! Notice after dropping index (b) column `b` remains ST=MEMORY

# -- Create table with PK + combined (a,b) ordered index

mysql> drop table t1; 
Query OK, 0 rows affected (0.91 sec)

mysql> create table t1 (a int unsigned primary key, b varchar(20), c varchar(20), INDEX `ab` (a,b)) ENGINE=NDB STORAGE DISK;
Query OK, 0 rows affected (1.53 sec)

mysql> \q
Bye
matt@silo:~/mysql/sandbox/7.1.6$ ndb_desc -d test t1; 
-- t1 --
[...]
-- Attributes -- 
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
c Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK

-- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
ab(a, b) - OrderedIndex
PRIMARY(a) - OrderedIndex

NDBT_ProgramExit: 0 - OK

matt@silo:~/mysql/sandbox/7.1.6$ mysql test
[...]
mysql> alter table t1 drop index `ab`; 
Query OK, 0 rows affected (2.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>\q
Bye

matt@silo:~/mysql/sandbox/7.1.6$ ndb_desc -d test t1; 
-- t1 --
[...]
-- Attributes -- 
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK
c Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK

-- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
PRIMARY(a) - OrderedIndex

NDBT_ProgramExit: 0 - OK

!! Notice after dropping index (a,b) column `b` is now ST=DISK

# -- Dropping PRIMARY KEY moves the column to DISK.

mysql> drop table t1; 
Query OK, 0 rows affected (0.64 sec)

mysql> create table t1 (a int unsigned primary key, b varchar(20) NOT NULL, c varchar(20), INDEX `b` (b) ) ENGINE=NDB STORAGE DISK;
Query OK, 0 rows affected (1.72 sec)

mysql> \q
Bye
matt@silo:~/mysql/sandbox/7.1.6$ ndb_desc -d test t1; 
-- t1 --
[...]
-- Attributes -- 
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
b Varchar(20;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
c Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK
-- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
b(b) - OrderedIndex
PRIMARY(a) - OrderedIndex

NDBT_ProgramExit: 0 - OK

matt@silo:~/mysql/sandbox/7.1.6$ mysql test 

mysql> alter table t1 drop primary key; 
Query OK, 0 rows affected (3.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \q
Bye
matt@silo:~/mysql/sandbox/7.1.6$ ndb_desc -d test t1; 
-- t1 --
[...]
-- Attributes -- 
a Unsigned NOT NULL AT=FIXED ST=DISK
b Varchar(20;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
c Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=DISK
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR

-- Indexes -- 
PRIMARY KEY($PK) - UniqueHashIndex
b(b) - OrderedIndex

!! Notice after dropping index PRIMARY column `a` is now ST=DISK

Suggested fix:
I can't say which behavior should be considered correct (to change the column storage format or not) but NDB should handle them the same in both of these cases.

case 1)
doesnt move column from memory to disk, as it's a online drop table
iff you want to move it you have to specify "alter offline table" in which case
it should move.

case 2)
look like a bug, this *should* be an online drop,
(in which a column can't move)

case 3)
is ok, as dropping a primary always requires table rebuild, hence is
equivalant of doing an "alter offline table"

Summary: The only think that I think is a bug here is that case 2 becomes an
  offline alter table (i.e table rebuild).

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

3355 Jonas Oreland	2010-11-30
      ndb - bug#55618, online drop of multi column index not working for table with storage disk

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

3355 Jonas Oreland	2010-11-30
      ndb - bug#55618, online drop of multi column index not supported for table with storage disk

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.40 (revid:jonas@mysql.com-20101130101952-pyudaz0lz9hlpijr) (version source revid:jonas@mysql.com-20101130101952-pyudaz0lz9hlpijr) (merge vers: 5.1.51-ndb-6.3.40) (pib:23)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:jonas@mysql.com-20101130103942-1uy7fby636vwplba) (version source revid:jonas@mysql.com-20101130103537-3drrpuwd50bmgsdv) (merge vers: 5.1.51-ndb-7.0.21) (pib:23)

pushed to 6.3.40, 7.0.21 and 7.1.10

DOCS: The problem was that multi-column indexes was treated differently that
  single-column indexes.

DOCS 2: The fact that online/offline alter table behaves differently wrt
  create/drop index on table with storage disk, is quite non-obvious
  and is likely not well articulated in docs...(guess)

Documented bugfix in the NDB-6.3.40, 7.0.21, and 7.1.10 changelogs, as follows:

        Performing what should have been an online drop of a
        multi-column index was actually performed offline.

Also updated description of ONLINE/OFFLINE in "ALTER TABLE Syntax" in the 5.1 Manual, in particular the section titled "Limitations of online operations".

Closed.