******************************* * online alter column tests ******************************* CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED, e INT UNIQUE, f TEXT) ROW_FORMAT=DYNAMIC ENGINE NDB; INSERT INTO t1 values (1,1,1,"One"); set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); *************************************** * Alter Table online change column name *************************************** ALTER TABLE t1 CHANGE b c INT UNSIGNED ; select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; name 't1' Attributes: a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC c Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC e Int NULL AT=FIXED ST=MEMORY DYNAMIC f Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC INSERT INTO t1 values (2,2,2,"Two"); SELECT * FROM t1 ORDER BY a; a c e f 1 1 1 One 2 2 2 Two ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE c D INT UNSIGNED; select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; name 't1' Attributes: a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC D Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC e Int NULL AT=FIXED ST=MEMORY DYNAMIC f Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC INSERT INTO t1 values (3,3,3,"Three"); SELECT * FROM t1 ORDER BY a; a D e f 1 1 1 One 2 2 2 Two 3 3 3 Three ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE D d INT UNSIGNED; select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; name 't1' Attributes: a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC d Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC e Int NULL AT=FIXED ST=MEMORY DYNAMIC f Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC INSERT INTO t1 values (4,4,4,"Four"); SELECT * FROM t1 ORDER BY a; a d e f 1 1 1 One 2 2 2 Two 3 3 3 Three 4 4 4 Four ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE f t TEXT; select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; name 't1' Attributes: a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC d Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC e Int NULL AT=FIXED ST=MEMORY DYNAMIC t Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC INSERT INTO t1 values (5,5,5,"Five"); SELECT * FROM t1 ORDER BY a; a d e t 1 1 1 One 2 2 2 Two 3 3 3 Three 4 4 4 Four 5 5 5 Five ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra1 VARCHAR(10); ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra2 VARCHAR(10); UPDATE t1 set extra1="XXXX", extra2="YYYY"; SELECT * FROM t1 ORDER BY a; a d e t extra1 extra2 1 1 1 One XXXX YYYY 2 2 2 Two XXXX YYYY 3 3 3 Three XXXX YYYY 4 4 4 Four XXXX YYYY 5 5 5 Five XXXX YYYY ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN extra1 TO extra2, RENAME COLUMN extra2 TO extra1; SELECT * FROM t1 ORDER BY a; a d e t extra2 extra1 1 1 1 One XXXX YYYY 2 2 2 Two XXXX YYYY 3 3 3 Three XXXX YYYY 4 4 4 Four XXXX YYYY 5 5 5 Five XXXX YYYY Node 1: STARTED Node 2: STARTED SELECT * FROM t1 ORDER BY a; a d e t extra2 extra1 1 1 1 One XXXX YYYY 2 2 2 Two XXXX YYYY 3 3 3 Three XXXX YYYY 4 4 4 Four XXXX YYYY 5 5 5 Five XXXX YYYY ********************************* * Backup and restore tables w/ changed column ********************************* DROP TABLE t1; SELECT * FROM t1 ORDER BY a; a d e t extra2 extra1 1 1 1 One XXXX YYYY 2 2 2 Two XXXX YYYY 3 3 3 Three XXXX YYYY 4 4 4 Four XXXX YYYY 5 5 5 Five XXXX YYYY -- t1 -- Version: Any Fragment type: HashMapPartition K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 6 Number of primary keys: 1 Length of frm data: XXX Max Rows: 0 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 PartitionCount: 8 FragmentCount: 8 PartitionBalance: FOR_RP_BY_LDM ExtraRowGciBits: 0 ExtraRowAuthorBits: 0 TableStatus: Retrieved Table options: readbackup HashMap: DEFAULT-HASHMAP-3840-8 -- Attributes -- a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC d Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC e Int NULL AT=FIXED ST=MEMORY DYNAMIC t Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_3 DYNAMIC extra2 Varchar(40;utf8mb4_0900_ai_ci) NULL AT=SHORT_VAR ST=MEMORY DYNAMIC extra1 Varchar(40;utf8mb4_0900_ai_ci) NULL AT=SHORT_VAR ST=MEMORY DYNAMIC -- Indexes -- PRIMARY KEY(a) - UniqueHashIndex e$unique(e) - UniqueHashIndex PRIMARY(a) - OrderedIndex e(e) - OrderedIndex -- t1/e$unique -- Attributes: e Index Type: Unique hash index Index Status: retrieved Partitions: 8 Fragments: 8 -- t1/PRIMARY -- Attributes: a Index Type: Ordered index Index Status: retrieved Partitions: 8 Fragments: 8 -- t1/e -- Attributes: e Index Type: Ordered index Index Status: retrieved Partitions: 8 Fragments: 8 -- Blob Tables -- Column name: t Inline size: 256 Part size: 2000 Stipe size: 0 Blob Table Name: NDB$BLOB_XX_3 Fragment type: HashMapPartition Temporary table: no Number of attributes: 4 Primary key: a,NDB$PART Max Rows: 0 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 Dynamic: 1 PartitionCount: 8 FragmentCount: 8 PartitionBalance: FOR_RP_BY_LDM ExtraRowGciBits: 0 ExtraRowAuthorBits: 0 TableStatus: Retrieved Table options: readbackup HashMap: DEFAULT-HASHMAP-3840-8 ********************************* * Error tests ********************************* ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE d a INT UNSIGNED; ERROR 42S21: Duplicate column name 'a' ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE a pk INT UNSIGNED; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Renaming column that is part of an index is not supported. Try ALGORITHM=COPY. ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE e ue INT; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Renaming column that is part of an index is not supported. Try ALGORITHM=COPY. ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN D TO D2, ADD t2 CHAR(10); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Only rename column exclusively can be performed inplace. Try ALGORITHM=COPY. DROP TABLE t1;