mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
223 lines
5.9 KiB
Text
223 lines
5.9 KiB
Text
#
|
|
# MDEV-11415 AVOID INTERMEDIATE COMMIT WHILE DOING
|
|
# ALTER TABLE...ALGORITHM=COPY
|
|
#
|
|
CREATE TABLE t(a SERIAL, b INT, c INT, d INT) ENGINE=InnoDB;
|
|
CREATE TABLE t1(a INT, b TEXT, c TEXT,
|
|
FULLTEXT(b), FULLTEXT(c(3)), FULLTEXT(b,c)) ENGINE=InnoDB;
|
|
BEGIN;
|
|
COMMIT;
|
|
SELECT COUNT(*) FROM t;
|
|
COUNT(*)
|
|
999
|
|
UPDATE t SET b=a%7, c=a%11, d=a%13;
|
|
INSERT INTO t1 VALUES(1, 'This is a first b column', 'This is a first c column');
|
|
INSERT INTO t1 VALUES(2, 'This is a second b column', 'This is a second c column');
|
|
INSERT INTO t1(a) VALUES(3);
|
|
INSERT INTO t1 VALUES(4, 'This is a third b column', 'This is a third c column');
|
|
DELETE FROM t1 WHERE a = 2;
|
|
SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
4 This is a third b column This is a third c column
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text DEFAULT NULL,
|
|
`c` text DEFAULT NULL,
|
|
FULLTEXT KEY `b` (`b`),
|
|
FULLTEXT KEY `c` (`c`),
|
|
FULLTEXT KEY `b_2` (`b`,`c`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
ALTER TABLE t1 FORCE, ALGORITHM=COPY;
|
|
connect hang,localhost,root;
|
|
SET DEBUG_SYNC='alter_table_copy_trans_commit SIGNAL hung WAIT_FOR ever';
|
|
# create 32 secondary indexes
|
|
ALTER TABLE t ADD INDEX(b,c,d,a),ADD INDEX(b,c,a,d),ADD INDEX(b,a,c,d),ADD INDEX(b,a,d,c),
|
|
ADD INDEX(b,d,a,c),ADD INDEX(b,d,c,a),ADD INDEX(a,b,c,d),ADD INDEX(a,b,d,c),
|
|
ADD INDEX(a,c,b,d),ADD INDEX(a,c,d,b),ADD INDEX(a,d,b,c),ADD INDEX(a,d,c,b),
|
|
ADD INDEX(c,a,b,d),ADD INDEX(c,a,d,b),ADD INDEX(c,b,a,d),ADD INDEX(c,b,d,a),
|
|
ADD INDEX(c,d,a,b),ADD INDEX(c,d,b,a),ADD INDEX(d,a,b,c),ADD INDEX(d,a,c,b),
|
|
ADD INDEX(d,b,a,c),ADD INDEX(d,b,c,a),ADD INDEX(d,c,a,b),ADD INDEX(d,c,b,a),
|
|
ADD INDEX(a,b,c), ADD INDEX(a,c,b), ADD INDEX(a,c,d), ADD INDEX(a,d,c),
|
|
ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c),
|
|
ALGORITHM=COPY;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR hung';
|
|
# restart: --innodb-force-recovery=3
|
|
disconnect hang;
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTSBEING_DELETED.ibd
|
|
FTSBEING_DELETED_CACHE.ibd
|
|
FTSCONFIG.ibd
|
|
FTSDELETED.ibd
|
|
FTSDELETED_CACHE.ibd
|
|
db.opt
|
|
t.frm
|
|
t.ibd
|
|
t1.frm
|
|
t1.ibd
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
`d` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `a` (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT COUNT(*) FROM t;
|
|
COUNT(*)
|
|
999
|
|
CHECK TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
4 This is a third b column This is a third c column
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text DEFAULT NULL,
|
|
`c` text DEFAULT NULL,
|
|
FULLTEXT KEY `b` (`b`),
|
|
FULLTEXT KEY `c` (`c`),
|
|
FULLTEXT KEY `b_2` (`b`,`c`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
# restart: --innodb-read-only
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTSBEING_DELETED.ibd
|
|
FTSBEING_DELETED_CACHE.ibd
|
|
FTSCONFIG.ibd
|
|
FTSDELETED.ibd
|
|
FTSDELETED_CACHE.ibd
|
|
db.opt
|
|
t.frm
|
|
t.ibd
|
|
t1.frm
|
|
t1.ibd
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
`c` int(11) DEFAULT NULL,
|
|
`d` int(11) DEFAULT NULL,
|
|
UNIQUE KEY `a` (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
SELECT COUNT(*) FROM t;
|
|
COUNT(*)
|
|
999
|
|
CHECK TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t check status OK
|
|
SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column');
|
|
a b c
|
|
1 This is a first b column This is a first c column
|
|
4 This is a third b column This is a third c column
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` text DEFAULT NULL,
|
|
`c` text DEFAULT NULL,
|
|
FULLTEXT KEY `b` (`b`),
|
|
FULLTEXT KEY `c` (`c`),
|
|
FULLTEXT KEY `b_2` (`b`,`c`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SET GLOBAL innodb_doublewrite=ON;
|
|
SELECT @@GLOBAL.innodb_doublewrite "OFF expected";
|
|
OFF expected
|
|
OFF
|
|
SET GLOBAL innodb_buf_flush_list_now=ON;
|
|
# restart
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTS_INDEX_1.ibd
|
|
FTS_INDEX_2.ibd
|
|
FTS_INDEX_3.ibd
|
|
FTS_INDEX_4.ibd
|
|
FTS_INDEX_5.ibd
|
|
FTS_INDEX_6.ibd
|
|
FTSBEING_DELETED.ibd
|
|
FTSBEING_DELETED_CACHE.ibd
|
|
FTSCONFIG.ibd
|
|
FTSDELETED.ibd
|
|
FTSDELETED_CACHE.ibd
|
|
db.opt
|
|
t.frm
|
|
t.ibd
|
|
t1.frm
|
|
t1.ibd
|
|
DROP TABLE t1,t;
|