Debian Codegraph @

mysql-test/suite/innodb/r/innodb-index-debug.result

raw
1 CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1)) Engine=InnoDB;
2 SHOW CREATE TABLE t1;
3 Table Create Table
4 t1 CREATE TABLE `t1` (
5 `c1` int NOT NULL,
6 `c2` int DEFAULT NULL,
7 PRIMARY KEY (`c1`)
8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
9 INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
10 SET SESSION DEBUG='+d,ib_build_indexes_too_many_concurrent_trxs, ib_rename_indexes_too_many_concurrent_trxs, ib_drop_index_too_many_concurrent_trxs';
11 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
12 ERROR HY000: Too many active concurrent transactions
13 SET SESSION DEBUG=DEFAULT;
14 SHOW CREATE TABLE t1;
15 Table Create Table
16 t1 CREATE TABLE `t1` (
17 `c1` int NOT NULL,
18 `c2` int DEFAULT NULL,
19 PRIMARY KEY (`c1`)
20 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
21 DROP TABLE t1;
22 CREATE TABLE bug13861218 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX(c2))
23 ENGINE=InnoDB;
24 INSERT INTO bug13861218 VALUES (8, 0), (4, 0), (0, 0);
25 SET DEBUG='+d,ddl_buf_add_two';
26 CREATE UNIQUE INDEX ui ON bug13861218(c1);
27 SET DEBUG='-d,ddl_buf_add_two';
28 DROP TABLE bug13861218;
29 CREATE TABLE bug13861218 (c1 INT NOT NULL, c2 INT UNIQUE) ENGINE=InnoDB;
30 INSERT INTO bug13861218 VALUES (8, NULL), (4, NULL), (0, NULL);
31 SET DEBUG='+d,ddl_buf_add_two';
32 CREATE UNIQUE INDEX ui ON bug13861218(c1);
33 SET DEBUG='-d,ddl_buf_add_two';
34 DROP TABLE bug13861218;
35 #
36 # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
37 #
38 create table t480(a serial)engine=innodb;
39 insert into t480
40 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
41 (),(),(),(),(),(),(),();
42 insert into t480 select 0 from t480;
43 insert into t480 select 0 from t480;
44 insert into t480 select 0 from t480;
45 insert into t480 select 0 from t480;
46 create table t1(f1 int auto_increment not null,
47 f2 char(200) not null, f3 char(200) not null,
48 primary key(f1,f2,f3), key(f1))engine=innodb charset latin1;
49 insert into t1 select NULL,'aaa','bbb' from t480;
50 insert into t1 select NULL,'aaaa','bbbb' from t480;
51 insert into t1 select NULL,'aaaaa','bbbbb' from t480;
52 insert into t1 select NULL,'aaaaaa','bbbbbb' from t480;
53 SET DEBUG = '+d,ddl_write_failure';
54 alter table t1 drop primary key,add primary key(f2,f1);
55 ERROR HY000: Temporary file write failure.
56 SET DEBUG = '-d,ddl_write_failure';
57 drop table t1;
58 create table t1(k1 int auto_increment primary key,
59 k2 char(200),k3 char(200))engine=innodb;
60 insert into t1 values(NULL,'a','b'), (NULL,'aa','bb');
61 SET DEBUG_SYNC= 'ddl_after_scan
62 SIGNAL opened WAIT_FOR flushed';
63 optimize table t1;
64 connection default
65 SET DEBUG_SYNC= 'now WAIT_FOR opened';
66 INSERT INTO t1 select NULL,'aaa','bbb' from t480;
67 SET DEBUG_SYNC= 'now SIGNAL flushed';
68 /*con1 reap*/ Optimize table t1;
69 Table Op Msg_type Msg_text
70 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
71 test.t1 optimize status OK
72 affected rows: 2
73 SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
74 COUNT(k1) k2 k3
75 1 a b
76 1 aa bb
77 480 aaa bbb
78 drop table t1;
79 create table t1(k1 int auto_increment primary key,
80 k2 char(200),k3 char(200))engine=innodb;
81 SET DEBUG_SYNC= 'ddl_after_scan
82 SIGNAL opened WAIT_FOR flushed';
83 ALTER TABLE t1 ADD COLUMN k4 int, ALGORITHM=INPLACE;
84 connection default
85 SET DEBUG_SYNC= 'now WAIT_FOR opened';
86 SET debug = '+d,row_log_tmpfile_fail';
87 INSERT INTO t1 select NULL,'aaa','bbb' from t480;
88 INSERT INTO t1 select NULL,'aaaa','bbbb' from t480;
89 SET DEBUG_SYNC= 'now SIGNAL flushed';
90 SET debug = '-d,row_log_tmpfile_fail';
91 /*con1 reap*/ ALTER TABLE t1 ADD COLUMN k4 int, ALGORITHM=INPLACE;
92 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
93 SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
94 COUNT(k1) k2 k3
95 480 aaa bbb
96 480 aaaa bbbb
97 show create table t1;
98 Table Create Table
99 t1 CREATE TABLE `t1` (
100 `k1` int NOT NULL AUTO_INCREMENT,
101 `k2` char(200) DEFAULT NULL,
102 `k3` char(200) DEFAULT NULL,
103 PRIMARY KEY (`k1`)
104 ) ENGINE=InnoDB AUTO_INCREMENT=1023 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
105 SET DEBUG_SYNC='RESET';
106 # SDI output shouldn't show new column "k4" because
107 # ALTER failed
108 # Remove json files
109 # restart
110 drop table t1;
111 drop table t480;
112 SET DEBUG_SYNC='RESET';
113 #
114 # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
115 # LAST COLUMN OF OLD PK
116 #
117 SET debug="+d,innodb_alter_table_pk_assert_no_sort";
118 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
119 insert into t1 values('abd'), ('acd');
120 alter table t1 drop primary key, add primary key(o1(3)), lock=none;
121 drop table t1;
122 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
123 insert into t1 values('abd'), ('acd');
124 alter table t1 drop primary key, add primary key(o1), lock=none;
125 drop table t1;
126 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
127 insert into t1 values('abd'), ('acd');
128 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
129 drop table t1;
130 create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
131 insert into t1 values('abd'), ('acd');
132 alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
133 drop table t1;
134 create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
135 insert into t1 values('abd', 1), ('acd', 2);
136 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
137 drop table t1;
138 create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
139 insert into t1 values('abd', 1), ('acd', 2);
140 alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
141 drop table t1;
142 create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
143 insert into t1 values('abd'), ('acd');
144 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
145 drop table t1;
146 create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
147 insert into t1 values('abd'), ('acd');
148 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
149 drop table t1;
150 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
151 insert into t1 values('abd', 1), ('acd', 2);
152 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
153 drop table t1;
154 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
155 insert into t1 values('abd', 1), ('acd', 2);
156 alter table t1 drop primary key, add primary key(o1(2)), lock=none;
157 drop table t1;
158 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
159 insert into t1 values('abd', 1), ('acd', 2);
160 alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
161 drop table t1;
162 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
163 insert into t1 values('abd', 1), ('acd', 2);
164 alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
165 drop table t1;
166 create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
167 insert into t1 values('abd', 1), ('acd', 2);
168 alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
169 drop table t1;
170 create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
171 insert into t1 values('abd', 1), ('acd', 2);
172 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
173 drop table t1;
174 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
175 insert into t1 values(1,'abd'), (2,'acd');
176 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
177 drop table t1;
178 create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
179 insert into t1 values(1,'abd'), (2,'acd');
180 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
181 drop table t1;
182 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
183 insert into t1 values(1, 'abd'), (2, 'acd');
184 alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
185 drop table t1;
186 create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
187 insert into t1 values(1, 'abd'), (2, 'acd');
188 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
189 drop table t1;
190 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
191 insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
192 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
193 drop table t1;
194 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
195 insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
196 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
197 drop table t1;
198 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
199 insert into t1 values('abd', 'acd'), ('acd', 'abd');
200 alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
201 drop table t1;
202 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
203 insert into t1 values('abd', 'acd'), ('acd', 'abd');
204 alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
205 drop table t1;
206 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
207 insert into t1 values('abd', 'acd'), ('acd', 'abd');
208 alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
209 drop table t1;
210 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
211 insert into t1 values('abd', 'acd'), ('acd', 'abd');
212 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
213 drop table t1;
214 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
215 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
216 alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
217 drop table t1;
218 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
219 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
220 alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
221 drop table t1;
222 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
223 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
224 alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
225 drop table t1;
226 create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
227 insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
228 alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
229 drop table t1;
230 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
231 insert into t1 values(1,1),(2,2);
232 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
233 drop table t1;
234 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
235 insert into t1 values(1,1),(2,2);
236 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
237 drop table t1;
238 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
239 insert into t1 values(1,1),(2,2);
240 alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
241 drop table t1;
242 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
243 insert into t1 values(1,1),(2,2);
244 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
245 drop table t1;
246 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
247 insert into t1 values(1,1),(2,2);
248 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
249 drop table t1;
250 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
251 insert into t1 values(1,1),(2,2);
252 alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
253 drop table t1;
254 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
255 insert into t1 values(1,1),(2,2);
256 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
257 drop table t1;
258 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
259 insert into t1 values(1,1),(2,2);
260 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
261 drop table t1;
262 create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
263 insert into t1 values(1,1),(2,2);
264 alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
265 drop table t1;
266 create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
267 insert into t1 values(1,1,2),(2,2,1);
268 alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
269 drop table t1;
270 create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
271 insert into t1 values(1,1,2),(2,2,1);
272 alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
273 drop table t1;
274 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
275 insert into t1 values(1,1,2),(2,2,1);
276 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
277 drop table t1;
278 create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
279 insert into t1 values(1,1,2,2),(2,2,1,1);
280 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
281 drop table t1;
282 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
283 insert into t1 values(1,1,2),(2,2,1);
284 alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
285 drop table t1;
286 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
287 insert into t1 values(1,1,2),(2,2,1);
288 alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
289 drop table t1;
290 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
291 insert into t1 values(1,1,2),(2,2,1);
292 alter table t1 drop primary key, add primary key(o1), lock=none;
293 drop table t1;
294 CREATE TABLE t1(o1 int, o2 int, o3 int, PRIMARY KEY(o1), KEY(o2)) ENGINE=INNODB;
295 INSERT INTO t1 VALUES(1,1,2),(2,2,1);
296 EXPLAIN SELECT * FROM t1 ORDER BY o2;
297 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
298 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
299 Warnings:
300 Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
301 SELECT * FROM t1 ORDER BY o2;
302 o1 o2 o3
303 1 1 2
304 2 2 1
305 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 DESC);
306 CHECK TABLE t1;
307 Table Op Msg_type Msg_text
308 test.t1 check status OK
309 SELECT * FROM t1;
310 o1 o2 o3
311 1 1 2
312 2 2 1
313 EXPLAIN SELECT * FROM t1 ORDER BY o2;
314 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
315 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
316 Warnings:
317 Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
318 SELECT * FROM t1 ORDER BY o2;
319 o1 o2 o3
320 1 1 2
321 2 2 1
322 ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 ASC);
323 CHECK TABLE t1;
324 Table Op Msg_type Msg_text
325 test.t1 check status OK
326 SELECT * FROM t1;
327 o1 o2 o3
328 1 1 2
329 2 2 1
330 EXPLAIN SELECT * FROM t1 ORDER BY o2;
331 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
332 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
333 Warnings:
334 Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
335 SELECT * FROM t1 ORDER BY o2;
336 o1 o2 o3
337 1 1 2
338 2 2 1
339 DROP TABLE t1;
340 SET debug="-d,innodb_alter_table_pk_assert_no_sort";