#
# 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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_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=latin1 COLLATE=latin1_swedish_ci
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
# 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;