Debian Codegraph @

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

raw
1 call mtr.add_suppression("Trying to free a missing index of table 'test/fts_test'");
2 call mtr.add_suppression("Failed to create index with index id .* of table 'test/fts_test'");
3 CREATE TABLE fts_test (
4 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
5 title VARCHAR(200),
6 body TEXT
7 ) ENGINE=InnoDB;
8 INSERT INTO fts_test (title,body) VALUES
9 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
10 ('How To Use MySQL Well','After you went through a ...'),
11 ('Optimizing MySQL','In this tutorial we will show ...'),
12 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
13 ('MySQL vs. YourSQL','In the following database comparison ...'),
14 ('MySQL Security','When configured properly, MySQL ...');
15 CREATE FULLTEXT INDEX idx on fts_test (title, body);
16 Warnings:
17 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
18 SELECT * FROM fts_test WHERE MATCH (title, body)
19 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
20 id title body
21 1 MySQL Tutorial DBMS stands for DataBase ...
22 3 Optimizing MySQL In this tutorial we will show ...
23 DROP INDEX idx ON fts_test;
24 INSERT INTO fts_test (title,body) VALUES
25 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
26 ('How To Use MySQL Well','After you went through a ...'),
27 ('Optimizing MySQL','In this tutorial we will show ...'),
28 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
29 ('MySQL vs. YourSQL','In the following database comparison ...'),
30 ('MySQL Security','When configured properly, MySQL ...');
31 CREATE FULLTEXT INDEX idx on fts_test (title, body);
32 SELECT * FROM fts_test WHERE MATCH (title, body)
33 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
34 id title body
35 1 MySQL Tutorial DBMS stands for DataBase ...
36 3 Optimizing MySQL In this tutorial we will show ...
37 7 MySQL Tutorial DBMS stands for DataBase ...
38 9 Optimizing MySQL In this tutorial we will show ...
39 SELECT * FROM fts_test WHERE MATCH (title,body)
40 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
41 id title body
42 1 MySQL Tutorial DBMS stands for DataBase ...
43 10 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
44 12 MySQL Security When configured properly, MySQL ...
45 2 How To Use MySQL Well After you went through a ...
46 3 Optimizing MySQL In this tutorial we will show ...
47 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
48 6 MySQL Security When configured properly, MySQL ...
49 7 MySQL Tutorial DBMS stands for DataBase ...
50 8 How To Use MySQL Well After you went through a ...
51 9 Optimizing MySQL In this tutorial we will show ...
52 TRUNCATE TABLE fts_test;
53 DROP INDEX idx ON fts_test;
54 INSERT INTO fts_test (title,body) VALUES
55 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
56 ('How To Use MySQL Well','After you went through a ...'),
57 ('Optimizing MySQL','In this tutorial we will show ...'),
58 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
59 ('MySQL vs. YourSQL','In the following database comparison ...'),
60 ('MySQL Security','When configured properly, MySQL ...');
61 CREATE FULLTEXT INDEX idx on fts_test (title, body);
62 SELECT * FROM fts_test WHERE MATCH (title, body)
63 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
64 id title body
65 1 MySQL Tutorial DBMS stands for DataBase ...
66 3 Optimizing MySQL In this tutorial we will show ...
67 DROP TABLE fts_test;
68 CREATE TABLE fts_test (
69 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
70 title VARCHAR(200),
71 body TEXT
72 ) ENGINE=InnoDB;
73 create unique index FTS_DOC_ID_INDEX on fts_test(FTS_DOC_ID DESC);
74 INSERT INTO fts_test (title,body) VALUES
75 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
76 ('How To Use MySQL Well','After you went through a ...'),
77 ('Optimizing MySQL','In this tutorial we will show ...'),
78 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
79 ('MySQL vs. YourSQL','In the following database comparison ...'),
80 ('MySQL Security','When configured properly, MySQL ...');
81 CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
82 ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.
83 CREATE FULLTEXT INDEX idx on fts_test (title, body);
84 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
85 ALTER TABLE fts_test DROP INDEX FTS_DOC_ID_INDEX,
86 ADD UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID), ALGORITHM=INPLACE;
87 CREATE FULLTEXT INDEX idx on fts_test (title, body);
88 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
89 ERROR 0A000: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.
90 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
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 index idx on fts_test;
104 drop index FTS_DOC_ID_INDEX on fts_test;
105 CREATE FULLTEXT INDEX idx on fts_test (title, body);
106 SELECT * FROM fts_test WHERE MATCH (title, body)
107 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
108 FTS_DOC_ID title body
109 1 MySQL Tutorial DBMS stands for DataBase ...
110 3 Optimizing MySQL In this tutorial we will show ...
111 drop table fts_test;
112 CREATE TABLE fts_test (
113 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
114 title varchar(255) NOT NULL DEFAULT '',
115 text mediumtext NOT NULL,
116 PRIMARY KEY (FTS_DOC_ID),
117 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
118 FULLTEXT KEY idx (title,text)
119 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
120 Warnings:
121 Warning 1681 Integer display width is deprecated and will be removed in a future release.
122 set @@auto_increment_increment=10;
123 INSERT INTO fts_test (title, text) VALUES
124 ('MySQL Tutorial','DBMS stands for DataBase ...'),
125 ('How To Use MySQL Well','After you went through a ...'),
126 ('Optimizing MySQL','In this tutorial we will show ...'),
127 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
128 ('MySQL vs. YourSQL','In the following database comparison ...'),
129 ('MySQL Security','When configured properly, MySQL ...');
130 ANALYZE TABLE fts_test;
131 set @@auto_increment_increment=1;
132 select *, match(title, text) AGAINST ('database') as score
133 from fts_test order by score desc;
134 FTS_DOC_ID title text score
135 11 MySQL Tutorial DBMS stands for DataBase ... 0.22764469683170319
136 51 MySQL vs. YourSQL In the following database comparison ... 0.22764469683170319
137 21 How To Use MySQL Well After you went through a ... 0
138 31 Optimizing MySQL In this tutorial we will show ... 0
139 41 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0
140 61 MySQL Security When configured properly, MySQL ... 0
141 drop index idx on fts_test;
142 drop table fts_test;
143 CREATE TABLE fts_test (
144 FTS_DOC_ID int(20) unsigned NOT NULL AUTO_INCREMENT,
145 title varchar(255) NOT NULL DEFAULT '',
146 text mediumtext NOT NULL,
147 PRIMARY KEY (FTS_DOC_ID),
148 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
149 FULLTEXT KEY idx (title,text)
150 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
151 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
152 CREATE TABLE fts_test (
153 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
154 title varchar(255) NOT NULL DEFAULT '',
155 text mediumtext NOT NULL,
156 PRIMARY KEY (FTS_DOC_ID),
157 KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
158 FULLTEXT KEY idx (title,text)
159 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
160 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
161 CREATE TABLE articles (
162 FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
163 title VARCHAR(200),
164 body TEXT
165 ) ENGINE=InnoDB;
166 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
167 (9, 'MySQL Tutorial','DBMS stands for DataBase ...'),
168 (10, 'How To Use MySQL Well','After you went through a ...'),
169 (12, 'Optimizing MySQL','In this tutorial we will show ...'),
170 (14,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
171 (19, 'MySQL vs. YourSQL','In the following database comparison ...'),
172 (20, 'MySQL Security','When configured properly, MySQL ...');
173 ALTER TABLE articles ADD FULLTEXT INDEX idx3 (title),
174 ADD FULLTEXT INDEX idx5 (title);
175 ERROR HY000: InnoDB presently supports one FULLTEXT index creation at a time
176 CREATE FULLTEXT INDEX idx on articles (title);
177 ALTER TABLE articles ADD FULLTEXT INDEX idx3 (title);
178 Warnings:
179 Warning 1831 Duplicate index 'idx3' defined on the table 'test.articles'. This is deprecated and will be disallowed in a future release.
180 ALTER TABLE articles ADD INDEX t20 (title(20)), LOCK=NONE;
181 ALTER TABLE articles DROP INDEX t20;
182 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
183 (29, 'MySQL Tutorial','DBMS stands for DataBase ...'),
184 (30, 'How To Use MySQL Well','After you went through a ...'),
185 (32, 'Optimizing MySQL','In this tutorial we will show ...'),
186 (34,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
187 (39, 'MySQL vs. YourSQL','In the following database comparison ...'),
188 (40, 'MySQL Security','When configured properly, MySQL ...');
189 SELECT * FROM articles WHERE MATCH (title)
190 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
191 FTS_DOC_ID title body
192 9 MySQL Tutorial DBMS stands for DataBase ...
193 29 MySQL Tutorial DBMS stands for DataBase ...
194 DROP INDEX idx ON articles;
195 SELECT * FROM articles WHERE MATCH (title)
196 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
197 FTS_DOC_ID title body
198 9 MySQL Tutorial DBMS stands for DataBase ...
199 29 MySQL Tutorial DBMS stands for DataBase ...
200 CREATE FULLTEXT INDEX idx on articles (title, body);
201 SELECT * FROM articles WHERE MATCH (title, body)
202 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
203 FTS_DOC_ID title body
204 9 MySQL Tutorial DBMS stands for DataBase ...
205 12 Optimizing MySQL In this tutorial we will show ...
206 29 MySQL Tutorial DBMS stands for DataBase ...
207 32 Optimizing MySQL In this tutorial we will show ...
208 DROP TABLE articles;
209 create table articles(`FTS_DOC_ID` serial,
210 `col32` timestamp not null,`col115` text) engine=innodb;
211 create fulltext index `idx5` on articles(`col115`) ;
212 alter table articles add primary key (`col32`) ;
213 drop table articles;
214 CREATE TABLE articles (
215 id INT UNSIGNED NOT NULL,
216 title VARCHAR(200),
217 body TEXT
218 ) ENGINE=InnoDB;
219 INSERT INTO articles VALUES
220 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
221 (2, 'How To Use MySQL Well','After you went through a ...'),
222 (3, 'Optimizing MySQL','In this tutorial we will show ...'),
223 (4, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
224 (5, 'MySQL vs. YourSQL','In the following database comparison ...'),
225 (6, 'MySQL Security','When configured properly, MySQL ...');
226 CREATE FULLTEXT INDEX idx on articles (title, body);
227 Warnings:
228 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
229 DROP INDEX idx ON articles;
230 CREATE UNIQUE INDEX idx2 ON articles(id);
231 CREATE FULLTEXT INDEX idx on articles (title, body);
232 Warnings:
233 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
234 SELECT * FROM articles WHERE MATCH (title, body)
235 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
236 id title body
237 1 MySQL Tutorial DBMS stands for DataBase ...
238 3 Optimizing MySQL In this tutorial we will show ...
239 DROP TABLE articles;
240 CREATE TABLE t(t TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, b INT);
241 CREATE UNIQUE INDEX FTS_DOC_Id_INDEX ON t(FTS_DOC_ID DESC);
242 CREATE FULLTEXT INDEX f ON t(t);
243 ERROR HY000: Index 'FTS_DOC_Id_INDEX' is of wrong type for an InnoDB FULLTEXT index
244 DROP INDEX FTS_DOC_Id_INDEX on t;
245 CREATE UNIQUE INDEX FTS_DOC_ID_INDEX ON t(FTS_DOC_ID DESC, b DESC);
246 CREATE FULLTEXT INDEX f ON t(t);
247 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
248 DROP TABLE t;
249 CREATE TABLE t(t TEXT, FTS_DOC_iD BIGINT UNSIGNED NOT NULL, b INT);
250 ERROR 42000: Incorrect column name 'FTS_DOC_iD'