Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
None
Description
There are 2 test case where innodb behaves different with copy and inplace algorithm
Test case 1:
--source include/have_innodb.inc
|
CREATE TABLE t1 (
|
f1 INT NOT NULL PRIMARY KEY,
|
f2 VARCHAR(64), FULLTEXT ft(f2)) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
|
connect(con1,localhost,root,,,);
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
connection default;
|
DELETE FROM t1 WHERE f1 = 2;
|
ALTER TABLE t1 DROP INDEX ft;
|
ALTER TABLE t1 ADD FULLTEXT INDEX ft (f2);
|
INSERT INTO t1 VALUES (3, 'innodb fts search');
|
SET GLOBAL innodb_optimize_fulltext_only=ON;
|
OPTIMIZE TABLE t1;
|
SET GLOBAL innodb_ft_aux_table = 'test/t1';
|
SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
SELECT * FROM t1 WHERE MATCH(f2) AGAINST("+innodb +search" IN BOOLEAN MODE);
|
DROP TABLE t1;
|
disconnect con1;
|
SET GLOBAL innodb_optimize_fulltext_only=OFF;
|
SET GLOBAL innodb_ft_aux_table = default;
|
|
|
By inplace algorithm:
|
|
SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
max(DOC_ID)
|
3
|
|
|
By copy algorithm:
|
|
SELECT max(DOC_ID) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
max(DOC_ID)
|
2
|
Test case 2:
CREATE TABLE wp(
|
FTS_DOC_ID bigint PRIMARY KEY,
|
title VARCHAR(255) NOT NULL DEFAULT '',
|
text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
|
|
INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
|
(1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
|
(2, 'How To Use MySQL Well','After you went through a ...');
|
|
--error ER_INNODB_FT_WRONG_DOCID_COLUMN
|
CREATE FULLTEXT INDEX idx ON wp(title, text);
|
|
DROP TABLE wp;
|
|
|
By inplace alorithm:
|
|
CREATE FULLTEXT INDEX idx ON wp(title, text);
|
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
|
|
|
By Copy algorithm:
|
|
CREATE FULLTEXT INDEX idx ON wp(title, text);
|
- Succeeds without failing
|