Debian Codegraph @

mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result

raw
1 CREATE TABLE fts_test (
2 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
3 title VARCHAR(200),
4 body TEXT
5 ) ENGINE=InnoDB;
6 INSERT INTO fts_test (title,body) VALUES
7 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
8 ('How To Use MySQL Well','After you went through a ...'),
9 ('Optimizing MySQL','In this tutorial we will show ...'),
10 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
11 ('MySQL vs. YourSQL','In the following database comparison ...'),
12 ('MySQL Security','When configured properly, MySQL ...');
13 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
14 ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE
15 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=INPLACE;
16 SELECT * FROM fts_test WHERE MATCH (title, body)
17 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
18 id title body
19 1 MySQL Tutorial DBMS stands for DataBase ...
20 3 Optimizing MySQL In this tutorial we will show ...
21 DROP INDEX idx ON fts_test;
22 INSERT INTO fts_test (title,body) VALUES
23 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
24 ('How To Use MySQL Well','After you went through a ...'),
25 ('Optimizing MySQL','In this tutorial we will show ...'),
26 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
27 ('MySQL vs. YourSQL','In the following database comparison ...'),
28 ('MySQL Security','When configured properly, MySQL ...');
29 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
30 SELECT * FROM fts_test WHERE MATCH (title, body)
31 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
32 id title body
33 1 MySQL Tutorial DBMS stands for DataBase ...
34 3 Optimizing MySQL In this tutorial we will show ...
35 7 MySQL Tutorial DBMS stands for DataBase ...
36 9 Optimizing MySQL In this tutorial we will show ...
37 SELECT * FROM fts_test WHERE MATCH (title,body)
38 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
39 id title body
40 6 MySQL Security When configured properly, MySQL ...
41 12 MySQL Security When configured properly, MySQL ...
42 1 MySQL Tutorial DBMS stands for DataBase ...
43 2 How To Use MySQL Well After you went through a ...
44 3 Optimizing MySQL In this tutorial we will show ...
45 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
46 7 MySQL Tutorial DBMS stands for DataBase ...
47 8 How To Use MySQL Well After you went through a ...
48 9 Optimizing MySQL In this tutorial we will show ...
49 10 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
50 TRUNCATE TABLE fts_test;
51 DROP INDEX idx ON fts_test;
52 INSERT INTO fts_test (title,body) VALUES
53 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
54 ('How To Use MySQL Well','After you went through a ...'),
55 ('Optimizing MySQL','In this tutorial we will show ...'),
56 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
57 ('MySQL vs. YourSQL','In the following database comparison ...'),
58 ('MySQL Security','When configured properly, MySQL ...');
59 CREATE FULLTEXT INDEX idx on fts_test (title, body);
60 SELECT * FROM fts_test WHERE MATCH (title, body)
61 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
62 id title body
63 1 MySQL Tutorial DBMS stands for DataBase ...
64 3 Optimizing MySQL In this tutorial we will show ...
65 DROP TABLE fts_test;
66 CREATE TABLE fts_test (
67 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
68 title VARCHAR(200),
69 body TEXT
70 ) ENGINE=InnoDB;
71 INSERT INTO fts_test (title,body) VALUES
72 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
73 ('How To Use MySQL Well','After you went through a ...'),
74 ('Optimizing MySQL','In this tutorial we will show ...'),
75 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
76 ('MySQL vs. YourSQL','In the following database comparison ...'),
77 ('MySQL Security','When configured properly, MySQL ...');
78 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE;
79 ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
80 ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY;
81 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
82 ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED
83 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
84 SELECT * FROM fts_test WHERE MATCH (title, body)
85 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
86 FTS_DOC_ID title body
87 1 MySQL Tutorial DBMS stands for DataBase ...
88 3 Optimizing MySQL In this tutorial we will show ...
89 drop index idx on fts_test;
90 CREATE FULLTEXT INDEX idx on fts_test (title, body);
91 SELECT * FROM fts_test WHERE MATCH (title, body)
92 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
93 FTS_DOC_ID title body
94 1 MySQL Tutorial DBMS stands for DataBase ...
95 3 Optimizing MySQL In this tutorial we will show ...
96 drop index idx on fts_test;
97 CREATE FULLTEXT INDEX idx on fts_test (title, body);
98 SELECT * FROM fts_test WHERE MATCH (title, body)
99 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
100 FTS_DOC_ID title body
101 1 MySQL Tutorial DBMS stands for DataBase ...
102 3 Optimizing MySQL In this tutorial we will show ...
103 drop table fts_test;
104 CREATE TABLE fts_test (
105 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
106 title varchar(255) NOT NULL DEFAULT '',
107 text mediumtext NOT NULL,
108 PRIMARY KEY (FTS_DOC_ID),
109 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
110 FULLTEXT KEY idx (title,text)
111 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
112 set @@auto_increment_increment=10;
113 INSERT INTO fts_test (title, text) VALUES
114 ('MySQL Tutorial','DBMS stands for DataBase ...'),
115 ('How To Use MySQL Well','After you went through a ...'),
116 ('Optimizing MySQL','In this tutorial we will show ...'),
117 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
118 ('MySQL vs. YourSQL','In the following database comparison ...'),
119 ('MySQL Security','When configured properly, MySQL ...');
120 ANALYZE TABLE fts_test;
121 set @@auto_increment_increment=1;
122 select *, match(title, text) AGAINST ('database') as score
123 from fts_test order by score desc;
124 FTS_DOC_ID title text score
125 11 MySQL Tutorial DBMS stands for DataBase ... 0.22764469683170319
126 51 MySQL vs. YourSQL In the following database comparison ... 0.22764469683170319
127 21 How To Use MySQL Well After you went through a ... 0
128 31 Optimizing MySQL In this tutorial we will show ... 0
129 41 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0
130 61 MySQL Security When configured properly, MySQL ... 0
131 drop index idx on fts_test;
132 drop table fts_test;
133 CREATE TABLE fts_test (
134 FTS_DOC_ID int(20) unsigned NOT NULL AUTO_INCREMENT,
135 title varchar(255) NOT NULL DEFAULT '',
136 text mediumtext NOT NULL,
137 PRIMARY KEY (FTS_DOC_ID),
138 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
139 FULLTEXT KEY idx (title,text)
140 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
141 ERROR 42000: Incorrect column name 'FTS_DOC_ID'
142 CREATE TABLE fts_test (
143 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
144 title varchar(255) NOT NULL DEFAULT '',
145 text mediumtext NOT NULL,
146 PRIMARY KEY (FTS_DOC_ID),
147 KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
148 FULLTEXT KEY idx (title,text)
149 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
150 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
151 CREATE TABLE articles (
152 FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
153 title VARCHAR(200),
154 body TEXT
155 ) ENGINE=InnoDB;
156 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
157 (9, 'MySQL Tutorial','DBMS stands for DataBase ...'),
158 (10, 'How To Use MySQL Well','After you went through a ...'),
159 (12, 'Optimizing MySQL','In this tutorial we will show ...'),
160 (14,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
161 (19, 'MySQL vs. YourSQL','In the following database comparison ...'),
162 (20, 'MySQL Security','When configured properly, MySQL ...');
163 ALTER TABLE articles ADD FULLTEXT INDEX idx (title),
164 ADD FULLTEXT INDEX idx3 (title), ALGORITHM=INPLACE;
165 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY
166 ALTER TABLE articles ADD FULLTEXT INDEX idx (title),
167 ADD FULLTEXT INDEX idx3 (title);
168 affected rows: 6
169 info: Records: 6 Duplicates: 0 Warnings: 1
170 Warnings:
171 Note 1831 Duplicate index `idx3`. This is deprecated and will be disallowed in a future release
172 ALTER TABLE articles ADD INDEX t20 (title(20)), LOCK=NONE;
173 ALTER TABLE articles DROP INDEX t20;
174 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
175 (29, 'MySQL Tutorial','DBMS stands for DataBase ...'),
176 (30, 'How To Use MySQL Well','After you went through a ...'),
177 (32, 'Optimizing MySQL','In this tutorial we will show ...'),
178 (34,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
179 (39, 'MySQL vs. YourSQL','In the following database comparison ...'),
180 (40, 'MySQL Security','When configured properly, MySQL ...');
181 SELECT * FROM articles WHERE MATCH (title)
182 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
183 FTS_DOC_ID title body
184 9 MySQL Tutorial DBMS stands for DataBase ...
185 29 MySQL Tutorial DBMS stands for DataBase ...
186 DROP INDEX idx ON articles;
187 SELECT * FROM articles WHERE MATCH (title)
188 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
189 FTS_DOC_ID title body
190 9 MySQL Tutorial DBMS stands for DataBase ...
191 29 MySQL Tutorial DBMS stands for DataBase ...
192 CREATE FULLTEXT INDEX idx on articles (title, body);
193 SELECT * FROM articles WHERE MATCH (title, body)
194 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
195 FTS_DOC_ID title body
196 9 MySQL Tutorial DBMS stands for DataBase ...
197 12 Optimizing MySQL In this tutorial we will show ...
198 29 MySQL Tutorial DBMS stands for DataBase ...
199 32 Optimizing MySQL In this tutorial we will show ...
200 DROP TABLE articles;
201 create table articles(`FTS_DOC_ID` serial,
202 `col32` timestamp not null,`col115` text) engine=innodb;
203 create fulltext index `idx5` on articles(`col115`) ;
204 alter ignore table articles add primary key (`col32`) ;
205 drop table articles;
206 CREATE TABLE articles (
207 id INT UNSIGNED NOT NULL,
208 title VARCHAR(200),
209 body TEXT
210 ) ENGINE=InnoDB;
211 INSERT INTO articles VALUES
212 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
213 (2, 'How To Use MySQL Well','After you went through a ...'),
214 (3, 'Optimizing MySQL','In this tutorial we will show ...'),
215 (4, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
216 (5, 'MySQL vs. YourSQL','In the following database comparison ...'),
217 (6, 'MySQL Security','When configured properly, MySQL ...');
218 CREATE FULLTEXT INDEX idx on articles (title, body);
219 DROP INDEX idx ON articles;
220 CREATE UNIQUE INDEX idx2 ON articles(id);
221 CREATE FULLTEXT INDEX idx on articles (title, body);
222 SELECT * FROM articles WHERE MATCH (title, body)
223 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
224 id title body
225 1 MySQL Tutorial DBMS stands for DataBase ...
226 3 Optimizing MySQL In this tutorial we will show ...
227 DROP TABLE articles;
228 #
229 # MDEV-22811 DDL fails to drop and re-create FTS index
230 #
231 CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY,
232 f1 VARCHAR(200),FULLTEXT fidx(f1))engine=innodb;
233 ALTER TABLE t1 DROP index fidx, ADD FULLTEXT INDEX(f1);
234 DROP TABLE t1;
235 #
236 # MDEV-21478 Inplace alter fails to report error when
237 # FTS_DOC_ID is added
238 SET NAMES utf8;
239 CREATE TABLE t1(f1 INT NOT NULL)ENGINE=InnoDB;
240 ALTER TABLE t1 ADD FTS_DOC_ıD BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
241 ERROR 42000: Incorrect column name 'FTS_DOC_ıD'
242 ALTER TABLE t1 ADD FTS_DOC_ıD BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
243 ERROR 42000: Incorrect column name 'FTS_DOC_ıD'
244 DROP TABLE t1;
245 CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
246 ALTER TABLE t1 ADD FTS_DOC_İD BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
247 DROP TABLE t1;
248 CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
249 ALTER TABLE t1 ADD FTS_DOC_İD BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
250 DROP TABLE t1;
251 CREATE TABLE t1 (f1 INT NOT NULL)ENGINE=InnoDB;
252 ALTER TABLE t1 ADD fts_doc_id INT, ALGORITHM=COPY;
253 ERROR 42000: Incorrect column name 'fts_doc_id'
254 ALTER TABLE t1 ADD fts_doc_id INT, ALGORITHM=INPLACE;
255 ERROR 42000: Incorrect column name 'fts_doc_id'
256 ALTER TABLE t1 ADD fts_doc_id BIGINT UNSIGNED NOT NULL, ALGORITHM=COPY;
257 ERROR 42000: Incorrect column name 'fts_doc_id'
258 ALTER TABLE t1 ADD fts_doc_id BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
259 ERROR 42000: Incorrect column name 'fts_doc_id'
260 ALTER TABLE t1 ADD FTS_DOC_ID INT UNSIGNED NOT NULL, ALGORITHM=COPY;
261 ERROR 42000: Incorrect column name 'FTS_DOC_ID'
262 ALTER TABLE t1 ADD FTS_DOC_ID INT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
263 ERROR 42000: Incorrect column name 'FTS_DOC_ID'
264 SHOW CREATE TABLE t1;
265 Table Create Table
266 t1 CREATE TABLE `t1` (
267 `f1` int(11) NOT NULL
268 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
269 DROP TABLE t1;
270 #
271 # MDEV-25271 Double free of table when inplace alter
272 # FTS add index fails
273 #
274 call mtr.add_suppression("InnoDB: Operating system error number .* in a file operation.");
275 call mtr.add_suppression("InnoDB: Error number .* means");
276 call mtr.add_suppression("InnoDB: Cannot create file");
277 call mtr.add_suppression("InnoDB: Failed to create");
278 call mtr.add_suppression("InnoDB: The file '.*test/FTS_[0-9a-f]*_BEING_DELETED\\.ibd' already exists");
279 CREATE TABLE t1(a TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL UNIQUE) ENGINE=InnoDB;
280 ALTER TABLE t1 ADD FULLTEXT(a), ALGORITHM=INPLACE;
281 ERROR HY000: Got error 11 "Resource temporarily unavailable" from storage engine InnoDB
282 DROP TABLE t1;
283 CREATE TABLE t1 (a VARCHAR(3)) ENGINE=InnoDB;
284 ALTER TABLE t1 ADD FULLTEXT KEY(a), ADD COLUMN b VARCHAR(3), ADD FULLTEXT KEY(b);
285 DROP TABLE t1;
286 #
287 # MDEV-18152 Assertion 'num_fts_index <= 1' failed
288 # in prepare_inplace_alter_table_dict
289 #
290 CREATE TABLE t1
291 (a VARCHAR(128), b VARCHAR(128), FULLTEXT INDEX(a), FULLTEXT INDEX(b))
292 ENGINE=InnoDB;
293 ALTER TABLE t1 ADD c SERIAL;
294 DROP TABLE t1;
295 # End of 10.3 tests
296 #
297 # MDEV-27582 Fulltext DDL decrements the FTS_DOC_ID value
298 #
299 CREATE TABLE t1 (
300 f1 INT NOT NULL PRIMARY KEY,
301 f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
302 INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
303 connect con1,localhost,root,,,;
304 START TRANSACTION WITH CONSISTENT SNAPSHOT;
305 connection default;
306 DELETE FROM t1 WHERE f1 = 2;
307 ALTER TABLE t1 DROP INDEX ft;
308 ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
309 INSERT INTO t1 VALUES (3, 'innodb fts search');
310 SET GLOBAL innodb_optimize_fulltext_only=ON;
311 OPTIMIZE TABLE t1;
312 Table Op Msg_type Msg_text
313 test.t1 optimize status OK
314 SET GLOBAL innodb_ft_aux_table = 'test/t1';
315 SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
316 max(DOC_ID)
317 3
318 SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
319 f1 f2
320 3 innodb fts search
321 DROP TABLE t1;
322 disconnect con1;
323 SET GLOBAL innodb_optimize_fulltext_only=OFF;
324 SET GLOBAL innodb_ft_aux_table = default;
325 #
326 # MDEV-32017 Auto-increment no longer works for
327 # explicit FTS_DOC_ID
328 #
329 CREATE TABLE t (
330 FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
331 f1 char(255), f2 char(255), f3 char(255), fulltext key (f3)
332 ) ENGINE=InnoDB;
333 INSERT INTO t (f1,f2,f3) VALUES ('foo','bar','baz');
334 ALTER TABLE t ADD FULLTEXT INDEX ft1(f1);
335 ALTER TABLE t ADD FULLTEXT INDEX ft2(f2);
336 INSERT INTO t (f1,f2,f3) VALUES ('bar','baz','qux');
337 DROP TABLE t;
338 #
339 # MDEV-31892 Server crash upon moving InnoDB table
340 # with fulltext index between databases
341 #
342 CREATE DATABASE db;
343 CREATE TABLE db.t (pk INT PRIMARY KEY, c CHAR(120), FULLTEXT KEY(c)) ENGINE=InnoDB;
344 INSERT INTO db.t VALUES (1,'foo'),(2,'bar');
345 ALTER TABLE db.t FORCE, RENAME to test.t;
346 DROP DATABASE db;
347 DROP TABLE IF EXISTS test.t;