Debian Codegraph @

mysql-test/suite/innodb_fts/r/sync.result

raw
1 # Case 1: Test select and insert(row in both disk and cache)
2 CREATE TABLE t1 (
3 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 title VARCHAR(200),
5 FULLTEXT(title)
6 ) ENGINE = InnoDB;
7 INSERT INTO t1(title) VALUES('mysql');
8 INSERT INTO t1(title) VALUES('database');
9 SET SESSION debug="+d,fts_instrument_sync_debug";
10 SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected';
11 INSERT INTO t1(title) VALUES('mysql database');
12 SET DEBUG_SYNC= 'now WAIT_FOR written';
13 SET GLOBAL innodb_ft_aux_table="test/t1";
14 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
15 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
16 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
17 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
18 database 2 3 2 2 0
19 database 2 3 2 3 6
20 mysql 1 3 2 1 0
21 mysql 1 3 2 3 0
22 SET GLOBAL innodb_ft_aux_table=default;
23 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
24 FTS_DOC_ID title
25 3 mysql database
26 1 mysql
27 2 database
28 SET DEBUG_SYNC= 'now SIGNAL selected';
29 /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
30 SET SESSION debug="-d,fts_instrument_sync_debug";
31 SET GLOBAL innodb_ft_aux_table="test/t1";
32 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
33 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
34 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
35 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
36 database 2 3 2 2 0
37 database 2 3 2 3 6
38 mysql 1 3 2 1 0
39 mysql 1 3 2 3 0
40 SET GLOBAL innodb_ft_aux_table=default;
41 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
42 FTS_DOC_ID title
43 3 mysql database
44 1 mysql
45 2 database
46 DROP TABLE t1;
47 # Case 2: Test insert and insert(sync)
48 CREATE TABLE t1 (
49 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
50 title VARCHAR(200),
51 FULLTEXT(title)
52 ) ENGINE = InnoDB;
53 INSERT INTO t1(title) VALUES('mysql');
54 INSERT INTO t1(title) VALUES('database');
55 SET SESSION debug="+d,fts_instrument_sync_debug";
56 SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted';
57 INSERT INTO t1(title) VALUES('mysql database');
58 SET DEBUG_SYNC= 'now WAIT_FOR written';
59 INSERT INTO t1(title) VALUES('mysql database');
60 SET DEBUG_SYNC= 'now SIGNAL inserted';
61 /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database');
62 SET SESSION debug="-d,fts_instrument_sync_debug";
63 SET GLOBAL innodb_ft_aux_table="test/t1";
64 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
65 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
66 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
67 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
68 database 2 3 2 2 0
69 database 2 3 2 3 6
70 database 4 4 1 4 6
71 mysql 1 4 3 1 0
72 mysql 1 4 3 3 0
73 mysql 1 4 3 4 0
74 SET GLOBAL innodb_ft_aux_table=default;
75 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
76 FTS_DOC_ID title
77 3 mysql database
78 4 mysql database
79 1 mysql
80 2 database
81 DROP TABLE t1;
82 # Case 3: Test insert crash recovery
83 CREATE TABLE t1 (
84 id INT,
85 title VARCHAR(200),
86 FULLTEXT(title)
87 ) ENGINE = InnoDB;
88 INSERT INTO t1(id, title) VALUES(1, 'database');
89 SET SESSION debug="+d,fts_instrument_sync_debug,fts_write_node_crash";
90 INSERT INTO t1(id, title) VALUES(2, 'mysql');
91 ERROR HY000: Lost connection to MySQL server during query
92 # restart
93 After restart
94 SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
95 id title
96 1 database
97 SET SESSION debug="+d,fts_instrument_sync_debug";
98 INSERT INTO t1(id, title) VALUES(3, 'mysql');
99 SET SESSION debug="-d,fts_instrument_sync_debug";
100 SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database');
101 id title
102 1 database
103 3 mysql
104 DROP TABLE t1;
105 # Case 4: Test sync commit & rollback in background
106 CREATE TABLE t1(
107 id INT AUTO_INCREMENT,
108 title VARCHAR(100),
109 FULLTEXT(title),
110 PRIMARY KEY(id)) ENGINE=InnoDB;
111 SET SESSION debug="+d,fts_instrument_sync";
112 INSERT INTO t1(title) VALUES('mysql');
113 SET SESSION debug="-d,fts_instrument_sync";
114 # restart
115 SET GLOBAL debug="+d,fts_instrument_sync,fts_instrument_sync_interrupted";
116 INSERT INTO t1(title) VALUES('database');
117 SET GLOBAL debug="-d,fts_instrument_sync,fts_instrument_sync_interrupted";
118 SET SESSION debug="+d,fts_instrument_sync_debug";
119 INSERT INTO t1(title) VALUES('good');
120 SET SESSION debug="-d,fts_instrument_sync_debug";
121 SET GLOBAL innodb_ft_aux_table="test/t1";
122 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
123 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
124 database 4 4 1 4 0
125 good 5 5 1 5 0
126 mysql 2 2 1 2 0
127 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
128 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
129 SET GLOBAL innodb_ft_aux_table=default;
130 SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good');
131 id title
132 1 mysql
133 2 database
134 3 good
135 DROP TABLE t1;
136 #
137 # Bug #31310404 I_S_FTS_INDEX_CACHE_FILL_ONE_INDEX() IS NOT PROTECTED BY THE LOCK
138 #
139 CREATE TABLE t1 (
140 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
141 title VARCHAR(200),
142 FULLTEXT(title)
143 ) ENGINE = InnoDB;
144 INSERT INTO t1(title) VALUES('mysql');
145 INSERT INTO t1(title) VALUES('database');
146 SET SESSION debug="+d,fts_instrument_sync_request";
147 INSERT INTO t1(title) VALUES('mysql database');
148 SET SESSION debug="-d,fts_instrument_sync_request";
149 SET GLOBAL innodb_ft_aux_table="test/t1";
150 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
151 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
152 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
153 WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
154 database 2 3 2 2 0
155 database 2 3 2 3 6
156 mysql 1 3 2 1 0
157 mysql 1 3 2 3 0
158 SET GLOBAL innodb_ft_aux_table=default;
159 SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database');
160 FTS_DOC_ID title
161 3 mysql database
162 1 mysql
163 2 database
164 DROP TABLE t1;