Debian Codegraph @

mysql-test/suite/innodb/r/innodb-alter-varchar-debug.result

raw
1 SET DEBUG_SYNC = 'RESET';
2 #
3 # Bug#15863023 SUPPORT IN-PLACE EXTENSION OF VARCHAR COLUMN
4 #
5 create table t1 (a SERIAL PRIMARY KEY, c varchar(2) UNIQUE) ENGINE=InnoDB;
6 INSERT INTO t1 (c) VALUES('a'),('bb');
7 SET DEBUG = '+d,ib_rename_column_error';
8 affected rows: 0
9 ALTER TABLE t1 CHANGE c b varchar(2), drop index c, add index (b,a);
10 ERROR HY000: The table 't1' is full
11 SET DEBUG = '-d,ib_rename_column_error';
12 affected rows: 0
13 ALTER TABLE t1 CHANGE c b varchar(2), drop index c, add index (b,a);
14 affected rows: 0
15 info: Records: 0 Duplicates: 0 Warnings: 0
16 SET DEBUG = '+d,ib_resize_column_error';
17 affected rows: 0
18 ALTER TABLE t1 CHANGE b fail varchar(4);
19 ERROR HY000: The table 't1' is full
20 SET DEBUG = '-d,ib_resize_column_error';
21 affected rows: 0
22 ALTER TABLE t1 CHANGE b c varchar(4);
23 affected rows: 0
24 info: Records: 0 Duplicates: 0 Warnings: 0
25 INSERT IGNORE INTO t1 (c) VALUES('eeeee'),('ffffff');
26 Warnings:
27 Warning 1265 Data truncated for column 'c' at row 1
28 Warning 1265 Data truncated for column 'c' at row 2
29 INSERT INTO t1 (c) VALUES('ccc'),('dddd');
30 SELECT * FROM t1 ORDER BY c;
31 a c
32 1 a
33 2 bb
34 5 ccc
35 6 dddd
36 3 eeee
37 4 ffff
38 DROP TABLE t1;
39 # Bug#15863023 - case1
40 SET DEBUG_SYNC = 'RESET';
41 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(254) charset latin1) ENGINE = Innodb;
42 INSERT IGNORE INTO t1 (b) VALUES (repeat('a',254)),(repeat('b',254)),(repeat('c',300));
43 Warnings:
44 Warning 1265 Data truncated for column 'b' at row 3
45 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
46 a LEFT(b,10) LENGTH(b) = 254
47 1 aaaaaaaaaa 1
48 2 bbbbbbbbbb 1
49 3 cccccccccc 1
50 SET DEBUG_SYNC = 'innodb_commit_inplace_alter_table_enter SIGNAL s1 WAIT_FOR s2';
51 ALTER TABLE t1 CHANGE b b varchar(255) charset latin1;;
52 SET lock_wait_timeout = 1;
53 SET DEBUG_SYNC='now WAIT_FOR s1';
54 INSERT INTO t1 (b) VALUES (repeat('d',300));
55 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
56 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
57 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
58 UPDATE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
59 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
60 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
61 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
62 DELETE FROM t1 WHERE b = repeat('y',254);
63 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
64 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
65 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
66 SET DEBUG_SYNC='now SIGNAL s2';
67 SHOW CREATE TABLE t1;
68 Table Create Table
69 t1 CREATE TABLE `t1` (
70 `a` int NOT NULL AUTO_INCREMENT,
71 `b` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
72 PRIMARY KEY (`a`)
73 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
74 INSERT IGNORE INTO t1 (b) VALUES (repeat('d',300));
75 Warnings:
76 Warning 1265 Data truncated for column 'b' at row 1
77 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
78 a LEFT(b,10) LENGTH(b) = 254
79 1 aaaaaaaaaa 1
80 2 bbbbbbbbbb 1
81 3 cccccccccc 1
82 4 dddddddddd 0
83 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
84 Warnings:
85 Warning 1265 Data truncated for column 'b' at row 1
86 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
87 a LEFT(b,10) LENGTH(b) = 254
88 1 yyyyyyyyyy 0
89 2 bbbbbbbbbb 1
90 3 cccccccccc 1
91 4 dddddddddd 0
92 DELETE FROM t1 WHERE b = repeat('y',255);
93 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
94 a LEFT(b,10) LENGTH(b) = 254
95 2 bbbbbbbbbb 1
96 3 cccccccccc 1
97 4 dddddddddd 0
98 DROP TABLE t1;
99 # Bug#15863023 - case2 . enlarge varchar with add.drop index/column
100 SET DEBUG_SYNC = 'RESET';
101 CREATE TABLE t1 (a INT , b VARCHAR(254) charset latin1) ENGINE=Innodb;
102 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
103 Warnings:
104 Warning 1265 Data truncated for column 'b' at row 3
105 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
106 a LEFT(b,10) LENGTH(b) = 254
107 1 aaaaaaaaaa 1
108 2 bbbbbbbbbb 1
109 3 cccccccccc 1
110 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2';
111 ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(255) charset latin1 ,ALGORITHM=INPLACE;;
112 SET DEBUG_SYNC='now WAIT_FOR s1';
113 INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300));
114 Warnings:
115 Warning 1265 Data truncated for column 'b' at row 1
116 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
117 a LEFT(b,10) LENGTH(b) = 254
118 1 aaaaaaaaaa 1
119 2 bbbbbbbbbb 1
120 3 cccccccccc 1
121 4 dddddddddd 1
122 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
123 Warnings:
124 Warning 1265 Data truncated for column 'b' at row 1
125 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
126 a LEFT(b,10) LENGTH(b) = 254
127 1 yyyyyyyyyy 1
128 2 bbbbbbbbbb 1
129 3 cccccccccc 1
130 4 dddddddddd 1
131 DELETE FROM t1 WHERE b = repeat('y',254);
132 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
133 a LEFT(b,10) LENGTH(b) = 254
134 2 bbbbbbbbbb 1
135 3 cccccccccc 1
136 4 dddddddddd 1
137 SET DEBUG_SYNC='now SIGNAL s2';
138 SHOW CREATE TABLE t1;
139 Table Create Table
140 t1 CREATE TABLE `t1` (
141 `a` int DEFAULT NULL,
142 `b` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
143 KEY `idx` (`b`)
144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 INSERT IGNORE INTO t1 (a,b) VALUES (5,repeat('d',300));
146 Warnings:
147 Warning 1265 Data truncated for column 'b' at row 1
148 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
149 a LEFT(b,10) LENGTH(b) = 254
150 2 bbbbbbbbbb 1
151 3 cccccccccc 1
152 4 dddddddddd 1
153 5 dddddddddd 0
154 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ;
155 Warnings:
156 Warning 1265 Data truncated for column 'b' at row 1
157 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
158 a LEFT(b,10) LENGTH(b) = 254
159 2 yyyyyyyyyy 0
160 3 cccccccccc 1
161 4 dddddddddd 1
162 5 dddddddddd 0
163 DELETE FROM t1 WHERE b = repeat('y',255);
164 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
165 a LEFT(b,10) LENGTH(b) = 254
166 3 cccccccccc 1
167 4 dddddddddd 1
168 5 dddddddddd 0
169 DROP TABLE t1;
170 # Bug#15863023 - case3. enlarge varchar other alter operations+charset
171 SET DEBUG_SYNC = 'RESET';
172 CREATE TABLE t1 (a INT DEFAULT 100 , b VARCHAR(254) CHARACTER SET latin1,
173 c VARCHAR(254) CHARACTER SET utf8) ENGINE=Innodb CHARSET=utf8;
174 Warnings:
175 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
176 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
177 CREATE INDEX c1 ON t1(c);
178 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
179 Warnings:
180 Warning 1265 Data truncated for column 'b' at row 3
181 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
182 a LEFT(b,10) LENGTH(b) = 254
183 1 aaaaaaaaaa 1
184 2 bbbbbbbbbb 1
185 3 cccccccccc 1
186 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2';
187 ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(255) CHARACTER SET latin1,CHANGE c c varchar(255) CHARACTER SET utf8,DROP INDEX c1 ,ALGORITHM=INPLACE;;
188 SET DEBUG_SYNC='now WAIT_FOR s1';
189 INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300));
190 Warnings:
191 Warning 1265 Data truncated for column 'b' at row 1
192 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
193 a LEFT(b,10) LENGTH(b) = 254 c
194 1 aaaaaaaaaa 1 NULL
195 2 bbbbbbbbbb 1 NULL
196 3 cccccccccc 1 NULL
197 4 dddddddddd 1 NULL
198 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
199 Warnings:
200 Warning 1265 Data truncated for column 'b' at row 1
201 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
202 a LEFT(b,10) LENGTH(b) = 254 c
203 1 yyyyyyyyyy 1 NULL
204 2 bbbbbbbbbb 1 NULL
205 3 cccccccccc 1 NULL
206 4 dddddddddd 1 NULL
207 DELETE FROM t1 WHERE b = repeat('y',254);
208 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
209 a LEFT(b,10) LENGTH(b) = 254 c
210 2 bbbbbbbbbb 1 NULL
211 3 cccccccccc 1 NULL
212 4 dddddddddd 1 NULL
213 INSERT IGNORE INTO t1 (b,c) VALUES (repeat('z',300),"Môžem");
214 Warnings:
215 Warning 1265 Data truncated for column 'b' at row 1
216 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
217 a LEFT(b,10) LENGTH(b) = 254 c
218 2 bbbbbbbbbb 1 NULL
219 3 cccccccccc 1 NULL
220 4 dddddddddd 1 NULL
221 100 zzzzzzzzzz 1 Môžem
222 SET DEBUG_SYNC='now SIGNAL s2';
223 Warnings:
224 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
225 SHOW CREATE TABLE t1;
226 Table Create Table
227 t1 CREATE TABLE `t1` (
228 `a` int DEFAULT '100',
229 `b` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
230 `c` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
231 KEY `idx` (`b`)
232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
233 INSERT IGNORE INTO t1 (a,b,c) VALUES (5,repeat('d',300),"Môžem");
234 Warnings:
235 Warning 1265 Data truncated for column 'b' at row 1
236 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
237 a LEFT(b,10) LENGTH(b) = 254 c
238 2 bbbbbbbbbb 1 NULL
239 3 cccccccccc 1 NULL
240 4 dddddddddd 1 NULL
241 5 dddddddddd 0 Môžem
242 100 zzzzzzzzzz 1 Môžem
243 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ;
244 Warnings:
245 Warning 1265 Data truncated for column 'b' at row 1
246 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
247 a LEFT(b,10) LENGTH(b) = 254 c
248 2 yyyyyyyyyy 0 NULL
249 3 cccccccccc 1 NULL
250 4 dddddddddd 1 NULL
251 5 dddddddddd 0 Môžem
252 100 zzzzzzzzzz 1 Môžem
253 DELETE FROM t1 WHERE b = repeat('y',255);
254 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
255 a LEFT(b,10) LENGTH(b) = 254 c
256 3 cccccccccc 1 NULL
257 4 dddddddddd 1 NULL
258 5 dddddddddd 0 Môžem
259 100 zzzzzzzzzz 1 Môžem
260 DROP TABLE t1;
261 # Bug#15863023 - case4. enlarge varchar other alter operations runs copy
262 # if length is more than 255
263 SET DEBUG_SYNC = 'RESET';
264 CREATE TABLE t1 (a INT DEFAULT 100 , b VARCHAR(254) CHARACTER SET latin1,
265 c VARCHAR(254) CHARACTER SET utf8, d CHAR(10) DEFAULT 'test') ENGINE=Innodb CHARSET=utf8;
266 Warnings:
267 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
268 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
269 CREATE INDEX c1 ON t1(c);
270 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
271 Warnings:
272 Warning 1265 Data truncated for column 'b' at row 3
273 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
274 a LEFT(b,10) LENGTH(b) = 254
275 1 aaaaaaaaaa 1
276 2 bbbbbbbbbb 1
277 3 cccccccccc 1
278 ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(256) CHARACTER SET latin1,CHANGE c c varchar(256) CHARACTER SET utf8,DROP INDEX c1 ,ALGORITHM=INPLACE;
279 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
280 ALTER TABLE t1 ADD INDEX idx(b),CHANGE d d char(100) ,ALGORITHM=INPLACE;
281 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
282 ALTER TABLE t1 ADD INDEX idx1(b),CHANGE d d VARCHAR(100) ,ALGORITHM=INPLACE;
283 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
284 ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(355) CHARACTER SET latin1,CHANGE c c varchar(255) CHARACTER SET utf8,DROP INDEX c1 ;
285 Warnings:
286 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
287 DROP TABLE t1;
288 # Bug#15863023 - case6 enlarge varchar other
289 # alter operations with fk on parent
290 SET DEBUG_SYNC = 'RESET';
291 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(254) charset latin1, KEY(b)) ENGINE=Innodb;
292 CREATE TABLE t2 (a2 INT , b2 VARCHAR(254) charset latin1, CONSTRAINT fk FOREIGN KEY (a2) REFERENCES t1 (a) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=Innodb;
293 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
294 Warnings:
295 Warning 1265 Data truncated for column 'b' at row 3
296 INSERT IGNORE INTO t2 (a2,b2) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
297 Warnings:
298 Warning 1265 Data truncated for column 'b2' at row 3
299 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1,t2 WHERE t1.a = t2.a2 ORDER BY a;
300 a LEFT(b,10) LENGTH(b) = 254
301 1 aaaaaaaaaa 1
302 2 bbbbbbbbbb 1
303 3 cccccccccc 1
304 ALTER TABLE t2 CHANGE b2 b2 varchar(255) charset latin1, DROP FOREIGN KEY fk,ADD CONSTRAINT fk FOREIGN KEY (b2) REFERENCES t1 (b),ADD INDEX idx(a2) ,ALGORITHM=INPLACE;
305 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.
306 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2';
307 ALTER TABLE t1 ADD INDEX idx(a),CHANGE b b varchar(255) charset latin1, ALGORITHM=INPLACE;;
308 SET DEBUG_SYNC='now WAIT_FOR s1';
309 INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300));
310 Warnings:
311 Warning 1265 Data truncated for column 'b' at row 1
312 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
313 a LEFT(b,10) LENGTH(b) = 254
314 1 aaaaaaaaaa 1
315 2 bbbbbbbbbb 1
316 3 cccccccccc 1
317 4 dddddddddd 1
318 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
319 Warnings:
320 Warning 1265 Data truncated for column 'b' at row 1
321 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
322 a LEFT(b,10) LENGTH(b) = 254
323 1 yyyyyyyyyy 1
324 2 bbbbbbbbbb 1
325 3 cccccccccc 1
326 4 dddddddddd 1
327 DELETE FROM t1 WHERE b = repeat('y',254);
328 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
329 a LEFT(b,10) LENGTH(b) = 254
330 2 bbbbbbbbbb 1
331 3 cccccccccc 1
332 4 dddddddddd 1
333 SET DEBUG_SYNC='now SIGNAL s2';
334 SET foreign_key_checks = 1;
335 SHOW CREATE TABLE t1;
336 Table Create Table
337 t1 CREATE TABLE `t1` (
338 `a` int NOT NULL,
339 `b` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
340 PRIMARY KEY (`a`),
341 KEY `b` (`b`),
342 KEY `idx` (`a`)
343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
344 INSERT IGNORE INTO t1 (a,b) VALUES (5,repeat('k',300));
345 Warnings:
346 Warning 1265 Data truncated for column 'b' at row 1
347 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
348 a LEFT(b,10) LENGTH(b) = 254
349 2 bbbbbbbbbb 1
350 3 cccccccccc 1
351 4 dddddddddd 1
352 5 kkkkkkkkkk 0
353 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ;
354 Warnings:
355 Warning 1265 Data truncated for column 'b' at row 1
356 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
357 a LEFT(b,10) LENGTH(b) = 254
358 2 yyyyyyyyyy 0
359 3 cccccccccc 1
360 4 dddddddddd 1
361 5 kkkkkkkkkk 0
362 DELETE FROM t1 WHERE b = repeat('y',255);
363 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
364 a LEFT(b,10) LENGTH(b) = 254
365 3 cccccccccc 1
366 4 dddddddddd 1
367 5 kkkkkkkkkk 0
368 DROP TABLE t2,t1;
369 SET DEBUG_SYNC = 'RESET';