1# 2# MDEV-11415 AVOID INTERMEDIATE COMMIT WHILE DOING 3# ALTER TABLE...ALGORITHM=COPY 4# 5CREATE TABLE t(a SERIAL, b INT, c INT, d INT) ENGINE=InnoDB; 6CREATE TABLE t1(a INT, b TEXT, c TEXT, 7FULLTEXT(b), FULLTEXT(c(3)), FULLTEXT(b,c)) ENGINE=InnoDB; 8BEGIN; 9COMMIT; 10SELECT COUNT(*) FROM t; 11COUNT(*) 12999 13UPDATE t SET b=a%7, c=a%11, d=a%13; 14INSERT INTO t1 VALUES(1, 'This is a first b column', 'This is a first c column'); 15INSERT INTO t1 VALUES(2, 'This is a second b column', 'This is a second c column'); 16INSERT INTO t1(a) VALUES(3); 17INSERT INTO t1 VALUES(4, 'This is a third b column', 'This is a third c column'); 18DELETE FROM t1 WHERE a = 2; 19SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first'); 20a b c 211 This is a first b column This is a first c column 22SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first'); 23a b c 241 This is a first b column This is a first c column 25SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column'); 26a b c 271 This is a first b column This is a first c column 284 This is a third b column This is a third c column 29SHOW CREATE TABLE t1; 30Table Create Table 31t1 CREATE TABLE `t1` ( 32 `a` int(11) DEFAULT NULL, 33 `b` text DEFAULT NULL, 34 `c` text DEFAULT NULL, 35 FULLTEXT KEY `b` (`b`), 36 FULLTEXT KEY `c` (`c`), 37 FULLTEXT KEY `b_2` (`b`,`c`) 38) ENGINE=InnoDB DEFAULT CHARSET=latin1 39ALTER TABLE t1 FORCE, ALGORITHM=COPY; 40connect hang,localhost,root; 41SET DEBUG_SYNC='alter_table_copy_trans_commit SIGNAL hung WAIT_FOR ever'; 42# create 32 secondary indexes 43ALTER 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), 44ADD INDEX(b,d,a,c),ADD INDEX(b,d,c,a),ADD INDEX(a,b,c,d),ADD INDEX(a,b,d,c), 45ADD INDEX(a,c,b,d),ADD INDEX(a,c,d,b),ADD INDEX(a,d,b,c),ADD INDEX(a,d,c,b), 46ADD INDEX(c,a,b,d),ADD INDEX(c,a,d,b),ADD INDEX(c,b,a,d),ADD INDEX(c,b,d,a), 47ADD INDEX(c,d,a,b),ADD INDEX(c,d,b,a),ADD INDEX(d,a,b,c),ADD INDEX(d,a,c,b), 48ADD INDEX(d,b,a,c),ADD INDEX(d,b,c,a),ADD INDEX(d,c,a,b),ADD INDEX(d,c,b,a), 49ADD INDEX(a,b,c), ADD INDEX(a,c,b), ADD INDEX(a,c,d), ADD INDEX(a,d,c), 50ADD INDEX(a,b,d), ADD INDEX(a,d,b), ADD INDEX(b,c,d), ADD INDEX(b,d,c), 51ALGORITHM=COPY; 52connection default; 53SET DEBUG_SYNC='now WAIT_FOR hung'; 54# restart: --innodb-force-recovery=3 55disconnect hang; 56#sql-temporary.frm 57#sql-temporary.ibd 58FTS_INDEX_1.ibd 59FTS_INDEX_2.ibd 60FTS_INDEX_3.ibd 61FTS_INDEX_4.ibd 62FTS_INDEX_5.ibd 63FTS_INDEX_6.ibd 64FTS_INDEX_1.ibd 65FTS_INDEX_2.ibd 66FTS_INDEX_3.ibd 67FTS_INDEX_4.ibd 68FTS_INDEX_5.ibd 69FTS_INDEX_6.ibd 70FTS_INDEX_1.ibd 71FTS_INDEX_2.ibd 72FTS_INDEX_3.ibd 73FTS_INDEX_4.ibd 74FTS_INDEX_5.ibd 75FTS_INDEX_6.ibd 76FTSBEING_DELETED.ibd 77FTSBEING_DELETED_CACHE.ibd 78FTSCONFIG.ibd 79FTSDELETED.ibd 80FTSDELETED_CACHE.ibd 81db.opt 82t.frm 83t.ibd 84t1.frm 85t1.ibd 86SHOW CREATE TABLE t; 87Table Create Table 88t CREATE TABLE `t` ( 89 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 90 `b` int(11) DEFAULT NULL, 91 `c` int(11) DEFAULT NULL, 92 `d` int(11) DEFAULT NULL, 93 UNIQUE KEY `a` (`a`) 94) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1 95SELECT COUNT(*) FROM t; 96COUNT(*) 97999 98CHECK TABLE t; 99Table Op Msg_type Msg_text 100test.t check status OK 101SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first'); 102a b c 1031 This is a first b column This is a first c column 104SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first'); 105a b c 1061 This is a first b column This is a first c column 107SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column'); 108a b c 1091 This is a first b column This is a first c column 1104 This is a third b column This is a third c column 111SHOW CREATE TABLE t1; 112Table Create Table 113t1 CREATE TABLE `t1` ( 114 `a` int(11) DEFAULT NULL, 115 `b` text DEFAULT NULL, 116 `c` text DEFAULT NULL, 117 FULLTEXT KEY `b` (`b`), 118 FULLTEXT KEY `c` (`c`), 119 FULLTEXT KEY `b_2` (`b`,`c`) 120) ENGINE=InnoDB DEFAULT CHARSET=latin1 121CHECK TABLE t1; 122Table Op Msg_type Msg_text 123test.t1 check status OK 124# restart: --innodb-read-only 125#sql-temporary.frm 126#sql-temporary.ibd 127FTS_INDEX_1.ibd 128FTS_INDEX_2.ibd 129FTS_INDEX_3.ibd 130FTS_INDEX_4.ibd 131FTS_INDEX_5.ibd 132FTS_INDEX_6.ibd 133FTS_INDEX_1.ibd 134FTS_INDEX_2.ibd 135FTS_INDEX_3.ibd 136FTS_INDEX_4.ibd 137FTS_INDEX_5.ibd 138FTS_INDEX_6.ibd 139FTS_INDEX_1.ibd 140FTS_INDEX_2.ibd 141FTS_INDEX_3.ibd 142FTS_INDEX_4.ibd 143FTS_INDEX_5.ibd 144FTS_INDEX_6.ibd 145FTSBEING_DELETED.ibd 146FTSBEING_DELETED_CACHE.ibd 147FTSCONFIG.ibd 148FTSDELETED.ibd 149FTSDELETED_CACHE.ibd 150db.opt 151t.frm 152t.ibd 153t1.frm 154t1.ibd 155SHOW CREATE TABLE t; 156Table Create Table 157t CREATE TABLE `t` ( 158 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 159 `b` int(11) DEFAULT NULL, 160 `c` int(11) DEFAULT NULL, 161 `d` int(11) DEFAULT NULL, 162 UNIQUE KEY `a` (`a`) 163) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1 164SELECT COUNT(*) FROM t; 165COUNT(*) 166999 167CHECK TABLE t; 168Table Op Msg_type Msg_text 169test.t check status OK 170SELECT * FROM t1 WHERE MATCH(b) AGAINST ('first'); 171a b c 1721 This is a first b column This is a first c column 173SELECT * FROM t1 WHERE MATCH(c) AGAINST ('first'); 174a b c 1751 This is a first b column This is a first c column 176SELECT * FROM t1 WHERE MATCH(b,c) AGAINST ('column'); 177a b c 1781 This is a first b column This is a first c column 1794 This is a third b column This is a third c column 180SHOW CREATE TABLE t1; 181Table Create Table 182t1 CREATE TABLE `t1` ( 183 `a` int(11) DEFAULT NULL, 184 `b` text DEFAULT NULL, 185 `c` text DEFAULT NULL, 186 FULLTEXT KEY `b` (`b`), 187 FULLTEXT KEY `c` (`c`), 188 FULLTEXT KEY `b_2` (`b`,`c`) 189) ENGINE=InnoDB DEFAULT CHARSET=latin1 190CHECK TABLE t1; 191Table Op Msg_type Msg_text 192test.t1 check status OK 193# restart 194#sql-temporary.frm 195FTS_INDEX_1.ibd 196FTS_INDEX_2.ibd 197FTS_INDEX_3.ibd 198FTS_INDEX_4.ibd 199FTS_INDEX_5.ibd 200FTS_INDEX_6.ibd 201FTS_INDEX_1.ibd 202FTS_INDEX_2.ibd 203FTS_INDEX_3.ibd 204FTS_INDEX_4.ibd 205FTS_INDEX_5.ibd 206FTS_INDEX_6.ibd 207FTS_INDEX_1.ibd 208FTS_INDEX_2.ibd 209FTS_INDEX_3.ibd 210FTS_INDEX_4.ibd 211FTS_INDEX_5.ibd 212FTS_INDEX_6.ibd 213FTSBEING_DELETED.ibd 214FTSBEING_DELETED_CACHE.ibd 215FTSCONFIG.ibd 216FTSDELETED.ibd 217FTSDELETED_CACHE.ibd 218db.opt 219t.frm 220t.ibd 221t1.frm 222t1.ibd 223DROP TABLE t1,t; 224