341 lines
16 KiB
Plaintext
341 lines
16 KiB
Plaintext
CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1)) Engine=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL,
|
|
`c2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
|
|
SET SESSION DEBUG='+d,ib_build_indexes_too_many_concurrent_trxs, ib_rename_indexes_too_many_concurrent_trxs, ib_drop_index_too_many_concurrent_trxs';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX(c2);
|
|
ERROR HY000: Too many active concurrent transactions
|
|
SET SESSION DEBUG=DEFAULT;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) NOT NULL,
|
|
`c2` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
DROP TABLE t1;
|
|
CREATE TABLE bug13861218 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX(c2))
|
|
ENGINE=InnoDB;
|
|
INSERT INTO bug13861218 VALUES (8, 0), (4, 0), (0, 0);
|
|
SET DEBUG='+d,ib_row_merge_buf_add_two';
|
|
CREATE UNIQUE INDEX ui ON bug13861218(c1);
|
|
SET DEBUG='-d,ib_row_merge_buf_add_two';
|
|
DROP TABLE bug13861218;
|
|
CREATE TABLE bug13861218 (c1 INT NOT NULL, c2 INT UNIQUE) ENGINE=InnoDB;
|
|
INSERT INTO bug13861218 VALUES (8, NULL), (4, NULL), (0, NULL);
|
|
SET DEBUG='+d,ib_row_merge_buf_add_two';
|
|
CREATE UNIQUE INDEX ui ON bug13861218(c1);
|
|
SET DEBUG='-d,ib_row_merge_buf_add_two';
|
|
DROP TABLE bug13861218;
|
|
#
|
|
# Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
|
|
#
|
|
create table t480(a serial)engine=innodb;
|
|
insert into t480
|
|
values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
|
|
(),(),(),(),(),(),(),();
|
|
insert into t480 select 0 from t480;
|
|
insert into t480 select 0 from t480;
|
|
insert into t480 select 0 from t480;
|
|
insert into t480 select 0 from t480;
|
|
create table t1(f1 int auto_increment not null,
|
|
f2 char(200) not null, f3 char(200) not null,
|
|
primary key(f1,f2,f3), key(f1))engine=innodb charset latin1;
|
|
insert into t1 select NULL,'aaa','bbb' from t480;
|
|
insert into t1 select NULL,'aaaa','bbbb' from t480;
|
|
insert into t1 select NULL,'aaaaa','bbbbb' from t480;
|
|
insert into t1 select NULL,'aaaaaa','bbbbbb' from t480;
|
|
SET DEBUG = '+d,row_merge_write_failure';
|
|
alter table t1 drop primary key,add primary key(f2,f1);
|
|
ERROR HY000: Temporary file write failure.
|
|
SET DEBUG = '-d,row_merge_write_failure';
|
|
drop table t1;
|
|
create table t1(k1 int auto_increment primary key,
|
|
k2 char(200),k3 char(200))engine=innodb;
|
|
insert into t1 values(NULL,'a','b'), (NULL,'aa','bb');
|
|
SET DEBUG_SYNC= 'row_merge_after_scan
|
|
SIGNAL opened WAIT_FOR flushed';
|
|
optimize table t1;
|
|
connection default
|
|
SET DEBUG_SYNC= 'now WAIT_FOR opened';
|
|
INSERT INTO t1 select NULL,'aaa','bbb' from t480;
|
|
SET DEBUG_SYNC= 'now SIGNAL flushed';
|
|
/*con1 reap*/ Optimize table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status OK
|
|
affected rows: 2
|
|
SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
|
|
COUNT(k1) k2 k3
|
|
1 a b
|
|
1 aa bb
|
|
480 aaa bbb
|
|
drop table t1;
|
|
create table t1(k1 int auto_increment primary key,
|
|
k2 char(200),k3 char(200))engine=innodb;
|
|
SET DEBUG_SYNC= 'row_merge_after_scan
|
|
SIGNAL opened WAIT_FOR flushed';
|
|
ALTER TABLE t1 ADD COLUMN k4 int, ALGORITHM=INPLACE;
|
|
connection default
|
|
SET DEBUG_SYNC= 'now WAIT_FOR opened';
|
|
SET debug = '+d,row_log_tmpfile_fail';
|
|
INSERT INTO t1 select NULL,'aaa','bbb' from t480;
|
|
INSERT INTO t1 select NULL,'aaaa','bbbb' from t480;
|
|
SET DEBUG_SYNC= 'now SIGNAL flushed';
|
|
SET debug = '-d,row_log_tmpfile_fail';
|
|
/*con1 reap*/ ALTER TABLE t1 ADD COLUMN k4 int, ALGORITHM=INPLACE;
|
|
ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
|
|
SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
|
|
COUNT(k1) k2 k3
|
|
480 aaa bbb
|
|
480 aaaa bbbb
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`k1` int(11) NOT NULL AUTO_INCREMENT,
|
|
`k2` char(200) DEFAULT NULL,
|
|
`k3` char(200) DEFAULT NULL,
|
|
PRIMARY KEY (`k1`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1023 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SET DEBUG_SYNC='RESET';
|
|
# SDI output shouldn't show new column "k4" because
|
|
# ALTER failed
|
|
# Remove json files
|
|
# restart
|
|
drop table t1;
|
|
drop table t480;
|
|
SET DEBUG_SYNC='RESET';
|
|
#
|
|
# BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
|
|
# LAST COLUMN OF OLD PK
|
|
#
|
|
SET debug="+d,innodb_alter_table_pk_assert_no_sort";
|
|
create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
|
|
insert into t1 values('abd'), ('acd');
|
|
alter table t1 drop primary key, add primary key(o1(3)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
|
|
insert into t1 values('abd'), ('acd');
|
|
alter table t1 drop primary key, add primary key(o1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
|
|
insert into t1 values('abd'), ('acd');
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb;
|
|
insert into t1 values('abd'), ('acd');
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb;
|
|
insert into t1 values('abd'), ('acd');
|
|
alter table t1 drop primary key, add primary key(o1(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), primary key(o1)) engine = innodb;
|
|
insert into t1 values('abd'), ('acd');
|
|
alter table t1 drop primary key, add primary key(o1(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 drop primary key, add primary key(o1(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 drop primary key, add primary key(o1(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb;
|
|
insert into t1 values('abd', 1), ('acd', 2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb;
|
|
insert into t1 values(1,'abd'), (2,'acd');
|
|
alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb;
|
|
insert into t1 values(1,'abd'), (2,'acd');
|
|
alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
|
|
insert into t1 values(1, 'abd'), (2, 'acd');
|
|
alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
|
|
insert into t1 values(1, 'abd'), (2, 'acd');
|
|
alter table t1 drop primary key, add primary key(o1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb;
|
|
insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
|
|
alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb;
|
|
insert into t1 values(1, 'abd', 1), (2, 'acd', 2);
|
|
alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
|
|
insert into t1 values('abd', 'acd'), ('acd', 'abd');
|
|
alter table t1 drop primary key, add primary key(o1(3),o2(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
|
|
insert into t1 values('abd', 'acd'), ('acd', 'abd');
|
|
alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb;
|
|
insert into t1 values('abd', 'acd'), ('acd', 'abd');
|
|
alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb;
|
|
insert into t1 values('abd', 'acd'), ('acd', 'abd');
|
|
alter table t1 drop primary key, add primary key(o1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb;
|
|
insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
|
|
alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb;
|
|
insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
|
|
alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb;
|
|
insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
|
|
alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb;
|
|
insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd');
|
|
alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 drop primary key, add primary key(o1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1),(2,2);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1,2),(2,2,1);
|
|
alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb;
|
|
insert into t1 values(1,1,2),(2,2,1);
|
|
alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
|
|
insert into t1 values(1,1,2),(2,2,1);
|
|
alter table t1 drop primary key, add primary key(o1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb;
|
|
insert into t1 values(1,1,2,2),(2,2,1,1);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
|
|
insert into t1 values(1,1,2),(2,2,1);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
|
|
insert into t1 values(1,1,2),(2,2,1);
|
|
alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none;
|
|
drop table t1;
|
|
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
|
|
insert into t1 values(1,1,2),(2,2,1);
|
|
alter table t1 drop primary key, add primary key(o1), lock=none;
|
|
drop table t1;
|
|
CREATE TABLE t1(o1 int, o2 int, o3 int, PRIMARY KEY(o1), KEY(o2)) ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1,1,2),(2,2,1);
|
|
EXPLAIN SELECT * FROM t1 ORDER BY o2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
|
|
SELECT * FROM t1 ORDER BY o2;
|
|
o1 o2 o3
|
|
1 1 2
|
|
2 2 1
|
|
ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 DESC);
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SELECT * FROM t1;
|
|
o1 o2 o3
|
|
1 1 2
|
|
2 2 1
|
|
EXPLAIN SELECT * FROM t1 ORDER BY o2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
|
|
SELECT * FROM t1 ORDER BY o2;
|
|
o1 o2 o3
|
|
1 1 2
|
|
2 2 1
|
|
ALTER TABLE t1 DROP INDEX o2, ADD INDEX (o2 ASC);
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SELECT * FROM t1;
|
|
o1 o2 o3
|
|
1 1 2
|
|
2 2 1
|
|
EXPLAIN SELECT * FROM t1 ORDER BY o2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`o1` AS `o1`,`test`.`t1`.`o2` AS `o2`,`test`.`t1`.`o3` AS `o3` from `test`.`t1` order by `test`.`t1`.`o2`
|
|
SELECT * FROM t1 ORDER BY o2;
|
|
o1 o2 o3
|
|
1 1 2
|
|
2 2 1
|
|
DROP TABLE t1;
|
|
SET debug="-d,innodb_alter_table_pk_assert_no_sort";
|