Debian Codegraph @

mysql-test/suite/innodb/t/innodb-index.test

raw
1 -- source include/have_innodb.inc
2 # Embedded server tests do not support restarting.
3 -- source include/not_embedded.inc
4 --source include/test_db_charset_latin1.inc
5
6 let $MYSQLD_DATADIR= `select @@datadir`;
7
8 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
9
10 # Bug#13654923 BOGUS DEBUG ASSERTION IN INDEX CREATION FOR ZERO-LENGTH RECORD
11 create table t1(a varchar(2) primary key) engine=innodb;
12 insert into t1 values('');
13 --enable_info
14 create index t1a1 on t1(a(1));
15 --disable_info
16 drop table t1;
17
18 create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
19 insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
20 commit;
21 --error ER_DUP_KEYNAME
22 alter table t1 add index b (b), add index b (b);
23 --error ER_DUP_FIELDNAME
24 alter table t1 add index (b,b);
25 --enable_info
26 alter table t1 add index d2 (d);
27 --disable_info
28 show create table t1;
29 -- disable_result_log
30 analyze table t1;
31 -- enable_result_log
32 explain select * from t1 force index(d2) order by d;
33 select * from t1 force index (d2) order by d;
34 --error ER_DUP_ENTRY
35 alter table t1 add unique index (b);
36 show create table t1;
37 --enable_info
38 alter table t1 add index (b);
39 --disable_info
40 show create table t1;
41
42 --enable_info
43 alter table t1 add unique index (c), add index (d);
44 --disable_info
45 show create table t1;
46 analyze table t1;
47 explain select * from t1 force index(c) order by c;
48 --enable_info
49 alter table t1 add primary key (a), drop index c;
50 show create table t1;
51 --error ER_MULTIPLE_PRI_KEY
52 alter table t1 add primary key (c);
53 --error ER_DUP_ENTRY
54 alter table t1 drop primary key, add primary key (b);
55 create unique index c on t1 (c);
56 --disable_info
57 show create table t1;
58 analyze table t1;
59 explain select * from t1 force index(c) order by c;
60 select * from t1 force index(c) order by c;
61 --enable_info
62 alter table t1 drop index b, add index (b);
63 --disable_info
64 show create table t1;
65 insert into t1 values(6,1,'ggg','ggg');
66 select * from t1;
67 select * from t1 force index(b) order by b;
68 select * from t1 force index(c) order by c;
69 select * from t1 force index(d) order by d;
70 -- disable_result_log
71 analyze table t1;
72 -- enable_result_log
73 explain select * from t1 force index(b) order by b;
74 explain select * from t1 force index(c) order by c;
75 explain select * from t1 force index(d) order by d;
76 show create table t1;
77 drop table t1;
78
79 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
80 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
81 commit;
82 --enable_info
83 alter table t1 add index (c(2));
84 show create table t1;
85 alter table t1 add unique index (d(10));
86 show create table t1;
87 --disable_info
88 insert into t1 values(5,1,'ggg','ggg');
89 -- disable_result_log
90 analyze table t1;
91 -- enable_result_log
92 select * from t1;
93 select * from t1 force index(c) order by c;
94 select * from t1 force index(d) order by d;
95 explain select * from t1 order by b;
96 explain select * from t1 force index(c) order by c;
97 explain select * from t1 force index(d) order by d;
98 show create table t1;
99 --enable_info
100 alter table t1 drop index d;
101 --disable_info
102 insert into t1 values(8,9,'fff','fff');
103 select * from t1;
104 select * from t1 force index(c) order by c;
105 -- disable_result_log
106 analyze table t1;
107 -- enable_result_log
108 explain select * from t1 order by b;
109 explain select * from t1 force index(c) order by c;
110 explain select * from t1 order by d;
111 show create table t1;
112 drop table t1;
113
114 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
115 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
116 commit;
117 --enable_info
118 alter table t1 add unique index (b,c);
119 --disable_info
120 insert into t1 values(8,9,'fff','fff');
121 select * from t1;
122 select * from t1 force index(b) order by b;
123 -- disable_result_log
124 analyze table t1;
125 -- enable_result_log
126 explain select * from t1 force index(b) order by b;
127 show create table t1;
128 --enable_info
129 alter table t1 add index (b,c);
130 --disable_info
131 insert into t1 values(11,11,'kkk','kkk');
132 select * from t1;
133 select * from t1 force index(b) order by b;
134 -- disable_result_log
135 analyze table t1;
136 -- enable_result_log
137 explain select * from t1 force index(b) order by b;
138 show create table t1;
139 --enable_info
140 alter table t1 add unique index (c,d);
141 --disable_info
142 insert into t1 values(13,13,'yyy','aaa');
143 select * from t1;
144 select * from t1 force index(b) order by b;
145 select * from t1 force index(c) order by c;
146 -- disable_result_log
147 analyze table t1;
148 -- enable_result_log
149 explain select * from t1 force index(b) order by b;
150 explain select * from t1 force index(c) order by c;
151 show create table t1;
152 drop table t1;
153
154 create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
155 create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
156 create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
157 create table t2(a int not null, b int, c int, d int, e int,
158 foreign key (b) references t1(b) on delete set null,
159 foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null)
160 engine = innodb;
161 --error ER_DROP_INDEX_FK
162 alter table t1 drop index b;
163 --error ER_DROP_INDEX_FK
164 alter table t3 drop index c;
165 --error ER_DROP_INDEX_FK
166 alter table t4 drop index d;
167 --error ER_DROP_INDEX_FK
168 alter table t2 drop index b;
169 --error ER_DROP_INDEX_FK
170 alter table t2 drop index b, drop index c, drop index d;
171 --error ER_FK_COLUMN_NOT_NULL
172 alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY;
173 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
174 set @old_sql_mode = @@sql_mode;
175 set @@sql_mode = 'STRICT_TRANS_TABLES';
176 --error ER_FK_COLUMN_NOT_NULL
177 alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
178 set @@sql_mode = @old_sql_mode;
179
180 SET FOREIGN_KEY_CHECKS=0;
181 --error ER_FK_COLUMN_CANNOT_DROP
182 alter table t2 DROP COLUMN b, ALGORITHM=COPY;
183 --error ER_FK_COLUMN_CANNOT_DROP
184 alter table t2 DROP COLUMN b;
185 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
186 alter table t1 DROP COLUMN b, ALGORITHM=COPY;
187 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
188 alter table t1 DROP COLUMN b;
189 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
190
191 --enable_info
192 # Apparently, the following makes mysql_alter_table() drop index d.
193 create unique index dc on t2 (d,c);
194 create index dc on t1 (b,c);
195 # This should preserve the foreign key constraints.
196 --disable_info
197 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
198 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
199 set @@sql_mode = 'STRICT_TRANS_TABLES';
200 --enable_info
201 --error ER_FK_COLUMN_NOT_NULL
202 alter table t2 add primary key (alpha), change a alpha int,
203 change b beta int not null, change c charlie int not null;
204 --error ER_FK_COLUMN_NOT_NULL
205 alter table t2 add primary key (alpha), change a alpha int,
206 change c charlie int not null, change d delta int not null;
207 alter table t2 add primary key (alpha), change a alpha int,
208 change b beta int, modify c int not null;
209 --disable_info
210 set @@sql_mode = @old_sql_mode;
211 insert into t1 values (1,1,1);
212 insert into t3 values (1,1,1);
213 insert into t4 values (1,1,1);
214 insert into t2 values (1,1,1,1,1);
215 commit;
216 --enable_info
217 alter table t4 add constraint dc foreign key (a) references t1(a);
218 --disable_info
219 show create table t4;
220 # mysqltest first does replace_regex, then replace_result
221 # Embedded server doesn't chdir to data directory
222 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
223 # a foreign key 'test/dc' already exists
224 --error ER_CANT_CREATE_TABLE
225 alter table t3 add constraint dc foreign key (a) references t1(a);
226 SET FOREIGN_KEY_CHECKS=0;
227 --error ER_FK_FAIL_ADD_SYSTEM
228 alter table t3 add constraint dc foreign key (a) references t1(a);
229 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
230 show create table t3;
231 --enable_info
232 alter table t2 drop index b, add index (beta);
233 --disable_info
234 show create table t2;
235 --error ER_ROW_IS_REFERENCED_2
236 delete from t1;
237 --error ER_CANT_DROP_FIELD_OR_KEY
238 drop index dc on t4;
239 --enable_info
240 --error ER_CANT_DROP_FIELD_OR_KEY
241 alter table t3 drop foreign key dc;
242 alter table t4 drop foreign key dc;
243 --disable_info
244 select * from t2;
245 delete from t1;
246 select * from t2;
247
248 drop table t2,t4,t3,t1;
249
250 -- let charset = utf8
251 -- source include/innodb-index.inc
252
253 create table t1(a int not null, b int) engine = innodb;
254 insert into t1 values (1,1),(1,1),(1,1),(1,1);
255 --error ER_DUP_ENTRY
256 alter table t1 add unique index (a);
257 --error ER_DUP_ENTRY
258 alter table t1 add unique index (b);
259 --error ER_DUP_ENTRY
260 alter table t1 add unique index (a), add unique index(b);
261 show create table t1;
262 drop table t1;
263
264 create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
265 alter table t1 drop index c, drop index b;
266 show create table t1;
267 drop table t1;
268
269 create table t1(a int not null, b int, primary key(a)) engine = innodb;
270 alter table t1 add index (b);
271 show create table t1;
272 drop table t1;
273
274 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
275 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
276 --error ER_DUP_ENTRY
277 alter table t1 add unique index (b), add unique index (c), add unique index (d);
278 --error ER_DUP_ENTRY
279 alter table t1 add unique index (c), add unique index (b), add index (d);
280 show create table t1;
281 drop table t1;
282
283 create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
284 insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
285 --enable_info
286 alter table t1 add unique index (b);
287 --disable_info
288 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
289 show create table t1;
290 check table t1;
291 -- disable_result_log
292 analyze table t1;
293 -- enable_result_log
294 explain select * from t1 force index(c) order by c;
295 explain select * from t1 order by a;
296 explain select * from t1 force index(b) order by b;
297 select * from t1 order by a;
298 select * from t1 force index(b) order by b;
299 select * from t1 force index(c) order by c;
300 drop table t1;
301
302 create table t1(a int not null, b int not null) engine=innodb;
303 insert into t1 values (1,1);
304 --enable_info
305 alter table t1 add primary key(b);
306 --disable_info
307 insert into t1 values (2,2);
308 show create table t1;
309 check table t1;
310 select * from t1;
311 analyze table t1;
312 explain select * from t1;
313 explain select * from t1 order by a;
314 explain select * from t1 order by b;
315 checksum table t1;
316 drop table t1;
317
318 create table t1(a int not null) engine=innodb;
319 insert into t1 values (1);
320 --enable_info
321 alter table t1 add primary key(a);
322 --disable_info
323 insert into t1 values (2);
324 show create table t1;
325 check table t1;
326 commit;
327 select * from t1;
328 analyze table t1;
329 explain select * from t1;
330 explain select * from t1 order by a;
331 drop table t1;
332
333 #
334 # Test to check whether CREATE INDEX handles implicit foreign key
335 # constraint modifications (Issue #70, Bug #38786)
336 #
337 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
338 SET FOREIGN_KEY_CHECKS=0;
339
340 CREATE TABLE t1(
341 c1 BIGINT(12) NOT NULL,
342 PRIMARY KEY (c1)
343 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
344
345 CREATE TABLE t2(
346 c1 BIGINT(16) NOT NULL,
347 c2 BIGINT(12) NOT NULL,
348 c3 BIGINT(12) NOT NULL,
349 PRIMARY KEY (c1)
350 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
351
352 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
353 FOREIGN KEY (c3) REFERENCES t1(c1);
354
355 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
356 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
357
358 SHOW CREATE TABLE t2;
359
360 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
361
362 SHOW CREATE TABLE t2;
363
364 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
365 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
366
367 --error ER_NO_REFERENCED_ROW_2
368 INSERT INTO t2 VALUES(0,0,0);
369 INSERT INTO t1 VALUES(0);
370 INSERT INTO t2 VALUES(0,0,0);
371
372 DROP TABLE t2;
373
374 CREATE TABLE t2(
375 c1 BIGINT(16) NOT NULL,
376 c2 BIGINT(12) NOT NULL,
377 c3 BIGINT(12) NOT NULL,
378 PRIMARY KEY (c1,c2,c3)
379 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
380
381 SET FOREIGN_KEY_CHECKS=0;
382 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
383 FOREIGN KEY (c3) REFERENCES t1(c1);
384 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
385
386 SHOW CREATE TABLE t2;
387
388 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
389
390 SHOW CREATE TABLE t2;
391 --error ER_NO_REFERENCED_ROW_2
392 INSERT INTO t2 VALUES(0,0,1);
393 INSERT INTO t2 VALUES(0,0,0);
394 --error ER_ROW_IS_REFERENCED_2
395 DELETE FROM t1;
396 DELETE FROM t2;
397
398 DROP TABLE t2;
399 DROP TABLE t1;
400
401 CREATE TABLE t1(
402 c1 BIGINT(12) NOT NULL,
403 c2 INT(4) NOT NULL,
404 PRIMARY KEY (c2,c1)
405 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
406
407 CREATE TABLE t2(
408 c1 BIGINT(16) NOT NULL,
409 c2 BIGINT(12) NOT NULL,
410 c3 BIGINT(12) NOT NULL,
411 PRIMARY KEY (c1)
412 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
413
414 SET FOREIGN_KEY_CHECKS=0;
415 --enable_info
416
417 # mysqltest first does replace_regex, then replace_result
418 # Embedded server doesn't chdir to data directory
419 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
420 --error ER_CANT_CREATE_TABLE
421 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
422 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
423 --error ER_FK_NO_INDEX_PARENT
424 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
425 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
426 # mysqltest first does replace_regex, then replace_result
427 # Embedded server doesn't chdir to data directory
428 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
429 --error ER_CANT_CREATE_TABLE
430 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
431 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
432 --error ER_FK_NO_INDEX_PARENT
433 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
434 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
435
436 --error ER_FK_INCORRECT_OPTION
437 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
438 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE;
439
440 # mysqltest first does replace_regex, then replace_result
441 # Embedded server doesn't chdir to data directory
442 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
443 --error ER_CANT_CREATE_TABLE
444 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
445 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
446
447 ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
448 # mysqltest first does replace_regex, then replace_result
449 # Embedded server doesn't chdir to data directory
450 --replace_result $MYSQLD_DATADIR ./ master-data/ ''
451 --error ER_CANT_CREATE_TABLE
452 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
453 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
454 --error ER_FK_NO_INDEX_PARENT
455 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
456 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
457
458 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
459 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
460
461 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
462
463 SHOW CREATE TABLE t1;
464 SHOW CREATE TABLE t2;
465 CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
466 SHOW CREATE TABLE t2;
467 CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
468 SHOW CREATE TABLE t2;
469 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
470 SHOW CREATE TABLE t2;
471 --disable_info
472
473 DROP TABLE t2;
474 DROP TABLE t1;
475
476 connect (a,localhost,root,,);
477 connect (b,localhost,root,,);
478 connection a;
479 CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
480 INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e');
481 CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB;
482 CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
483 CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
484 INSERT INTO t2 SELECT * FROM t1;
485 INSERT INTO t2i SELECT * FROM t1;
486 INSERT INTO t2c SELECT * FROM t1;
487 connection b;
488 BEGIN;
489 # This acquires a MDL lock on t1 until commit.
490 SELECT * FROM t1;
491 connection a;
492 # This times out before of the MDL lock held by connection b.
493 SET lock_wait_timeout=1;
494 --error ER_LOCK_WAIT_TIMEOUT
495 CREATE INDEX t1a ON t1(a);
496 --enable_info
497 CREATE INDEX t2a ON t2(a);
498 --disable_info
499 set @old_sql_mode = @@sql_mode;
500 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
501 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
502 set @@sql_mode = 'STRICT_TRANS_TABLES';
503 --enable_info
504 ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
505 --disable_info
506 set @@sql_mode = @old_sql_mode;
507 --enable_info
508 ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
509 --disable_info
510 connection b;
511 # t2i and t2c are too new for this transaction, because they were rebuilt
512 --error ER_TABLE_DEF_CHANGED
513 SELECT * FROM t2i;
514 --error ER_TABLE_DEF_CHANGED
515 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
516 --error ER_TABLE_DEF_CHANGED
517 SELECT * FROM t2c;
518 --error ER_TABLE_DEF_CHANGED
519 SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
520 # In t2, only the new index t2a is too new for this transaction.
521 SELECT * FROM t2;
522 --error ER_TABLE_DEF_CHANGED
523 SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
524 SELECT * FROM t2;
525 COMMIT;
526 # For a new transaction, all of t2, t2i, t2c are accessible.
527 SELECT * FROM t2;
528 SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
529 SELECT * FROM t2i;
530 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
531 SELECT * FROM t2c;
532 SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
533 connection default;
534 disconnect a;
535 disconnect b;
536
537 --error ER_DUP_KEYNAME
538 alter table t2 add index t2a(b);
539 alter table t2 drop index t2a, add index t2a(b);
540 show create table t2;
541 show create table t2i;
542 show create table t2c;
543
544 --disable_info
545
546 DROP TABLE t1,t2,t2c,t2i;
547
548 CREATE TABLE t1 (c VARCHAR(1024),
549 c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL,
550 c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL,
551 c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL,
552 ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL,
553 cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL,
554 d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL,
555 d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL,
556 d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL,
557 d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL,
558 dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL,
559 UNIQUE KEY(c))
560 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
561 INSERT INTO t1 VALUES
562 (repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''),
563 (CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
564 --error ER_INDEX_COLUMN_TOO_LONG
565 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace;
566 --error ER_INDEX_COLUMN_TOO_LONG
567 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy;
568 SELECT COUNT(*) FROM t1;
569 CHECK TABLE t1;
570 DROP TABLE t1;
571
572 --echo #
573 --echo # Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH
574 --echo # IN I_S TABLES
575 --echo #
576 let $i_s_query=SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D,
577 cast(INDEX_LENGTH/@@innodb_page_size as int) I
578 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
579
580 CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1;
581 eval $i_s_query;
582 --enable_info
583 ALTER TABLE t1 ADD INDEX (a);
584 --disable_info
585 eval $i_s_query;
586 --enable_info
587 ALTER TABLE t1 ADD INDEX (b);
588 --disable_info
589 eval $i_s_query;
590 DROP TABLE t1;
591
592 --echo #
593 --echo # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
594 --echo #
595
596 SET GLOBAL innodb_monitor_enable = module_ddl;
597 let $innodb_metrics_select=
598 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
599 subsystem = 'ddl' and count_reset > 0;
600
601 # Table with Blob data.
602 create table t1(f1 int not null, f2 blob)engine=innodb;
603 insert into t1 values(1, repeat('a',20000));
604 --echo # Skip sort
605 --echo # Reusing the same pk
606 --enable_info
607 alter table t1 force;
608 --disable_info
609 eval $innodb_metrics_select;
610 SET GLOBAL innodb_monitor_reset = module_ddl;
611 drop table t1;
612
613 # Table with small data.
614 create table t1(f1 int not null, f2 int not null,
615 primary key(f1))engine=innodb;
616 insert into t1 values(1,2), (3,4);
617 --echo # Add Secondary index.
618 --echo # Skip temp file usage due to small table size
619 --enable_info
620 alter table t1 add key(f2);
621 --disable_info
622 eval $innodb_metrics_select;
623 SET GLOBAL innodb_monitor_reset = module_ddl;
624 drop table t1;
625
626 # Table with large data which is greater than sort buffer
627 create table t480(a serial)engine=innodb;
628 insert into t480
629 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
630 (),(),(),(),(),(),(),();
631 insert into t480 select 0 from t480;
632 insert into t480 select 0 from t480;
633 insert into t480 select 0 from t480;
634 insert into t480 select 0 from t480;
635 create table t1(f1 int auto_increment not null,
636 f2 char(200) not null, f3 char(200) not null,
637 f4 char(200) not null,primary key(f1))engine=innodb;
638 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
639 insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
640 insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
641 insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
642 insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
643 insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
644 select count(*) from t1;
645
646 --echo # Skip sort
647 --echo # Change PK from (f1) to (f1,f2,f3,f4)
648 --enable_info
649 alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
650 --disable_info
651 eval $innodb_metrics_select;
652 SET GLOBAL innodb_monitor_reset = module_ddl;
653
654
655 # Temp file not used during clustered index rebuild
656 # for the following alter table commands.
657
658 --echo # Skip sort
659 --echo # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
660 --enable_info
661 alter table t1 drop primary key,add column f5 int not null,
662 add column f6 int not null,add primary key(f1,f2,f5,f6);
663 --disable_info
664 eval $innodb_metrics_select;
665 SET GLOBAL innodb_monitor_reset = module_ddl;
666
667 --echo # Skip sort
668 --echo # Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
669 --error ER_KEY_COLUMN_DOES_NOT_EXIST
670 alter table t1 drop column f6;
671 --enable_info
672 alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5);
673 --disable_info
674 eval $innodb_metrics_select;
675 SET GLOBAL innodb_monitor_reset = module_ddl;
676
677 --echo # Skip sort
678 --echo # Reusing the same PK
679 --enable_info
680 alter table t1 add column f6 int;
681 --disable_info
682 eval $innodb_metrics_select;
683 SET GLOBAL innodb_monitor_reset = module_ddl;
684
685 --echo # Skip sort
686 --echo # Reusing the same pk
687 --enable_info
688 alter table t1 drop column f6;
689 --disable_info
690 eval $innodb_metrics_select;
691 SET GLOBAL innodb_monitor_reset = module_ddl;
692
693 --echo # Must sort
694 --echo # Change PK from (f1,f2,f5) to (f1,f5)
695 --error ER_KEY_COLUMN_DOES_NOT_EXIST
696 alter table t1 drop column f2;
697 --enable_info
698 alter table t1 drop column f2, drop primary key, add primary key(f1,f5);
699 --disable_info
700 eval $innodb_metrics_select;
701 SET GLOBAL innodb_monitor_reset = module_ddl;
702
703 --echo # Skip sort
704 --echo # Reusing the same pk
705 --enable_info
706 alter table t1 add column f2n int after f1, drop primary key, add
707 primary key (f1,f5,f2n);
708 --disable_info
709 eval $innodb_metrics_select;
710 SET GLOBAL innodb_monitor_reset = module_ddl;
711
712 --echo # Skip sort
713 --echo # Reusing the same pk
714 --enable_info
715 alter table t1 change f5 f2n int not null,change f2n f5 int not null,
716 add column f8 int not null;
717 --disable_info
718 eval $innodb_metrics_select;
719 SET GLOBAL innodb_monitor_reset = module_ddl;
720
721 --echo # Skip sort
722 --echo # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
723 --enable_info
724 alter table t1 add column f7 int, drop primary key,
725 add primary key (f1,f5,f7,f2n);
726 --disable_info
727 eval $innodb_metrics_select;
728 SET GLOBAL innodb_monitor_reset = module_ddl;
729
730 --echo # Skip sort
731 --echo # Reusing the same pk
732 --enable_info
733 alter table t1 force;
734 --disable_info
735 eval $innodb_metrics_select;
736 SET GLOBAL innodb_monitor_reset = module_ddl;
737
738 --echo # Skip sort
739 --echo # Reusing the same pk
740 --enable_info
741 alter table t1 row_format=compact;
742 --disable_info
743 eval $innodb_metrics_select;
744 SET GLOBAL innodb_monitor_reset = module_ddl;
745
746 --echo # Skip sort
747 --echo # Reusing the same pk
748 --enable_info
749 alter table t1 engine=innodb;
750 --disable_info
751 eval $innodb_metrics_select;
752 SET GLOBAL innodb_monitor_reset = module_ddl;
753
754 --echo # Skip sort
755 --echo # Optimize table
756 --enable_info
757 optimize table t1;
758 --disable_info
759 eval $innodb_metrics_select;
760 SET GLOBAL innodb_monitor_reset = module_ddl;
761
762 --echo # Sort files used for adding secondary index
763 --enable_info
764 alter table t1 drop primary key, add primary key(f1,f5,f7), add index
765 i(f3);
766 --disable_info
767 eval $innodb_metrics_select;
768 SET GLOBAL innodb_monitor_reset = module_ddl;
769
770 --echo # No sort files used for dropping secondary index
771 --enable_info
772 alter table t1 drop primary key, add primary key(f1,f5),drop index i;
773 --disable_info
774 eval $innodb_metrics_select;
775 SET GLOBAL innodb_monitor_reset = module_ddl;
776
777 --echo # Skip sort
778 --echo # Change PK(f1,f5) to (f1,added_columns) and drop f5
779 --enable_info
780 alter table t1 drop primary key, add primary key(f1,f12),
781 drop column f5, add column f12 int not null;
782 --disable_info
783 eval $innodb_metrics_select;
784 SET GLOBAL innodb_monitor_reset = module_ddl;
785
786 --echo # Must sort
787 --echo # Change PK(f1,f12) to (f1,existing_columns)
788 --enable_info
789 alter table t1 drop primary key, add primary key(f1,f3);
790 --disable_info
791 eval $innodb_metrics_select;
792 SET GLOBAL innodb_monitor_reset = module_ddl;
793
794 --echo # Skip sort
795 --echo # Change PK(f1,f3) to (f1,added_column,f3,added_column)
796 --enable_info
797 alter table t1 drop primary key, add column f3n int,
798 add column f4n int, add primary key(f1,f3n,f3,f4n);
799 --disable_info
800 eval $innodb_metrics_select;
801 SET GLOBAL innodb_monitor_reset = module_ddl;
802
803 --echo # Adding Secondary index alone.
804 --enable_info
805 alter table t1 add key(f1);
806 --disable_info
807 eval $innodb_metrics_select;
808 SET GLOBAL innodb_monitor_reset = module_ddl;
809
810 --echo # Must sort
811 --echo # Change PK(f1,f3) to (existing_column,f1)
812 --enable_info
813 alter table t1 drop primary key, add primary key(f4,f1);
814 --disable_info
815 eval $innodb_metrics_select;
816 SET GLOBAL innodb_monitor_reset = module_ddl;
817
818 --echo # Skip sort for PK.
819 --echo # Change PK(f4,f1) to (added_columns,f4,f1)
820 --echo # Secondary index rebuild happens
821 --enable_info
822 alter table t1 drop primary key, add column f5n int,
823 add column f6n int, add primary key(f5n,f6n,f4,f1);
824 --disable_info
825 eval $innodb_metrics_select;
826 SET GLOBAL innodb_monitor_reset = module_ddl;
827
828 --echo # Skip sort for PK.
829 --echo # Change PK(f5n,f6n,f4,f1) to
830 --echo # (added_columns,f5n,added_column,f6n,f4,f1)
831 --echo # Secondary index rebuild happens
832 --enable_info
833 alter table t1 drop primary key, add column f7n int,
834 add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
835 --disable_info
836 eval $innodb_metrics_select;
837 SET GLOBAL innodb_monitor_reset = module_ddl;
838
839 SET GLOBAL innodb_monitor_disable = module_ddl;
840 select count(*) from t1;
841 drop table t1;
842 --disable_warnings
843 SET GLOBAL innodb_monitor_reset = default;
844 SET GLOBAL innodb_monitor_enable = default;
845 SET GLOBAL innodb_monitor_disable = default;
846 --enable_warnings
847
848 --echo # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
849 --echo # WHEN SKIPPING SORT FOR CLUSTER INDEX
850
851 # last mtuple in previous buffer and first mtuple in next buffer
852 # are equal.
853 SELECT @@innodb_sort_buffer_size;
854 create table t1(f1 int auto_increment not null,
855 f2 char(200) not null, f3 char(200) not null,
856 f4 char(200) not null,primary key(f1,f2,f3,f4));
857 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
858 insert into t1 values(106, 'aaa','bbb','cccc');
859 select count(*) from t1;
860 --echo # Skip sort
861 --echo # Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
862 --error ER_DUP_ENTRY
863 alter table t1 drop primary key, add primary key(f1,f2,f3);
864 select count(*) from t1;
865 drop table t1;
866
867 # Duplicates exist with in the buffer
868 create table t1(f1 int auto_increment not null,
869 f2 char(200) not null, f3 char(200) not null,
870 f4 char(200) not null,primary key(f1,f2,f3,f4));
871 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
872 insert into t1 values(108,'aaa','bbb','cccc');
873 select count(*) from t1;
874 --error ER_DUP_ENTRY
875 alter table t1 drop primary key, add primary key(f1,f2,f3);
876 select count(*) from t1;
877 drop table t1, t480;
878
879 --echo #
880 --echo # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
881 --echo # FIELD IS CHANGED
882 --echo #
883
884 # Prefix length changes for the varchar column.
885 create table t1(a int not null, b varchar(30) not null,
886 primary key (b(10), a)) engine = innodb;
887 insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
888 select * from t1;
889 alter table t1 drop primary key, add primary key (b(18),a);
890 select * from t1;
891 drop table t1;
892
893 create table t1(a int not null, b varchar(30) not null,
894 primary key (b(10), a)) engine = innodb;
895 insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
896 select * from t1;
897 alter table t1 drop primary key, add primary key (b(8),a);
898 select * from t1;
899 drop table t1;
900
901
902 --echo #
903 --echo # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE
904 --echo # COLUMN PRIMARY KEY
905 --echo #
906
907 # Drop primary key column.
908 create table t1(f1 int not null, f2 int not null,
909 primary key (f1), unique key(f1, f2))engine=innodb;
910 insert into t1 values(1,3), (2,2);
911 --error ER_KEY_COLUMN_DOES_NOT_EXIST
912 alter table t1 drop column f1;
913 --error ER_KEY_COLUMN_DOES_NOT_EXIST
914 alter table t1 drop column f1, drop primary key;
915 # DROP PRIMARY KEY is implied for a single-column PRIMARY KEY
916 alter table t1 drop column f1, drop key f1;
917 drop table t1;
918
919 # Drop Primary key when lock is none.
920 create table t1(f1 int not null, f2 int not null,
921 primary key (f1), unique key(f1, f2))engine=innodb;
922 insert into t1 values(1,3), (2,2);
923 alter table t1 drop primary key, lock=none;
924 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
925 alter table t1 drop index f1, algorithm=inplace, lock=none;
926 drop table t1;
927
928 --echo #
929 --echo # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
930 --echo # LAST COLUMN OF OLD PK
931 --echo #
932
933 # no skip sort cases
934 # pk(o1,o2) to pk(o1,o3), drop o2
935 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
936 insert into t1 values(1,1,2),(1,2,1);
937 alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
938 drop table t1;
939
940 # pk(o1,o2) to pk(o3), drop o1, o2
941 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
942 insert into t1 values(1,1,2),(1,2,1);
943 alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
944 drop table t1;
945
946 # pk(o1,o2) to pk(o1,o3)
947 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
948 insert into t1 values(1,1,2),(1,2,1);
949 alter table t1 drop primary key, add primary key(o1,o3), lock=none;
950 drop table t1;
951
952 # pk(o1,o2) to pk(o3)
953 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
954 insert into t1 values(1,1,2),(1,2,1);
955 alter table t1 drop primary key, add primary key(o3), lock=none;
956 drop table t1;
957
958 # pk(o1,o2) to pk(n1,o3)
959 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
960 insert into t1 values(1,1,2),(1,2,1);
961 alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
962 drop table t1;
963
964 # pk(o1,o2) to pk(o3,n1)
965 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
966 insert into t1 values(1,1,2),(2,2,1);
967 alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
968 drop table t1;
969
970 # pk(o1,o2) to pk(o2,o1)
971 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
972 insert into t1 values(1,2,2),(2,1,1);
973 alter table t1 drop primary key, add primary key(o2, o1), lock=none;
974 drop table t1;
975
976 # pk(o1,o2) to pk(o2)
977 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
978 insert into t1 values(1,2,2),(2,1,1);
979 alter table t1 drop primary key, add primary key(o2), lock=none;
980 drop table t1;
981
982 # pk(o1,o2) to pk(o2,o3)
983 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
984 insert into t1 values(1,2,2),(2,1,1);
985 alter table t1 drop primary key, add primary key(o2,o3), lock=none;
986 drop table t1;
987
988 # pk(o2,o1) to pk(o2,o3)
989 create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
990 insert into t1 values(1,1,2),(2,1,1);
991 alter table t1 drop primary key, add primary key(o2,o3), lock=none;
992 drop table t1;
993
994 # pk(o1,o2) to pk(o1,o3,o2)
995 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
996 insert into t1 values(1,1,2),(1,2,1);
997 alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
998 drop table t1;
999
1000 # pk(o1,o2) to pk(o3,o1,o2)
1001 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
1002 insert into t1 values(1,2,2),(2,1,1);
1003 alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
1004 drop table t1;
1005
1006 # pk(o1,o2,o3) to pk(o1,o3)
1007 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1008 insert into t1 values(1,1,2),(1,2,1);
1009 alter table t1 drop primary key, add primary key(o1,o3), lock=none;
1010 drop table t1;
1011
1012 # pk(o1,o2,o3) to pk(o2,o3) by drop o1
1013 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1014 insert into t1 values(1,2,2),(2,1,1);
1015 --error ER_KEY_COLUMN_DOES_NOT_EXIST
1016 alter table t1 drop o1, lock=none;
1017 alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none;
1018 drop table t1;
1019
1020 # pk(o1,o2,o3) to pk(o1,o3) by drop o2
1021 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1022 insert into t1 values(1,1,2),(1,2,1);
1023 --error ER_KEY_COLUMN_DOES_NOT_EXIST
1024 alter table t1 drop o2, lock=none;
1025 alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none;
1026 drop table t1;
1027
1028 # pk(o1,o2,o3) to pk(o3) by drop o1,o2
1029 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
1030 insert into t1 values(1,2,2),(2,1,1);
1031 --error ER_KEY_COLUMN_DOES_NOT_EXIST
1032 alter table t1 drop o1, drop o2, lock=none;
1033 alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none;
1034 drop table t1;
1035
1036 # no skip sort for prefix change
1037 # pk(o1(2),o2) to pk(o1(3),o2)
1038 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1039 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1040 alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
1041 drop table t1;
1042
1043 # pk(o1(2),o2) to pk(o1,o2)
1044 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1045 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1046 alter table t1 drop primary key, add primary key(o1, o2), lock=none;
1047 drop table t1;
1048
1049 # pk(o1(2),o2) to pk(o1(3),o3)
1050 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1051 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1052 alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
1053 drop table t1;
1054
1055 # pk(o1(2),o2) to pk(o1,o3)
1056 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
1057 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
1058 alter table t1 drop primary key, add primary key(o1, o3), lock=none;
1059 drop table t1;
1060
1061 # pk(o1(3),o2) to pk(o1(2),o2)
1062 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
1063 insert into t1 values('abc', 2, 1), ('abd', 1, 2);
1064 alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
1065 drop table t1;
1066
1067 # pk(o1,o2) to pk(o1(2),o2)
1068 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
1069 insert into t1 values('abc', 2, 1), ('abd', 1, 2);
1070 alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
1071 drop table t1;
1072
1073 # pk(o1(3),o2) to pk(o1(2),o3)
1074 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
1075 insert into t1 values('abc', 2, 2), ('abd', 1, 1);
1076 alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
1077 drop table t1;
1078
1079 # pk(o1,o2) to pk(o1(2),o3)
1080 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
1081 insert into t1 values('abc', 2, 2), ('abd', 1, 1);
1082 alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
1083 drop table t1;
1084
1085 # pk(o1,o2(2),o3) to pk(o1,o2(3))
1086 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
1087 insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
1088 alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
1089 drop table t1;
1090
1091 # pk(o1,o2(2),o3) to pk(o1,o2)
1092 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
1093 insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
1094 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1095 drop table t1;
1096
1097 # pk(o1(3),o2(3)) to pk(o1(2),o2(3))
1098 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1099 insert into t1 values('abc', 'acd'), ('abd', 'abd');
1100 alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
1101 drop table t1;
1102
1103 # pk(o1,o2) to pk(o1(2),o2)
1104 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1105 insert into t1 values('abc', 'acd'), ('abd', 'abd');
1106 alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
1107 drop table t1;
1108
1109 # pk(o1(3),o2(3)) to pk(o2(3),o1(3))
1110 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1111 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1112 alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
1113 drop table t1;
1114
1115 # pk(o1,o2) to pk(o2,o1)
1116 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1117 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1118 alter table t1 drop primary key, add primary key(o2,o1), lock=none;
1119 drop table t1;
1120
1121 # no skip sort cases
1122 --source suite/innodb/include/alter_table_pk_no_sort.inc
1123
1124 --echo #
1125 --echo # MDEV-15325 Incomplete validation of missing tablespace during recovery
1126 --echo #
1127
1128 --source include/no_checkpoint_start.inc
1129 CREATE TABLE t1(f1 INT PRIMARY KEY)ENGINE=InnoDB;
1130
1131 CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB;
1132
1133 --let CLEANUP_IF_CHECKPOINT=DROP TABLE t1, t2;
1134 --source include/no_checkpoint_end.inc
1135
1136 --echo # Wrong space_id in a dirty file and a missing file
1137 --move_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd
1138
1139 --source include/start_mysqld.inc
1140 # Because the tables were created after the checkpoint,
1141 # InnoDB will start up, but the tables will be corrupted.
1142 CHECK TABLE t1, t2;
1143
1144 DROP TABLE t1, t2;
1145
1146 --echo #
1147 --echo # MDEV-18186 assertion failure on missing InnoDB index
1148 --echo #
1149
1150 --disable_query_log
1151 call mtr.add_suppression("Cannot find index i1 in InnoDB index dictionary");
1152 call mtr.add_suppression("InnoDB indexes are inconsistent with what defined");
1153 call mtr.add_suppression("Table test/t contains 0 indexes");
1154 call mtr.add_suppression("InnoDB could not find key no");
1155 --enable_query_log
1156
1157 # Test an attempt to rename a nonexistent index inside InnoDB
1158 -- let $MYSQL_DATA_DIR = `SELECT @@datadir`
1159 CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
1160 -- copy_file $MYSQL_DATA_DIR/test/t.frm $MYSQL_DATA_DIR/test/t.fr_
1161 DROP TABLE t;
1162 CREATE TABLE t (a INT) ENGINE=INNODB;
1163 -- remove_file $MYSQL_DATA_DIR/test/t.frm
1164 -- move_file $MYSQL_DATA_DIR/test/t.fr_ $MYSQL_DATA_DIR/test/t.frm
1165 --enable_prepare_warnings
1166 SHOW CREATE TABLE t;
1167 --disable_prepare_warnings
1168 DROP TABLE t;
1169
1170 --echo #
1171 --echo # MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column
1172 --echo #
1173 CREATE TABLE t1 (a VARCHAR(8), PRIMARY KEY(a DESC)) ENGINE=InnoDB;
1174 ALTER TABLE t1 RENAME COLUMN a TO b, ALGORITHM=INPLACE;
1175 --disable_cursor_protocol
1176 SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
1177 SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id;
1178 --enable_cursor_protocol
1179 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
1180 DROP TABLE t1;
1181
1182 --echo #
1183 --echo # MDEV-27432 ASC/DESC primary and unique keys cause index
1184 --echo # inconsistency between InnoDB and server
1185 --echo #
1186 CREATE TABLE t1 (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
1187 ALTER TABLE t1 ADD PRIMARY KEY (id), ALGORITHM=INPLACE;
1188 SHOW CREATE TABLE t1;
1189 DROP TABLE t1;
1190
1191 --echo #
1192 --echo # MDEV-27445 Index inconsistency and assertion failure after
1193 --echo # renaming virtual column with DESC key
1194 --echo #
1195 CREATE TABLE t1(a INT, b INT AS (a), KEY(a, b DESC)) ENGINE=InnoDB;
1196 ALTER TABLE t1 RENAME COLUMN IF EXISTS b TO v;
1197 ALTER TABLE t1 FORCE;
1198 DROP TABLE t1;
1199
1200 --echo #
1201 --echo # MDEV-27592 DESC primary index fails to set wide format
1202 --echo # while renaming the column
1203 --echo #
1204 CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(8), b INT, KEY(a DESC,b)) ENGINE=InnoDB;
1205 INSERT INTO t1 VALUES (1,'foo',10);
1206 ALTER TABLE t1 RENAME COLUMN b TO c, ALGORITHM=INPLACE;
1207 --disable_cursor_protocol
1208 SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
1209 SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id ORDER BY INDEX_ID DESC LIMIT 1;
1210 --enable_cursor_protocol
1211 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
1212 DROP TABLE t1;
1213
1214 --echo # End of 10.8 tests
1215
1216 --echo #
1217 --echo # MDEV-35849: index records in a wrong order
1218 --echo #
1219
1220 CREATE TABLE t (pk INT PRIMARY KEY, a VARCHAR(1), KEY (a DESC)) ENGINE=InnoDB;
1221 INSERT INTO t VALUES (1,NULL),(2,'x');
1222 CHECK TABLE t;
1223 DROP TABLE t;
1224
1225 --echo # End of 11.8 tests
1226
1227 --disable_query_log
1228
1229 call mtr.add_suppression("InnoDB: Tablespace .* was not found at .*t[12].ibd.");
1230 call mtr.add_suppression("InnoDB: Plugin initialization aborted");
1231 call mtr.add_suppression("Plugin 'InnoDB' init function returned error");
1232 call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed");
1233 call mtr.add_suppression("Fewer engines are enabled now than were before the crash");
1234
1235 --enable_query_log
1236 --source include/test_db_charset_restore.inc