Debian Codegraph @

mysql-test/suite/ndb/r/ndb_alter_table_column_online.result

raw
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;