Debian Codegraph @

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

raw
1 set @global_fully_replicated=@@global.ndb_fully_replicated;
2 set @global_read_backup=@@global.ndb_read_backup;
3 # Create table with fully_replicated=1 #
4 # will change read_backup to 1 implicitly, #
5 # but will not be shown in the comment. #
6 # This should not affect the following alters #
7 set global ndb_fully_replicated=1;
8 set global ndb_read_backup=0;
9 CREATE TABLE t (x int primary key)
10 ENGINE=ndbcluster;
11 SHOW CREATE TABLE t;
12 Table Create Table
13 t CREATE TABLE `t` (
14 `x` int NOT NULL,
15 PRIMARY KEY (`x`)
16 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
17 ALTER TABLE t ALGORITHM=copy, ADD COLUMN y int;
18 SHOW CREATE TABLE t;
19 Table Create Table
20 t CREATE TABLE `t` (
21 `x` int NOT NULL,
22 `y` int DEFAULT NULL,
23 PRIMARY KEY (`x`)
24 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
25 DROP TABLE t;
26
27 # Change the default of read_backup and alter another comment item. #
28 # Will fail due to the new default of the non-specified RB #
29 set global ndb_fully_replicated=1;
30 set global ndb_read_backup=1;
31 CREATE TABLE t (x int primary key)
32 ENGINE=ndbcluster comment="A comment";
33 set global ndb_read_backup=0;
34 ALTER TABLE t algorithm=copy, comment="NDB_TABLE=NOLOGGING=1";
35 ERROR 0A000: Alter table is not supported. Reason: Alter will use the default value for READ_BACKUP (=0) which is different from the table's current value. Try either 'set global ndb_read_backup=1;' or add READ_BACKUP=1 to comment.
36 ALTER TABLE t algorithm=inplace, comment="NDB_TABLE=NOLOGGING=1";
37 ERROR 0A000: Alter table is not supported. Reason: Alter will use the default value for READ_BACKUP (=0) which is different from the table's current value. Try either 'set global ndb_read_backup=1;' or add READ_BACKUP=1 to comment.
38 SHOW CREATE TABLE t;
39 Table Create Table
40 t CREATE TABLE `t` (
41 `x` int NOT NULL,
42 PRIMARY KEY (`x`)
43 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='A comment'
44
45 # Change the default of FR and alter another comment item #
46 # Will fail due to the new default of the non-specified FR #
47 set global ndb_fully_replicated=0;
48 ALTER TABLE t ALGORITHM=copy, comment="NDB_TABLE=NOLOGGING=0";
49 ERROR 0A000: Alter table is not supported. Reason: Alter will use the default value for FULLY_REPLICATED (=0) which is different from the table's current value. Try either 'set global ndb_fully_replicated=1;' or add FULLY_REPLICATED=1 to comment.
50 DROP TABLE t;
51
52 # Change the default to NL=1 and alter another comment item #
53 # Will fail due to the new default of the non-specified NL #
54 CREATE TABLE t (x int primary key)
55 ENGINE=ndbcluster comment="A comment";
56 set session ndb_table_no_logging=1;
57 ALTER TABLE t algorithm=copy, comment="NDB_TABLE=READ_BACKUP=1";
58 ERROR 0A000: Alter table is not supported. Reason: Alter will use the default value for NOLOGGING (=1) which is different from the table's current value. Try either 'set table_no_logging=0;' or add NOLOGGING=0 to comment.
59 set session ndb_table_no_logging=0;
60 ALTER TABLE t algorithm=copy, comment="NDB_TABLE=READ_BACKUP=1";
61 SHOW CREATE TABLE t;
62 Table Create Table
63 t CREATE TABLE `t` (
64 `x` int NOT NULL,
65 PRIMARY KEY (`x`)
66 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=READ_BACKUP=1'
67 DROP TABLE t;
68
69 # Check if alters preserve the values of the other comment items #
70 CREATE TABLE t30 (col1 int PRIMARY KEY, col2 int)
71 ENGINE=ndb COMMENT="NDB_TABLE=NOLOGGING=1,READ_BACKUP=0";
72 ALTER TABLE t30 ALGORITHM=inplace,
73 COMMENT="NDB_TABLE=FULLY_REPLICATED=0";
74 ALTER TABLE t30 ALGORITHM=COPY,
75 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
76 SHOW CREATE TABLE t30;
77 Table Create Table
78 t30 CREATE TABLE `t30` (
79 `col1` int NOT NULL,
80 `col2` int DEFAULT NULL,
81 PRIMARY KEY (`col1`)
82 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1,READ_BACKUP=0,FULLY_REPLICATED=0,PARTITION_BALANCE=FOR_RA_BY_NODE'
83
84 # Cannot alter fully replicated tables when read backup=0 #
85 # is specified by the user #
86 ALTER TABLE t30 ALGORITHM=inplace, COMMENT="NDB_TABLE=FULLY_REPLICATED=1";
87 ERROR 0A000: Alter table is not supported. Reason: READ_BACKUP=0 cannot be used for fully replicated tables. Try either 'set global ndb_read_backup=1;' or add READ_BACKUP=1 to comment.
88 ALTER TABLE t30 ALGORITHM=copy, COMMENT="NDB_TABLE=FULLY_REPLICATED=1";
89 ERROR 0A000: Alter table is not supported. Reason: READ_BACKUP=0 cannot be used for fully replicated tables. Try either 'set global ndb_read_backup=1;' or add READ_BACKUP=1 to comment.
90 drop table t30;
91
92 # Even though a table is created with default #
93 # ndb_read backup=0, changing FR to 1 #
94 # will change it implicitly to 1, but will not be shown #
95 set global ndb_read_backup=0;
96 CREATE TABLE t30 (col1 int PRIMARY KEY, col2 int) ENGINE=ndb
97 COMMENT="NDB_TABLE=NOLOGGING=1";
98 ALTER TABLE t30 ALGORITHM=copy, COMMENT="NDB_TABLE=FULLY_REPLICATED=1";
99 SHOW CREATE TABLE t30;
100 Table Create Table
101 t30 CREATE TABLE `t30` (
102 `col1` int NOT NULL,
103 `col2` int DEFAULT NULL,
104 PRIMARY KEY (`col1`)
105 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1,FULLY_REPLICATED=1'
106 # Cannot change read_backup to 0 when FR is on #
107 ALTER TABLE t30 algorithm=copy, comment="NDB_TABLE=READ_BACKUP=0";
108 ERROR 0A000: Alter table is not supported. Reason: cannot change READ_BACKUP to 0 for fully replicated tables. Try fully_replicated=0 if that is the intention.
109 drop table t30;
110 set global ndb_read_backup=1;
111
112 # Cannot change logging with inplace #
113 CREATE TABLE t30 (col1 int PRIMARY KEY, col2 int)
114 ENGINE=ndb;
115 ALTER TABLE t30 ALGORITHM=inplace, COMMENT="NDB_TABLE=NOLOGGING=1";
116 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter NOLOGGING inplace. Try ALGORITHM=COPY.
117 ALTER TABLE t30 ALGORITHM=copy, COMMENT="NDB_TABLE=NOLOGGING=1";
118 SHOW CREATE TABLE t30;
119 Table Create Table
120 t30 CREATE TABLE `t30` (
121 `col1` int NOT NULL,
122 `col2` int DEFAULT NULL,
123 PRIMARY KEY (`col1`)
124 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1'
125
126 # Execute some more alters #
127 ALTER TABLE t30 ALGORITHM=COPY,
128 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
129 ALTER TABLE t30 ALGORITHM=COPY, COMMENT="NDB_TABLE=FULLY_REPLICATED=0";
130 SHOW CREATE TABLE t30;
131 Table Create Table
132 t30 CREATE TABLE `t30` (
133 `col1` int NOT NULL,
134 `col2` int DEFAULT NULL,
135 PRIMARY KEY (`col1`)
136 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=NOLOGGING=1,FULLY_REPLICATED=0,PARTITION_BALANCE=FOR_RA_BY_LDM'
137 drop table t30;
138
139 # Check if changing the comment gives assertion error on fullyreplicated table#
140 set global ndb_fully_replicated=1;
141 set global ndb_read_backup=1;
142 CREATE TABLE t (x int primary key)
143 ENGINE=ndbcluster;
144 SHOW CREATE TABLE t;
145 Table Create Table
146 t CREATE TABLE `t` (
147 `x` int NOT NULL,
148 PRIMARY KEY (`x`)
149 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
150 ALTER TABLE t ALGORITHM=inplace, comment="A comment";
151 SHOW CREATE TABLE t;
152 Table Create Table
153 t CREATE TABLE `t` (
154 `x` int NOT NULL,
155 PRIMARY KEY (`x`)
156 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='A comment'
157
158 # Perform an alter of an already existing NDB table option
159 ALTER TABLE t ALGORITHM=inplace, comment="NDB_TABLE=READ_BACKUP=1";
160 SHOW CREATE TABLE t;
161 Table Create Table
162 t CREATE TABLE `t` (
163 `x` int NOT NULL,
164 PRIMARY KEY (`x`)
165 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=READ_BACKUP=1'
166 DROP TABLE t;
167
168 # Change PB to the existing value
169 CREATE TABLE t (a int primary key)
170 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM" engine=ndb;
171 ALTER TABLE t ALGORITHM=inplace,
172 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM";
173 SHOW CREATE TABLE t;
174 Table Create Table
175 t CREATE TABLE `t` (
176 `a` int NOT NULL,
177 PRIMARY KEY (`a`)
178 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='NDB_TABLE=PARTITION_BALANCE=FOR_RP_BY_LDM'
179
180 # Changing PB to a new value should fail for inplace alter#
181 # and pass for copy alter#
182 ALTER TABLE t ALGORITHM=inplace,
183 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
184 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Changing PARTITION_BALANCE with FULLY_REPLICATED on. Try ALGORITHM=COPY.
185 ALTER TABLE t ALGORITHM=copy,
186 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
187 DROP TABLE t;
188 set global ndb_fully_replicated=@global_fully_replicated;
189 set global ndb_read_backup=@global_read_backup;
190 #
191 # BUG#31776392 ALTER TABLE COMMENT HITS ASSERT IN DEBUG BUILD
192 # Turning off NOLOGGING is not supported inplace, check that
193 # a message with reason why it's not supported is returned
194 #
195 CREATE TABLE t1 (a int PRIMARY KEY) engine=ndbcluster
196 COMMENT="NDB_TABLE=NOLOGGING=1,READ_BACKUP=0";
197 ALTER TABLE t1 ALGORITHM=inplace, COMMENT="NDB_TABLE=NOLOGGING=0";
198 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter NOLOGGING inplace. Try ALGORITHM=COPY.
199 ALTER TABLE t1 COMMENT="NDB_TABLE=NOLOGGING=0";
200 DROP TABLE t1;
201 #
202 # Test CREATE and ALTER TABLE with unknown PARTITION_BALANCE value
203 # - will push warning an silently ignore the modifier
204 #
205 CREATE TABLE t1 (a int PRIMARY KEY) engine=ndbcluster
206 COMMENT="NDB_TABLE=PARTITION_BALANCE=unknown_part_bal";
207 Warnings:
208 Warning 1296 Got error 4500 'Comment contains non-supported fragment count type' from NDB
209 # NOTE! Double warnings from the double parse of comment
210 ALTER TABLE t1 ALGORITHM=inplace,
211 COMMENT="NDB_TABLE=PARTITION_BALANCE=unknown_part_bal";
212 Warnings:
213 Warning 1296 Got error 4500 'Comment contains non-supported fragment count type' from NDB
214 Warning 1296 Got error 4500 'Comment contains non-supported fragment count type' from NDB
215 ALTER TABLE t1 ALGORITHM=copy,
216 COMMENT="NDB_TABLE=PARTITION_BALANCE=unknown_part_bal";
217 Warnings:
218 Warning 1296 Got error 4500 'Comment contains non-supported fragment count type' from NDB
219 DROP TABLE t1;