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
|
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
DROP INDEX title ON articles;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...') ,
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
BEGIN;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...');
# Kill and restart
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...');
CREATE FULLTEXT INDEX idx ON articles (title,body);
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
5 MySQL vs. YourSQL In the following database comparison ...
7 MySQL Tutorial DBMS stands for DataBase ...
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...') ,
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
BEGIN;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...');
# Kill and restart
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...');
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
5 MySQL vs. YourSQL In the following database comparison ...
7 MySQL Tutorial DBMS stands for DataBase ...
8 MySQL Tutorial DBMS stands for DataBase ...
12 MySQL vs. YourSQL In the following database comparison ...
14 MySQL Tutorial DBMS stands for DataBase ...
DROP TABLE articles;
CREATE TABLE articles (
id int PRIMARY KEY,
FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx1 on articles (title, body);
INSERT INTO articles VALUES
(1, 10, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
(2, 1, 'How To Use MySQL Well','After you went through a ...'),
(3, 2, 'Optimizing MySQL','In this tutorial we will show ...'),
(4, 11, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
(5, 6, 'MySQL vs. YourSQL','In the following database comparison ...'),
(7, 4, 'MySQL Security','When configured properly, MySQL ...');
BEGIN;
INSERT INTO articles VALUES
(100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...');
#
# MDEV-19073 FTS row mismatch after crash recovery
#
CREATE TABLE mdev19073(id SERIAL, title VARCHAR(200), body TEXT,
FULLTEXT(title,body)) ENGINE=InnoDB;
INSERT INTO mdev19073 (title, body) VALUES
('MySQL Tutorial', 'DBMS stands for Database...');
CREATE FULLTEXT INDEX idx ON mdev19073(title, body);
CREATE TABLE mdev19073_2 LIKE mdev19073;
INSERT INTO mdev19073_2 (title, body) VALUES
('MySQL Tutorial', 'DBMS stands for Database...');
INSERT INTO mdev19073 (title, body) VALUES
('MariaDB Tutorial', 'DB means Database ...');
INSERT INTO mdev19073_2 (title, body) VALUES
('MariaDB Tutorial', 'DB means Database ...');
SELECT * FROM mdev19073 WHERE MATCH (title, body)
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for Database...
2 MariaDB Tutorial DB means Database ...
SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for Database...
2 MariaDB Tutorial DB means Database ...
# Kill and restart
INSERT INTO articles VALUES (8, 12, 'MySQL Tutorial','DBMS stands for DataBase ...');
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id FTS_DOC_ID title body
3 2 Optimizing MySQL In this tutorial we will show ...
1 10 MySQL Tutorial DBMS stands for DataBase ...
8 12 MySQL Tutorial DBMS stands for DataBase ...
DROP TABLE articles;
SELECT * FROM mdev19073 WHERE MATCH (title, body)
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for Database...
2 MariaDB Tutorial DB means Database ...
SELECT * FROM mdev19073_2 WHERE MATCH (title, body)
AGAINST ('Database' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for Database...
2 MariaDB Tutorial DB means Database ...
DROP TABLE mdev19073, mdev19073_2;
|
