193 lines
4.9 KiB
Plaintext
193 lines
4.9 KiB
Plaintext
# Alter a fts table non-rebuilt with hidden fts doc id index
|
|
# but without fts index, the hidden index should be kept.
|
|
|
|
--echo # Case 1 : WITHOUT FTS_DOC_ID
|
|
CREATE TABLE t1 (
|
|
id INT,
|
|
title VARCHAR(200),
|
|
FULLTEXT fidx(title)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (1, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (2, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
ALTER TABLE t1 DROP KEY fidx;
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
DELETE FROM t1 WHERE id < 2;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (3, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
INSERT INTO t1 (id, title) VALUES (4, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ADD FULLTEXT KEY fidx (title);
|
|
|
|
INSERT INTO t1 (id, title) VALUES (5, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 DROP KEY fidx;
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
DELETE FROM t1 WHERE id < 5;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (6, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ADD FULLTEXT KEY fidx(title);
|
|
|
|
INSERT INTO t1 (id, title) VALUES (7, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo # Case 2 : WITH FTS_DOC_ID PRIMARY KEY
|
|
CREATE TABLE t1 (
|
|
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
|
|
id INT,
|
|
title VARCHAR(200),
|
|
PRIMARY KEY(FTS_DOC_ID),
|
|
FULLTEXT fidx(title)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (1, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (2, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
ALTER TABLE t1 DROP KEY fidx;
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
DELETE FROM t1 WHERE id < 2;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (3, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
INSERT INTO t1 (id, title) VALUES (4, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ADD FULLTEXT KEY fidx (title);
|
|
|
|
INSERT INTO t1 (id, title) VALUES (5, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 DROP KEY fidx;
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
DELETE FROM t1 WHERE id < 5;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (6, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ADD FULLTEXT KEY fidx(title);
|
|
|
|
INSERT INTO t1 (id, title) VALUES (7, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo # Case 3 : WITH FTS_DOC_ID FTS_DOC_ID_INDEX KEY
|
|
CREATE TABLE t1 (
|
|
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
|
|
id INT,
|
|
title VARCHAR(200),
|
|
UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID),
|
|
FULLTEXT fidx(title)
|
|
) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (1, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (2, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
ALTER TABLE t1 DROP KEY fidx;
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
DELETE FROM t1 WHERE id < 2;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (3, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
INSERT INTO t1 (id, title) VALUES (4, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ADD FULLTEXT KEY fidx (title);
|
|
|
|
INSERT INTO t1 (id, title) VALUES (5, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 DROP KEY fidx;
|
|
|
|
ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
|
|
|
|
DELETE FROM t1 WHERE id < 5;
|
|
|
|
INSERT INTO t1 (id, title) VALUES (6, 'MySQL Tutorial');
|
|
|
|
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
ALTER TABLE t1 ADD FULLTEXT KEY fidx(title);
|
|
|
|
INSERT INTO t1 (id, title) VALUES (7, 'MySQL Tutorial');
|
|
|
|
SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
|
|
|
|
DROP TABLE t1;
|