1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
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 server during query
# restart
After restart
SELECT title FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
title
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 title FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
title
database
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;
# restart
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;
#
# MDEV-26273 InnoDB fts DDL fails when
# innodb_force_recovery is set to 2
#
# restart: --innodb_force_recovery=2
CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED KEY,
f1 CHAR(200)) ENGINE=InnoDB;
ALTER TABLE t1 ADD FULLTEXT INDEX(f1);
DROP TABLE t1;
# restart
|
