| 1 |
******************************* |
| 2 |
* online alter column tests |
| 3 |
******************************* |
| 4 |
CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED, e INT UNIQUE, f TEXT) ROW_FORMAT=DYNAMIC ENGINE NDB; |
| 5 |
INSERT INTO t1 values (1,1,1,"One"); |
| 6 |
set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); |
| 7 |
*************************************** |
| 8 |
* Alter Table online change column name |
| 9 |
*************************************** |
| 10 |
ALTER TABLE t1 CHANGE b c INT UNSIGNED ; |
| 11 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; |
| 12 |
name |
| 13 |
't1' |
| 14 |
Attributes: |
| 15 |
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC |
| 16 |
c Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC |
| 17 |
e Int NULL AT=FIXED ST=MEMORY DYNAMIC |
| 18 |
f Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC |
| 19 |
INSERT INTO t1 values (2,2,2,"Two"); |
| 20 |
SELECT * FROM t1 ORDER BY a; |
| 21 |
a c e f |
| 22 |
1 1 1 One |
| 23 |
2 2 2 Two |
| 24 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE c D INT UNSIGNED; |
| 25 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; |
| 26 |
name |
| 27 |
't1' |
| 28 |
Attributes: |
| 29 |
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC |
| 30 |
D Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC |
| 31 |
e Int NULL AT=FIXED ST=MEMORY DYNAMIC |
| 32 |
f Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC |
| 33 |
INSERT INTO t1 values (3,3,3,"Three"); |
| 34 |
SELECT * FROM t1 ORDER BY a; |
| 35 |
a D e f |
| 36 |
1 1 1 One |
| 37 |
2 2 2 Two |
| 38 |
3 3 3 Three |
| 39 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE D d INT UNSIGNED; |
| 40 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; |
| 41 |
name |
| 42 |
't1' |
| 43 |
Attributes: |
| 44 |
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC |
| 45 |
d Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC |
| 46 |
e Int NULL AT=FIXED ST=MEMORY DYNAMIC |
| 47 |
f Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC |
| 48 |
INSERT INTO t1 values (4,4,4,"Four"); |
| 49 |
SELECT * FROM t1 ORDER BY a; |
| 50 |
a d e f |
| 51 |
1 1 1 One |
| 52 |
2 2 2 Two |
| 53 |
3 3 3 Three |
| 54 |
4 4 4 Four |
| 55 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE f t TEXT; |
| 56 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; |
| 57 |
name |
| 58 |
't1' |
| 59 |
Attributes: |
| 60 |
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC |
| 61 |
d Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC |
| 62 |
e Int NULL AT=FIXED ST=MEMORY DYNAMIC |
| 63 |
t Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_#_# DYNAMIC |
| 64 |
INSERT INTO t1 values (5,5,5,"Five"); |
| 65 |
SELECT * FROM t1 ORDER BY a; |
| 66 |
a d e t |
| 67 |
1 1 1 One |
| 68 |
2 2 2 Two |
| 69 |
3 3 3 Three |
| 70 |
4 4 4 Four |
| 71 |
5 5 5 Five |
| 72 |
ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra1 VARCHAR(10); |
| 73 |
ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra2 VARCHAR(10); |
| 74 |
UPDATE t1 set extra1="XXXX", extra2="YYYY"; |
| 75 |
SELECT * FROM t1 ORDER BY a; |
| 76 |
a d e t extra1 extra2 |
| 77 |
1 1 1 One XXXX YYYY |
| 78 |
2 2 2 Two XXXX YYYY |
| 79 |
3 3 3 Three XXXX YYYY |
| 80 |
4 4 4 Four XXXX YYYY |
| 81 |
5 5 5 Five XXXX YYYY |
| 82 |
ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN extra1 TO extra2, RENAME COLUMN extra2 TO extra1; |
| 83 |
SELECT * FROM t1 ORDER BY a; |
| 84 |
a d e t extra2 extra1 |
| 85 |
1 1 1 One XXXX YYYY |
| 86 |
2 2 2 Two XXXX YYYY |
| 87 |
3 3 3 Three XXXX YYYY |
| 88 |
4 4 4 Four XXXX YYYY |
| 89 |
5 5 5 Five XXXX YYYY |
| 90 |
Node 1: STARTED |
| 91 |
Node 2: STARTED |
| 92 |
SELECT * FROM t1 ORDER BY a; |
| 93 |
a d e t extra2 extra1 |
| 94 |
1 1 1 One XXXX YYYY |
| 95 |
2 2 2 Two XXXX YYYY |
| 96 |
3 3 3 Three XXXX YYYY |
| 97 |
4 4 4 Four XXXX YYYY |
| 98 |
5 5 5 Five XXXX YYYY |
| 99 |
********************************* |
| 100 |
* Backup and restore tables w/ changed column |
| 101 |
********************************* |
| 102 |
DROP TABLE t1; |
| 103 |
SELECT * FROM t1 ORDER BY a; |
| 104 |
a d e t extra2 extra1 |
| 105 |
1 1 1 One XXXX YYYY |
| 106 |
2 2 2 Two XXXX YYYY |
| 107 |
3 3 3 Three XXXX YYYY |
| 108 |
4 4 4 Four XXXX YYYY |
| 109 |
5 5 5 Five XXXX YYYY |
| 110 |
-- t1 -- |
| 111 |
Version: Any |
| 112 |
Fragment type: HashMapPartition |
| 113 |
K Value: 6 |
| 114 |
Min load factor: 78 |
| 115 |
Max load factor: 80 |
| 116 |
Temporary table: no |
| 117 |
Number of attributes: 6 |
| 118 |
Number of primary keys: 1 |
| 119 |
Length of frm data: XXX |
| 120 |
Max Rows: 0 |
| 121 |
Row Checksum: 1 |
| 122 |
Row GCI: 1 |
| 123 |
SingleUserMode: 0 |
| 124 |
ForceVarPart: 1 |
| 125 |
PartitionCount: 8 |
| 126 |
FragmentCount: 8 |
| 127 |
PartitionBalance: FOR_RP_BY_LDM |
| 128 |
ExtraRowGciBits: 0 |
| 129 |
ExtraRowAuthorBits: 0 |
| 130 |
TableStatus: Retrieved |
| 131 |
Table options: readbackup |
| 132 |
HashMap: DEFAULT-HASHMAP-3840-8 |
| 133 |
-- Attributes -- |
| 134 |
a Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY DYNAMIC |
| 135 |
d Unsigned NULL AT=FIXED ST=MEMORY DYNAMIC |
| 136 |
e Int NULL AT=FIXED ST=MEMORY DYNAMIC |
| 137 |
t Text(256,2000,0;utf8mb4_0900_ai_ci) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_XX_3 DYNAMIC |
| 138 |
extra2 Varchar(40;utf8mb4_0900_ai_ci) NULL AT=SHORT_VAR ST=MEMORY DYNAMIC |
| 139 |
extra1 Varchar(40;utf8mb4_0900_ai_ci) NULL AT=SHORT_VAR ST=MEMORY DYNAMIC |
| 140 |
-- Indexes -- |
| 141 |
PRIMARY KEY(a) - UniqueHashIndex |
| 142 |
e$unique(e) - UniqueHashIndex |
| 143 |
PRIMARY(a) - OrderedIndex |
| 144 |
e(e) - OrderedIndex |
| 145 |
|
| 146 |
-- t1/e$unique -- |
| 147 |
Attributes: e |
| 148 |
Index Type: Unique hash index |
| 149 |
Index Status: retrieved |
| 150 |
Partitions: 8 |
| 151 |
Fragments: 8 |
| 152 |
-- t1/PRIMARY -- |
| 153 |
Attributes: a |
| 154 |
Index Type: Ordered index |
| 155 |
Index Status: retrieved |
| 156 |
Partitions: 8 |
| 157 |
Fragments: 8 |
| 158 |
-- t1/e -- |
| 159 |
Attributes: e |
| 160 |
Index Type: Ordered index |
| 161 |
Index Status: retrieved |
| 162 |
Partitions: 8 |
| 163 |
Fragments: 8 |
| 164 |
|
| 165 |
-- Blob Tables -- |
| 166 |
Column name: t |
| 167 |
Inline size: 256 |
| 168 |
Part size: 2000 |
| 169 |
Stipe size: 0 |
| 170 |
Blob Table Name: NDB$BLOB_XX_3 |
| 171 |
Fragment type: HashMapPartition |
| 172 |
Temporary table: no |
| 173 |
Number of attributes: 4 |
| 174 |
Primary key: a,NDB$PART |
| 175 |
Max Rows: 0 |
| 176 |
Row Checksum: 1 |
| 177 |
Row GCI: 1 |
| 178 |
SingleUserMode: 0 |
| 179 |
Dynamic: 1 |
| 180 |
PartitionCount: 8 |
| 181 |
FragmentCount: 8 |
| 182 |
PartitionBalance: FOR_RP_BY_LDM |
| 183 |
ExtraRowGciBits: 0 |
| 184 |
ExtraRowAuthorBits: 0 |
| 185 |
TableStatus: Retrieved |
| 186 |
Table options: readbackup |
| 187 |
HashMap: DEFAULT-HASHMAP-3840-8 |
| 188 |
|
| 189 |
********************************* |
| 190 |
* Error tests |
| 191 |
********************************* |
| 192 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE d a INT UNSIGNED; |
| 193 |
ERROR 42S21: Duplicate column name 'a' |
| 194 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE a pk INT UNSIGNED; |
| 195 |
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Renaming column that is part of an index is not supported. Try ALGORITHM=COPY. |
| 196 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE e ue INT; |
| 197 |
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Renaming column that is part of an index is not supported. Try ALGORITHM=COPY. |
| 198 |
ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN D TO D2, ADD t2 CHAR(10); |
| 199 |
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Only rename column exclusively can be performed inplace. Try ALGORITHM=COPY. |
| 200 |
DROP TABLE t1; |