Merge remote-tracking branch 'origin/10.2' into bb-10.2-ext

12 files changed, 734 insertions, 5 deletions

diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result
new file mode 100644
index 00000000000..5644075038a
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result
@@ -0,0 +1,11 @@
+CREATE TABLE t1(a VARCHAR(5),FULLTEXT KEY(a)) ENGINE=InnoDB;
+SET DEBUG_SYNC = 'get_next_FTS_DOC_ID SIGNAL prepared WAIT_FOR race';
+REPLACE INTO t1(a) values('aaa');
+connect dml, localhost, root, ,;
+SET DEBUG_SYNC = 'now WAIT_FOR prepared';
+REPLACE INTO t1(a) VALUES('aaa');
+SET DEBUG_SYNC = 'now SIGNAL race';
+disconnect dml;
+connection default;
+SET DEBUG_SYNC = 'RESET';
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result
index f096f1c7dd5..42e294b3293 100644
--- a/mysql-test/suite/innodb_fts/r/fulltext.result
+++ b/mysql-test/suite/innodb_fts/r/fulltext.result
@@ -1,4 +1,3 @@
-drop table if exists t1,t2,t3;
 CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB;
 INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
 ('Full-text indexes', 'are called collections'),
@@ -637,3 +636,58 @@ EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 End of 5.1 tests
+CREATE TABLE z(a INTEGER) engine=innodb;
+CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb;
+EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
+ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
+SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
+ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
+EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z;
+ERROR 42S22: Unknown column 'b' in 'field list'
+SELECT MATCH(b) AGAINST ('*') FROM z;
+ERROR 42S22: Unknown column 'b' in 'field list'
+EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z;
+ERROR HY000: Can't find FULLTEXT index matching the column list
+SELECT MATCH(a) AGAINST ('*') FROM z;
+ERROR HY000: Can't find FULLTEXT index matching the column list
+EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	q	ALL	NULL	NULL	NULL	NULL	1	
+SELECT MATCH(b) AGAINST ('*') FROM q;
+ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
+DROP TABLE z, q;
+create table t (
+FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t)
+) ENGINE=InnoDB;
+INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo');
+SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+LIMIT 0;
+FTS_DOC_ID	t
+SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+LIMIT 1;
+FTS_DOC_ID	t
+1	foo bar
+SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+LIMIT 2;
+FTS_DOC_ID	t
+1	foo bar
+2	foo bar
+SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+LIMIT 3;
+FTS_DOC_ID	t
+1	foo bar
+2	foo bar
+3	foo
+SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+LIMIT 4;
+FTS_DOC_ID	t
+1	foo bar
+2	foo bar
+3	foo
+SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+LIMIT 5;
+FTS_DOC_ID	t
+1	foo bar
+2	foo bar
+3	foo
+DROP TABLE t;
diff --git a/mysql-test/suite/innodb_fts/r/sync.result b/mysql-test/suite/innodb_fts/r/sync.result
new file mode 100644
index 00000000000..82959cfea96
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/r/sync.result
@@ -0,0 +1,145 @@
+connect  con1,localhost,root,,;
+connection default;
+# Case 1: Test select and insert(row in both disk and cache)
+CREATE TABLE t1 (
+FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+title VARCHAR(200),
+FULLTEXT(title)
+) ENGINE = InnoDB;
+INSERT INTO t1(title) VALUES('mysql');
+INSERT INTO t1(title) VALUES('database');
+connection con1;
+SET @old_dbug = @@SESSION.debug_dbug;
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected';
+INSERT INTO t1(title) VALUES('mysql database');
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR written';
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+database	2	3	2	2	0
+database	2	3	2	3	6
+mysql	1	3	2	1	0
+mysql	1	3	2	3	0
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+SET GLOBAL innodb_ft_aux_table=default;
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+FTS_DOC_ID	title
+1	mysql
+2	database
+SET DEBUG_SYNC= 'now SIGNAL selected';
+connection con1;
+SET @old_dbug = @@SESSION.debug_dbug;
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+database	2	3	2	2	0
+database	2	3	2	3	6
+mysql	1	3	2	1	0
+mysql	1	3	2	3	0
+SET GLOBAL innodb_ft_aux_table=default;
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+FTS_DOC_ID	title
+3	mysql database
+1	mysql
+2	database
+connection default;
+DROP TABLE t1;
+# Case 2: Test insert and insert(sync)
+CREATE TABLE t1 (
+FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+title VARCHAR(200),
+FULLTEXT(title)
+) ENGINE = InnoDB;
+INSERT INTO t1(title) VALUES('mysql');
+INSERT INTO t1(title) VALUES('database');
+connection con1;
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted';
+INSERT INTO t1(title) VALUES('mysql database');
+connection default;
+SET DEBUG_SYNC= 'now WAIT_FOR written';
+INSERT INTO t1(title) VALUES('mysql database');
+SET DEBUG_SYNC= 'now SIGNAL inserted';
+connection con1;
+SET debug_dbug = @old_dbug;
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+database	2	3	2	2	0
+database	2	3	2	3	6
+database	4	4	1	4	6
+mysql	1	4	3	1	0
+mysql	1	4	3	3	0
+mysql	1	4	3	4	0
+SET GLOBAL innodb_ft_aux_table=default;
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+FTS_DOC_ID	title
+3	mysql database
+4	mysql database
+1	mysql
+2	database
+connection default;
+disconnect con1;
+DROP TABLE t1;
+# Case 3: Test insert crash recovery
+CREATE TABLE t1 (
+FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+title VARCHAR(200),
+FULLTEXT(title)
+) ENGINE = InnoDB;
+INSERT INTO t1(title) VALUES('database');
+SET debug_dbug = '+d,fts_instrument_sync_debug,fts_write_node_crash';
+INSERT INTO t1(title) VALUES('mysql');
+ERROR HY000: Lost connection to MySQL server during query
+After restart
+SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
+FTS_DOC_ID	title
+1	database
+SET @old_dbug = @@SESSION.debug_dbug;
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+INSERT INTO t1(title) VALUES('mysql');
+SET debug_dbug = @old_dbug;
+SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
+FTS_DOC_ID	title
+1	database
+2	mysql
+DROP TABLE t1;
+# Case 4: Test sync commit & rollback in background
+CREATE TABLE t1(
+id INT AUTO_INCREMENT,
+title VARCHAR(100),
+FULLTEXT(title),
+PRIMARY KEY(id)) ENGINE=InnoDB;
+SET debug_dbug = '+d,fts_instrument_sync';
+INSERT INTO t1(title) VALUES('mysql');
+SET debug_dbug = @old_dbug;
+SET @old_global_dbug = @@GLOBAL.debug_dbug;
+SET @old_dbug = @@SESSION.debug_dbug;
+SET GLOBAL debug_dbug='+d,fts_instrument_sync,fts_instrument_sync_interrupted';
+INSERT INTO t1(title) VALUES('database');
+SET GLOBAL debug_dbug = @old_global_dbug;
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+INSERT INTO t1(title) VALUES('good');
+SET debug_dbug = @old_dbug;
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+database	2	2	1	2	0
+good	3	3	1	3	0
+mysql	1	1	1	1	0
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
+SET GLOBAL innodb_ft_aux_table=default;
+SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good');
+id	title
+1	mysql
+2	database
+3	good
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb_fts/r/sync_block.result b/mysql-test/suite/innodb_fts/r/sync_block.result
new file mode 100644
index 00000000000..f9f695c42f4
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/r/sync_block.result
@@ -0,0 +1,83 @@
+SET @old_log_output = @@global.log_output;
+SET @old_slow_query_log = @@global.slow_query_log;
+SET @old_general_log = @@global.general_log;
+SET @old_long_query_time = @@global.long_query_time;
+SET @old_debug = @@global.debug_dbug;
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = 1;
+SET GLOBAL slow_query_log = 1;
+SET GLOBAL long_query_time = 1;
+connect  con1,localhost,root,,;
+connect  con2,localhost,root,,;
+connection default;
+# Case 1: Sync blocks DML(insert) on the same table.
+CREATE TABLE t1 (
+FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+title VARCHAR(200),
+FULLTEXT(title)
+) ENGINE = InnoDB;
+connection con1;
+SET GLOBAL debug_dbug='+d,fts_instrument_sync_debug,fts_instrument_sync_sleep';
+SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue';
+INSERT INTO t1(title) VALUES('mysql database');
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR begin';
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+connection default;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection con1;
+/* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
+connection con2;
+/* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+FTS_DOC_ID	title
+connection default;
+# make con1 & con2 show up in mysql.slow_log
+SELECT SLEEP(2);
+SLEEP(2)
+0
+# slow log results should only contain INSERT INTO t1.
+SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02';
+sql_text
+INSERT INTO t1(title) VALUES('mysql database')
+SET GLOBAL debug_dbug = @old_debug_dbug;
+TRUNCATE TABLE mysql.slow_log;
+DROP TABLE t1;
+# Case 2: Sync blocks DML(insert) on other tables.
+CREATE TABLE t1 (
+FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+title VARCHAR(200),
+FULLTEXT(title)
+) ENGINE = InnoDB;
+CREATE TABLE t2(id INT);
+connection con1;
+SET GLOBAL debug_dbug='+d,fts_instrument_sync_request,fts_instrument_sync_sleep';
+SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue';
+INSERT INTO t1(title) VALUES('mysql database');
+connection con2;
+SET DEBUG_SYNC= 'now WAIT_FOR begin';
+INSERT INTO t2 VALUES(1);
+connection default;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+connection con1;
+/* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
+connection con2;
+/* conneciton con2 */ INSERT INTO t2 VALUES(1);
+connection default;
+SET DEBUG_SYNC = 'RESET';
+# make con1 & con2 show up in mysql.slow_log
+SELECT SLEEP(2);
+SLEEP(2)
+0
+# slow log results should be empty here.
+SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02';
+sql_text
+SET GLOBAL debug_dbug = @old_debug_dbug;
+TRUNCATE TABLE mysql.slow_log;
+DROP TABLE t1,t2;
+disconnect con1;
+disconnect con2;
+# Restore slow log settings.
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL general_log = @old_general_log;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL long_query_time = @old_long_query_time;
diff --git a/mysql-test/suite/innodb_fts/r/truncate.result b/mysql-test/suite/innodb_fts/r/truncate.result
new file mode 100644
index 00000000000..0e21717a4d3
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/r/truncate.result
@@ -0,0 +1,31 @@
+#
+# Bug#25053705 - INVALID I/O ON TABLE AFTER TRUNCATE
+#
+CREATE TABLE t1 (a INT, d INT, b VARCHAR(198), c CHAR(158), FULLTEXT fts1(c,b),
+FULLTEXT fts2(c));
+TRUNCATE TABLE t1;
+INSERT INTO t1 (a,d,b,c) VALUES (
+'79795','6',repeat('uololoaroolccaaruolraloouuoocorrcorurlu','1'),
+repeat('orouculcaocuocloooolooloooaorlroclouulrrucclulalouaulrluorooaclllluuorc
+cuullucocraloracurooulrooauuar','1'));
+CREATE TABLE t2 (a INT, d INT, b VARCHAR(198), c CHAR(158), FULLTEXT fts1(c,b));
+INSERT INTO t2 VALUES (1, 1, repeat('uololoaroolccaaruolraloouuoocorrcorurlu','1'),
+repeat('orouculcaocuocloooolooloooaorlroclouulrrucclulalouaulrluorooaclllluuorccuullucocraloracurooulrooauuar','1'));
+create procedure insert_t2(IN total int)
+begin
+declare i int default 1;
+while (i <= total) DO
+insert into t2 select * from t2;
+set i = i + 1;
+end while;
+end|
+CALL insert_t2(15);
+SET @save_dbug = @@SESSION.DEBUG_DBUG;
+SET DEBUG_DBUG = '+d,innodb_invalid_read_after_truncate';
+INSERT INTO t1 (a,d,b,c) VALUES (
+'7795','6',repeat('uololoaroolccaaruolraloouuoocorrcorurlu','1'),
+repeat('orouculcaocuocloooolooloooaorlroclouulrrucclulalouaulrluorooaclllluuorc
+cuullucocraloracurooulrooauuar','1'));
+SET DEBUG_DBUG = @save_dbug;
+DROP PROCEDURE insert_t2;
+DROP TABLE t1,t2;
diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test
new file mode 100644
index 00000000000..e5d61cd8b05
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test
@@ -0,0 +1,20 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+
+CREATE TABLE t1(a VARCHAR(5),FULLTEXT KEY(a)) ENGINE=InnoDB;
+SET DEBUG_SYNC = 'get_next_FTS_DOC_ID SIGNAL prepared WAIT_FOR race';
+--send
+REPLACE INTO t1(a) values('aaa');
+
+connect(dml, localhost, root, ,);
+SET DEBUG_SYNC = 'now WAIT_FOR prepared';
+REPLACE INTO t1(a) VALUES('aaa');
+SET DEBUG_SYNC = 'now SIGNAL race';
+disconnect dml;
+
+connection default;
+reap;
+SET DEBUG_SYNC = 'RESET';
+
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb_fts/t/fulltext.test b/mysql-test/suite/innodb_fts/t/fulltext.test
index 90d5d5c71e0..663b202265b 100644
--- a/mysql-test/suite/innodb_fts/t/fulltext.test
+++ b/mysql-test/suite/innodb_fts/t/fulltext.test
@@ -4,10 +4,6 @@
 
 --source include/have_innodb.inc
 
---disable_warnings
-drop table if exists t1,t2,t3;
---enable_warnings
-
 CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB;
 INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
                        ('Full-text indexes', 'are called collections'),
@@ -679,3 +675,45 @@ DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
 
 --echo End of 5.1 tests
+
+# This is an adapted and extended version of an Oracle test for
+# Bug#21140111: Explain ... match against: Assertion failed: ret ...
+# No bug was repeatable for MariaDB.
+
+CREATE TABLE z(a INTEGER) engine=innodb;
+CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb;
+
+--error ER_PARSE_ERROR
+EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
+--error ER_PARSE_ERROR
+SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
+--error ER_BAD_FIELD_ERROR
+EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z;
+--error ER_BAD_FIELD_ERROR
+SELECT MATCH(b) AGAINST ('*') FROM z;
+--error ER_FT_MATCHING_KEY_NOT_FOUND
+EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z;
+--error ER_FT_MATCHING_KEY_NOT_FOUND
+SELECT MATCH(a) AGAINST ('*') FROM z;
+EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q;
+--error ER_PARSE_ERROR
+SELECT MATCH(b) AGAINST ('*') FROM q;
+
+DROP TABLE z, q;
+
+create table t (
+  FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t)
+) ENGINE=InnoDB;
+
+INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo');
+let $limit=0;
+let $N=6;
+while ($N)
+{
+ eval SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
+ LIMIT $limit;
+ inc $limit;
+ dec $N;
+}
+
+DROP TABLE t;
diff --git a/mysql-test/suite/innodb_fts/t/sync.opt b/mysql-test/suite/innodb_fts/t/sync.opt
new file mode 100644
index 00000000000..7724f97647f
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/sync.opt
@@ -0,0 +1,2 @@
+--innodb-ft-index-cache
+--innodb-ft-index-table
diff --git a/mysql-test/suite/innodb_fts/t/sync.test b/mysql-test/suite/innodb_fts/t/sync.test
new file mode 100644
index 00000000000..2ad5423d73b
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/sync.test
@@ -0,0 +1,170 @@
+#
+# BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX
+#
+
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+--source include/not_valgrind.inc
+--source include/not_embedded.inc
+--source include/not_crashrep.inc
+
+connect (con1,localhost,root,,);
+connection default;
+
+--echo # Case 1: Test select and insert(row in both disk and cache)
+CREATE TABLE t1 (
+        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+        title VARCHAR(200),
+        FULLTEXT(title)
+) ENGINE = InnoDB;
+
+INSERT INTO t1(title) VALUES('mysql');
+INSERT INTO t1(title) VALUES('database');
+
+connection con1;
+
+SET @old_dbug = @@SESSION.debug_dbug;
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+
+SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected';
+
+send INSERT INTO t1(title) VALUES('mysql database');
+
+connection default;
+
+SET DEBUG_SYNC= 'now WAIT_FOR written';
+
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+SET GLOBAL innodb_ft_aux_table=default;
+
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+
+SET DEBUG_SYNC= 'now SIGNAL selected';
+
+connection con1;
+--reap
+
+SET @old_dbug = @@SESSION.debug_dbug;
+
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+SET GLOBAL innodb_ft_aux_table=default;
+
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+
+connection default;
+
+DROP TABLE t1;
+
+--echo # Case 2: Test insert and insert(sync)
+CREATE TABLE t1 (
+        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+        title VARCHAR(200),
+        FULLTEXT(title)
+) ENGINE = InnoDB;
+
+INSERT INTO t1(title) VALUES('mysql');
+INSERT INTO t1(title) VALUES('database');
+
+connection con1;
+
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+
+SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted';
+
+send INSERT INTO t1(title) VALUES('mysql database');
+
+connection default;
+
+SET DEBUG_SYNC= 'now WAIT_FOR written';
+
+INSERT INTO t1(title) VALUES('mysql database');
+
+SET DEBUG_SYNC= 'now SIGNAL inserted';
+
+connection con1;
+--reap
+
+SET debug_dbug = @old_dbug;
+
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+SET GLOBAL innodb_ft_aux_table=default;
+
+SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+
+connection default;
+disconnect con1;
+
+DROP TABLE t1;
+
+--echo # Case 3: Test insert crash recovery
+--let $_expect_file_name=$MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
+
+CREATE TABLE t1 (
+        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+        title VARCHAR(200),
+        FULLTEXT(title)
+) ENGINE = InnoDB;
+
+INSERT INTO t1(title) VALUES('database');
+
+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+
+SET debug_dbug = '+d,fts_instrument_sync_debug,fts_write_node_crash';
+
+--error 2013
+INSERT INTO t1(title) VALUES('mysql');
+
+--source include/start_mysqld.inc
+
+-- echo After restart
+SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
+
+SET @old_dbug = @@SESSION.debug_dbug;
+
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+
+INSERT INTO t1(title) VALUES('mysql');
+
+SET debug_dbug = @old_dbug;
+
+SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
+
+DROP TABLE t1;
+
+--echo # Case 4: Test sync commit & rollback in background
+CREATE TABLE t1(
+        id INT AUTO_INCREMENT,
+        title VARCHAR(100),
+        FULLTEXT(title),
+        PRIMARY KEY(id)) ENGINE=InnoDB;
+
+SET debug_dbug = '+d,fts_instrument_sync';
+INSERT INTO t1(title) VALUES('mysql');
+SET debug_dbug = @old_dbug;
+
+--source include/restart_mysqld.inc
+
+SET @old_global_dbug = @@GLOBAL.debug_dbug;
+SET @old_dbug = @@SESSION.debug_dbug;
+SET GLOBAL debug_dbug='+d,fts_instrument_sync,fts_instrument_sync_interrupted';
+INSERT INTO t1(title) VALUES('database');
+SET GLOBAL debug_dbug = @old_global_dbug;
+
+SET debug_dbug = '+d,fts_instrument_sync_debug';
+INSERT INTO t1(title) VALUES('good');
+SET debug_dbug = @old_dbug;
+
+SET GLOBAL innodb_ft_aux_table="test/t1";
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
+SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+SET GLOBAL innodb_ft_aux_table=default;
+
+SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good');
+
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb_fts/t/sync_block.test b/mysql-test/suite/innodb_fts/t/sync_block.test
new file mode 100644
index 00000000000..1dd3c2bc38e
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/sync_block.test
@@ -0,0 +1,124 @@
+#
+# BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX
+#
+
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_log_bin.inc
+--source include/count_sessions.inc
+
+SET @old_log_output = @@global.log_output;
+SET @old_slow_query_log = @@global.slow_query_log;
+SET @old_general_log = @@global.general_log;
+SET @old_long_query_time = @@global.long_query_time;
+SET @old_debug = @@global.debug_dbug;
+
+SET GLOBAL log_output = 'TABLE';
+SET GLOBAL general_log = 1;
+SET GLOBAL slow_query_log = 1;
+SET GLOBAL long_query_time = 1;
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+connection default;
+
+--echo # Case 1: Sync blocks DML(insert) on the same table.
+CREATE TABLE t1 (
+        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+        title VARCHAR(200),
+        FULLTEXT(title)
+) ENGINE = InnoDB;
+
+connection con1;
+
+SET GLOBAL debug_dbug='+d,fts_instrument_sync_debug,fts_instrument_sync_sleep';
+
+SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue';
+
+send INSERT INTO t1(title) VALUES('mysql database');
+
+connection con2;
+
+SET DEBUG_SYNC= 'now WAIT_FOR begin';
+
+send SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+
+connection default;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+connection con1;
+--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
+--reap
+
+connection con2;
+--echo /* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
+--reap
+
+connection default;
+-- echo # make con1 & con2 show up in mysql.slow_log
+SELECT SLEEP(2);
+-- echo # slow log results should only contain INSERT INTO t1.
+SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02';
+
+SET GLOBAL debug_dbug = @old_debug_dbug;
+TRUNCATE TABLE mysql.slow_log;
+
+DROP TABLE t1;
+
+--echo # Case 2: Sync blocks DML(insert) on other tables.
+CREATE TABLE t1 (
+        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
+        title VARCHAR(200),
+        FULLTEXT(title)
+) ENGINE = InnoDB;
+
+CREATE TABLE t2(id INT);
+
+connection con1;
+
+SET GLOBAL debug_dbug='+d,fts_instrument_sync_request,fts_instrument_sync_sleep';
+
+SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue';
+
+send INSERT INTO t1(title) VALUES('mysql database');
+
+connection con2;
+
+SET DEBUG_SYNC= 'now WAIT_FOR begin';
+
+send INSERT INTO t2 VALUES(1);
+
+connection default;
+SET DEBUG_SYNC= 'now SIGNAL continue';
+
+connection con1;
+--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
+--reap
+
+connection con2;
+--echo /* conneciton con2 */ INSERT INTO t2 VALUES(1);
+--reap
+
+connection default;
+SET DEBUG_SYNC = 'RESET';
+-- echo # make con1 & con2 show up in mysql.slow_log
+SELECT SLEEP(2);
+-- echo # slow log results should be empty here.
+SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02';
+
+SET GLOBAL debug_dbug = @old_debug_dbug;
+TRUNCATE TABLE mysql.slow_log;
+
+DROP TABLE t1,t2;
+
+disconnect con1;
+disconnect con2;
+
+--source include/wait_until_count_sessions.inc
+
+-- echo # Restore slow log settings.
+SET GLOBAL log_output = @old_log_output;
+SET GLOBAL general_log = @old_general_log;
+SET GLOBAL slow_query_log = @old_slow_query_log;
+SET GLOBAL long_query_time = @old_long_query_time;
diff --git a/mysql-test/suite/innodb_fts/t/truncate.opt b/mysql-test/suite/innodb_fts/t/truncate.opt
new file mode 100644
index 00000000000..d143088d352
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/truncate.opt
@@ -0,0 +1 @@
+--innodb-random-read-ahead=1
diff --git a/mysql-test/suite/innodb_fts/t/truncate.test b/mysql-test/suite/innodb_fts/t/truncate.test
new file mode 100644
index 00000000000..46ba732b019
--- /dev/null
+++ b/mysql-test/suite/innodb_fts/t/truncate.test
@@ -0,0 +1,50 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+
+--echo #
+--echo # Bug#25053705 - INVALID I/O ON TABLE AFTER TRUNCATE
+--echo #
+
+CREATE TABLE t1 (a INT, d INT, b VARCHAR(198), c CHAR(158), FULLTEXT fts1(c,b),
+FULLTEXT fts2(c));
+
+TRUNCATE TABLE t1;
+
+INSERT INTO t1 (a,d,b,c) VALUES (
+'79795','6',repeat('uololoaroolccaaruolraloouuoocorrcorurlu','1'),
+repeat('orouculcaocuocloooolooloooaorlroclouulrrucclulalouaulrluorooaclllluuorc
+cuullucocraloracurooulrooauuar','1'));
+
+# The following CREATE TABLE and INSERTs are used to remove the pages related to table t1
+# from the buffer pool.
+CREATE TABLE t2 (a INT, d INT, b VARCHAR(198), c CHAR(158), FULLTEXT fts1(c,b));
+
+INSERT INTO t2 VALUES (1, 1, repeat('uololoaroolccaaruolraloouuoocorrcorurlu','1'),
+repeat('orouculcaocuocloooolooloooaorlroclouulrrucclulalouaulrluorooaclllluuorccuullucocraloracurooulrooauuar','1'));
+
+delimiter |;
+create procedure insert_t2(IN total int)
+begin
+        declare i int default 1;
+        while (i <= total) DO
+                insert into t2 select * from t2;
+                set i = i + 1;
+        end while;
+end|
+delimiter ;|
+
+CALL insert_t2(15);
+
+SET @save_dbug = @@SESSION.DEBUG_DBUG;
+SET DEBUG_DBUG = '+d,innodb_invalid_read_after_truncate';
+
+INSERT INTO t1 (a,d,b,c) VALUES (
+'7795','6',repeat('uololoaroolccaaruolraloouuoocorrcorurlu','1'),
+repeat('orouculcaocuocloooolooloooaorlroclouulrrucclulalouaulrluorooaclllluuorc
+cuullucocraloracurooulrooauuar','1'));
+
+SET DEBUG_DBUG = @save_dbug;
+
+DROP PROCEDURE insert_t2;
+
+DROP TABLE t1,t2;