Debian Codegraph @

mysql-test/suite/innodb/r/innodb-table-online.result

raw
1 SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table;
2 SET GLOBAL innodb_file_per_table = on;
3 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 TEXT NOT NULL)
4 ENGINE = InnoDB;
5 INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
6 SET GLOBAL innodb_monitor_enable = module_ddl;
7 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
8 subsystem = 'ddl' and name not in
9 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
10 name count
11 SET DEBUG_SYNC = 'RESET';
12 SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
13 INSERT INTO t1 VALUES(1,2,3);
14 # Establish session con1 (user=root)
15 SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
16 SET lock_wait_timeout = 1;
17 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
18 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
19 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
20 # session default
21 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
22 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
23 subsystem = 'ddl' and name not in
24 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
25 name count
26 # session con1
27 SET SESSION DEBUG = '+d,innodb_OOM_prepare_inplace_alter';
28 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
29 ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
30 SET SESSION DEBUG = '-d,innodb_OOM_prepare_inplace_alter';
31 SET SESSION DEBUG = '+d,innodb_OOM_inplace_alter';
32 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
33 ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
34 SET SESSION DEBUG = '-d,innodb_OOM_inplace_alter';
35 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
36 # session default
37 SHOW CREATE TABLE t1;
38 Table Create Table
39 t1 CREATE TABLE `t1` (
40 `c1` int NOT NULL,
41 `c2` int NOT NULL,
42 `c3` text NOT NULL,
43 PRIMARY KEY (`c1`)
44 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
45 BEGIN;
46 INSERT INTO t1 VALUES(7,4,2);
47 # session con1
48 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
49 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
50 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
51 # session default
52 COMMIT;
53 # session con1
54 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
55 ERROR 23000: Duplicate entry '4' for key 't1.c2'
56 # session default
57 DELETE FROM t1 WHERE c1 = 7;
58 # session con1
59 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ROW_FORMAT=COMPACT,
60 LOCK = SHARED, ALGORITHM = INPLACE;
61 ALTER TABLE t1 ADD UNIQUE INDEX(c2),
62 LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
63 Warnings:
64 Warning 1831 Duplicate index 'c2_2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
65 SHOW CREATE TABLE t1;
66 Table Create Table
67 t1 CREATE TABLE `t1` (
68 `c1` int NOT NULL,
69 `c2` int NOT NULL,
70 `c3` text NOT NULL,
71 UNIQUE KEY `c2` (`c2`),
72 UNIQUE KEY `c2_2` (`c2`)
73 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
74 ALTER TABLE t1 DROP INDEX c2, ALGORITHM = INPLACE;
75 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.
76 SHOW CREATE TABLE t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `c1` int NOT NULL,
80 `c2` int NOT NULL,
81 `c3` text NOT NULL,
82 UNIQUE KEY `c2` (`c2`),
83 UNIQUE KEY `c2_2` (`c2`)
84 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
85 ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1);
86 # session default
87 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
88 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
89 subsystem = 'ddl' and name not in
90 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
91 name count
92 ddl_online_create_index 1
93 ddl_pending_alter_table 1
94 BEGIN;
95 INSERT INTO t1 VALUES(4,7,2);
96 SET DEBUG_SYNC = 'now SIGNAL insert_done';
97 # session con1
98 ERROR 23000: Duplicate entry '4' for key 't1.PRIMARY'
99 # session default
100 ROLLBACK;
101 # session con1
102 SHOW CREATE TABLE t1;
103 Table Create Table
104 t1 CREATE TABLE `t1` (
105 `c1` int NOT NULL,
106 `c2` int NOT NULL,
107 `c3` text NOT NULL,
108 UNIQUE KEY `c2` (`c2`),
109 UNIQUE KEY `c2_2` (`c2`)
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
111 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ALGORITHM = INPLACE;
112 ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
113 ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1), ALGORITHM = INPLACE;
114 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
115 subsystem = 'ddl' and name not in
116 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
117 name count
118 # session default
119 INSERT INTO t1 VALUES(6,3,1);
120 ERROR 23000: Duplicate entry '3' for key 't1.c2_2'
121 INSERT INTO t1 VALUES(7,4,2);
122 ERROR 23000: Duplicate entry '4' for key 't1.c2_2'
123 DROP INDEX c2_2 ON t1;
124 BEGIN;
125 INSERT INTO t1 VALUES(7,4,2);
126 ROLLBACK;
127 # session con1
128 KILL QUERY @id;
129 ERROR 70100: Query execution was interrupted
130 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR kill_done';
131 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
132 # session default
133 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt';
134 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
135 subsystem = 'ddl' and name not in
136 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
137 name count
138 ddl_online_create_index 1
139 ddl_pending_alter_table 1
140 KILL QUERY @id;
141 SET DEBUG_SYNC = 'now SIGNAL kill_done';
142 # session con1
143 ERROR 70100: Query execution was interrupted
144 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
145 subsystem = 'ddl' and name not in
146 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
147 name count
148 # session default
149 CHECK TABLE t1;
150 Table Op Msg_type Msg_text
151 test.t1 check status OK
152 INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
153 INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
154 INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
155 INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
156 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
157 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
158 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 80 33.33 Using where
159 Warnings:
160 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`c2` > 3)
161 ANALYZE TABLE t1;
162 Table Op Msg_type Msg_text
163 test.t1 analyze status OK
164 # session con1
165 SHOW CREATE TABLE t1;
166 Table Create Table
167 t1 CREATE TABLE `t1` (
168 `c1` int NOT NULL,
169 `c2` int NOT NULL,
170 `c3` text NOT NULL,
171 PRIMARY KEY (`c1`)
172 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
173 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt2 WAIT_FOR dml2_done';
174 SET lock_wait_timeout = 10;
175 ALTER TABLE t1 ROW_FORMAT=COMPACT, ALGORITHM = INPLACE;
176 # session default
177 INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
178 INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
179 UPDATE t1 SET c2 = c2 + 1;
180 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt2';
181 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
182 subsystem = 'ddl' and name not in
183 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
184 name count
185 ddl_online_create_index 1
186 ddl_pending_alter_table 1
187 BEGIN;
188 DELETE FROM t1;
189 ROLLBACK;
190 UPDATE t1 SET c2 = c2 + 1;
191 BEGIN;
192 UPDATE t1 SET c2 = c2 + 1;
193 DELETE FROM t1;
194 ROLLBACK;
195 BEGIN;
196 DELETE FROM t1;
197 ROLLBACK;
198 UPDATE t1 SET c2 = c2 + 1;
199 BEGIN;
200 UPDATE t1 SET c2 = c2 + 1;
201 DELETE FROM t1;
202 ROLLBACK;
203 BEGIN;
204 DELETE FROM t1;
205 ROLLBACK;
206 UPDATE t1 SET c2 = c2 + 1;
207 BEGIN;
208 UPDATE t1 SET c2 = c2 + 1;
209 DELETE FROM t1;
210 ROLLBACK;
211 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
212 subsystem = 'ddl' and name not in
213 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
214 name count
215 ddl_online_create_index 1
216 ddl_pending_alter_table 1
217 SET DEBUG_SYNC = 'now SIGNAL dml2_done';
218 # session con1
219 ERROR HY000: Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
220 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
221 subsystem = 'ddl' and name not in
222 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
223 name count
224 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt3 WAIT_FOR dml3_done';
225 ALTER TABLE t1 ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
226 ERROR 42000: Multiple primary key defined
227 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
228 ERROR 23000: Duplicate entry 'N' for key 't1.PRIMARY'
229 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c22f,c4(5)),
230 CHANGE c2 c22f INT, CHANGE c3 c3 TEXT NULL, CHANGE c1 c1 INT AFTER c22f,
231 ADD COLUMN c4 VARCHAR(6) DEFAULT 'Online';
232 # session default
233 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt3';
234 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
235 subsystem = 'ddl' and name not in
236 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
237 name count
238 ddl_online_create_index 1
239 ddl_pending_alter_table 1
240 BEGIN;
241 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 240;
242 DELETE FROM t1 WHERE c1 > 320;
243 ROLLBACK;
244 BEGIN;
245 UPDATE t1 SET c2 = c2 + 1;
246 DELETE FROM t1;
247 ROLLBACK;
248 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
249 subsystem = 'ddl' and name not in
250 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
251 name count
252 ddl_online_create_index 1
253 ddl_pending_alter_table 1
254 SET DEBUG_SYNC = 'now SIGNAL dml3_done';
255 # session con1
256 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
257 subsystem = 'ddl' and name not in
258 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
259 name count
260 SELECT COUNT(c22f) FROM t1;
261 COUNT(c22f)
262 320
263 CHECK TABLE t1;
264 Table Op Msg_type Msg_text
265 test.t1 check status OK
266 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5));
267 ERROR 23000: Duplicate entry '' for key 't1.PRIMARY'
268 UPDATE t1 SET c3 = NULL WHERE c3 = '';
269 SET lock_wait_timeout = 1;
270 ALTER TABLE t1 DROP COLUMN c22f, ADD PRIMARY KEY c3p5(c3(5));
271 ERROR 42000: Multiple primary key defined
272 ALTER TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
273 ALGORITHM = INPLACE;
274 ERROR 22004: Invalid use of NULL value
275 ALTER TABLE t1 MODIFY c3 TEXT NOT NULL;
276 ERROR 22004: Invalid use of NULL value
277 UPDATE t1 SET c3=CONCAT(c1,REPEAT('foo',c1)) WHERE c3 IS NULL;
278 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created0 WAIT_FOR ins_done0';
279 ALTER TABLE t1 MODIFY c3 TEXT NOT NULL, DROP COLUMN c22f,
280 ADD COLUMN c5 CHAR(5) DEFAULT 'tired' FIRST;
281 # session default
282 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created0';
283 BEGIN;
284 INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti','YLETV2');
285 INSERT INTO t1 VALUES(33101,347,NULL,'');
286 SET DEBUG_SYNC = 'now SIGNAL ins_done0';
287 # session con1
288 ERROR 22004: Invalid use of NULL value
289 # session default
290 ROLLBACK;
291 # session con1
292 ALTER TABLE t1 MODIFY c3 TEXT NOT NULL;
293 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created WAIT_FOR ins_done';
294 ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN c22f,
295 ADD COLUMN c6 VARCHAR(1000) DEFAULT
296 'I love tracking down hard-to-reproduce bugs.',
297 ADD PRIMARY KEY c3p5(c3(5), c6(2));
298 # session default
299 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
300 SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
301 INSERT INTO t1 VALUES(347,33101,NULL,'');
302 ERROR 23000: Column 'c3' cannot be null
303 INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti','');
304 # session con1
305 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
306 SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
307 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
308 subsystem = 'ddl' and name not in
309 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
310 name count
311 # session default
312 SELECT COUNT(*) FROM t1;
313 COUNT(*)
314 321
315 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
316 SELECT * FROM t1 LIMIT 10;
317 c22f c1 c3 c4
318 5 1 1foo Online
319 6 2 2foofoo Online
320 7 3 3foofoofoo Online
321 8 4 4foofoofoofoo Online
322 9 5 5foofoofoofoofoo Online
323 5 6 6foofoofoofoofoofoo Online
324 6 7 7foofoofoofoofoofoofoo Online
325 7 8 8foofoofoofoofoofoofoofoo Online
326 8 9 9foofoofoofoofoofoofoofoofoo Online
327 9 10 10foofoofoofoofoofoofoofoofoofoo Online
328 # session con1
329 ALTER TABLE t1 DISCARD TABLESPACE;
330 # session default
331 SHOW CREATE TABLE t1;
332 Table Create Table
333 t1 CREATE TABLE `t1` (
334 `c22f` int NOT NULL,
335 `c1` int NOT NULL,
336 `c3` text NOT NULL,
337 `c4` varchar(6) NOT NULL DEFAULT 'Online',
338 PRIMARY KEY (`c1`,`c22f`,`c4`(5))
339 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
340 DROP TABLE t1;
341 #
342 # Bug#18894337 ONLINE ALTER TABLE LOG APPLY FAILS TO SKIP LOCKING
343 #
344 # session con1
345 CREATE TABLE t1 (a SERIAL, c64 VARCHAR(64) UNIQUE) ENGINE=InnoDB;
346 INSERT INTO t1 VALUES(0, NULL);
347 SET DEBUG='+d,innodb_trx_duplicates';
348 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL t1_ddl WAIT_FOR t1_dml';
349 ALTER TABLE t1 ENGINE=InnoDB;
350 # session default
351 SET DEBUG_SYNC='now WAIT_FOR t1_ddl';
352 INSERT INTO t1 VALUES(0, NULL), (0, NULL);
353 SET DEBUG_SYNC='now SIGNAL t1_dml';
354 # session con1
355 # Disconnect session con1
356 # session default
357 DROP TABLE t1;
358 SET DEBUG_SYNC = 'RESET';
359 SET DEBUG = '';
360 SET GLOBAL innodb_monitor_disable = module_ddl;
361 SET GLOBAL DEBUG = '';
362 SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig;
363 SET GLOBAL innodb_monitor_enable = default;
364 SET GLOBAL innodb_monitor_disable = default;