Debian Codegraph @

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

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