Debian Codegraph @

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

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