1#
2# BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX
3#
4
5--source include/have_innodb.inc
6--source include/have_debug.inc
7--source include/have_debug_sync.inc
8--source include/have_log_bin.inc
9--source include/count_sessions.inc
10
11SET @old_log_output = @@global.log_output;
12SET @old_slow_query_log = @@global.slow_query_log;
13SET @old_general_log = @@global.general_log;
14SET @old_long_query_time = @@global.long_query_time;
15SET @old_binlog_order_commits = @@global.binlog_order_commits;
16
17SET GLOBAL log_output = 'TABLE';
18SET GLOBAL general_log = 1;
19SET GLOBAL slow_query_log = 1;
20SET GLOBAL long_query_time = 1;
21SET GLOBAL binlog_order_commits = 1;
22
23connect (con1,localhost,root,,);
24connect (con2,localhost,root,,);
25connection default;
26
27--echo # Case 1: Sync blocks DML(insert) on the same table.
28CREATE TABLE t1 (
29        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
30        title VARCHAR(200),
31        FULLTEXT(title)
32) ENGINE = InnoDB;
33
34connection con1;
35
36SET GLOBAL debug="+d,fts_instrument_sync_debug,fts_instrument_sync_sleep";
37
38SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue';
39
40send INSERT INTO t1(title) VALUES('mysql database');
41
42connection con2;
43
44SET DEBUG_SYNC= 'now WAIT_FOR begin';
45
46send SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
47
48connection default;
49SET DEBUG_SYNC= 'now SIGNAL continue';
50
51connection con1;
52--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
53--reap
54
55connection con2;
56--echo /* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
57--reap
58
59connection default;
60-- echo # make con1 & con2 show up in mysql.slow_log
61SELECT SLEEP(2);
62-- echo # slow log results should only contain INSERT INTO t1.
63SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02';
64
65SET GLOBAL debug="-d,fts_instrument_sync_debug,fts_instrument_sync_sleep";
66TRUNCATE TABLE mysql.slow_log;
67
68DROP TABLE t1;
69
70--echo # Case 2: Sync blocks DML(insert) on other tables.
71CREATE TABLE t1 (
72        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
73        title VARCHAR(200),
74        FULLTEXT(title)
75) ENGINE = InnoDB;
76
77CREATE TABLE t2(id INT);
78
79connection con1;
80
81SET GLOBAL debug="+d,fts_instrument_sync_request,fts_instrument_sync_sleep";
82
83SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue';
84
85send INSERT INTO t1(title) VALUES('mysql database');
86
87connection con2;
88
89SET DEBUG_SYNC= 'now WAIT_FOR begin';
90
91send INSERT INTO t2 VALUES(1);
92
93connection default;
94SET DEBUG_SYNC= 'now SIGNAL continue';
95
96connection con1;
97--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
98--reap
99
100connection con2;
101--echo /* conneciton con2 */ INSERT INTO t2 VALUES(1);
102--reap
103
104connection default;
105-- echo # make con1 & con2 show up in mysql.slow_log
106SELECT SLEEP(2);
107-- echo # slow log results should be empty here.
108SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02';
109
110SET GLOBAL debug="-d,fts_instrument_sync_request,fts_instrument_sync_sleep";
111TRUNCATE TABLE mysql.slow_log;
112
113DROP TABLE t1,t2;
114
115disconnect con1;
116disconnect con2;
117
118--source include/wait_until_count_sessions.inc
119
120-- echo # Restore slow log settings.
121SET GLOBAL log_output = @old_log_output;
122SET GLOBAL general_log = @old_general_log;
123SET GLOBAL slow_query_log = @old_slow_query_log;
124SET GLOBAL long_query_time = @old_long_query_time;
125SET GLOBAL binlog_order_commits = @old_binlog_order_commits;
126