Debian Codegraph @

mysql-test/suite/innodb_fts/t/sync.test

raw
1 #
2 # BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX
3 #
4 --source include/big_test.inc
5 --source include/have_debug_sync.inc
6 --source include/not_valgrind.inc
7 --source include/not_crashrep.inc
8 --source include/not_log_bin.inc
9 --source include/count_sessions.inc
10
11 connect (con1,localhost,root,,);
12 connection default;
13
14 --echo # Case 1: Test select and insert(row in both disk and cache)
15 CREATE TABLE t1 (
16 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
17 title VARCHAR(200),
18 FULLTEXT(title)
19 ) ENGINE = InnoDB;
20
21 INSERT INTO t1(title) VALUES('mysql');
22 INSERT INTO t1(title) VALUES('database');
23
24 connection con1;
25
26 SET SESSION debug="+d,fts_instrument_sync_debug";
27
28 SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected';
29
30 send INSERT INTO t1(title) VALUES('mysql database');
31
32 connection default;
33
34 SET DEBUG_SYNC= 'now WAIT_FOR written';
35
36 SET GLOBAL innodb_ft_aux_table="test/t1";
37 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
38 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
39 SET GLOBAL innodb_ft_aux_table=default;
40
41 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
42
43 SET DEBUG_SYNC= 'now SIGNAL selected';
44
45 connection con1;
46 --echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
47 --reap
48
49 SET SESSION debug="-d,fts_instrument_sync_debug";
50
51 SET GLOBAL innodb_ft_aux_table="test/t1";
52 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
53 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
54 SET GLOBAL innodb_ft_aux_table=default;
55
56 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
57
58 connection default;
59
60 DROP TABLE t1;
61
62 --echo # Case 2: Test insert and insert(sync)
63 CREATE TABLE t1 (
64 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
65 title VARCHAR(200),
66 FULLTEXT(title)
67 ) ENGINE = InnoDB;
68
69 INSERT INTO t1(title) VALUES('mysql');
70 INSERT INTO t1(title) VALUES('database');
71
72 connection con1;
73
74 SET SESSION debug="+d,fts_instrument_sync_debug";
75
76 SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted';
77
78 send INSERT INTO t1(title) VALUES('mysql database');
79
80 connection default;
81
82 SET DEBUG_SYNC= 'now WAIT_FOR written';
83
84 INSERT INTO t1(title) VALUES('mysql database');
85
86 SET DEBUG_SYNC= 'now SIGNAL inserted';
87
88 connection con1;
89 --echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
90 --reap
91
92 SET SESSION debug="-d,fts_instrument_sync_debug";
93
94 SET GLOBAL innodb_ft_aux_table="test/t1";
95 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
96 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
97 SET GLOBAL innodb_ft_aux_table=default;
98
99 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
100
101 connection default;
102 disconnect con1;
103
104 DROP TABLE t1;
105
106 --echo # Case 3: Test insert crash recovery
107
108 CREATE TABLE t1 (
109 id INT,
110 title VARCHAR(200),
111 FULLTEXT(title)
112 ) ENGINE = InnoDB;
113
114 INSERT INTO t1(id, title) VALUES(1, 'database');
115
116 --source include/expect_crash.inc
117
118 SET SESSION debug="+d,fts_instrument_sync_debug,fts_write_node_crash";
119
120 --error 2013
121 INSERT INTO t1(id, title) VALUES(2, 'mysql');
122
123 --source include/start_mysqld.inc
124
125 -- echo After restart
126 SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
127
128 SET SESSION debug="+d,fts_instrument_sync_debug";
129
130 INSERT INTO t1(id, title) VALUES(3, 'mysql');
131
132 SET SESSION debug="-d,fts_instrument_sync_debug";
133
134 SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
135
136 DROP TABLE t1;
137
138 --echo # Case 4: Test sync commit & rollback in background
139 CREATE TABLE t1(
140 id INT AUTO_INCREMENT,
141 title VARCHAR(100),
142 FULLTEXT(title),
143 PRIMARY KEY(id)) ENGINE=InnoDB;
144
145 SET SESSION debug="+d,fts_instrument_sync";
146 INSERT INTO t1(title) VALUES('mysql');
147 SET SESSION debug="-d,fts_instrument_sync";
148
149 --source include/restart_mysqld.inc
150
151 SET GLOBAL debug="+d,fts_instrument_sync,fts_instrument_sync_interrupted";
152 INSERT INTO t1(title) VALUES('database');
153 SET GLOBAL debug="-d,fts_instrument_sync,fts_instrument_sync_interrupted";
154
155 SET SESSION debug="+d,fts_instrument_sync_debug";
156 INSERT INTO t1(title) VALUES('good');
157 SET SESSION debug="-d,fts_instrument_sync_debug";
158
159 SET GLOBAL innodb_ft_aux_table="test/t1";
160 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
161 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
162 SET GLOBAL innodb_ft_aux_table=default;
163
164 SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good');
165
166 DROP TABLE t1;
167
168 --source include/wait_until_count_sessions.inc
169
170 --echo #
171 --echo # Bug #31310404 I_S_FTS_INDEX_CACHE_FILL_ONE_INDEX() IS NOT PROTECTED BY THE LOCK
172 --echo #
173
174 CREATE TABLE t1 (
175 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
176 title VARCHAR(200),
177 FULLTEXT(title)
178 ) ENGINE = InnoDB;
179
180 INSERT INTO t1(title) VALUES('mysql');
181 INSERT INTO t1(title) VALUES('database');
182
183 SET SESSION debug="+d,fts_instrument_sync_request";
184 INSERT INTO t1(title) VALUES('mysql database');
185 SET SESSION debug="-d,fts_instrument_sync_request";
186
187 SET GLOBAL innodb_ft_aux_table="test/t1";
188
189 # If FTS SYNC is in progress then wait for SYNC to finish.
190 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
191 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
192 SET GLOBAL innodb_ft_aux_table=default;
193
194 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
195
196 DROP TABLE t1;