From e52315a4a2c7e752e786f9fcf63b0b5685b0d474 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Mon, 30 Jul 2018 18:06:30 +0300 Subject: MDEV-16855 Fix fts_sync_synchronization in InnoDB MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This is a backport of the following fix from MySQL 5.7.23. Some code refactoring has been omitted, and the test case has been adapted to MariaDB. commit 7a689acaa65e9d602575f7aa53fe36a64a07460f Author: Krzysztof KapuĊ›cik Date: Tue Mar 13 12:34:03 2018 +0100 Bug#27082268 Invalid FTS sync synchronization The fix closes two issues: Bug #27082268 - INNODB: FAILING ASSERTION: SYM_NODE->TABLE != NULL DURING FTS SYNC Bug #27095935 - DEADLOCK BETWEEN FTS_DROP_INDEX AND FTS_OPTIMIZE_SYNC_TABLE Both issues were related to a FTS cache sync being done during operations that perfomed DDL actions on internal FTS tables (ALTER TABLE, TRUNCATE). In some cases the FTS tables and/or internal cache structures could get removed while still being used to perform FTS synchronization leading to crashes. In other the sync operations could not get finishes as it was waiting for dict lock which was taken by thread waiting for the background sync to be finished. The changes done includes: - Stopping background operations during ALTER TABLE and TRUNCATE. - Removal of unused code in FTS. - Cleanup of FTS sync related code to make it more readable and easier to maintain. RB#18262 --- mysql-test/suite/innodb_fts/r/sync_ddl.result | 117 +++++++++++++++++ mysql-test/suite/innodb_fts/t/sync_ddl.test | 177 ++++++++++++++++++++++++++ 2 files changed, 294 insertions(+) create mode 100644 mysql-test/suite/innodb_fts/r/sync_ddl.result create mode 100644 mysql-test/suite/innodb_fts/t/sync_ddl.test (limited to 'mysql-test/suite/innodb_fts') diff --git a/mysql-test/suite/innodb_fts/r/sync_ddl.result b/mysql-test/suite/innodb_fts/r/sync_ddl.result new file mode 100644 index 00000000000..1e98594b28e --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/sync_ddl.result @@ -0,0 +1,117 @@ +CREATE TABLE t1 ( +id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, +value VARCHAR(1024) +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx1 ON t1(value); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SET @save_debug = @@GLOBAL.debug_dbug; +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_sync_before_syncing,ib_trunc_sleep_before_fts_cache_clear'; +INSERT INTO t1 (value) VALUES +('By default or with the IN NATURAL LANGUAGE MODE modifier') +; +TRUNCATE TABLE t1; +DROP TABLE t1; +SET GLOBAL debug_dbug = @save_debug; +CREATE TABLE t1 ( +id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, +value VARCHAR(1024) +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx1 ON t1(value); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_write_words_before_select_index,ib_trunc_sleep_before_fts_cache_clear'; +INSERT INTO t1 (value) VALUES +('By default or with the IN NATURAL LANGUAGE MODE modifier'), +('performs a natural language search for a string'), +('collection is a set of one or more columns included'), +('returns a relevance value; that is, a similarity measure'), +('and the text in that row in the columns named in'), +('By default, the search is performed in case-insensitive'), +('sensitive full-text search, use a binary collation '), +('example, a column that uses the latin1 character'), +('collation of latin1_bin to make it case sensitive') +; +TRUNCATE TABLE t1; +DROP TABLE t1; +SET GLOBAL debug_dbug = @save_debug; +CREATE TABLE t1 ( +value VARCHAR(1024) +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx1 ON t1(value); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; +INSERT INTO t1 (value) VALUES +('By default or with the IN NATURAL LANGUAGE MODE modifier'), +('performs a natural language search for a string'), +('collection is a set of one or more columns included'), +('returns a relevance value; that is, a similarity measure'), +('and the text in that row in the columns named in'), +('By default, the search is performed in case-insensitive'), +('sensitive full-text search, use a binary collation '), +('example, a column that uses the latin1 character'), +('collation of latin1_bin to make it case sensitive') +; +DROP INDEX idx1 ON t1; +DROP TABLE t1; +SET GLOBAL debug_dbug = @save_debug; +CREATE TABLE t1 ( +value VARCHAR(1024) +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx1 ON t1(value); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; +INSERT INTO t1 (value) VALUES +('By default or with the IN NATURAL LANGUAGE MODE modifier'), +('performs a natural language search for a string'), +('collection is a set of one or more columns included'), +('returns a relevance value; that is, a similarity measure'), +('and the text in that row in the columns named in'), +('By default, the search is performed in case-insensitive'), +('sensitive full-text search, use a binary collation '), +('example, a column that uses the latin1 character'), +('collation of latin1_bin to make it case sensitive') +; +ALTER TABLE t1 +DROP INDEX idx1, +ALGORITHM=INPLACE; +DROP TABLE t1; +SET GLOBAL debug_dbug = @save_debug; +CREATE TABLE t1 ( +value VARCHAR(1024) +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx1 ON t1(value); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; +INSERT INTO t1 (value) VALUES +('example, a column that uses the latin1 character'), +('collation of latin1_bin to make it case sensitive') +; +ALTER TABLE t1 +DROP INDEX idx1, +ALGORITHM=COPY; +DROP TABLE t1; +SET GLOBAL debug_dbug = @save_debug; +CREATE TABLE t1 ( +id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +value VARCHAR(1024) +) ENGINE=InnoDB; +CREATE FULLTEXT INDEX idx1 ON t1(value); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; +INSERT INTO t1 (value) VALUES +('example, a column that uses the latin1 character'), +('collation of latin1_bin to make it case sensitive') +; +ALTER TABLE t1 +DROP COLUMN id1, +ADD COLUMN id2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +DROP INDEX idx1, +ADD FULLTEXT INDEX idx2(value), +ALGORITHM=INPLACE; +DROP TABLE t1; +SET GLOBAL debug_dbug = @save_debug; diff --git a/mysql-test/suite/innodb_fts/t/sync_ddl.test b/mysql-test/suite/innodb_fts/t/sync_ddl.test new file mode 100644 index 00000000000..2950297d5bb --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/sync_ddl.test @@ -0,0 +1,177 @@ +# +# BUG#27082268 FTS synchronization issues +# + +--source include/have_innodb.inc +--source include/have_debug.inc + +#-------------------------------------- +# Check FTS_sync vs TRUNCATE (1) +#-------------------------------------- + +CREATE TABLE t1 ( + id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, + value VARCHAR(1024) + ) ENGINE=InnoDB; + +CREATE FULLTEXT INDEX idx1 ON t1(value); + +SET @save_debug = @@GLOBAL.debug_dbug; +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_sync_before_syncing,ib_trunc_sleep_before_fts_cache_clear'; + +INSERT INTO t1 (value) VALUES + ('By default or with the IN NATURAL LANGUAGE MODE modifier') + ; + +TRUNCATE TABLE t1; + +DROP TABLE t1; + +SET GLOBAL debug_dbug = @save_debug; + +#-------------------------------------- +# Check FTS sync vs DROP INDEX (2) +#-------------------------------------- + +CREATE TABLE t1 ( + id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, + value VARCHAR(1024) + ) ENGINE=InnoDB; + +CREATE FULLTEXT INDEX idx1 ON t1(value); + +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_write_words_before_select_index,ib_trunc_sleep_before_fts_cache_clear'; + +INSERT INTO t1 (value) VALUES + ('By default or with the IN NATURAL LANGUAGE MODE modifier'), + ('performs a natural language search for a string'), + ('collection is a set of one or more columns included'), + ('returns a relevance value; that is, a similarity measure'), + ('and the text in that row in the columns named in'), + ('By default, the search is performed in case-insensitive'), + ('sensitive full-text search, use a binary collation '), + ('example, a column that uses the latin1 character'), + ('collation of latin1_bin to make it case sensitive') + ; + +TRUNCATE TABLE t1; + +DROP TABLE t1; + +SET GLOBAL debug_dbug = @save_debug; + +#-------------------------------------- +# Check FTS sync vs DROP INDEX +#-------------------------------------- + +CREATE TABLE t1 ( + value VARCHAR(1024) + ) ENGINE=InnoDB; + +CREATE FULLTEXT INDEX idx1 ON t1(value); + +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; + +INSERT INTO t1 (value) VALUES + ('By default or with the IN NATURAL LANGUAGE MODE modifier'), + ('performs a natural language search for a string'), + ('collection is a set of one or more columns included'), + ('returns a relevance value; that is, a similarity measure'), + ('and the text in that row in the columns named in'), + ('By default, the search is performed in case-insensitive'), + ('sensitive full-text search, use a binary collation '), + ('example, a column that uses the latin1 character'), + ('collation of latin1_bin to make it case sensitive') + ; + +DROP INDEX idx1 ON t1; + +DROP TABLE t1; + +SET GLOBAL debug_dbug = @save_debug; + +#-------------------------------------- +# Check FTS sync vs ALTER TABLE DROP INDEX (INPLACE) +#-------------------------------------- + +CREATE TABLE t1 ( + value VARCHAR(1024) + ) ENGINE=InnoDB; + +CREATE FULLTEXT INDEX idx1 ON t1(value); + +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; + +INSERT INTO t1 (value) VALUES + ('By default or with the IN NATURAL LANGUAGE MODE modifier'), + ('performs a natural language search for a string'), + ('collection is a set of one or more columns included'), + ('returns a relevance value; that is, a similarity measure'), + ('and the text in that row in the columns named in'), + ('By default, the search is performed in case-insensitive'), + ('sensitive full-text search, use a binary collation '), + ('example, a column that uses the latin1 character'), + ('collation of latin1_bin to make it case sensitive') + ; + +ALTER TABLE t1 + DROP INDEX idx1, + ALGORITHM=INPLACE; + +DROP TABLE t1; + +SET GLOBAL debug_dbug = @save_debug; + +#-------------------------------------- +# Check FTS sync vs ALTER TABLE DROP INDEX (COPY) +#-------------------------------------- + +CREATE TABLE t1 ( + value VARCHAR(1024) + ) ENGINE=InnoDB; + +CREATE FULLTEXT INDEX idx1 ON t1(value); + +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; + +INSERT INTO t1 (value) VALUES + ('example, a column that uses the latin1 character'), + ('collation of latin1_bin to make it case sensitive') + ; + +ALTER TABLE t1 + DROP INDEX idx1, + ALGORITHM=COPY; + +DROP TABLE t1; + +SET GLOBAL debug_dbug = @save_debug; + +#-------------------------------------- +# Check FTS sync vs ALTER TABLE (INPLACE, new cluster) +#-------------------------------------- + +CREATE TABLE t1 ( + id1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + value VARCHAR(1024) + ) ENGINE=InnoDB; + +CREATE FULLTEXT INDEX idx1 ON t1(value); + +SET GLOBAL debug_dbug = '+d,fts_instrument_sync_request,fts_instrument_msg_sync_sleep'; + +INSERT INTO t1 (value) VALUES + ('example, a column that uses the latin1 character'), + ('collation of latin1_bin to make it case sensitive') + ; + +ALTER TABLE t1 + DROP COLUMN id1, + ADD COLUMN id2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + DROP INDEX idx1, + ADD FULLTEXT INDEX idx2(value), + ALGORITHM=INPLACE; + +DROP TABLE t1; + +SET GLOBAL debug_dbug = @save_debug; -- cgit v1.2.1 From a7f84f09bfec22e26b3bd54505a9c8e1d0faff40 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 31 Jul 2018 16:12:38 +0300 Subject: MDEV-16865 InnoDB fts_query() ignores KILL The functions fts_ast_visit() and fts_query() inside InnoDB FULLTEXT INDEX query processing are not checking for THD::killed (trx_is_interrupted()), like anything that potentially takes a long time should do. This is a port of the following change from MySQL 5.7.23, with a completely rewritten test case. commit c58c6f8f66ddd0357ecd0c99646aa6bf1dae49c8 Author: Aakanksha Verma Date: Fri May 4 15:53:13 2018 +0530 Bug #27155294 MAX_EXECUTION_TIME NOT INTERUPTED WITH FULLTEXT SEARCH USING MECAB --- .../suite/innodb_fts/r/fts_kill_query.result | 6 +++++ mysql-test/suite/innodb_fts/t/fts_kill_query.test | 30 ++++++++++++++++++++++ 2 files changed, 36 insertions(+) create mode 100644 mysql-test/suite/innodb_fts/r/fts_kill_query.result create mode 100644 mysql-test/suite/innodb_fts/t/fts_kill_query.test (limited to 'mysql-test/suite/innodb_fts') diff --git a/mysql-test/suite/innodb_fts/r/fts_kill_query.result b/mysql-test/suite/innodb_fts/r/fts_kill_query.result new file mode 100644 index 00000000000..45623f96ab0 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/fts_kill_query.result @@ -0,0 +1,6 @@ +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); +KILL QUERY @id; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/fts_kill_query.test b/mysql-test/suite/innodb_fts/t/fts_kill_query.test new file mode 100644 index 00000000000..3dda29a3876 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/fts_kill_query.test @@ -0,0 +1,30 @@ +--source include/have_innodb.inc + +CREATE TABLE t1 (a VARCHAR(7), b text, FULLTEXT KEY idx (a,b)) ENGINE=InnoDB; + +--disable_query_log +BEGIN; +let $n=1000; +while ($n) { +INSERT INTO t1 VALUES('foo bar','boo far'); +dec $n; +} +--enable_query_log +COMMIT; + +let $id = `SELECT CONNECTION_ID()`; +send SELECT COUNT(*) FROM t1 +WHERE MATCH (a,b) AGAINST ('foo bar' IN BOOLEAN MODE); + +connect (con1,localhost,root,,); +let $ignore= `SELECT @id := $ID`; +KILL QUERY @id; +disconnect con1; + +connection default; +# The following would return a result set if the KILL was not fast enough. +--disable_result_log +--error 0,ER_QUERY_INTERRUPTED,HA_ERR_ABORTED_BY_USER +reap; +--enable_result_log +DROP TABLE t1; -- cgit v1.2.1 From a39337415d173f791b2def2bc63818134319f669 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 29 Jun 2018 11:01:55 +0200 Subject: MDEV-14900 Upstream 10.3 debian patches applied (at least partially): * armhf_mroonga_storage_fail.patch (unaligned write) * mysqld_multi.server_lsb-header.patch (add LSB header) * fix-spelling-errors.patch (tabxml.cpp) * hurd_socket.patch (in Platform.pm) * remove-systemd-obsolete-target.patch --- mysql-test/suite/innodb_fts/t/innodb_fts_multiple_index.test | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/suite/innodb_fts') diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_multiple_index.test b/mysql-test/suite/innodb_fts/t/innodb_fts_multiple_index.test index 012881173e0..c8293655d1b 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_multiple_index.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_multiple_index.test @@ -25,7 +25,7 @@ ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a); ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b); EVAL SHOW CREATE TABLE t1; -# check mutiple index with transaction +# check multiple index with transaction START TRANSACTION; # Insert rows INSERT INTO t1 (a,b) VALUES -- cgit v1.2.1