Debian Codegraph @

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

raw
1 -- source include/no_valgrind_without_big.inc
2
3 let $MYSQLD_DATADIR= `select @@datadir`;
4
5 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
6
7 # Bug#13654923 BOGUS DEBUG ASSERTION IN INDEX CREATION FOR ZERO-LENGTH RECORD
8 create table t1(a varchar(2) primary key) engine=innodb;
9 insert into t1 values('');
10 --enable_info
11 create index t1a1 on t1(a(1));
12 --disable_info
13 drop table t1;
14
15 create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
16 insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
17 commit;
18 --error ER_DUP_KEYNAME
19 alter table t1 add index b (b), add index b (b);
20 --error ER_DUP_FIELDNAME
21 alter table t1 add index (b,b);
22 --enable_info
23 alter table t1 add index d2 (d);
24 --disable_info
25 show create table t1;
26 -- disable_result_log
27 analyze table t1;
28 -- enable_result_log
29 explain select * from t1 force index(d2) order by d;
30 select * from t1 force index (d2) order by d;
31 --error ER_DUP_ENTRY
32 alter table t1 add unique index (b);
33 show create table t1;
34 --enable_info
35 alter table t1 add index (b);
36 --disable_info
37 show create table t1;
38
39 --enable_info
40 alter table t1 add unique index (c), add index (d);
41 --disable_info
42 show create table t1;
43 analyze table t1;
44 explain select * from t1 force index(c) order by c;
45 --enable_info
46 alter table t1 add primary key (a), drop index c;
47 show create table t1;
48 --error ER_MULTIPLE_PRI_KEY
49 alter table t1 add primary key (c);
50 --error ER_DUP_ENTRY
51 alter table t1 drop primary key, add primary key (b);
52 create unique index c on t1 (c);
53 --disable_info
54 show create table t1;
55 analyze table t1;
56 explain select * from t1 force index(c) order by c;
57 select * from t1 force index(c) order by c;
58 --enable_info
59 alter table t1 drop index b, add index (b);
60 --disable_info
61 show create table t1;
62 insert into t1 values(6,1,'ggg','ggg');
63 select * from t1;
64 select * from t1 force index(b) order by b;
65 select * from t1 force index(c) order by c;
66 select * from t1 force index(d) order by d;
67 -- disable_result_log
68 analyze table t1;
69 -- enable_result_log
70 explain select * from t1 force index(b) order by b;
71 explain select * from t1 force index(c) order by c;
72 explain select * from t1 force index(d) order by d;
73 show create table t1;
74 drop table t1;
75
76 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
77 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
78 commit;
79 --enable_info
80 alter table t1 add index (c(2));
81 show create table t1;
82 alter table t1 add unique index (d(10));
83 show create table t1;
84 --disable_info
85 insert into t1 values(5,1,'ggg','ggg');
86 -- disable_result_log
87 analyze table t1;
88 -- enable_result_log
89 select * from t1;
90 select * from t1 force index(c) order by c;
91 select * from t1 force index(d) order by d;
92 explain select * from t1 order by b;
93 explain select * from t1 force index(c) order by c;
94 explain select * from t1 force index(d) order by d;
95 show create table t1;
96 --enable_info
97 alter table t1 drop index d;
98 --disable_info
99 insert into t1 values(8,9,'fff','fff');
100 select * from t1;
101 select * from t1 force index(c) order by c;
102 -- disable_result_log
103 analyze table t1;
104 -- enable_result_log
105 explain select * from t1 order by b;
106 explain select * from t1 force index(c) order by c;
107 explain select * from t1 order by d;
108 show create table t1;
109 drop table t1;
110
111 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
112 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
113 commit;
114 --enable_info
115 alter table t1 add unique index (b,c);
116 --disable_info
117 insert into t1 values(8,9,'fff','fff');
118 select * from t1;
119 select * from t1 force index(b) order by b;
120 -- disable_result_log
121 analyze table t1;
122 -- enable_result_log
123 explain select * from t1 force index(b) order by b;
124 show create table t1;
125 --enable_info
126 alter table t1 add index (b,c);
127 --disable_info
128 insert into t1 values(11,11,'kkk','kkk');
129 select * from t1;
130 select * from t1 force index(b) order by b;
131 -- disable_result_log
132 analyze table t1;
133 -- enable_result_log
134 explain select * from t1 force index(b) order by b;
135 show create table t1;
136 --enable_info
137 alter table t1 add unique index (c,d);
138 --disable_info
139 insert into t1 values(13,13,'yyy','aaa');
140 select * from t1;
141 select * from t1 force index(b) order by b;
142 select * from t1 force index(c) order by c;
143 -- disable_result_log
144 analyze table t1;
145 -- enable_result_log
146 explain select * from t1 force index(b) order by b;
147 explain select * from t1 force index(c) order by c;
148 show create table t1;
149 drop table t1;
150
151 create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
152 create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
153 create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
154 create table t2(a int not null, b int, c int, d int, e int,
155 foreign key (b) references t1(b) on delete set null,
156 foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null)
157 engine = innodb;
158 --error ER_DROP_INDEX_FK
159 alter table t1 drop index b;
160 --error ER_DROP_INDEX_FK
161 alter table t3 drop index c;
162 --error ER_DROP_INDEX_FK
163 alter table t4 drop index d;
164 --error ER_DROP_INDEX_FK
165 alter table t2 drop index b;
166 --error ER_DROP_INDEX_FK
167 alter table t2 drop index b, drop index c, drop index d;
168 --error ER_FK_COLUMN_NOT_NULL
169 alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY;
170 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
171 --error ER_FK_COLUMN_NOT_NULL
172 alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
173
174 SET FOREIGN_KEY_CHECKS=0;
175 --error ER_FK_COLUMN_CANNOT_DROP
176 alter table t2 DROP COLUMN b, ALGORITHM=COPY;
177 --error ER_FK_COLUMN_CANNOT_DROP
178 alter table t2 DROP COLUMN b;
179 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
180 alter table t1 DROP COLUMN b, ALGORITHM=COPY;
181 --error ER_FK_COLUMN_CANNOT_DROP_CHILD
182 alter table t1 DROP COLUMN b;
183 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
184
185 --enable_info
186 # Apparently, the following makes mysql_alter_table() drop index d.
187 create unique index dc on t2 (d,c);
188 create index dc on t1 (b,c);
189 # This should preserve the foreign key constraints.
190 --disable_info
191 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
192 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
193 --enable_info
194 --error ER_FK_COLUMN_NOT_NULL
195 alter table t2 add primary key (alpha), change a alpha int,
196 change b beta int not null, change c charlie int not null;
197 --error ER_FK_COLUMN_NOT_NULL
198 alter table t2 add primary key (alpha), change a alpha int,
199 change c charlie int not null, change d delta int not null;
200 alter table t2 add primary key (alpha), change a alpha int,
201 change b beta int, modify c int not null;
202 --disable_info
203 insert into t1 values (1,1,1);
204 insert into t3 values (1,1,1);
205 insert into t4 values (1,1,1);
206 insert into t2 values (1,1,1,1,1);
207 commit;
208 --enable_info
209 alter table t4 add constraint dc foreign key (a) references t1(a);
210 --disable_info
211 show create table t4;
212 # a foreign key 'test/dc' already exists
213 --error ER_FK_DUP_NAME
214 alter table t3 add constraint dc foreign key (a) references t1(a);
215 SET FOREIGN_KEY_CHECKS=0;
216 --error ER_FK_DUP_NAME
217 alter table t3 add constraint dc foreign key (a) references t1(a);
218 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
219 show create table t3;
220 --enable_info
221 alter table t2 drop index b, add index (beta);
222 --disable_info
223 show create table t2;
224 --error ER_ROW_IS_REFERENCED_2
225 delete from t1;
226 --error ER_CANT_DROP_FIELD_OR_KEY
227 drop index dc on t4;
228 --enable_info
229 --error ER_CANT_DROP_FIELD_OR_KEY
230 alter table t3 drop foreign key dc;
231 alter table t4 drop foreign key dc;
232 --disable_info
233 select * from t2;
234 delete from t1;
235 select * from t2;
236
237 drop table t2,t4,t3,t1;
238
239 -- let charset = utf8
240 -- source include/innodb-index.inc
241
242 create table t1(a int not null, b int) engine = innodb;
243 insert into t1 values (1,1),(1,1),(1,1),(1,1);
244 --error ER_DUP_ENTRY
245 alter table t1 add unique index (a);
246 --error ER_DUP_ENTRY
247 alter table t1 add unique index (b);
248 --error ER_DUP_ENTRY
249 alter table t1 add unique index (a), add unique index(b);
250 show create table t1;
251 drop table t1;
252
253 create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
254 alter table t1 drop index c, drop index b;
255 show create table t1;
256 drop table t1;
257
258 create table t1(a int not null, b int, primary key(a)) engine = innodb;
259 alter table t1 add index (b);
260 show create table t1;
261 drop table t1;
262
263 create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
264 insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
265 --error ER_DUP_ENTRY
266 alter table t1 add unique index (b), add unique index (c), add unique index (d);
267 --error ER_DUP_ENTRY
268 alter table t1 add unique index (c), add unique index (b), add index (d);
269 show create table t1;
270 drop table t1;
271
272 create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
273 insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
274 --enable_info
275 alter table t1 add unique index (b);
276 --disable_info
277 insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
278 show create table t1;
279 check table t1;
280 -- disable_result_log
281 analyze table t1;
282 -- enable_result_log
283 explain select * from t1 force index(c) order by c;
284 explain select * from t1 order by a;
285 explain select * from t1 force index(b) order by b;
286 select * from t1 order by a;
287 select * from t1 force index(b) order by b;
288 select * from t1 force index(c) order by c;
289 drop table t1;
290
291 create table t1(a int not null, b int not null) engine=innodb;
292 insert into t1 values (1,1);
293 --enable_info
294 alter table t1 add primary key(b);
295 --disable_info
296 insert into t1 values (2,2);
297 show create table t1;
298 check table t1;
299 select * from t1;
300 analyze table t1;
301 explain select * from t1;
302 explain select * from t1 order by a;
303 explain select * from t1 order by b;
304 checksum table t1;
305 drop table t1;
306
307 create table t1(a int not null) engine=innodb;
308 insert into t1 values (1);
309 --enable_info
310 alter table t1 add primary key(a);
311 --disable_info
312 insert into t1 values (2);
313 show create table t1;
314 check table t1;
315 commit;
316 select * from t1;
317 analyze table t1;
318 explain select * from t1;
319 explain select * from t1 order by a;
320 drop table t1;
321
322 #
323 # Test to check whether CREATE INDEX handles implicit foreign key
324 # constraint modifications (Issue #70, Bug #38786)
325 #
326 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
327 SET FOREIGN_KEY_CHECKS=0;
328
329 CREATE TABLE t1(
330 c1 BIGINT(12) NOT NULL,
331 PRIMARY KEY (c1)
332 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
333
334 CREATE TABLE t2(
335 c1 BIGINT(16) NOT NULL,
336 c2 BIGINT(12) NOT NULL,
337 c3 BIGINT(12) NOT NULL,
338 PRIMARY KEY (c1)
339 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
340
341 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
342 FOREIGN KEY (c3) REFERENCES t1(c1);
343
344 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
345 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
346
347 SHOW CREATE TABLE t2;
348
349 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
350
351 SHOW CREATE TABLE t2;
352
353 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
354 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
355
356 --error ER_NO_REFERENCED_ROW_2
357 INSERT INTO t2 VALUES(0,0,0);
358 INSERT INTO t1 VALUES(0);
359 INSERT INTO t2 VALUES(0,0,0);
360
361 DROP TABLE t2;
362
363 CREATE TABLE t2(
364 c1 BIGINT(16) NOT NULL,
365 c2 BIGINT(12) NOT NULL,
366 c3 BIGINT(12) NOT NULL,
367 PRIMARY KEY (c1,c2,c3)
368 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
369
370 SET FOREIGN_KEY_CHECKS=0;
371 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
372 FOREIGN KEY (c3) REFERENCES t1(c1);
373 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
374
375 SHOW CREATE TABLE t2;
376
377 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
378
379 SHOW CREATE TABLE t2;
380 --error ER_NO_REFERENCED_ROW_2
381 INSERT INTO t2 VALUES(0,0,1);
382 INSERT INTO t2 VALUES(0,0,0);
383 --error ER_ROW_IS_REFERENCED_2
384 DELETE FROM t1;
385 DELETE FROM t2;
386
387 DROP TABLE t2;
388 DROP TABLE t1;
389
390 CREATE TABLE t1(
391 c1 BIGINT(12) NOT NULL,
392 c2 INT(4) NOT NULL,
393 PRIMARY KEY (c2,c1)
394 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
395
396 CREATE TABLE t2(
397 c1 BIGINT(16) NOT NULL,
398 c2 BIGINT(12) NOT NULL,
399 c3 BIGINT(12) NOT NULL,
400 PRIMARY KEY (c1)
401 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
402
403 SET FOREIGN_KEY_CHECKS=0;
404 --enable_info
405
406 --error ER_FK_NO_INDEX_PARENT
407 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
408 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
409 --error ER_FK_NO_INDEX_PARENT
410 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
411 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
412 --error ER_FK_INCOMPATIBLE_COLUMNS
413 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
414 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
415 --error ER_FK_INCOMPATIBLE_COLUMNS
416 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
417 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
418
419 --error ER_FK_INCOMPATIBLE_COLUMNS
420 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
421 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE;
422
423 --error ER_FK_INCOMPATIBLE_COLUMNS
424 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
425 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
426
427 ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
428 --error ER_FK_NO_INDEX_PARENT
429 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
430 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
431 --error ER_FK_NO_INDEX_PARENT
432 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
433 FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
434
435 ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
436 FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
437
438 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
439
440 SHOW CREATE TABLE t1;
441 SHOW CREATE TABLE t2;
442 CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
443 SHOW CREATE TABLE t2;
444 CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
445 SHOW CREATE TABLE t2;
446 CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
447 SHOW CREATE TABLE t2;
448 --disable_info
449
450 DROP TABLE t2;
451 DROP TABLE t1;
452
453 connect (a,localhost,root,,);
454 connect (b,localhost,root,,);
455 connection a;
456 CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
457 INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e');
458 CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB;
459 CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
460 CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
461 INSERT INTO t2 SELECT * FROM t1;
462 INSERT INTO t2i SELECT * FROM t1;
463 INSERT INTO t2c SELECT * FROM t1;
464 connection b;
465 BEGIN;
466 # This acquires a MDL lock on t1 until commit.
467 SELECT * FROM t1;
468 connection a;
469 # This times out before of the MDL lock held by connection b.
470 SET lock_wait_timeout=1;
471 --error ER_LOCK_WAIT_TIMEOUT
472 CREATE INDEX t1a ON t1(a);
473 --enable_info
474 CREATE INDEX t2a ON t2(a);
475 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
476 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
477 ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
478 ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
479 --disable_info
480 connection b;
481 # t2i and t2c are too new for this transaction, because they were rebuilt
482 --error ER_TABLE_DEF_CHANGED
483 SELECT * FROM t2i;
484 --error ER_TABLE_DEF_CHANGED
485 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
486 --error ER_TABLE_DEF_CHANGED
487 SELECT * FROM t2c;
488 --error ER_TABLE_DEF_CHANGED
489 SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
490 # In t2, only the new index t2a is too new for this transaction.
491 SELECT * FROM t2;
492 --error ER_TABLE_DEF_CHANGED
493 SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
494 SELECT * FROM t2;
495 COMMIT;
496 # For a new transaction, all of t2, t2i, t2c are accessible.
497 SELECT * FROM t2;
498 SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
499 SELECT * FROM t2i;
500 SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
501 SELECT * FROM t2c;
502 SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
503 connection default;
504 disconnect a;
505 disconnect b;
506
507 --error ER_DUP_KEYNAME
508 alter table t2 add index t2a(b);
509 alter table t2 drop index t2a, add index t2a(b);
510 show create table t2;
511 show create table t2i;
512 show create table t2c;
513
514 --disable_info
515
516 DROP TABLE t1,t2,t2c,t2i;
517 --echo #
518 --echo # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
519 --echo #
520
521 SET GLOBAL innodb_monitor_enable = module_ddl;
522 let $innodb_metrics_select=
523 SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
524 subsystem = 'ddl' and count_reset > 0 AND count_reset > 16;
525
526 # Table with Blob data.
527 create table t1(f1 int not null, f2 blob)engine=innodb;
528 insert into t1 values(1, repeat('a',20000));
529 --echo # Skip sort
530 --echo # Reusing the same pk
531 --enable_info
532 alter table t1 force;
533 --disable_info
534 eval $innodb_metrics_select;
535 SET GLOBAL innodb_monitor_reset = module_ddl;
536 drop table t1;
537
538 # Table with small data.
539 create table t1(f1 int not null, f2 int not null,
540 primary key(f1))engine=innodb;
541 insert into t1 values(1,2), (3,4);
542 --echo # Add Secondary index.
543 --echo # Skip temp file usage due to small table size
544 --enable_info
545 alter table t1 add key(f2);
546 --disable_info
547 eval $innodb_metrics_select;
548 SET GLOBAL innodb_monitor_reset = module_ddl;
549 drop table t1;
550
551 # Table with large data which is greater than sort buffer
552 create table t480(a serial)engine=innodb;
553 insert into t480
554 values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
555 (),(),(),(),(),(),(),();
556 insert into t480 select 0 from t480;
557 insert into t480 select 0 from t480;
558 insert into t480 select 0 from t480;
559 insert into t480 select 0 from t480;
560 create table t1(f1 int auto_increment not null,
561 f2 char(200) not null, f3 char(200) not null,
562 f4 char(200) not null,primary key(f1))engine=innodb charset latin1;
563 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
564 insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
565 insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
566 insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
567 insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
568 insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
569 select count(*) from t1;
570
571 --echo # Skip sort
572 --echo # Change PK from (f1) to (f1,f2,f3,f4)
573 --enable_info
574 alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
575 --disable_info
576 eval $innodb_metrics_select;
577 SET GLOBAL innodb_monitor_reset = module_ddl;
578
579
580 # Temp file not used during clustered index rebuild
581 # for the following alter table commands.
582
583 --echo # Skip sort
584 --echo # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
585 --enable_info
586 alter table t1 drop primary key,add column f5 int not null,
587 add column f6 int not null,add primary key(f1,f2,f5,f6);
588 --disable_info
589 eval $innodb_metrics_select;
590 SET GLOBAL innodb_monitor_reset = module_ddl;
591
592 --echo # Skip sort
593 --echo # Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
594 --enable_info
595 alter table t1 drop column f6;
596 --disable_info
597 eval $innodb_metrics_select;
598 SET GLOBAL innodb_monitor_reset = module_ddl;
599
600 --echo # Skip sort
601 --echo # Reusing the same PK
602 --enable_info
603 alter table t1 add column f6 int;
604 --disable_info
605 eval $innodb_metrics_select;
606 SET GLOBAL innodb_monitor_reset = module_ddl;
607
608 --echo # Skip sort
609 --echo # Reusing the same pk
610 --enable_info
611 alter table t1 drop column f6;
612 --disable_info
613 eval $innodb_metrics_select;
614 SET GLOBAL innodb_monitor_reset = module_ddl;
615
616 --echo # Must sort
617 --echo # Change PK from (f1,f2,f5) to (f1,f5)
618 --enable_info
619 alter table t1 drop column f2;
620 --disable_info
621 eval $innodb_metrics_select;
622 SET GLOBAL innodb_monitor_reset = module_ddl;
623
624 --echo # Skip sort
625 --echo # Reusing the same pk
626 --enable_info
627 alter table t1 add column f2n int after f1, drop primary key, add
628 primary key (f1,f5,f2n);
629 --disable_info
630 eval $innodb_metrics_select;
631 SET GLOBAL innodb_monitor_reset = module_ddl;
632
633 --echo # Skip sort
634 --echo # Reusing the same pk
635 --enable_info
636 alter table t1 change f5 f2n int not null,change f2n f5 int not null,
637 add column f8 int not null;
638 --disable_info
639 eval $innodb_metrics_select;
640 SET GLOBAL innodb_monitor_reset = module_ddl;
641
642 --echo # Skip sort
643 --echo # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
644 --enable_info
645 alter table t1 add column f7 int, drop primary key,
646 add primary key (f1,f5,f7,f2n);
647 --disable_info
648 eval $innodb_metrics_select;
649 SET GLOBAL innodb_monitor_reset = module_ddl;
650
651 --echo # Skip sort
652 --echo # Reusing the same pk
653 --enable_info
654 alter table t1 force;
655 --disable_info
656 eval $innodb_metrics_select;
657 SET GLOBAL innodb_monitor_reset = module_ddl;
658
659 --echo # Skip sort
660 --echo # Reusing the same pk
661 --enable_info
662 alter table t1 row_format=compact;
663 --disable_info
664 eval $innodb_metrics_select;
665 SET GLOBAL innodb_monitor_reset = module_ddl;
666
667 --echo # Skip sort
668 --echo # Reusing the same pk
669 --enable_info
670 alter table t1 engine=innodb;
671 --disable_info
672 eval $innodb_metrics_select;
673 SET GLOBAL innodb_monitor_reset = module_ddl;
674
675 --echo # Skip sort
676 --echo # Optimize table
677 --enable_info
678 optimize table t1;
679 --disable_info
680 eval $innodb_metrics_select;
681 SET GLOBAL innodb_monitor_reset = module_ddl;
682
683 --echo # Sort files used for adding secondary index
684 --enable_info
685 alter table t1 drop primary key, add primary key(f1,f5,f7), add index
686 i(f3);
687 --disable_info
688 eval $innodb_metrics_select;
689 SET GLOBAL innodb_monitor_reset = module_ddl;
690
691 --echo # No sort files used for dropping secondary index
692 --enable_info
693 alter table t1 drop primary key, add primary key(f1,f5),drop index i;
694 --disable_info
695 eval $innodb_metrics_select;
696 SET GLOBAL innodb_monitor_reset = module_ddl;
697
698 --echo # Skip sort
699 --echo # Change PK(f1,f5) to (f1,added_columns) and drop f5
700 --enable_info
701 alter table t1 drop primary key, add primary key(f1,f12),
702 drop column f5, add column f12 int not null;
703 --disable_info
704 eval $innodb_metrics_select;
705 SET GLOBAL innodb_monitor_reset = module_ddl;
706
707 --echo # Must sort
708 --echo # Change PK(f1,f12) to (f1,existing_columns)
709 --enable_info
710 alter table t1 drop primary key, add primary key(f1,f3);
711 --disable_info
712 eval $innodb_metrics_select;
713 SET GLOBAL innodb_monitor_reset = module_ddl;
714
715 --echo # Skip sort
716 --echo # Change PK(f1,f3) to (f1,added_column,f3,added_column)
717 --enable_info
718 alter table t1 drop primary key, add column f3n int,
719 add column f4n int, add primary key(f1,f3n,f3,f4n);
720 --disable_info
721 eval $innodb_metrics_select;
722 SET GLOBAL innodb_monitor_reset = module_ddl;
723
724 --echo # Adding Secondary index alone.
725 --enable_info
726 alter table t1 add key(f1);
727 --disable_info
728 eval $innodb_metrics_select;
729 SET GLOBAL innodb_monitor_reset = module_ddl;
730
731 --echo # Must sort
732 --echo # Change PK(f1,f3) to (existing_column,f1)
733 --enable_info
734 alter table t1 drop primary key, add primary key(f4,f1);
735 --disable_info
736 eval $innodb_metrics_select;
737 SET GLOBAL innodb_monitor_reset = module_ddl;
738
739 --echo # Skip sort for PK.
740 --echo # Change PK(f4,f1) to (added_columns,f4,f1)
741 --echo # Secondary index rebuild happens
742 --enable_info
743 alter table t1 drop primary key, add column f5n int,
744 add column f6n int, add primary key(f5n,f6n,f4,f1);
745 --disable_info
746 eval $innodb_metrics_select;
747 SET GLOBAL innodb_monitor_reset = module_ddl;
748
749 --echo # Skip sort for PK.
750 --echo # Change PK(f5n,f6n,f4,f1) to
751 --echo # (added_columns,f5n,added_column,f6n,f4,f1)
752 --echo # Secondary index rebuild happens
753 --enable_info
754 alter table t1 drop primary key, add column f7n int,
755 add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
756 --disable_info
757 eval $innodb_metrics_select;
758 SET GLOBAL innodb_monitor_reset = module_ddl;
759
760 SET GLOBAL innodb_monitor_disable = module_ddl;
761 select count(*) from t1;
762 drop table t1;
763 --disable_warnings
764 SET GLOBAL innodb_monitor_reset = default;
765 SET GLOBAL innodb_monitor_enable = default;
766 SET GLOBAL innodb_monitor_disable = default;
767 --enable_warnings
768
769 --echo # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
770 --echo # WHEN SKIPPING SORT FOR CLUSTER INDEX
771
772 # last mtuple in previous buffer and first mtuple in next buffer
773 # are equal.
774 SELECT @@innodb_sort_buffer_size;
775 create table t1(f1 int auto_increment not null,
776 f2 char(200) not null, f3 char(200) not null,
777 f4 char(200) not null,primary key(f1,f2,f3,f4)) charset latin1;
778 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
779 insert into t1 values(106, 'aaa','bbb','cccc');
780 select count(*) from t1;
781 --echo # Skip sort
782 --echo # Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
783 --error ER_DUP_ENTRY
784 alter table t1 drop primary key, add primary key(f1,f2,f3);
785 select count(*) from t1;
786 drop table t1;
787
788 # Duplicates exist with in the buffer
789 create table t1(f1 int auto_increment not null,
790 f2 char(200) not null, f3 char(200) not null,
791 f4 char(200) not null,primary key(f1,f2,f3,f4)) charset latin1;
792 insert into t1 select NULL,'aaa','bbb','ccc' from t480;
793 insert into t1 values(108,'aaa','bbb','cccc');
794 select count(*) from t1;
795 --error ER_DUP_ENTRY
796 alter table t1 drop primary key, add primary key(f1,f2,f3);
797 select count(*) from t1;
798 drop table t1, t480;
799
800 --echo #
801 --echo # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
802 --echo # FIELD IS CHANGED
803 --echo #
804
805 # Prefix length changes for the varchar column.
806 create table t1(a int not null, b varchar(30) not null,
807 primary key (b(10), a)) engine = innodb;
808 insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
809 select * from t1;
810 alter table t1 drop primary key, add primary key (b(18),a);
811 select * from t1;
812 drop table t1;
813
814 create table t1(a int not null, b varchar(30) not null,
815 primary key (b(10), a)) engine = innodb;
816 insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
817 select * from t1;
818 alter table t1 drop primary key, add primary key (b(8),a);
819 select * from t1;
820 drop table t1;
821
822
823 --echo #
824 --echo # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE
825 --echo # COLUMN PRIMARY KEY
826 --echo #
827
828 # Drop primary key column.
829 create table t1(f1 int not null, f2 int not null,
830 primary key (f1), unique key(f1, f2))engine=innodb;
831 insert into t1 values(1,3), (2,2);
832 alter table t1 drop column f1;
833 drop table t1;
834
835 # Drop Primary key when lock is none.
836 create table t1(f1 int not null, f2 int not null,
837 primary key (f1), unique key(f1, f2))engine=innodb;
838 insert into t1 values(1,3), (2,2);
839 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
840 alter table t1 drop primary key, lock=none;
841 drop table t1;
842
843 --echo #
844 --echo # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
845 --echo # LAST COLUMN OF OLD PK
846 --echo #
847
848 # no skip sort cases
849 # pk(o1,o2) to pk(o1,o3), drop o2
850 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
851 insert into t1 values(1,1,2),(1,2,1);
852 alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
853 drop table t1;
854
855 # pk(o1,o2) to pk(o3), drop o1, o2
856 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
857 insert into t1 values(1,1,2),(1,2,1);
858 alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
859 drop table t1;
860
861 # pk(o1,o2) to pk(o1,o3)
862 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
863 insert into t1 values(1,1,2),(1,2,1);
864 alter table t1 drop primary key, add primary key(o1,o3), lock=none;
865 drop table t1;
866
867 # pk(o1,o2) to pk(o3)
868 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
869 insert into t1 values(1,1,2),(1,2,1);
870 alter table t1 drop primary key, add primary key(o3), lock=none;
871 drop table t1;
872
873 # pk(o1,o2) to pk(n1,o3)
874 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
875 insert into t1 values(1,1,2),(1,2,1);
876 alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
877 drop table t1;
878
879 # pk(o1,o2) to pk(o3,n1)
880 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
881 insert into t1 values(1,1,2),(2,2,1);
882 alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
883 drop table t1;
884
885 # pk(o1,o2) to pk(o2,o1)
886 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
887 insert into t1 values(1,2,2),(2,1,1);
888 alter table t1 drop primary key, add primary key(o2, o1), lock=none;
889 drop table t1;
890
891 # pk(o1,o2) to pk(o2)
892 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
893 insert into t1 values(1,2,2),(2,1,1);
894 alter table t1 drop primary key, add primary key(o2), lock=none;
895 drop table t1;
896
897 # pk(o1,o2) to pk(o2,o3)
898 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
899 insert into t1 values(1,2,2),(2,1,1);
900 alter table t1 drop primary key, add primary key(o2,o3), lock=none;
901 drop table t1;
902
903 # pk(o2,o1) to pk(o2,o3)
904 create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
905 insert into t1 values(1,1,2),(2,1,1);
906 alter table t1 drop primary key, add primary key(o2,o3), lock=none;
907 drop table t1;
908
909 # pk(o1,o2) to pk(o1,o3,o2)
910 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
911 insert into t1 values(1,1,2),(1,2,1);
912 alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
913 drop table t1;
914
915 # pk(o1,o2) to pk(o3,o1,o2)
916 create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
917 insert into t1 values(1,2,2),(2,1,1);
918 alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
919 drop table t1;
920
921 # pk(o1,o2,o3) to pk(o1,o3)
922 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
923 insert into t1 values(1,1,2),(1,2,1);
924 alter table t1 drop primary key, add primary key(o1,o3), lock=none;
925 drop table t1;
926
927 # pk(o1,o2,o3) to pk(o2,o3) by drop o1
928 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
929 insert into t1 values(1,2,2),(2,1,1);
930 alter table t1 drop o1, lock=none;
931 drop table t1;
932
933 # pk(o1,o2,o3) to pk(o1,o3) by drop o2
934 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
935 insert into t1 values(1,1,2),(1,2,1);
936 alter table t1 drop o2, lock=none;
937 drop table t1;
938
939 # pk(o1,o2,o3) to pk(o3) by drop o1,o2
940 create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
941 insert into t1 values(1,2,2),(2,1,1);
942 alter table t1 drop o1, drop o2, lock=none;
943 drop table t1;
944
945 # no skip sort for prefix change
946 # pk(o1(2),o2) to pk(o1(3),o2)
947 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
948 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
949 alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
950 drop table t1;
951
952 # pk(o1(2),o2) to pk(o1,o2)
953 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
954 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
955 alter table t1 drop primary key, add primary key(o1, o2), lock=none;
956 drop table t1;
957
958 # pk(o1(2),o2) to pk(o1(3),o3)
959 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
960 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
961 alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
962 drop table t1;
963
964 # pk(o1(2),o2) to pk(o1,o3)
965 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
966 insert into t1 values('abd', 1, 1), ('abc', 2, 2);
967 alter table t1 drop primary key, add primary key(o1, o3), lock=none;
968 drop table t1;
969
970 # pk(o1(3),o2) to pk(o1(2),o2)
971 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
972 insert into t1 values('abc', 2, 1), ('abd', 1, 2);
973 alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
974 drop table t1;
975
976 # pk(o1,o2) to pk(o1(2),o2)
977 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
978 insert into t1 values('abc', 2, 1), ('abd', 1, 2);
979 alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
980 drop table t1;
981
982 # pk(o1(3),o2) to pk(o1(2),o3)
983 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
984 insert into t1 values('abc', 2, 2), ('abd', 1, 1);
985 alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
986 drop table t1;
987
988 # pk(o1,o2) to pk(o1(2),o3)
989 create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
990 insert into t1 values('abc', 2, 2), ('abd', 1, 1);
991 alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
992 drop table t1;
993
994 # pk(o1,o2(2),o3) to pk(o1,o2(3))
995 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
996 insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
997 alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
998 drop table t1;
999
1000 # pk(o1,o2(2),o3) to pk(o1,o2)
1001 create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
1002 insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
1003 alter table t1 drop primary key, add primary key(o1,o2), lock=none;
1004 drop table t1;
1005
1006 # pk(o1(3),o2(3)) to pk(o1(2),o2(3))
1007 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1008 insert into t1 values('abc', 'acd'), ('abd', 'abd');
1009 alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
1010 drop table t1;
1011
1012 # pk(o1,o2) to pk(o1(2),o2)
1013 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1014 insert into t1 values('abc', 'acd'), ('abd', 'abd');
1015 alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
1016 drop table t1;
1017
1018 # pk(o1(3),o2(3)) to pk(o2(3),o1(3))
1019 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
1020 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1021 alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
1022 drop table t1;
1023
1024 # pk(o1,o2) to pk(o2,o1)
1025 create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
1026 insert into t1 values('abd', 'acd'), ('acd', 'abd');
1027 alter table t1 drop primary key, add primary key(o2,o1), lock=none;
1028 drop table t1;
1029
1030 CREATE TABLE t1(a INT NOT NULL, b INT, PRIMARY KEY (a ASC)) ENGINE=INNODB;
1031 SHOW CREATE TABLE t1;
1032 INSERT INTO t1 VALUES(5,5), (4,4), (3,4), (2,3);
1033 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a DESC);
1034 CHECK TABLE t1;
1035 SELECT * FROM t1;
1036 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a ASC);
1037 CHECK TABLE t1;
1038 SELECT * FROM t1;
1039 DROP TABLE t1;
1040
1041 # no skip sort cases
1042 --source suite/innodb/include/alter_table_pk_no_sort.inc