diff options
Diffstat (limited to 'mysql-test/suite/innodb_fts')
43 files changed, 1370 insertions, 327 deletions
diff --git a/mysql-test/suite/innodb_fts/r/basic.result b/mysql-test/suite/innodb_fts/r/basic.result index ae23b93dc84..d96127fbc34 100644 --- a/mysql-test/suite/innodb_fts/r/basic.result +++ b/mysql-test/suite/innodb_fts/r/basic.result @@ -1,3 +1,10 @@ +CREATE TEMPORARY TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title,body) +) ENGINE=InnoDB; +ERROR HY000: Cannot create FULLTEXT index on temporary InnoDB table CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), diff --git a/mysql-test/suite/innodb_fts/r/concurrent_insert.result b/mysql-test/suite/innodb_fts/r/concurrent_insert.result index 416e8bf432c..e91ea02b1de 100644 --- a/mysql-test/suite/innodb_fts/r/concurrent_insert.result +++ b/mysql-test/suite/innodb_fts/r/concurrent_insert.result @@ -1,9 +1,12 @@ CREATE TABLE t1(a VARCHAR(5),FULLTEXT KEY(a)) ENGINE=InnoDB; SET DEBUG_SYNC = 'get_next_FTS_DOC_ID SIGNAL prepared WAIT_FOR race'; REPLACE INTO t1(a) values('aaa'); +connect dml, localhost, root, ,; SET DEBUG_SYNC = 'now WAIT_FOR prepared'; REPLACE INTO t1(a) VALUES('aaa'); SET DEBUG_SYNC = 'now SIGNAL race'; +disconnect dml; +connection default; SET DEBUG_SYNC = 'RESET'; DROP TABLE t1; # @@ -13,14 +16,20 @@ CREATE TABLE t1(f1 CHAR(100), FULLTEXT(f1))ENGINE=InnoDB; INSERT INTO t1 VALUES('test'); CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB; INSERT INTO t2 VALUES('mariadb'); +connection default; +SET @saved_dbug = @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang'; SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL drop_index_start WAIT_FOR sync_op'; INSERT INTO t1 VALUES('Keyword'); +connect con1,localhost,root,,,; SET DEBUG_SYNC='now WAIT_FOR drop_index_start'; SET DEBUG_SYNC= 'norebuild_fts_drop SIGNAL sync_op WAIT_FOR fts_drop_index'; ALTER TABLE t2 drop index idx1; +connection default; set DEBUG_SYNC= 'now SIGNAL fts_drop_index'; -SET global DEBUG_DBUG=RESET; +connection con1; drop table t1, t2; +connection default; set DEBUG_SYNC=RESET; +SET @@GLOBAL.debug_dbug = @saved_dbug; diff --git a/mysql-test/suite/innodb_fts/r/crash_recovery.result b/mysql-test/suite/innodb_fts/r/crash_recovery.result index 648128424a8..44d3521df98 100644 --- a/mysql-test/suite/innodb_fts/r/crash_recovery.result +++ b/mysql-test/suite/innodb_fts/r/crash_recovery.result @@ -1,3 +1,4 @@ +FLUSH TABLES; CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), @@ -15,7 +16,14 @@ INSERT INTO articles (title,body) VALUES BEGIN; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); -# Kill and restart +# Make durable the AUTO_INCREMENT in the above incomplete transaction. +connect flush_redo_log,localhost,root,,; +SET GLOBAL innodb_flush_log_at_trx_commit=1; +BEGIN; +DELETE FROM articles LIMIT 1; +ROLLBACK; +disconnect flush_redo_log; +connection default; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); CREATE FULLTEXT INDEX idx ON articles (title,body); @@ -25,7 +33,7 @@ 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 ... INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('How To Use MySQL Well','After you went through a ...'), @@ -33,10 +41,20 @@ INSERT INTO articles (title,body) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); +connect dml, localhost, root,,; BEGIN; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); -# Kill and restart +connection default; +# Make durable the AUTO_INCREMENT in the above incomplete transaction. +connect flush_redo_log,localhost,root,,; +SET GLOBAL innodb_flush_log_at_trx_commit=1; +BEGIN; +DELETE FROM articles LIMIT 1; +ROLLBACK; +disconnect flush_redo_log; +connection default; +disconnect dml; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); SELECT * FROM articles @@ -45,10 +63,10 @@ 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 ... +9 MySQL Tutorial DBMS stands for DataBase ... +13 MySQL vs. YourSQL In the following database comparison ... +16 MySQL Tutorial DBMS stands for DataBase ... DROP TABLE articles; CREATE TABLE articles ( id int PRIMARY KEY, @@ -64,9 +82,11 @@ INSERT INTO articles VALUES (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 ...'); +connect dml, localhost, root,,; BEGIN; INSERT INTO articles VALUES (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...'); +connect dml2, localhost, root,,; # # MDEV-19073 FTS row mismatch after crash recovery # @@ -92,7 +112,9 @@ 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 +connection default; +disconnect dml; +disconnect dml2; 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); diff --git a/mysql-test/suite/innodb_fts/r/create.result b/mysql-test/suite/innodb_fts/r/create.result index c537aa81efd..c3a14fa0281 100644 --- a/mysql-test/suite/innodb_fts/r/create.result +++ b/mysql-test/suite/innodb_fts/r/create.result @@ -16,12 +16,12 @@ INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84); INSERT INTO t SET t=REPEAT('after',17); INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15); # The data below is not 3-byte UTF-8, but 4-byte chars. -INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84); +INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84); Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x96\x95\xF0\x9F...' for column 't' at row 1 -INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85); +Warning 1366 Incorrect string value: '\xF0\x9F\x96\x95\xF0\x9F...' for column `test`.`t`.`t` at row 1 +INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85); Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x96\x96\xF0\x9F...' for column 't' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x96\x96\xF0\x9F...' for column `test`.`t`.`t` at row 1 SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5)); COUNT(*) @@ -166,3 +166,19 @@ SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' len COUNT(*) 84 6 DROP TABLE t; +# +# MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in +# trx_undo_page_report_modify upon optimizing table +# under innodb_optimize_fulltext_only +# +CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB; +INSERT INTO t1 (f1) VALUES ('foo'),('bar'); +DELETE FROM t1 LIMIT 1; +ALTER TABLE t1 ADD FULLTEXT KEY (f1); +SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only; +SET GLOBAL innodb_optimize_fulltext_only= 1; +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; +SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save; diff --git a/mysql-test/suite/innodb_fts/r/fts_kill_query.result b/mysql-test/suite/innodb_fts/r/fts_kill_query.result index 45623f96ab0..53ad9b3a37c 100644 --- a/mysql-test/suite/innodb_fts/r/fts_kill_query.result +++ b/mysql-test/suite/innodb_fts/r/fts_kill_query.result @@ -2,5 +2,8 @@ CREATE TABLE t1 (a VARCHAR(7), b text, FULLTEXT KEY idx (a,b)) ENGINE=InnoDB; COMMIT; SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('foo bar' IN BOOLEAN MODE); +connect con1,localhost,root,,; KILL QUERY @id; +disconnect con1; +connection default; DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index 6802894e44b..42e294b3293 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -7,8 +7,8 @@ INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ANALYZE TABLE t1; SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 1 a 1 a NULL 5 NULL NULL YES FULLTEXT -t1 1 a 2 b NULL 5 NULL NULL YES FULLTEXT +t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT +t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Full-text indexes are called collections @@ -234,12 +234,12 @@ id show keys from t2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t2 1 tig 1 ticket A 3 NULL NULL YES BTREE -t2 1 tix 1 inhalt NULL 3 NULL NULL YES FULLTEXT +t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `ticket` int(11) DEFAULT NULL, - `inhalt` text, + `inhalt` text DEFAULT NULL, KEY `tig` (`ticket`), FULLTEXT KEY `tix` (`inhalt`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 diff --git a/mysql-test/suite/innodb_fts/r/fulltext2.result b/mysql-test/suite/innodb_fts/r/fulltext2.result index 2aa7d2a6754..0ce3dfa43a9 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext2.result +++ b/mysql-test/suite/innodb_fts/r/fulltext2.result @@ -242,3 +242,33 @@ a „MySQL“ DROP TABLE t1; SET NAMES latin1; +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +id int(10) not null , +first_name varchar(50) NOT NULL, +last_name varchar(50) NOT NULL, +PRIMARY KEY (FTS_DOC_ID), +UNIQUE KEY idx_1 (first_name, last_name), +FULLTEXT KEY `idx_2` (first_name) +) ENGINE=InnoDB; +INSERT INTO t1 (id, first_name, last_name) VALUES +(10, 'Bart', 'Simpson'), +(11, 'Homer', 'Simpson'), +(12, 'Marge', 'Simpson'), +(13, 'Lisa', 'Simpson'), +(14, 'Maggie', 'Simpson'), +(15, 'Ned', 'Flanders'), +(16, 'Nelson', 'Muntz'); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1; +fts_doc_id first_name last_name score +1 Bart Simpson 0 +2 Homer Simpson 0.7141907215118408 +4 Lisa Simpson 0 +5 Maggie Simpson 0 +3 Marge Simpson 0 +6 Ned Flanders 0 +7 Nelson Muntz 0 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/fulltext3.result b/mysql-test/suite/innodb_fts/r/fulltext3.result index 98fd91a072c..8d89cb74bfd 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext3.result +++ b/mysql-test/suite/innodb_fts/r/fulltext3.result @@ -7,3 +7,12 @@ DROP TABLE t1; CREATE TABLE t1(a VARCHAR(2) CHARACTER SET big5 COLLATE big5_chinese_ci, FULLTEXT(a)) ENGINE=<default_engine>; DROP TABLE t1; +CREATE TABLE t1 (a SERIAL, t TEXT, FULLTEXT f1(t), FULLTEXT f2(t)) ENGINE=InnoDB; +Warnings: +Note 1831 Duplicate index `f2`. This is deprecated and will be disallowed in a future release +INSERT INTO t1 (a,t) VALUES (1,'1'),(2,'1'); +ALTER TABLE t1 ADD COLUMN g TEXT GENERATED ALWAYS AS (t) VIRTUAL; +DELETE FROM t1 WHERE a = 1; +ALTER TABLE t1 DROP INDEX f1; +INSERT INTO t1 (a,t) VALUES (1,'1'); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/fulltext_distinct.result b/mysql-test/suite/innodb_fts/r/fulltext_distinct.result index 95a9a617fa6..e660b55c448 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext_distinct.result +++ b/mysql-test/suite/innodb_fts/r/fulltext_distinct.result @@ -8,8 +8,6 @@ KEY kt(tag), KEY kv(value(15)), FULLTEXT KEY kvf(value) ) ENGINE = InnoDB; -Warnings: -Warning 1101 BLOB/TEXT column 'value' can't have a default value CREATE TABLE t2 ( id_t2 mediumint unsigned NOT NULL default '0', id_t1 mediumint unsigned NOT NULL default '0', diff --git a/mysql-test/suite/innodb_fts/r/fulltext_misc.result b/mysql-test/suite/innodb_fts/r/fulltext_misc.result index ce7fe46f4e1..6a24d9aea58 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext_misc.result +++ b/mysql-test/suite/innodb_fts/r/fulltext_misc.result @@ -148,7 +148,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 UNION t1 ALL NULL NULL NULL NULL 2 100.00 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (`a` + 12) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` + 12 # Should not crash SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; a @@ -181,7 +181,7 @@ NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #-1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by <expr_cache><`a`>((select `a` from `test`.`t2` where (`test`.`t2`.`b` = 12))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` union select `test`.`t1`.`a` AS `a` from `test`.`t1` order by <expr_cache><`a`>((select `a` from `test`.`t2` where `test`.`t2`.`b` = 12)) # Should not crash SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY (SELECT a FROM t2 WHERE b = 12); diff --git a/mysql-test/suite/innodb_fts/r/fulltext_update.result b/mysql-test/suite/innodb_fts/r/fulltext_update.result index df323996dd9..00d0777362f 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext_update.result +++ b/mysql-test/suite/innodb_fts/r/fulltext_update.result @@ -9,8 +9,6 @@ name VARCHAR(80) DEFAULT '' NOT NULL, FULLTEXT(url,description,shortdesc,longdesc), PRIMARY KEY(gnr) ) ENGINE = InnoDB; -Warnings: -Warning 1101 BLOB/TEXT column 'longdesc' can't have a default value insert into test (url,shortdesc,longdesc,description,name) VALUES ("http:/test.at", "kurz", "lang","desc", "name"); insert into test (url,shortdesc,longdesc,description,name) VALUES diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result index a39c430ef37..983f254cf90 100644 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result +++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result @@ -77,10 +77,10 @@ INSERT INTO fts_test (title,body) VALUES ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED. +ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED CREATE FULLTEXT INDEX idx on fts_test (title, body); ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED. +ERROR 0A000: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED ALTER TABLE fts_test ROW_FORMAT=REDUNDANT; SELECT * FROM fts_test WHERE MATCH (title, body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result b/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result index e5df6ca8b05..731abad9198 100644 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result +++ b/mysql-test/suite/innodb_fts/r/innodb-fts-fic.result @@ -1,3 +1,5 @@ +call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); +call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`articles`"); CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result b/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result index ea0ec381faa..dea2f2360da 100644 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result +++ b/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result @@ -113,6 +113,7 @@ SELECT * FROM articles_4 WHERE MATCH (title,body) AGAINST ('this' IN NATURAL LANGUAGE MODE); id title body 1 test for session stopwords this should also be excluded... +connect con1,localhost,root,,; CREATE TABLE articles_5 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), @@ -125,6 +126,7 @@ SELECT * FROM articles_5 WHERE MATCH (title,body) AGAINST ('session' IN NATURAL LANGUAGE MODE); id title body 1 test for session stopwords this should also be excluded... +connection default; drop table articles; drop table articles_2; drop table articles_3; @@ -145,7 +147,7 @@ Table Create Table articles CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, - `body` text, + `body` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`title`,`body`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -241,7 +243,7 @@ Table Create Table articles CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, - `body` text, + `body` text DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); @@ -435,7 +437,7 @@ Table Create Table articles CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, - `body` text, + `body` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`title`,`body`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -546,7 +548,7 @@ Table Create Table articles CREATE TABLE `articles` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, - `body` text, + `body` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`title`,`body`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 @@ -563,7 +565,7 @@ select @@innodb_ft_enable_stopword; 0 ALTER TABLE articles DROP INDEX idx; ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -"In connection 1" +connection con1; select @@innodb_ft_enable_stopword; @@innodb_ft_enable_stopword 1 @@ -629,7 +631,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOO id title body SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); id title body -"In connection default" +connection default; select @@innodb_ft_enable_stopword; @@innodb_ft_enable_stopword 0 @@ -661,7 +663,7 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN M id title body SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); id title body -"In connection 1" +connection con1; SET SESSION innodb_ft_enable_stopword = 1; create table user_stopword(value varchar(30)) engine = innodb; set session innodb_ft_user_stopword_table = "test/user_stopword"; @@ -672,7 +674,7 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN id title body SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); id title body -"In connection default" +connection default; SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); id title body SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); @@ -698,7 +700,7 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); id title body SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); id title body -"In connection 1" +connection con1; SET SESSION innodb_ft_enable_stopword = 1; SET SESSION innodb_ft_user_stopword_table=default; select @@innodb_ft_user_stopword_table; @@ -719,7 +721,8 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); id title body SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the'); id title body -"In connection default" +disconnect con1; +connection default; SET SESSION innodb_ft_enable_stopword = 1; SET SESSION innodb_ft_user_stopword_table=default; select @@innodb_ft_server_stopword_table; diff --git a/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result b/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result index cb233a466a5..a1ffb0a5d09 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result +++ b/mysql-test/suite/innodb_fts/r/innodb_ft_aux_table.result @@ -1,8 +1,10 @@ CREATE TABLE t1 (v VARCHAR(100), FULLTEXT INDEX (v)) ENGINE=InnoDB; insert into t1 VALUES('First record'),('Second record'),('Third record'); SET @save_ft_aux_table = @@GLOBAL.innodb_ft_aux_table; +connect con1,localhost,root,,; SET GLOBAL innodb_ft_aux_table = 'test/t0'; ERROR 42000: Variable 'innodb_ft_aux_table' can't be set to the value of 'test/t0' +connection default; SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD; value a @@ -51,7 +53,10 @@ SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; KEY VALUE +connection con1; SET GLOBAL innodb_ft_aux_table = 'test/t1'; +disconnect con1; +connection default; SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; DOC_ID SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_BEING_DELETED; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result index a2a73840723..8c3c3b5be68 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result @@ -15,7 +15,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(200) DEFAULT NULL, - `b` text, + `b` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 @@ -301,7 +301,7 @@ MATCH a,b AGAINST ('support') ; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(10) unsigned NOT NULL DEFAULT '0' + `id` int(10) unsigned NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT id FROM t2; id @@ -313,7 +313,7 @@ MATCH a,b AGAINST("+support +collections" IN BOOLEAN MODE); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(10) unsigned NOT NULL DEFAULT '0' + `id` int(10) unsigned NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT id FROM t2; id @@ -324,7 +324,7 @@ MATCH a,b AGAINST ('"proximity search"@10' IN BOOLEAN MODE); SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( - `id` int(10) unsigned NOT NULL DEFAULT '0' + `id` int(10) unsigned NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT id FROM t2; id @@ -561,7 +561,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(200) DEFAULT NULL, - `b` text, + `b` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 @@ -968,7 +968,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(200) DEFAULT NULL, - `b` text, + `b` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 @@ -1017,9 +1017,9 @@ CREATE TABLE `t21` (`a` text, `b` int not null, fulltext key (`a`), fulltext key (`a`) ) ENGINE=INNODB DEFAULT CHARSET=LATIN1; Warnings: -Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release. +Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release ALTER TABLE `t21` ADD UNIQUE INDEX (`b`), ALGORITHM=INPLACE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY ALTER TABLE `t21` ADD UNIQUE INDEX (`b`); DROP TABLE t21; CREATE TABLE `t21` (`a` text, `b` int not null, diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result index 9f5c1f271fc..b76784d4ffd 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result @@ -1,5 +1,6 @@ -drop table if exists t2,t1; set names utf8; +call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); +call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`"); CREATE TABLE t1 ( id1 INT , a1 VARCHAR(200) , @@ -34,46 +35,52 @@ INSERT INTO t2 (a2,b2) VALUES ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) DELETE FROM t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +ANALYZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; id1 1 3 -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; id2 1 3 -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; id1 1 -3 -6 2 +3 4 5 -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +6 +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; id2 1 -3 -6 2 +3 4 5 -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ; +6 +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; id1 1 +2 3 +4 5 6 -2 -4 -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; id2 1 +2 3 +4 5 6 -2 -4 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; id1 1 @@ -93,23 +100,23 @@ SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN id2 3 6 -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ; +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1; id1 -4 -2 -6 1 -5 +2 3 -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ; -id2 4 -2 +5 6 +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2; +id2 1 -5 +2 3 -SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%'; +4 +5 +6 +SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; id2 1 2 @@ -221,8 +228,7 @@ id1 a1 b1 SELECT * FROM t2 WHERE a2 LIKE '%tutorial%'; id2 a2 b2 DROP TABLE t2 , t1; -DROP TABLE IF EXISTS t2,t1; -SET NAMES utf8; +call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`"); CREATE TABLE t1 ( id1 INT , a1 VARCHAR(200) , @@ -258,34 +264,34 @@ INSERT INTO t2 (a2,b2) VALUES ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) DELETE FROM t1; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE) -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; id1 a1 b1 -3 Optimizing MySQL In this tutorial we will show ... 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; -id2 a2 b2 3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; +id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ; +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... -3 Optimizing MySQL In this tutorial we will show ... 2 How To Use MySQL Well After you went through a ... -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ; +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... -3 Optimizing MySQL In this tutorial we will show ... 2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; id1 a1 b1 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... @@ -308,15 +314,15 @@ SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOL id1 a1 b1 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; id2 a2 b2 -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY id1; id1 a1 b1 -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -2 How To Use MySQL Well After you went through a ... -6 MySQL Security When configured properly, MySQL ... 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... -5 MySQL vs. YourSQL In the following database comparison ... +2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... -SELECT * FROM t2; +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM t2 ORDER BY id2; id2 a2 b2 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... @@ -335,23 +341,23 @@ SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN B id2 a2 b2 3 changing column - on UPDATE cascade In this tutorial we will show ... 6 changing column - on UPDATE cascade When configured properly, MySQL ... -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1; id1 a1 b1 -4 changing column - on UPDATE cascade to check foreign constraint -2 changing column - on UPDATE cascade to check foreign constraint -6 changing column - on UPDATE cascade to check foreign constraint 1 changing column - on UPDATE cascade to check foreign constraint -5 changing column - on UPDATE cascade to check foreign constraint +2 changing column - on UPDATE cascade to check foreign constraint 3 changing column - on UPDATE cascade to check foreign constraint -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ; +4 changing column - on UPDATE cascade to check foreign constraint +5 changing column - on UPDATE cascade to check foreign constraint +6 changing column - on UPDATE cascade to check foreign constraint +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2; id2 a2 b2 -4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ... -2 changing column - on UPDATE cascade After you went through a ... -6 changing column - on UPDATE cascade When configured properly, MySQL ... 1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ... -5 changing column - on UPDATE cascade In the following database comparison ... +2 changing column - on UPDATE cascade After you went through a ... 3 changing column - on UPDATE cascade In this tutorial we will show ... -SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%'; +4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ... +5 changing column - on UPDATE cascade In the following database comparison ... +6 changing column - on UPDATE cascade When configured properly, MySQL ... +SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; id2 a2 b2 1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ... 2 changing column - on UPDATE cascade After you went through a ... @@ -456,10 +462,7 @@ ROLLBACK; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; -set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; -set global innodb_large_prefix=1; -set names utf8; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), @@ -477,17 +480,20 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(200) DEFAULT NULL, - `b` text, + `b` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED +SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0; +count(*) +11 INSERT INTO t1 (a,b) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); ANALYZE TABLE t1; SELECT * FROM t1 WHERE MATCH (a,b) -AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... @@ -499,14 +505,14 @@ ERROR 42000: syntax error, unexpected '-' select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); id a b 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; id a b -6 MySQL Security When configured properly, MySQL ... 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 5 MySQL vs. YourSQL In the following database comparison ... -select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1; +6 MySQL Security When configured properly, MySQL ... +select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id; id a b x 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 @@ -519,18 +525,18 @@ id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); id a b -select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION); +select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... +2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... -2 How To Use MySQL Well After you went through a ... -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... ALTER TABLE t1 DROP INDEX idx; CREATE FULLTEXT INDEX idx on t1 (a,b); SELECT * FROM t1 WHERE MATCH (a,b) -AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 3 Optimizing MySQL In this tutorial we will show ... @@ -543,14 +549,14 @@ id a b select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); id a b 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; id a b -6 MySQL Security When configured properly, MySQL ... 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... 5 MySQL vs. YourSQL In the following database comparison ... -select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1; +6 MySQL Security When configured properly, MySQL ... +select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id; id a b x 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 @@ -560,14 +566,14 @@ id a b x 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); id a b -select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION); +select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; id a b 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... +2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 5 MySQL vs. YourSQL In the following database comparison ... 6 MySQL Security When configured properly, MySQL ... -2 How To Use MySQL Well After you went through a ... -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); INSERT INTO t1 (a,b) VALUES ('test proximity search, test, proximity and phrase', @@ -589,14 +595,14 @@ AGAINST ('"proximity search"@1' IN BOOLEAN MODE); id a b SELECT * FROM t1 WHERE MATCH (a,b) -AGAINST ('"proximity search"@3' IN BOOLEAN MODE); +AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id; id a b 8 test proximity search, test, proximity and phrase search, with proximity innodb 9 test proximity fts search, test, proximity and phrase search, with proximity innodb 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb SELECT * FROM t1 WHERE MATCH (a,b) -AGAINST ('"test proximity"@5' IN BOOLEAN MODE); +AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id; id a b 8 test proximity search, test, proximity and phrase search, with proximity innodb 9 test proximity fts search, test, proximity and phrase search, with proximity innodb @@ -624,7 +630,7 @@ select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE); id a b 1 MYSQL TUTORIAL dbms stands for database vðʒwi... SELECT * FROM t1 WHERE MATCH (a,b) -AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; id a b 1 MYSQL TUTORIAL dbms stands for database vðʒwi... 3 OPTIMIZING MYSQL in this tutorial we will show ... @@ -633,7 +639,7 @@ DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MOD SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); id a b -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY id; id a b 2 HOW TO USE MYSQL WELL after you went through a ... 4 1001 MYSQL TRICKS 1. never run mysqld as root. 2. ... @@ -641,7 +647,7 @@ id a b 6 MYSQL SECURITY when configured properly, mysql ... 7 TEST QUERY EXPANSION for database ... DROP TABLE t1; -set names utf8; +SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), @@ -662,14 +668,14 @@ INSERT INTO t1 (a,b) VALUES CREATE FULLTEXT INDEX idx on t1 (a,b); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID -SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς"); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id; id a b 1 Я могу есть стекло оно мне не вредит 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION); id a b 1 Я могу есть стекло оно мне не вредит -SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id; id a b 1 Я могу есть стекло оно мне не вредит 2 Мога да ям стъкло то не ми вреди @@ -731,7 +737,7 @@ id a b 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); id a b -SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id; id a b 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ @@ -742,7 +748,7 @@ WHERE MATCH (a,b) AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE); id a b 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY id; id a b 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_multiple_index.result b/mysql-test/suite/innodb_fts/r/innodb_fts_multiple_index.result index 6a255ddffc5..b0f7d7727d6 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_multiple_index.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_multiple_index.result @@ -17,7 +17,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` varchar(200) DEFAULT NULL, - `b` text, + `b` text DEFAULT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `idx_1` (`a`), FULLTEXT KEY `idx_2` (`b`) @@ -46,9 +46,9 @@ id a b 1 MySQL Tutorial DBMS stands for DataBase ... select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1; id a b x -1 MySQL Tutorial DBMS stands for DataBase ... 0.0906190574169159 -2 How To Use MySQL Well After you went through a ... 0.0906190574169159 -3 Optimizing MySQL In this tutorial we will show ... 0.6961383819580078 +1 MySQL Tutorial DBMS stands for DataBase ... 0.000000001885928302414186 +2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 +3 Optimizing MySQL In this tutorial we will show ... 0.22764469683170319 select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; id a b x 1 MySQL Tutorial DBMS stands for DataBase ... 0 @@ -90,9 +90,9 @@ id a b 1 MySQL Tutorial DBMS stands for DataBase ... select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1; id a b x -1 MySQL Tutorial DBMS stands for DataBase ... 0.0906190574169159 -2 How To Use MySQL Well After you went through a ... 0.0906190574169159 -3 Optimizing MySQL In this tutorial we will show ... 0.6961383819580078 +1 MySQL Tutorial DBMS stands for DataBase ... 0.000000001885928302414186 +2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186 +3 Optimizing MySQL In this tutorial we will show ... 0.22764469683170319 select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; id a b x 1 MySQL Tutorial DBMS stands for DataBase ... 0 diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result index 569de081762..b7688e9ef0f 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result @@ -1,29 +1,215 @@ INSTALL PLUGIN simple_parser SONAME 'mypluglib'; +# Test Part 1: Grammar Test CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), -body TEXT, FULLTEXT (title) WITH PARSER simple_parser ) ENGINE=MyISAM; ALTER TABLE articles ENGINE=InnoDB; -ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table DROP TABLE articles; CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, +comment TEXT, FULLTEXT (title) WITH PARSER simple_parser ) ENGINE=InnoDB; -ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table +ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser; +CREATE FULLTEXT INDEX ft_index ON articles(comment) WITH PARSER simple_parser; +DROP TABLE articles; +# Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX) CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, -FULLTEXT (title) +FULLTEXT (title, body) WITH PARSER simple_parser ) ENGINE=InnoDB; -ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser; -ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table -CREATE FULLTEXT INDEX ft_index ON articles(body) WITH PARSER simple_parser; -ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table +INSERT INTO articles (title, body) VALUES +('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','How to use full-text search engine'), +('Go MySQL Tricks','How to use full text search engine'); +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('mysql'); +id title body +1 MySQL Tutorial DBMS stands for MySQL DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks How to use full-text search engine +5 Go MySQL Tricks How to use full text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('will go'); +id title body +# Test plugin parser tokenizer difference +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full-text'); +id title body +4 1001 MySQL Tricks How to use full-text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full text'); +id title body +5 Go MySQL Tricks How to use full text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE); +id title body +DROP TABLE articles; +# Test Part 3: Row Merge Create Index Test(ALTER TABLE ADD FULLTEXT INDEX) +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT +) ENGINE=InnoDB; +INSERT INTO articles (title, body) VALUES +('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','How to use full-text search engine'), +('Go MySQL Tricks','How to use full text search engine'); +ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser; +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('mysql'); +id title body +1 MySQL Tutorial DBMS stands for MySQL DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks How to use full-text search engine +5 Go MySQL Tricks How to use full text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('will go'); +id title body +# Test plugin parser tokenizer difference +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full-text'); +id title body +4 1001 MySQL Tricks How to use full-text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full text'); +id title body +5 Go MySQL Tricks How to use full text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION); +id title body +4 1001 MySQL Tricks How to use full-text search engine +5 Go MySQL Tricks How to use full text search engine +2 How To Use MySQL Well After you went through a ... +1 MySQL Tutorial DBMS stands for MySQL DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION); +id title body +5 Go MySQL Tricks How to use full text search engine +4 1001 MySQL Tricks How to use full-text search engine +2 How To Use MySQL Well After you went through a ... +1 MySQL Tutorial DBMS stands for MySQL DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE); +id title body +DROP TABLE articles; +# Test Part 3 END +# Test Part 4:crash on commit(before/after) +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title, body) WITH PARSER simple_parser +) ENGINE=InnoDB; +BEGIN; +INSERT INTO articles (title, body) VALUES +('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','How to use full-text search engine'), +('Go MySQL Tricks','How to use full text search engine'); +SELECT COUNT(*) FROM articles; +COUNT(*) +0 +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('mysql'); +id title body +INSERT INTO articles (title, body) VALUES +('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','How to use full-text search engine'), +('Go MariaDB Tricks','How to use full text search engine'); +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('MySQL'); +id title body +6 MySQL Tutorial DBMS stands for MySQL DataBase ... +7 How To Use MySQL Well After you went through a ... +8 Optimizing MySQL In this tutorial we will show ... +9 1001 MySQL Tricks How to use full-text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('tutorial'); +id title body +6 MySQL Tutorial DBMS stands for MySQL DataBase ... +8 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('Tricks'); +id title body +9 1001 MySQL Tricks How to use full-text search engine +10 Go MariaDB Tricks How to use full text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full text search'); +id title body +10 Go MariaDB Tricks How to use full text search engine +9 1001 MySQL Tricks How to use full-text search engine +SELECT COUNT(*) FROM articles; +COUNT(*) +5 +DROP TABLE articles; +# Test Part 5: Test Uninstall Plugin After Index is Built +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title, body) WITH PARSER simple_parser +) ENGINE=InnoDB; +UNINSTALL PLUGIN simple_parser; +INSERT INTO articles (title, body) VALUES +('MySQL Tutorial','DBMS stands for MySQL DataBase ...'); +ERROR HY000: Plugin 'simple_parser' is not loaded +INSTALL PLUGIN simple_parser SONAME 'mypluglib'; +INSERT INTO articles (title, body) VALUES +('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','How to use full-text search engine'), +('Go MySQL Tricks','How to use full text search engine'); +UNINSTALL PLUGIN simple_parser; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('mysql'); +id title body +1 MySQL Tutorial DBMS stands for MySQL DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks How to use full-text search engine +5 Go MySQL Tricks How to use full text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('will go'); +id title body +# Test plugin parser tokenizer difference +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full-text'); +id title body +4 1001 MySQL Tricks How to use full-text search engine +SELECT * FROM articles WHERE +MATCH(title, body) AGAINST('full text'); +id title body +5 Go MySQL Tricks How to use full text search engine +CREATE TABLE articles2 ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title, body) WITH PARSER simple_parser +) ENGINE=InnoDB; +ERROR HY000: Function 'simple_parser' is not defined DROP TABLE articles; UNINSTALL PLUGIN simple_parser; +ERROR 42000: PLUGIN simple_parser does not exist diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result b/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result index a61ff47c9a1..ca9a57dc3e7 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result @@ -128,7 +128,7 @@ WHERE MATCH (a,b) AGAINST ('"mysql use"@1' IN BOOLEAN MODE); id a b INSERT INTO t1 (a,b) VALUES ('XYZ, long blob', repeat("a", 9000)); -INSERT INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob'); +INSERT IGNORE INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 SELECT count(*) FROM t1 @@ -137,7 +137,6 @@ AGAINST ('"xyz blob"@3' IN BOOLEAN MODE); count(*) 2 DROP TABLE t1; -set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -225,5 +224,4 @@ AGAINST ('"very blob"@3' IN BOOLEAN MODE); count(*) 1 DROP TABLE t1; -SET GLOBAL innodb_file_format=Antelope; SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result b/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result index d8954e73e33..c49c7f8ae6c 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result @@ -13,7 +13,7 @@ CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) ) ENGINE=InnoDB; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); CREATE FULLTEXT INDEX ft_idx ON articles(title); @@ -42,7 +42,7 @@ CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); CREATE FULLTEXT INDEX ft_idx ON articles(title); @@ -72,7 +72,7 @@ CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); CREATE FULLTEXT INDEX ft_idx ON articles(title); @@ -233,7 +233,7 @@ CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) ) ENGINE=InnoDB; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); CREATE FULLTEXT INDEX ft_idx ON articles(title); @@ -257,7 +257,7 @@ AGAINST ('love' IN NATURAL LANGUAGE MODE); id title # Shutdown and restart mysqld SET NAMES utf8; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); SELECT * FROM articles WHERE MATCH (title) @@ -273,7 +273,7 @@ CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) ) ENGINE=InnoDB; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); CREATE FULLTEXT INDEX ft_idx ON articles(title); @@ -298,7 +298,7 @@ id title DROP TABLE user_stopword; # Shutdown and restart mysqld SET NAMES utf8; -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); SELECT * FROM articles WHERE MATCH (title) diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result b/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result index 61a1b1bef53..6f5409f63cd 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result @@ -316,7 +316,7 @@ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8,ENGINE = InnoDB; -"In connection 1" +connect con1,localhost,root,,; SET NAMES UTF8; INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , @@ -325,12 +325,12 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'), ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), ('Sævör grét', 'áðan því úlpan var ónýt'); -"In connection 2" +connect con2,localhost,root,,; SET NAMES UTF8; CREATE FULLTEXT INDEX idx on t1 (a,b); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID -"In connection 1" +connection con1; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE); @@ -365,7 +365,7 @@ WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); id a b 6 Sævör grét áðan því úlpan var ónýt -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+record +multiple' IN BOOLEAN MODE); @@ -422,7 +422,7 @@ id a b 1 MySQL from Tutorial DBMS stands for DataBase ... 2 when To Use MySQL Well After that you went through a ... 3 where will Optimizing MySQL what In this tutorial we will show ... -"In connection 1" +connection con1; COMMIT; SELECT * FROM t1 WHERE MATCH (a,b) @@ -443,7 +443,7 @@ INSERT INTO t1 (a,b) VALUES (NULL,NULL); SELECT * FROM t1 WHERE a IS NULL AND b IS NULL; id a b 11 NULL NULL -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('usin* multipl*' IN BOOLEAN MODE); @@ -487,7 +487,7 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('request'); id a b -"In connection 1" +connection con1; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('query performace' IN BOOLEAN MODE); @@ -500,14 +500,14 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+mail +MySQL' IN BOOLEAN MODE); id a b -"In connection 2" +connection con2; COMMIT; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+query +performace' IN BOOLEAN MODE); id a b 9 TRIAL VERSION PŘÍLIŠ ŽLUŤOUČKÝ query performace 1255 minute on 2.1Hz Memory 2GB... -"In connection 1" +connection con1; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+query +performace' IN BOOLEAN MODE); @@ -521,6 +521,9 @@ AGAINST ('well free') ORDER BY 1; id a b 2 when To Use MySQL Well After that you went through a ... 5 when To Use MySQL Well for free faq xyz.com ... +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; SET NAMES UTF8; CREATE TABLE t1 ( @@ -528,7 +531,7 @@ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8,ENGINE = InnoDB; -"In connection 1" +connect con1,localhost,root,,; SET NAMES UTF8; INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , @@ -537,7 +540,7 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'), ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), ('Sævör grét', 'áðan því úlpan var ónýt'); -"In connection 2" +connect con2,localhost,root,,; SET NAMES UTF8; select @@session.tx_isolation; @@session.tx_isolation @@ -548,7 +551,7 @@ Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID INSERT INTO t1 (a,b) VALUES ('adding record using session 1','for verifying multiple concurrent transactions'), ('Мога да ям стъкло', 'то Mне ми вреди'); -"In connection 1" +connection con1; SELECT * FROM t1; id a b 1 MySQL from Tutorial DBMS stands for DataBase ... @@ -589,7 +592,7 @@ id a b 4 Я могу есть стекло оно мне Mне вредит 5 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 7 adding record using session 1 for verifying multiple concurrent transactions -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -610,7 +613,7 @@ id a b 6 Sævör grét áðan því úlpan var ónýt 7 adding record using session 1 for verifying multiple concurrent transactions 8 Мога да ям стъкло то Mне ми вреди -"In connection 1" +connection con1; COMMIT; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b @@ -620,7 +623,7 @@ id a b 4 Я могу есть стекло оно мне Mне вредит SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); id a b -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -637,6 +640,9 @@ id a b 4 Я могу есть стекло оно мне Mне вредит 5 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 7 adding record using session 1 for verifying multiple concurrent transactions +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; SET NAMES UTF8; CREATE TABLE t1 ( @@ -644,14 +650,14 @@ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8,ENGINE = InnoDB; -"In connection 1" +connect con1,localhost,root,,; SET NAMES UTF8; INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , ('when To Use MySQL Well','After that you went through a ...'), ('where will Optimizing MySQL','what In this tutorial we will show ...'), ('Я могу есть стекло', 'оно мне Mне вредит'); -"In connection 2" +connect con2,localhost,root,,; SET NAMES UTF8; select @@session.tx_isolation; @@session.tx_isolation @@ -662,7 +668,7 @@ Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID INSERT INTO t1 (a,b) VALUES ('adding record using session 1','for verifying multiple concurrent transactions'), ('Мога да ям стъкло', 'то Mне ми вреди'); -"In connection 1" +connection con1; SELECT * FROM t1; id a b 1 MySQL from Tutorial DBMS stands for DataBase ... @@ -703,7 +709,7 @@ id a b 5 adding record using session 1 for verifying multiple concurrent transactions 6 Мога да ям стъкло то Mне ми вреди 7 Sævör grét áðan því úlpan var ónýt -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -751,7 +757,7 @@ id a b 4 Я могу есть стекло оно мне Mне вредит 5 adding record using session 1 for verifying multiple concurrent transactions 6 Мога да ям стъкло то Mне ми вреди -"In connection 1" +connection con1; COMMIT; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b @@ -761,7 +767,7 @@ id a b SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); id a b 7 Sævör grét áðan því úlpan var ónýt -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 6 Мога да ям стъкло то Mне ми вреди @@ -779,6 +785,9 @@ id a b 5 adding record using session 1 for verifying multiple concurrent transactions 6 Мога да ям стъкло то Mне ми вреди 7 Sævör grét áðan því úlpan var ónýt +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; SET NAMES UTF8; CREATE TABLE t1 ( @@ -786,7 +795,7 @@ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8,ENGINE = InnoDB; -"In connection 1" +connect con1,localhost,root,,; SET NAMES UTF8; INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , @@ -795,7 +804,7 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'), ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), ('Sævör grét', 'áðan því úlpan var ónýt'); -"In connection 2" +connect con2,localhost,root,,; SET NAMES UTF8; select @@session.tx_isolation; @@session.tx_isolation @@ -806,7 +815,7 @@ Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID INSERT INTO t1 (a,b) VALUES ('adding record using session 1','for verifying multiple concurrent transactions'), ('Мога да ям стъкло', 'то Mне ми вреди'); -"In connection 1" +connection con1; SELECT * FROM t1; id a b 1 MySQL from Tutorial DBMS stands for DataBase ... @@ -846,7 +855,7 @@ id a b 4 Я могу есть стекло оно мне Mне вредит 5 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα 7 adding record using session 1 for verifying multiple concurrent transactions -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -867,7 +876,7 @@ id a b 6 Sævör grét áðan því úlpan var ónýt 7 adding record using session 1 for verifying multiple concurrent transactions 8 Мога да ям стъкло то Mне ми вреди -"In connection 1" +connection con1; ROLLBACK; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b @@ -879,7 +888,7 @@ id a b SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); id a b 6 Sævör grét áðan því úlpan var ónýt -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -900,6 +909,9 @@ id a b 6 Sævör grét áðan því úlpan var ónýt 7 adding record using session 1 for verifying multiple concurrent transactions 8 Мога да ям стъкло то Mне ми вреди +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; SET NAMES UTF8; CREATE TABLE t1 ( @@ -907,14 +919,14 @@ id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8,ENGINE = InnoDB; -"In connection 1" +connect con1,localhost,root,,; SET NAMES UTF8; INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , ('when To Use MySQL Well','After that you went through a ...'), ('where will Optimizing MySQL','what In this tutorial we will show ...'), ('Я могу есть стекло', 'оно мне Mне вредит'); -"In connection 2" +connect con2,localhost,root,,; SET NAMES UTF8; select @@session.tx_isolation; @@session.tx_isolation @@ -925,7 +937,7 @@ Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID INSERT INTO t1 (a,b) VALUES ('adding record using session 1','for verifying multiple concurrent transactions'), ('Мога да ям стъкло', 'то Mне ми вреди'); -"In connection 1" +connection con1; SELECT * FROM t1; id a b 1 MySQL from Tutorial DBMS stands for DataBase ... @@ -967,7 +979,7 @@ id a b 5 adding record using session 1 for verifying multiple concurrent transactions 6 Мога да ям стъкло то Mне ми вреди 7 Sævör grét áðan því úlpan var ónýt -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -1016,7 +1028,7 @@ id a b 4 Я могу есть стекло оно мне Mне вредит 5 adding record using session 1 for verifying multiple concurrent transactions 6 Мога да ям стъкло то Mне ми вреди -"In connection 1" +connection con1; ROLLBACK; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b @@ -1027,7 +1039,7 @@ id a b 6 Мога да ям стъкло то Mне ми вреди SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); id a b -"In connection 2" +connection con2; SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); id a b 4 Я могу есть стекло оно мне Mне вредит @@ -1045,4 +1057,7 @@ id a b 4 Я могу есть стекло оно мне Mне вредит 5 adding record using session 1 for verifying multiple concurrent transactions 6 Мога да ям стъкло то Mне ми вреди +disconnect con1; +disconnect con2; +connection default; DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/misc_debug.result b/mysql-test/suite/innodb_fts/r/misc_debug.result index 1fcc89b165b..8ef2ac425fc 100644 --- a/mysql-test/suite/innodb_fts/r/misc_debug.result +++ b/mysql-test/suite/innodb_fts/r/misc_debug.result @@ -17,3 +17,12 @@ ALTER TABLE t ADD FULLTEXT INDEX (b(64)); ERROR HY000: Unknown error SET SESSION debug_dbug=@saved_debug_dbug; DROP TABLE t; +CREATE TABLE t1 (pk INT, a VARCHAR(8), PRIMARY KEY(pk), +FULLTEXT KEY(a)) ENGINE=InnoDB; +CREATE TABLE t2 (b INT, FOREIGN KEY(b) REFERENCES t1(pk)) ENGINE=InnoDB; +DROP TABLE t1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails +SET DEBUG_DBUG="+d,fts_instrument_sync"; +INSERT INTO t1 VALUES(1, "mariadb"); +ALTER TABLE t1 FORCE; +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb_fts/r/sync.result b/mysql-test/suite/innodb_fts/r/sync.result new file mode 100644 index 00000000000..82959cfea96 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/sync.result @@ -0,0 +1,145 @@ +connect con1,localhost,root,,; +connection default; +# Case 1: Test select and insert(row in both disk and cache) +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +INSERT INTO t1(title) VALUES('mysql'); +INSERT INTO t1(title) VALUES('database'); +connection con1; +SET @old_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,fts_instrument_sync_debug'; +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected'; +INSERT INTO t1(title) VALUES('mysql database'); +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR written'; +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +database 2 3 2 2 0 +database 2 3 2 3 6 +mysql 1 3 2 1 0 +mysql 1 3 2 3 0 +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SET GLOBAL innodb_ft_aux_table=default; +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +FTS_DOC_ID title +1 mysql +2 database +SET DEBUG_SYNC= 'now SIGNAL selected'; +connection con1; +SET @old_dbug = @@SESSION.debug_dbug; +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +database 2 3 2 2 0 +database 2 3 2 3 6 +mysql 1 3 2 1 0 +mysql 1 3 2 3 0 +SET GLOBAL innodb_ft_aux_table=default; +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +FTS_DOC_ID title +3 mysql database +1 mysql +2 database +connection default; +DROP TABLE t1; +# Case 2: Test insert and insert(sync) +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +INSERT INTO t1(title) VALUES('mysql'); +INSERT INTO t1(title) VALUES('database'); +connection con1; +SET debug_dbug = '+d,fts_instrument_sync_debug'; +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted'; +INSERT INTO t1(title) VALUES('mysql database'); +connection default; +SET DEBUG_SYNC= 'now WAIT_FOR written'; +INSERT INTO t1(title) VALUES('mysql database'); +SET DEBUG_SYNC= 'now SIGNAL inserted'; +connection con1; +SET debug_dbug = @old_dbug; +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +database 2 3 2 2 0 +database 2 3 2 3 6 +database 4 4 1 4 6 +mysql 1 4 3 1 0 +mysql 1 4 3 3 0 +mysql 1 4 3 4 0 +SET GLOBAL innodb_ft_aux_table=default; +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +FTS_DOC_ID title +3 mysql database +4 mysql database +1 mysql +2 database +connection default; +disconnect con1; +DROP TABLE t1; +# Case 3: Test insert crash recovery +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +INSERT INTO t1(title) VALUES('database'); +SET debug_dbug = '+d,fts_instrument_sync_debug,fts_write_node_crash'; +INSERT INTO t1(title) VALUES('mysql'); +ERROR HY000: Lost connection to MySQL server during query +After restart +SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database'); +FTS_DOC_ID title +1 database +SET @old_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,fts_instrument_sync_debug'; +INSERT INTO t1(title) VALUES('mysql'); +SET debug_dbug = @old_dbug; +SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database'); +FTS_DOC_ID title +1 database +2 mysql +DROP TABLE t1; +# Case 4: Test sync commit & rollback in background +CREATE TABLE t1( +id INT AUTO_INCREMENT, +title VARCHAR(100), +FULLTEXT(title), +PRIMARY KEY(id)) ENGINE=InnoDB; +SET debug_dbug = '+d,fts_instrument_sync'; +INSERT INTO t1(title) VALUES('mysql'); +SET debug_dbug = @old_dbug; +SET @old_global_dbug = @@GLOBAL.debug_dbug; +SET @old_dbug = @@SESSION.debug_dbug; +SET GLOBAL debug_dbug='+d,fts_instrument_sync,fts_instrument_sync_interrupted'; +INSERT INTO t1(title) VALUES('database'); +SET GLOBAL debug_dbug = @old_global_dbug; +SET debug_dbug = '+d,fts_instrument_sync_debug'; +INSERT INTO t1(title) VALUES('good'); +SET debug_dbug = @old_dbug; +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +database 2 2 1 2 0 +good 3 3 1 3 0 +mysql 1 1 1 1 0 +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION +SET GLOBAL innodb_ft_aux_table=default; +SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good'); +id title +1 mysql +2 database +3 good +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/r/sync_block.result b/mysql-test/suite/innodb_fts/r/sync_block.result new file mode 100644 index 00000000000..65bee127e80 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/sync_block.result @@ -0,0 +1,83 @@ +SET @old_log_output = @@global.log_output; +SET @old_slow_query_log = @@global.slow_query_log; +SET @old_general_log = @@global.general_log; +SET @old_long_query_time = @@global.long_query_time; +SET @old_debug = @@global.debug_dbug; +SET GLOBAL log_output = 'TABLE'; +SET GLOBAL general_log = 1; +SET GLOBAL slow_query_log = 1; +SET GLOBAL long_query_time = 1; +connect con1,localhost,root,,; +connect con2,localhost,root,,; +connection default; +# Case 1: Sync blocks DML(insert) on the same table. +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +connection con1; +SET GLOBAL debug_dbug='+d,fts_instrument_sync_debug,fts_instrument_sync_sleep'; +SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue'; +INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR begin'; +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection con1; +/* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +/* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +FTS_DOC_ID title +connection default; +# make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +SLEEP(2) +0 +# slow log results should only contain INSERT INTO t1. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; +sql_text +INSERT INTO t1(title) VALUES('mysql database') +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; +DROP TABLE t1; +# Case 2: Sync blocks DML(insert) on other tables. +CREATE TABLE t1 ( +FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +FULLTEXT(title) +) ENGINE = InnoDB; +CREATE TABLE t2(id INT); +connection con1; +SET GLOBAL debug_dbug='+d,fts_instrument_sync_request,fts_instrument_sync_sleep'; +SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue'; +INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +SET DEBUG_SYNC= 'now WAIT_FOR begin'; +INSERT INTO t2 VALUES(1); +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection con1; +/* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +connection con2; +/* conneciton con2 */ INSERT INTO t2 VALUES(1); +connection default; +SET DEBUG_SYNC = 'RESET'; +# make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +SLEEP(2) +0 +# slow log results should be empty here. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; +sql_text +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; +DROP TABLE t1,t2; +disconnect con1; +disconnect con2; +# Restore slow log settings. +SET GLOBAL log_output = @old_log_output; +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; +SET GLOBAL long_query_time = @old_long_query_time; diff --git a/mysql-test/suite/innodb_fts/t/basic.test b/mysql-test/suite/innodb_fts/t/basic.test index 58f36be08a5..0c0920c5f16 100644 --- a/mysql-test/suite/innodb_fts/t/basic.test +++ b/mysql-test/suite/innodb_fts/t/basic.test @@ -3,6 +3,14 @@ -- source include/have_innodb.inc # Create FTS table +--error ER_INNODB_NO_FT_TEMP_TABLE +CREATE TEMPORARY TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), diff --git a/mysql-test/suite/innodb_fts/t/concurrent_insert.test b/mysql-test/suite/innodb_fts/t/concurrent_insert.test index 77097d44dc5..35debd87cea 100644 --- a/mysql-test/suite/innodb_fts/t/concurrent_insert.test +++ b/mysql-test/suite/innodb_fts/t/concurrent_insert.test @@ -29,6 +29,7 @@ CREATE TABLE t2 (f1 char(100), FULLTEXT idx1(f1))ENGINE=InnoDB; INSERT INTO t2 VALUES('mariadb'); connection default; +SET @saved_dbug = @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug ='+d,fts_instrument_sync_request,ib_optimize_wq_hang'; SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL drop_index_start WAIT_FOR sync_op'; @@ -45,7 +46,7 @@ set DEBUG_SYNC= 'now SIGNAL fts_drop_index'; connection con1; reap; -SET global DEBUG_DBUG=RESET; drop table t1, t2; connection default; set DEBUG_SYNC=RESET; +SET @@GLOBAL.debug_dbug = @saved_dbug; diff --git a/mysql-test/suite/innodb_fts/t/crash_recovery.test b/mysql-test/suite/innodb_fts/t/crash_recovery.test index 861dfdfd658..1b321af236a 100644 --- a/mysql-test/suite/innodb_fts/t/crash_recovery.test +++ b/mysql-test/suite/innodb_fts/t/crash_recovery.test @@ -8,6 +8,7 @@ --source include/not_embedded.inc --source include/maybe_debug.inc +FLUSH TABLES; # Following are test for crash recovery on FTS index, the first scenario # is for bug Bug #14586855 INNODB: FAILING ASSERTION: (DICT_INDEX_GET_N_UNIQUE( # PLAN->INDEX) <= PLAN->N_EXAC @@ -39,7 +40,17 @@ BEGIN; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); ---source include/kill_and_restart_mysqld.inc +--echo # Make durable the AUTO_INCREMENT in the above incomplete transaction. +--connect (flush_redo_log,localhost,root,,) +SET GLOBAL innodb_flush_log_at_trx_commit=1; +BEGIN; +DELETE FROM articles LIMIT 1; +ROLLBACK; +--disconnect flush_redo_log +--connection default + +let $shutdown_timeout=0; +--source include/restart_mysqld.inc # This insert will re-initialize the Doc ID counter, it should not crash INSERT INTO articles (title,body) VALUES @@ -70,7 +81,16 @@ INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); connection default; ---source include/kill_and_restart_mysqld.inc +--echo # Make durable the AUTO_INCREMENT in the above incomplete transaction. +--connect (flush_redo_log,localhost,root,,) +SET GLOBAL innodb_flush_log_at_trx_commit=1; +BEGIN; +DELETE FROM articles LIMIT 1; +ROLLBACK; +--disconnect flush_redo_log +--connection default + +--source include/restart_mysqld.inc disconnect dml; @@ -109,6 +129,8 @@ INSERT INTO articles VALUES connect(dml, localhost, root,,); BEGIN; +# Below we do not depend on the durability of the AUTO_INCREMENT sequence, +# so we can skip the above flush_redo_log trick. INSERT INTO articles VALUES (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...'); @@ -152,7 +174,7 @@ SELECT * FROM mdev19073_2 WHERE MATCH (title, body) AGAINST ('Database' IN NATURAL LANGUAGE MODE); connection default; ---source include/kill_and_restart_mysqld.inc +--source include/restart_mysqld.inc disconnect dml; disconnect dml2; diff --git a/mysql-test/suite/innodb_fts/t/create.test b/mysql-test/suite/innodb_fts/t/create.test index f0329602ed1..4e522994fcc 100644 --- a/mysql-test/suite/innodb_fts/t/create.test +++ b/mysql-test/suite/innodb_fts/t/create.test @@ -26,8 +26,8 @@ INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84); INSERT INTO t SET t=REPEAT('after',17); # too long, will not be indexed INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15); --echo # The data below is not 3-byte UTF-8, but 4-byte chars. -INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84); -INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85); +INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84); +INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85); SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5)); SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation'); @@ -90,3 +90,19 @@ ENGINE=InnoDB; # The column length should be 84 bytes (84 characters * 1 byte/character). SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len; DROP TABLE t; + +--echo # +--echo # MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in +--echo # trx_undo_page_report_modify upon optimizing table +--echo # under innodb_optimize_fulltext_only +--echo # + +CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB; +INSERT INTO t1 (f1) VALUES ('foo'),('bar'); +DELETE FROM t1 LIMIT 1; +ALTER TABLE t1 ADD FULLTEXT KEY (f1); +SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only; +SET GLOBAL innodb_optimize_fulltext_only= 1; +OPTIMIZE TABLE t1; +DROP TABLE t1; +SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save; diff --git a/mysql-test/suite/innodb_fts/t/fulltext2.test b/mysql-test/suite/innodb_fts/t/fulltext2.test index 33b6a7ac88e..6529630b01e 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext2.test +++ b/mysql-test/suite/innodb_fts/t/fulltext2.test @@ -11,11 +11,6 @@ DROP TABLE IF EXISTS t1; --enable_warnings -if (`select plugin_auth_version <= "5.6.10" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB 5.6.10 or earlier -} - CREATE TABLE t1 ( i int(10) unsigned not null auto_increment primary key, a varchar(255) not null, @@ -239,3 +234,29 @@ INSERT INTO t1 VALUES('„MySQL“'); SELECT a FROM t1 WHERE MATCH a AGAINST('“MySQL„' IN BOOLEAN MODE); DROP TABLE t1; SET NAMES latin1; + +# +# Bug #20597981 - WRONG RELEVANCE RANKING FOR FULL TEXT SEARCHES +# WHEN FTS_DOC_ID IS PRIMARY KEY +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + id int(10) not null , + first_name varchar(50) NOT NULL, + last_name varchar(50) NOT NULL, + PRIMARY KEY (FTS_DOC_ID), + UNIQUE KEY idx_1 (first_name, last_name), + FULLTEXT KEY `idx_2` (first_name) +) ENGINE=InnoDB; + +INSERT INTO t1 (id, first_name, last_name) VALUES +(10, 'Bart', 'Simpson'), +(11, 'Homer', 'Simpson'), +(12, 'Marge', 'Simpson'), +(13, 'Lisa', 'Simpson'), +(14, 'Maggie', 'Simpson'), +(15, 'Ned', 'Flanders'), +(16, 'Nelson', 'Muntz'); + +analyze table t1; +SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/fulltext3.test b/mysql-test/suite/innodb_fts/t/fulltext3.test index 11e6e1af734..9c7941d7b5c 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext3.test +++ b/mysql-test/suite/innodb_fts/t/fulltext3.test @@ -35,3 +35,12 @@ FULLTEXT(a)) ENGINE=$default_engine; DROP TABLE t1; # End of 5.1 tests + + +CREATE TABLE t1 (a SERIAL, t TEXT, FULLTEXT f1(t), FULLTEXT f2(t)) ENGINE=InnoDB; +INSERT INTO t1 (a,t) VALUES (1,'1'),(2,'1'); +ALTER TABLE t1 ADD COLUMN g TEXT GENERATED ALWAYS AS (t) VIRTUAL; +DELETE FROM t1 WHERE a = 1; +ALTER TABLE t1 DROP INDEX f1; +INSERT INTO t1 (a,t) VALUES (1,'1'); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-fic.test b/mysql-test/suite/innodb_fts/t/innodb-fts-fic.test index 6570e6cf216..669aa69e835 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-fic.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-fic.test @@ -2,6 +2,8 @@ -- source include/have_innodb.inc +call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); +call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`articles`"); # Create FTS table CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test index 5c2d383d101..0f29d092541 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test @@ -524,7 +524,6 @@ ALTER TABLE articles DROP INDEX idx; ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); ---echo "In connection 1" --connection con1 select @@innodb_ft_enable_stopword; @@ -557,7 +556,6 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOO SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); ---echo "In connection default" --connection default select @@innodb_ft_enable_stopword; # no result expected as word not indexed from connection 1 @@ -580,7 +578,6 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN M SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); ---echo "In connection 1" --connection con1 SET SESSION innodb_ft_enable_stopword = 1; # Define a correct formated user stopword table @@ -597,7 +594,6 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); ---echo "In connection default" --connection default # no result expected as innodb_ft_user_stopword_table filter it from connection1 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); @@ -619,7 +615,6 @@ ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); ---echo "In connection 1" --connection con1 SET SESSION innodb_ft_enable_stopword = 1; SET SESSION innodb_ft_user_stopword_table=default; @@ -641,7 +636,6 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the'); disconnect con1; --source include/wait_until_disconnected.inc ---echo "In connection default" --connection default SET SESSION innodb_ft_enable_stopword = 1; SET SESSION innodb_ft_user_stopword_table=default; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test index 2e8ff4ea48e..5b11a418dab 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test @@ -1,7 +1,9 @@ -#------------------------------------------------------------------------------ +#----------------------------------------------------------------------------- # Test With alter/create/drop index -#------------------------------------------------------------------------------ +#----------------------------------------------------------------------------- + --source include/have_innodb.inc +--source include/default_charset.inc let collation=UTF8_UNICODE_CI; --source include/have_collation.inc diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test index 6ef0452f4c6..3c794ec6440 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test @@ -1,19 +1,13 @@ +--source include/have_innodb.inc + #------------------------------------------------------------------------------ # FTS with FK and update cascade #------------------------------------------------------------------------------- ---source include/have_innodb.inc - -if (`select plugin_auth_version <= "5.6.10" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB 5.6.10 or earlier -} - ---disable_warnings -drop table if exists t2,t1; ---enable_warnings - set names utf8; +call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); +call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`"); + # Create FTS table CREATE TABLE t1 ( id1 INT , @@ -63,14 +57,17 @@ INSERT INTO t2 (a2,b2) VALUES --error 1451 DELETE FROM t1; -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ; -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ; +ANALYZE TABLE t1; +ANALYZE TABLE t2; -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ; -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ; +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; + +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; @@ -89,10 +86,10 @@ SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; # it shows updated record -SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ; +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1; # InnoDB:Error child table does not show the expected record -SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ; -SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%'; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2; +SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; DROP TABLE t2 , t1; @@ -220,11 +217,8 @@ DROP TABLE t2 , t1; #------------------------------------------------------------------------------ # FTS with FK+transactions and UPDATE casecade with transaction #------------------------------------------------------------------------------- ---disable_warnings -DROP TABLE IF EXISTS t2,t1; ---enable_warnings -SET NAMES utf8; +call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`"); # Create FTS table CREATE TABLE t1 ( @@ -277,12 +271,12 @@ INSERT INTO t2 (a2,b2) VALUES DELETE FROM t1; # records expected -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ; -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ; -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ; -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; @@ -296,8 +290,8 @@ SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ; SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; -SELECT * FROM t1; -SELECT * FROM t2; +SELECT * FROM t1 ORDER BY id1; +SELECT * FROM t2 ORDER BY id2; COMMIT; @@ -312,9 +306,9 @@ SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN B SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; # it shows updated record -SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ; -SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ; -SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%'; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2; +SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; DROP TABLE t2 , t1; @@ -434,18 +428,9 @@ DROP TABLE t2 , t1; #------------------------------------------------------------------------------ # Save innodb variables ---disable_query_log -let $innodb_file_format_orig=`select @@innodb_file_format`; let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; -let $innodb_file_format_max_orig=`select @@innodb_file_format_max`; -let $innodb_large_prefix_orig=`select @@innodb_large_prefix`; ---enable_query_log -# Set Innodb file format as feature works for Barracuda file format -set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; -set global innodb_large_prefix=1; -set names utf8; # Create FTS table CREATE TABLE t1 ( @@ -464,6 +449,9 @@ INSERT INTO t1 (a,b) VALUES ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); EVAL SHOW CREATE TABLE t1; +# Check whether individual space id created for AUX tables +SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0; + # Insert rows INSERT INTO t1 (a,b) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), @@ -476,20 +464,20 @@ ANALYZE TABLE t1; # Select word "tutorial" in the table SELECT * FROM t1 WHERE MATCH (a,b) - AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; # boolean mode select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); --error ER_PARSE_ERROR select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); -select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE); -select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1; +select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; +select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id; select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); # query expansion -select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION); +select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; # Drop index ALTER TABLE t1 DROP INDEX idx; @@ -505,20 +493,20 @@ ANALYZE TABLE t1; # Select word "tutorial" in the table SELECT * FROM t1 WHERE MATCH (a,b) - AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; # boolean mode select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); -select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE); -select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1; +select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; +select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id; # Innodb:Assert eval0eval.c line 148 #select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷWİ*" IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); # query expansion -select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION); +select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; # insert for proximity search @@ -549,12 +537,12 @@ SELECT * FROM t1 # This give you all three documents SELECT * FROM t1 WHERE MATCH (a,b) - AGAINST ('"proximity search"@3' IN BOOLEAN MODE); + AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id; # Similar boundary testing for the words SELECT * FROM t1 WHERE MATCH (a,b) - AGAINST ('"test proximity"@5' IN BOOLEAN MODE); + AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id; # Test with more word The last document will return, please notice there # is no ordering requirement for proximity search. @@ -579,7 +567,7 @@ select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) - AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE); @@ -588,20 +576,14 @@ DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MOD SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY id; DROP TABLE t1; ---disable_query_log -eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; -eval SET GLOBAL innodb_file_format_max=$innodb_file_format_max_orig; -eval SET GLOBAL innodb_large_prefix=$innodb_large_prefix_orig; ---enable_query_log #------------------------------------------------------------------------------ # FTS index with utf8 character testcase #------------------------------------------------------------------------------ -set names utf8; # Create FTS table EVAL CREATE TABLE t1 ( @@ -631,10 +613,10 @@ INSERT INTO t1 (a,b) VALUES CREATE FULLTEXT INDEX idx on t1 (a,b); # FTS Queries -SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς"); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id; SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION); -SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id; SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE); @@ -682,14 +664,14 @@ DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem"); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); -SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id; SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE); -SELECT * FROM t1; +SELECT * FROM t1 ORDER BY id; DROP TABLE t1; # This is to test the update operation on FTS indexed and non-indexed diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test index e800faed0f5..cd31500b23f 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test @@ -1,45 +1,218 @@ ---source include/have_simple_parser.inc --source include/have_innodb.inc +--source include/have_simple_parser.inc +# Restart is not supported in embedded +--source include/not_embedded.inc # Install fts parser plugin INSTALL PLUGIN simple_parser SONAME 'mypluglib'; +-- echo # Test Part 1: Grammar Test # Create a myisam table and alter it to innodb table CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), - body TEXT, FULLTEXT (title) WITH PARSER simple_parser ) ENGINE=MyISAM; ---error ER_INNODB_NO_FT_USES_PARSER + ALTER TABLE articles ENGINE=InnoDB; DROP TABLE articles; # Create a table having a full text index with parser ---error ER_INNODB_NO_FT_USES_PARSER CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, + comment TEXT, FULLTEXT (title) WITH PARSER simple_parser ) ENGINE=InnoDB; +# Alter table to add a full text index with parser +ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser; + +# Create a full text index with parser +CREATE FULLTEXT INDEX ft_index ON articles(comment) WITH PARSER simple_parser; + +DROP TABLE articles; + +-- echo # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX) CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, - FULLTEXT (title) + FULLTEXT (title, body) WITH PARSER simple_parser ) ENGINE=InnoDB; -# Alter table to add a full text index with parser ---error ER_INNODB_NO_FT_USES_PARSER -ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser; +INSERT INTO articles (title, body) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MySQL Tricks','How to use full text search engine'); -# Create a full text index with parser ---error ER_INNODB_NO_FT_USES_PARSER -CREATE FULLTEXT INDEX ft_index ON articles(body) WITH PARSER simple_parser; +# Simple term search +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('mysql'); + +# Test stopword and word len less than fts_min_token_size +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('will go'); + +-- echo # Test plugin parser tokenizer difference +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full-text'); + +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full text'); + +# No result here, we get '"mysql' 'database"' by simple parser +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE); + +DROP TABLE articles; + +-- echo # Test Part 3: Row Merge Create Index Test(ALTER TABLE ADD FULLTEXT INDEX) +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT + ) ENGINE=InnoDB; + +INSERT INTO articles (title, body) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MySQL Tricks','How to use full text search engine'); + +# Create fulltext index +ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser; + +# Simple term search +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('mysql'); + +# Test stopword and word len less than fts_min_token_size +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('will go'); + +-- echo # Test plugin parser tokenizer difference +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full-text'); + +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full text'); + +# Test query expansion +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION); + +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION); + +# No result here, we get '"mysql' 'database"' by simple parser +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE); + +DROP TABLE articles; +-- echo # Test Part 3 END + +-- echo # Test Part 4:crash on commit(before/after) +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title, body) WITH PARSER simple_parser +) ENGINE=InnoDB; + +BEGIN; +INSERT INTO articles (title, body) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MySQL Tricks','How to use full text search engine'); + +--source include/restart_mysqld.inc + +SELECT COUNT(*) FROM articles; +# Simple term search - no records expected +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('mysql'); + +INSERT INTO articles (title, body) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MariaDB Tricks','How to use full text search engine'); + +--source include/restart_mysqld.inc + +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('MySQL'); +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('tutorial'); +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('Tricks'); +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full text search'); +SELECT COUNT(*) FROM articles; +DROP TABLE articles; + +-- echo # Test Part 5: Test Uninstall Plugin After Index is Built +# Note: this test should be the last one because we uninstall plugin +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title, body) WITH PARSER simple_parser + ) ENGINE=InnoDB; + +# Uninstall plugin +UNINSTALL PLUGIN simple_parser; + +-- error ER_PLUGIN_IS_NOT_LOADED +INSERT INTO articles (title, body) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'); + +# Reinstall plugin +INSTALL PLUGIN simple_parser SONAME 'mypluglib'; + +INSERT INTO articles (title, body) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MySQL Tricks','How to use full text search engine'); + +# Get warning here +UNINSTALL PLUGIN simple_parser; + +# Simple term search +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('mysql'); + +# Test stopword and word len less than fts_min_token_size +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('will go'); + +-- echo # Test plugin parser tokenizer difference +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full-text'); + +SELECT * FROM articles WHERE + MATCH(title, body) AGAINST('full text'); + +-- error ER_FUNCTION_NOT_DEFINED +CREATE TABLE articles2 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title, body) WITH PARSER simple_parser + ) ENGINE=InnoDB; DROP TABLE articles; # Uninstall plugin +-- error ER_SP_DOES_NOT_EXIST UNINSTALL PLUGIN simple_parser; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test index b2ac81e2840..20eee3fac23 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test @@ -1,19 +1,14 @@ +--source include/have_innodb.inc + # This is the DDL function tests for innodb FTS # Functional testing with FTS proximity search using '@' # and try search default words ---source include/have_innodb.inc - -if (`select plugin_auth_version <= "5.6.10" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB 5.6.10 or earlier -} --disable_warnings drop table if exists t1; --enable_warnings --disable_query_log -let $innodb_file_format_orig = `select @@innodb_file_format`; let $innodb_file_per_table_orig = `select @@innodb_file_per_table`; --enable_query_log @@ -156,7 +151,7 @@ SELECT * FROM t1 INSERT INTO t1 (a,b) VALUES ('XYZ, long blob', repeat("a", 9000)); -INSERT INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob'); +INSERT IGNORE INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob'); # 2 rows match SELECT count(*) FROM t1 @@ -165,7 +160,6 @@ SELECT count(*) FROM t1 DROP TABLE t1; -set global innodb_file_format="Barracuda"; set global innodb_file_per_table=1; # Test fts with externally stored long column @@ -263,5 +257,4 @@ SELECT count(*) FROM t1 DROP TABLE t1; -eval SET GLOBAL innodb_file_format=$innodb_file_format_orig; eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test b/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test index 4092c324bf9..16ee91c30f4 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test @@ -19,7 +19,7 @@ CREATE TABLE articles ( ) ENGINE=InnoDB; --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); @@ -58,7 +58,7 @@ CREATE TABLE articles ( ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); @@ -98,7 +98,7 @@ CREATE TABLE articles ( ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci; --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); @@ -297,7 +297,7 @@ CREATE TABLE articles ( ) ENGINE=InnoDB; --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); @@ -332,7 +332,7 @@ SET NAMES utf8; # Insert rows to check if it uses user stopword --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); --enable_warnings @@ -355,7 +355,7 @@ CREATE TABLE articles ( ) ENGINE=InnoDB; --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); @@ -393,7 +393,7 @@ SET NAMES utf8; # Insert rows to check if it uses user stopword --disable_warnings -INSERT INTO articles (title) VALUES +INSERT IGNORE INTO articles (title) VALUES ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), ('lṓve'),('LṒVE'); --enable_warnings diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test b/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test index 3327e0ccd77..11571f346a2 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test @@ -345,7 +345,6 @@ EVAL CREATE TABLE t1 ( ) CHARACTER SET = UTF8,ENGINE = InnoDB; ---echo "In connection 1" --connect (con1,localhost,root,,) SET NAMES UTF8; @@ -359,7 +358,6 @@ INSERT INTO t1 (a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt'); ---echo "In connection 2" --connect (con2,localhost,root,,) SET NAMES UTF8; @@ -367,7 +365,6 @@ SET NAMES UTF8; CREATE FULLTEXT INDEX idx on t1 (a,b); ---echo "In connection 1" --connection con1 # first and third record expected @@ -407,7 +404,6 @@ SELECT * FROM t1 AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); ---echo "In connection 2" --connection con2 # one records expected @@ -461,7 +457,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY 1; ---echo "In connection 1" --connection con1 # transaction commit ,now we will be able to see records with FTS index COMMIT; @@ -482,7 +477,6 @@ SELECT * FROM t1 INSERT INTO t1 (a,b) VALUES (NULL,NULL); SELECT * FROM t1 WHERE a IS NULL AND b IS NULL; ---echo "In connection 2" --connection con2 # one record expected @@ -540,7 +534,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('request'); ---echo "In connection 1" --connection con1 # no records expected as update tnx is not committed yet. @@ -557,7 +550,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+mail +MySQL' IN BOOLEAN MODE); ---echo "In connection 2" --connection con2 COMMIT; SELECT * FROM t1 @@ -566,7 +558,6 @@ SELECT * FROM t1 ---echo "In connection 1" --connection con1 SELECT * FROM t1 WHERE MATCH (a,b) @@ -601,7 +592,6 @@ EVAL CREATE TABLE t1 ( ) CHARACTER SET = UTF8,ENGINE = InnoDB; ---echo "In connection 1" --connect (con1,localhost,root,,) SET NAMES UTF8; @@ -615,7 +605,6 @@ INSERT INTO t1 (a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt'); ---echo "In connection 2" --connect (con2,localhost,root,,) SET NAMES UTF8; select @@session.tx_isolation; @@ -627,7 +616,6 @@ INSERT INTO t1 (a,b) VALUES ('Мога да ям стъкло', 'то Mне ми вреди'); ---echo "In connection 1" --connection con1 @@ -650,7 +638,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE SELECT * FROM t1; ---echo "In connection 2" --connection con2 # records expected due to repeatable read SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -660,7 +647,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE SELECT * FROM t1; ---echo "In connection 1" --connection con1 COMMIT; # 1 record expected @@ -670,7 +656,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло'); # No record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); ---echo "In connection 2" --connection con2 # 1 record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -705,7 +690,6 @@ EVAL CREATE TABLE t1 ( ) CHARACTER SET = UTF8,ENGINE = InnoDB; ---echo "In connection 1" --connect (con1,localhost,root,,) SET NAMES UTF8; @@ -717,7 +701,6 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'); ---echo "In connection 2" --connect (con2,localhost,root,,) SET NAMES UTF8; select @@session.tx_isolation; @@ -729,7 +712,6 @@ INSERT INTO t1 (a,b) VALUES ('Мога да ям стъкло', 'то Mне ми вреди'); ---echo "In connection 1" --connection con1 @@ -753,7 +735,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE); SELECT * FROM t1; ---echo "In connection 2" --connection con2 # 2 records expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -784,7 +765,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE); SELECT * FROM t1; ---echo "In connection 1" --connection con1 COMMIT; # 1 record expected @@ -794,7 +774,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стекло'); # 1 record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); ---echo "In connection 2" --connection con2 # 1 record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -828,7 +807,6 @@ EVAL CREATE TABLE t1 ( ) CHARACTER SET = UTF8,ENGINE = InnoDB; ---echo "In connection 1" --connect (con1,localhost,root,,) SET NAMES UTF8; @@ -842,7 +820,6 @@ INSERT INTO t1 (a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt'); ---echo "In connection 2" --connect (con2,localhost,root,,) SET NAMES UTF8; select @@session.tx_isolation; @@ -854,7 +831,6 @@ INSERT INTO t1 (a,b) VALUES ('Мога да ям стъкло', 'то Mне ми вреди'); ---echo "In connection 1" --connection con1 @@ -877,7 +853,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE SELECT * FROM t1; ---echo "In connection 2" --connection con2 # records expected due to repeatable read SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -887,7 +862,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE SELECT * FROM t1; ---echo "In connection 1" --connection con1 ROLLBACK; # 2 record expected @@ -897,7 +871,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло'); # 1 record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); ---echo "In connection 2" --connection con2 # 2 record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -932,7 +905,6 @@ EVAL CREATE TABLE t1 ( ) CHARACTER SET = UTF8,ENGINE = InnoDB; ---echo "In connection 1" --connect (con1,localhost,root,,) SET NAMES UTF8; @@ -944,7 +916,6 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'); ---echo "In connection 2" --connect (con2,localhost,root,,) SET NAMES UTF8; select @@session.tx_isolation; @@ -956,7 +927,6 @@ INSERT INTO t1 (a,b) VALUES ('Мога да ям стъкло', 'то Mне ми вреди'); ---echo "In connection 1" --connection con1 @@ -980,7 +950,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE); SELECT * FROM t1; ---echo "In connection 2" --connection con2 # 2 records expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); @@ -1010,7 +979,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE); SELECT * FROM t1; ---echo "In connection 1" --connection con1 ROLLBACK; # 2 record expected @@ -1020,7 +988,6 @@ SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло'); # no record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Sævör grét"@18' IN BOOLEAN MODE); ---echo "In connection 2" --connection con2 # 2 record expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE); diff --git a/mysql-test/suite/innodb_fts/t/misc_debug.test b/mysql-test/suite/innodb_fts/t/misc_debug.test index 4b32afb848c..aaf628abe6d 100644 --- a/mysql-test/suite/innodb_fts/t/misc_debug.test +++ b/mysql-test/suite/innodb_fts/t/misc_debug.test @@ -39,3 +39,16 @@ ALTER TABLE t ADD FULLTEXT INDEX (b(64)); SET SESSION debug_dbug=@saved_debug_dbug; DROP TABLE t; + +# MDEV-21550 Assertion `!table->fts->in_queue' failed in +# fts_optimize_remove_table +CREATE TABLE t1 (pk INT, a VARCHAR(8), PRIMARY KEY(pk), + FULLTEXT KEY(a)) ENGINE=InnoDB; +CREATE TABLE t2 (b INT, FOREIGN KEY(b) REFERENCES t1(pk)) ENGINE=InnoDB; +--error ER_ROW_IS_REFERENCED_2 +DROP TABLE t1; +SET DEBUG_DBUG="+d,fts_instrument_sync"; +INSERT INTO t1 VALUES(1, "mariadb"); +ALTER TABLE t1 FORCE; +# Cleanup +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb_fts/t/sync.opt b/mysql-test/suite/innodb_fts/t/sync.opt new file mode 100644 index 00000000000..7724f97647f --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/sync.opt @@ -0,0 +1,2 @@ +--innodb-ft-index-cache +--innodb-ft-index-table diff --git a/mysql-test/suite/innodb_fts/t/sync.test b/mysql-test/suite/innodb_fts/t/sync.test new file mode 100644 index 00000000000..2ad5423d73b --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/sync.test @@ -0,0 +1,170 @@ +# +# BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX +# + +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/not_valgrind.inc +--source include/not_embedded.inc +--source include/not_crashrep.inc + +connect (con1,localhost,root,,); +connection default; + +--echo # Case 1: Test select and insert(row in both disk and cache) +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +INSERT INTO t1(title) VALUES('mysql'); +INSERT INTO t1(title) VALUES('database'); + +connection con1; + +SET @old_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,fts_instrument_sync_debug'; + +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR selected'; + +send INSERT INTO t1(title) VALUES('mysql database'); + +connection default; + +SET DEBUG_SYNC= 'now WAIT_FOR written'; + +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SET GLOBAL innodb_ft_aux_table=default; + +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); + +SET DEBUG_SYNC= 'now SIGNAL selected'; + +connection con1; +--reap + +SET @old_dbug = @@SESSION.debug_dbug; + +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SET GLOBAL innodb_ft_aux_table=default; + +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); + +connection default; + +DROP TABLE t1; + +--echo # Case 2: Test insert and insert(sync) +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +INSERT INTO t1(title) VALUES('mysql'); +INSERT INTO t1(title) VALUES('database'); + +connection con1; + +SET debug_dbug = '+d,fts_instrument_sync_debug'; + +SET DEBUG_SYNC= 'fts_write_node SIGNAL written WAIT_FOR inserted'; + +send INSERT INTO t1(title) VALUES('mysql database'); + +connection default; + +SET DEBUG_SYNC= 'now WAIT_FOR written'; + +INSERT INTO t1(title) VALUES('mysql database'); + +SET DEBUG_SYNC= 'now SIGNAL inserted'; + +connection con1; +--reap + +SET debug_dbug = @old_dbug; + +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SET GLOBAL innodb_ft_aux_table=default; + +SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); + +connection default; +disconnect con1; + +DROP TABLE t1; + +--echo # Case 3: Test insert crash recovery +--let $_expect_file_name=$MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect + +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +INSERT INTO t1(title) VALUES('database'); + +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +SET debug_dbug = '+d,fts_instrument_sync_debug,fts_write_node_crash'; + +--error 2013 +INSERT INTO t1(title) VALUES('mysql'); + +--source include/start_mysqld.inc + +-- echo After restart +SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database'); + +SET @old_dbug = @@SESSION.debug_dbug; + +SET debug_dbug = '+d,fts_instrument_sync_debug'; + +INSERT INTO t1(title) VALUES('mysql'); + +SET debug_dbug = @old_dbug; + +SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database'); + +DROP TABLE t1; + +--echo # Case 4: Test sync commit & rollback in background +CREATE TABLE t1( + id INT AUTO_INCREMENT, + title VARCHAR(100), + FULLTEXT(title), + PRIMARY KEY(id)) ENGINE=InnoDB; + +SET debug_dbug = '+d,fts_instrument_sync'; +INSERT INTO t1(title) VALUES('mysql'); +SET debug_dbug = @old_dbug; + +--source include/restart_mysqld.inc + +SET @old_global_dbug = @@GLOBAL.debug_dbug; +SET @old_dbug = @@SESSION.debug_dbug; +SET GLOBAL debug_dbug='+d,fts_instrument_sync,fts_instrument_sync_interrupted'; +INSERT INTO t1(title) VALUES('database'); +SET GLOBAL debug_dbug = @old_global_dbug; + +SET debug_dbug = '+d,fts_instrument_sync_debug'; +INSERT INTO t1(title) VALUES('good'); +SET debug_dbug = @old_dbug; + +SET GLOBAL innodb_ft_aux_table="test/t1"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SET GLOBAL innodb_ft_aux_table=default; + +SELECT * FROM t1 WHERE MATCH(title) AGAINST ('mysql database good'); + +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/sync_block.test b/mysql-test/suite/innodb_fts/t/sync_block.test new file mode 100644 index 00000000000..895d2ba8a59 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/sync_block.test @@ -0,0 +1,124 @@ +# +# BUG#22516559 MYSQL INSTANCE STALLS WHEN SYNCING FTS INDEX +# + +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_log_bin.inc +--source include/count_sessions.inc + +SET @old_log_output = @@global.log_output; +SET @old_slow_query_log = @@global.slow_query_log; +SET @old_general_log = @@global.general_log; +SET @old_long_query_time = @@global.long_query_time; +SET @old_debug = @@global.debug_dbug; + +SET GLOBAL log_output = 'TABLE'; +SET GLOBAL general_log = 1; +SET GLOBAL slow_query_log = 1; +SET GLOBAL long_query_time = 1; + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +connection default; + +--echo # Case 1: Sync blocks DML(insert) on the same table. +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +connection con1; + +SET GLOBAL debug_dbug='+d,fts_instrument_sync_debug,fts_instrument_sync_sleep'; + +SET DEBUG_SYNC= 'fts_sync_begin SIGNAL begin WAIT_FOR continue'; + +send INSERT INTO t1(title) VALUES('mysql database'); + +connection con2; + +SET DEBUG_SYNC= 'now WAIT_FOR begin'; + +send SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); + +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +connection con1; +--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +--reap + +connection con2; +--echo /* conneciton con2 */ SELECT * FROM t1 WHERE MATCH(title) AGAINST('mysql database'); +--reap + +connection default; +-- echo # make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +-- echo # slow log results should only contain INSERT INTO t1. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; + +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; + +DROP TABLE t1; + +--echo # Case 2: Sync blocks DML(insert) on other tables. +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT(title) +) ENGINE = InnoDB; + +CREATE TABLE t2(id INT); + +connection con1; + +SET GLOBAL debug_dbug='+d,fts_instrument_sync_request,fts_instrument_sync_sleep'; + +SET DEBUG_SYNC= 'fts_instrument_sync_request SIGNAL begin WAIT_FOR continue'; + +send INSERT INTO t1(title) VALUES('mysql database'); + +connection con2; + +SET DEBUG_SYNC= 'now WAIT_FOR begin'; + +send INSERT INTO t2 VALUES(1); + +connection default; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +connection con1; +--echo /* connection con1 */ INSERT INTO t1(title) VALUES('mysql database'); +--reap + +connection con2; +--echo /* conneciton con2 */ INSERT INTO t2 VALUES(1); +--reap + +connection default; +SET DEBUG_SYNC = 'RESET'; +-- echo # make con1 & con2 show up in mysql.slow_log +SELECT SLEEP(2); +-- echo # slow log results should be empty here. +SELECT sql_text FROM mysql.slow_log WHERE query_time >= '00:00:02'; + +SET GLOBAL debug_dbug = @old_debug; +TRUNCATE TABLE mysql.slow_log; + +DROP TABLE t1,t2; + +disconnect con1; +disconnect con2; + +--source include/wait_until_count_sessions.inc + +-- echo # Restore slow log settings. +SET GLOBAL log_output = @old_log_output; +SET GLOBAL general_log = @old_general_log; +SET GLOBAL slow_query_log = @old_slow_query_log; +SET GLOBAL long_query_time = @old_long_query_time; |