Bug #27611 ALTER TABLE : disk columns become memory columns

• Submitted: 3 Apr 2007 12:26
• Modified: 4 Apr 2007 6:09
• Reporter: Steve Edwards
• Status: Duplicate
• Category: MySQL Cluster: Disk Data
• Severity: S2 (Serious)
• Version: 5.1.16
• OS: Linux (Debian Linux 64bit (sarge))
• CPU Architecture: Any
• Tags: cluster disk alter table memory column schema

[3 Apr 2007 12:26] Steve Edwards

Description:
ALTER TABLE causes disk storage columns to become memory columns.

How to repeat:
CREATE TABLE `test` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `f01` varchar(10),
  `f02` int,
  `f03` text,
  `f04` date NOT NULL,
  PRIMARY KEY (`ID`)
) TABLESPACE ts_1 STORAGE DISK 
ENGINE=NDB;

root@xxxxx:~# ndb_desc -d xxx test

-- Attributes --
ID Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
f01 Varchar(10;latin1_swedish_ci) NULL AT=FIXED ST=DISK
f02 Int NULL AT=FIXED ST=DISK
f03 Text(256,2000;16;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY
f04 Date NOT NULL AT=FIXED ST=DISK

mysql> alter table test add index (f01);
Query OK, 0 rows affected (3.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@xxxxx:~# ndb_desc -d xxx test

-- Attributes --
ID Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
f01 Varchar(10;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
f02 Int NULL AT=FIXED ST=MEMORY
f03 Text(256,2000;16;latin1_swedish_ci) NULL AT=FIXED ST=MEMORY
f04 Date NOT NULL AT=FIXED ST=MEMORY

Similarly other ALTER TABLE.. variations cause the same result.

Suggested fix:
Looks like the temporary table created during ALTER copy is not fully aware of disk columns.

[4 Apr 2007 6:09] Adam Dixon

Thanks for your report, this however is a duplicate bug of http://bugs.mysql.com/bug.php?id=25295
Which is to be fixed in 5.1.17, I have verified this behavior on both 5.1.16 and the lack thereof in 5.1.17BK.

You can work around this problem if you need by the following statement;
mysql> alter table t1 add index (nonindex1), TABLESPACE ts_1 STORAGE DISK;