From c562ccf796c085211461386510ea5f7a8137cb96 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 15 Dec 2022 11:14:23 +0200 Subject: MDEV-30233 DROP DATABASE test fails: Directory not empty Some tests drop the default mtr database "test". This may fail due to the directory not being empty. InnoDB may not delete all tables immediately, due to the "background drop table queue" or its replacement in commit 1bd681c8b3c5213ce1f7976940a7dc38b48a0d39 (the purge of history would clean up after a DDL operation during which the server was killed). Let us try to avoid "drop database test" whenever it is easily possible. Where it is not, SET GLOBAL innodb_max_purge_lag_wait=0 will ensure that the replacement of the "background drop table queue" will have completed its job. --- mysql-test/main/cte_recursive.result | 5 +++-- mysql-test/main/cte_recursive.test | 5 +++-- mysql-test/main/information_schema.result | 5 +++-- mysql-test/main/information_schema.test | 5 +++-- mysql-test/main/mysqldump.result | 1 + mysql-test/main/mysqldump.test | 2 ++ mysql-test/main/view.result | 5 +++-- mysql-test/main/view.test | 5 +++-- mysql-test/suite/funcs_2/r/innodb_charset.result | 1 + mysql-test/suite/funcs_2/t/innodb_charset.test | 4 ++++ mysql-test/suite/innodb/r/information_schema_grants.result | 1 + mysql-test/suite/innodb/t/information_schema_grants.test | 2 ++ mysql-test/suite/innodb/t/innodb-mdev7046.test | 2 ++ 13 files changed, 31 insertions(+), 12 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 7f535fc3125..c4b66794e3d 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -4235,7 +4235,9 @@ drop table t1; # # MDEV-24019: query with recursive CTE when no default database is set # -drop database test; +create database dummy; +use dummy; +drop database dummy; with recursive a as (select 1 from dual union select * from a as r) select * from a; @@ -4274,7 +4276,6 @@ a 1 deallocate prepare stmt; drop database db1; -create database test; use test; # # MDEV-23406: query with mutually recursive CTEs when big_tables=1 diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index f5babc6cb65..270a5023d31 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2729,7 +2729,9 @@ drop table t1; --echo # MDEV-24019: query with recursive CTE when no default database is set --echo # -drop database test; +create database dummy; +use dummy; +drop database dummy; let $q= with recursive a as @@ -2757,7 +2759,6 @@ deallocate prepare stmt; drop database db1; -create database test; use test; --echo # diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index fba61b354f3..82d34002617 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -2217,8 +2217,9 @@ Warning 1931 Query execution was interrupted. The query examined at least ### ro # m_status == DA_OK_BULK' failed in Diagnostics_area::message() # call mtr.add_suppression("Sort aborted.*"); -DROP DATABASE test; -CREATE DATABASE test; +create database dummy; +use dummy; +drop database dummy; USE test; CREATE VIEW v AS SELECT table_schema AS object_schema, table_name AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema; SELECT * FROM v LIMIT ROWS EXAMINED 9; diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test index 2fac02d2fe0..469be44ef94 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -1940,8 +1940,9 @@ SELECT * FROM INFORMATION_SCHEMA.`COLUMNS` LIMIT ROWS EXAMINED 10; call mtr.add_suppression("Sort aborted.*"); -DROP DATABASE test; -CREATE DATABASE test; +create database dummy; +use dummy; +drop database dummy; USE test; CREATE VIEW v AS SELECT table_schema AS object_schema, table_name AS object_name, table_type AS object_type FROM information_schema.tables ORDER BY object_schema; diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index afaafb4907c..a517a5e3c48 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -4469,6 +4469,7 @@ Db Name Definer Time zone Type Execute at Interval value Interval field Starts E # MDEV-13336: add ignore-database option # with --all-databases # +SET GLOBAL innodb_max_purge_lag_wait=0; DROP DATABASE test; SHOW DATABASES LIKE 'test'; Database (test) diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index 89155a435b1..a099727ba38 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -1921,6 +1921,8 @@ SHOW EVENTS; --echo # with --all-databases --echo # --exec $MYSQL_DUMP --default-character-set=utf8mb4 --ignore-database test --all-databases > $MYSQLTEST_VARDIR/tmp/mysqldump-MDEV-13336.sql +# Starting with MariaDB 10.6, ensure that DDL recovery will have completed. +SET GLOBAL innodb_max_purge_lag_wait=0; DROP DATABASE test; --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqldump-MDEV-13336.sql SHOW DATABASES LIKE 'test'; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 7354e1dbb91..b042e27997f 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6723,7 +6723,9 @@ DROP TABLE t1; # # MDEV-24314: create view with derived table without default database # -drop database test; +create database dummy; +use dummy; +drop database dummy; create database db1; create table db1.t1 (a int); insert into db1.t1 values (3),(7),(1); @@ -6753,7 +6755,6 @@ a drop view db1.v1; drop table db1.t1; drop database db1; -create database test; use test; # # MDEV-16940: update of multi-table view returning error used in SP diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 987e96fc79d..11355c211ca 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6437,7 +6437,9 @@ DROP TABLE t1; --echo # MDEV-24314: create view with derived table without default database --echo # -drop database test; +create database dummy; +use dummy; +drop database dummy; create database db1; create table db1.t1 (a int); @@ -6460,7 +6462,6 @@ drop view db1.v1; drop table db1.t1; drop database db1; -create database test; use test; --echo # diff --git a/mysql-test/suite/funcs_2/r/innodb_charset.result b/mysql-test/suite/funcs_2/r/innodb_charset.result index 1ce5972eccc..6333e8739e3 100644 --- a/mysql-test/suite/funcs_2/r/innodb_charset.result +++ b/mysql-test/suite/funcs_2/r/innodb_charset.result @@ -1,3 +1,4 @@ +SET GLOBAL innodb_max_purge_lag_wait=0; set @save_character_set_database= @@character_set_database; DROP TABLE IF EXISTS test.t1; SET NAMES armscii8; diff --git a/mysql-test/suite/funcs_2/t/innodb_charset.test b/mysql-test/suite/funcs_2/t/innodb_charset.test index da4dea44ad7..631c20352d4 100644 --- a/mysql-test/suite/funcs_2/t/innodb_charset.test +++ b/mysql-test/suite/funcs_2/t/innodb_charset.test @@ -9,6 +9,10 @@ --source include/no_valgrind_without_big.inc --source include/have_innodb.inc +# Starting with MariaDB 10.6, ensure that DDL recovery will have completed +# before DROP DATABASE test. +SET GLOBAL innodb_max_purge_lag_wait=0; + let $engine_type= InnoDB; --source suite/funcs_2/charset/charset_master.test diff --git a/mysql-test/suite/innodb/r/information_schema_grants.result b/mysql-test/suite/innodb/r/information_schema_grants.result index 04550bfa114..b2bd8b657a6 100644 --- a/mysql-test/suite/innodb/r/information_schema_grants.result +++ b/mysql-test/suite/innodb/r/information_schema_grants.result @@ -299,6 +299,7 @@ select count(*) > -1 from d_trx; count(*) > -1 1 connection default; +SET GLOBAL innodb_max_purge_lag_wait=0; drop database test; create database test; drop user select_only@localhost; diff --git a/mysql-test/suite/innodb/t/information_schema_grants.test b/mysql-test/suite/innodb/t/information_schema_grants.test index 34565f76352..32eaefdacb3 100644 --- a/mysql-test/suite/innodb/t/information_schema_grants.test +++ b/mysql-test/suite/innodb/t/information_schema_grants.test @@ -306,6 +306,8 @@ select count(*) > -1 from i_trx; select count(*) > -1 from d_trx; connection default; +# Starting with MariaDB 10.6, ensure that DDL recovery will have completed. +SET GLOBAL innodb_max_purge_lag_wait=0; drop database test; create database test; drop user select_only@localhost; diff --git a/mysql-test/suite/innodb/t/innodb-mdev7046.test b/mysql-test/suite/innodb/t/innodb-mdev7046.test index 27c140689c1..52b8e83695a 100644 --- a/mysql-test/suite/innodb/t/innodb-mdev7046.test +++ b/mysql-test/suite/innodb/t/innodb-mdev7046.test @@ -31,6 +31,8 @@ RENAME TABLE t1 TO `t2_new..............................................end`; show warnings; drop table t1; +# Starting with MariaDB 10.6, ensure that DDL recovery will have completed. +SET GLOBAL innodb_max_purge_lag_wait=0; drop database test; create database test; use test; -- cgit v1.2.1 From 9f8fc983d5fe23698f8fd6f7457568cd689ed0e2 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 16 Dec 2022 09:59:09 +0200 Subject: MDEV-30242 MTR fails to report stack traces of all threads by default An unfortunate change to the default behavior of the handling of core dumps was implemented in commit e9be5428a27eaaccf142f2bd53f4d30e8e368484 by making MTR_PRINT_CORE=small the default value, that is, to only display the stack trace of one thread in crash reports. Many if not most failures that occur in regression tests are sporadic and involve race conditions or deadlocks. To be able to analyze such failures, having the stack traces of all active threads is a must, because CI environments typically do not save any core dumps. While the environment variable MTR_PRINT_CORE could be set in CI environments to compensate for the unfortunate change, it is better to revert to the old default (dumping all threads) so that no explicit action will be required from maintainers of independent CI systems. In that case, if something fails once in a blue moon, we can have some hope of diagnosing it based on the output. We fix this regression by defaulting the unset environment variable MTR_PRINT_CORE to "medium". --- mysql-test/lib/My/CoreDump.pm | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/My/CoreDump.pm b/mysql-test/lib/My/CoreDump.pm index 298cf9ef877..05b6edf1385 100644 --- a/mysql-test/lib/My/CoreDump.pm +++ b/mysql-test/lib/My/CoreDump.pm @@ -108,7 +108,7 @@ for my $f (keys %print_formats) register_opt('print-core|C', ':s', "Print core dump format: ". $print_formats. " (for not printing cores). ". - "Defaults to value of MTR_PRINT_CORE or 'short'"); + "Defaults to value of MTR_PRINT_CORE or 'medium'"); if (!IS_WINDOWS) { register_opt('print-method', '=s', @@ -134,7 +134,7 @@ sub env_or_default($$) { } sub pre_setup() { - $config{print_core}= env_or_default('short', 'MTR_PRINT_CORE') + $config{print_core}= env_or_default('medium', 'MTR_PRINT_CORE') if not defined $config{print_core}; $config{print_method}= (IS_WINDOWS) ? 'cdb' : 'auto' if not defined $config{print_method}; -- cgit v1.2.1 From 0ca3aaa75fe084daabacfed0924598621dffec15 Mon Sep 17 00:00:00 2001 From: Lena Startseva Date: Thu, 15 Dec 2022 16:12:49 +0000 Subject: MDEV-27691: make working view-protocol Excluded one case from view-protocol in gis.test --- mysql-test/main/gis.test | 3 +++ 1 file changed, 3 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test index 716fab9bfeb..72b4661ad24 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -3093,12 +3093,15 @@ SELECT IS_USED_LOCK(POINT(1,1)); --echo # --echo # MDEV-26161 crash in Gis_point::calculate_haversine --echo # +#enable after fix MDEV-30229 +--disable_view_protocol --error ER_CANT_CREATE_GEOMETRY_OBJECT select st_distance_sphere(x'01030000000400000004000000000000', multipoint(point(124,204)), 10); --error ER_CANT_CREATE_GEOMETRY_OBJECT select st_distance_sphere(x'010300000004000000040000', multipoint(point(124,204)), 10); --error ER_CANT_CREATE_GEOMETRY_OBJECT select st_distance_sphere(x'010300000001000000040000', multipoint(point(124,204)), 10); +--enable_view_protocol --echo # --echo # End of 10.3 tests -- cgit v1.2.1 From 3ddc00dc3bf29f7cf326268265e47fda61e6a83e Mon Sep 17 00:00:00 2001 From: Vlad Lesin Date: Tue, 13 Dec 2022 16:06:13 +0300 Subject: MDEV-30225 RR isolation violation with locking unique search Before the fix next-key lock was requested only if a record was delete-marked for locking unique search in RR isolation level. There can be several delete-marked records for the same unique key, that's why InnoDB scans the records until eighter non-delete-marked record is reached or all delete-marked records with the same unique key are scanned. For range scan next-key locks are used for RR to protect scanned range from inserting new records by other transactions. And this is the reason of why next-key locks are used for delete-marked records for unique searches. If a record is not delete-marked, the requested lock type was "not-gap". When a record is not delete-marked during lock request by trx 1, and some other transaction holds conflicting lock, trx 1 creates waiting not-gap lock on the record and suspends. During trx 1 suspending the record can be delete-marked. And when the lock is granted on conflicting transaction commit or rollback, its type is still "not-gap". So we have "not-gap" lock on delete-marked record for RR. And this let some other transaction to insert some record with the same unique key when trx 1 is not committed, what can cause isolation level violation. The fix is to set next-key locks for both delete-marked and non-delete-marked records for unique search in RR. --- .../suite/innodb/r/cursor-restore-locking.result | 1 + .../suite/innodb/r/insert-before-delete.result | 35 +++++++++++ mysql-test/suite/innodb/r/monitor.result | 2 +- .../suite/innodb/t/cursor-restore-locking.test | 4 ++ .../suite/innodb/t/insert-before-delete.test | 72 ++++++++++++++++++++++ mysql-test/suite/innodb/t/monitor.test | 3 + 6 files changed, 116 insertions(+), 1 deletion(-) create mode 100644 mysql-test/suite/innodb/r/insert-before-delete.result create mode 100644 mysql-test/suite/innodb/t/insert-before-delete.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/cursor-restore-locking.result b/mysql-test/suite/innodb/r/cursor-restore-locking.result index bc1127f57b3..da95c57ce64 100644 --- a/mysql-test/suite/innodb/r/cursor-restore-locking.result +++ b/mysql-test/suite/innodb/r/cursor-restore-locking.result @@ -11,6 +11,7 @@ SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL first_ins_locked'; SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL first_ins_row_inserted WAIT_FOR first_ins_cont'; INSERT INTO t VALUES(10, 20); connect con_del_2,localhost,root,,; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET DEBUG_SYNC = 'now WAIT_FOR first_ins_locked'; SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL second_del_locked'; DELETE FROM t WHERE b = 20; diff --git a/mysql-test/suite/innodb/r/insert-before-delete.result b/mysql-test/suite/innodb/r/insert-before-delete.result new file mode 100644 index 00000000000..11e8fcea9d6 --- /dev/null +++ b/mysql-test/suite/innodb/r/insert-before-delete.result @@ -0,0 +1,35 @@ +connect pause_purge,localhost,root; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; +CREATE TABLE t (pk int PRIMARY KEY, sk INT UNIQUE) ENGINE=InnoDB; +INSERT INTO t VALUES (10, 100); +connect con1,localhost,root; +BEGIN; +SELECT * FROM t WHERE sk = 100 FOR UPDATE; +pk sk +10 100 +connect con2,localhost,root; +SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL insert_wait_started"; +INSERT INTO t VALUES (5, 100) # trx 1; +connect con3,localhost,root; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET DEBUG_SYNC="now WAIT_FOR insert_wait_started"; +SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL delete_started_waiting"; +DELETE FROM t WHERE sk = 100 # trx 2; +connection con1; +SET DEBUG_SYNC="now WAIT_FOR delete_started_waiting"; +DELETE FROM t WHERE sk=100; +COMMIT; +disconnect con1; +connection con2; +disconnect con2; +connection con3; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +disconnect con3; +connection default; +SELECT * FROM t; +pk sk +5 100 +disconnect pause_purge; +SET DEBUG_SYNC="RESET"; +DROP TABLE t; diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result index b65bba276f0..9143ea33a02 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -673,7 +673,7 @@ SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); SELECT @end - @start; @end - @start -0 +1 DROP TABLE t1; SET GLOBAL innodb_monitor_enable=default; SET GLOBAL innodb_monitor_disable=default; diff --git a/mysql-test/suite/innodb/t/cursor-restore-locking.test b/mysql-test/suite/innodb/t/cursor-restore-locking.test index d032d8a8def..f3a60f25568 100644 --- a/mysql-test/suite/innodb/t/cursor-restore-locking.test +++ b/mysql-test/suite/innodb/t/cursor-restore-locking.test @@ -27,6 +27,10 @@ SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL first_ins_row_inserted WAIT_FOR fir --send INSERT INTO t VALUES(10, 20) --connect(con_del_2,localhost,root,,) +# After MDEV-30225 is fixed, the following DELETE creates next-key lock for +# unqique search for RR, and the above INSERT kills it as deadlock victim. +# But it still requests not-gap lock for RC. +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET DEBUG_SYNC = 'now WAIT_FOR first_ins_locked'; SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL second_del_locked'; ############################################################################### diff --git a/mysql-test/suite/innodb/t/insert-before-delete.test b/mysql-test/suite/innodb/t/insert-before-delete.test new file mode 100644 index 00000000000..17a885c7bd8 --- /dev/null +++ b/mysql-test/suite/innodb/t/insert-before-delete.test @@ -0,0 +1,72 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/count_sessions.inc + +--connect (pause_purge,localhost,root) +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connection default +CREATE TABLE t (pk int PRIMARY KEY, sk INT UNIQUE) ENGINE=InnoDB; +INSERT INTO t VALUES (10, 100); + +--connect (con1,localhost,root) +BEGIN; # trx 0 +SELECT * FROM t WHERE sk = 100 FOR UPDATE; + +--connect (con2,localhost,root) +SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL insert_wait_started"; +# trx 1 is locked on try to read the record in secondary index during duplicates +# check. It's the first in waiting queue, that's why it will be woken up firstly +# when trx 0 commits. +--send INSERT INTO t VALUES (5, 100) # trx 1 + +--connect (con3,localhost,root) +# MDEV-30225 is fixed only for RR +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SET DEBUG_SYNC="now WAIT_FOR insert_wait_started"; +SET DEBUG_SYNC="lock_wait_suspend_thread_enter SIGNAL delete_started_waiting"; +# trx 2 can delete (5, 100) on master, but not on slave, as on slave trx 1 +# can insert (5, 100) after trx 2 positioned it's cursor. Trx 2 lock is placed +# in waiting queue after trx 1 lock, but its persistent cursor position was +# stored on (100, 10) record in secondary index before suspending. After trx 1 +# is committed, trx 2 will restore persistent cursor position on (100, 10). As +# (100, 5) secondary index record was inserted before (100, 10) in logical +# order, and (100, 10) record is delete-marked, trx 2 just continues scanning. +# +# Note. There can be several records with the same key in unique secondary +# index, but only one of them must be non-delete-marked. That's why when we do +# point query, cursor position is set in the first record in logical order, and +# then records are iterated until either non-delete-marked record is found or +# all records with the same unique fields are iterated. +--send DELETE FROM t WHERE sk = 100 # trx 2 + +--connection con1 +SET DEBUG_SYNC="now WAIT_FOR delete_started_waiting"; +DELETE FROM t WHERE sk=100; # trx 0 +COMMIT; +--disconnect con1 + +--connection con2 +--reap +--disconnect con2 + +--connection con3 +# If the bug is fixed, deadlock error will be there, as trx 2 owns +# next-key lock waiting for trx 1, and trx 1 requests +# insert-intention lock, conflicting with trx 2 next-key lock. +--error ER_LOCK_DEADLOCK +--reap +--disconnect con3 + +--connection default +# If the bug is not fixed, we will see the row inserted by trx 1 here. This can +# cause duplicate key error on slave, when some other trx tries in insert row +# with the same secondary key, as was inserted by trx 1, and not deleted by trx +# 2. +SELECT * FROM t; + +--disconnect pause_purge +SET DEBUG_SYNC="RESET"; +DROP TABLE t; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/monitor.test b/mysql-test/suite/innodb/t/monitor.test index 3535c9c85ad..f1cf4b0c39e 100644 --- a/mysql-test/suite/innodb/t/monitor.test +++ b/mysql-test/suite/innodb/t/monitor.test @@ -435,6 +435,9 @@ INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d'); DELETE FROM t1 WHERE a = 9999 AND b='b'; COMMIT; +# After MDEV-30225 is fixed, the above DELETE creates next-key lock during +# secondary index unique search. That's why the result of the following must +# be 1. SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created'); SELECT @end - @start; -- cgit v1.2.1 From 7c5609fb647b7b013694a16acae7c5c5739b394b Mon Sep 17 00:00:00 2001 From: musvaage Date: Tue, 20 Dec 2022 17:41:24 -0600 Subject: typos --- mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test | 16 ++++++++-------- mysql-test/suite/innodb_fts/t/stopword.test | 16 ++++++++-------- mysql-test/suite/parts/t/partition_repair_myisam.test | 2 +- 3 files changed, 17 insertions(+), 17 deletions(-) (limited to 'mysql-test') 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 0f29d092541..de14deab328 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test @@ -39,7 +39,7 @@ select @@innodb_ft_user_stopword_table; --error 1231 set global innodb_ft_server_stopword_table = "not_defined"; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful @@ -319,11 +319,11 @@ INSERT INTO articles (title,body) VALUES # No records expeced for select SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; -# Define a correct formated server stopword table +# Define a correct formatted server stopword table create table server_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set global innodb_ft_server_stopword_table = "test/server_stopword"; @@ -422,7 +422,7 @@ INSERT INTO articles (title,body) VALUES # No records expeced for select SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; @@ -444,7 +444,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); # set user stopword list empty set session innodb_ft_user_stopword_table = default; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table server_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set global innodb_ft_server_stopword_table = "test/server_stopword"; @@ -580,7 +580,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOL --connection con1 SET SESSION innodb_ft_enable_stopword = 1; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; @@ -599,7 +599,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); select @@innodb_ft_user_stopword_table; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword_1(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword_1"; @@ -620,7 +620,7 @@ SET SESSION innodb_ft_enable_stopword = 1; SET SESSION innodb_ft_user_stopword_table=default; select @@innodb_ft_user_stopword_table; select @@innodb_ft_server_stopword_table; -# Define a correct formated server stopword table +# Define a correct formatted server stopword table create table server_stopword(value varchar(30)) engine = innodb; # The set operation should be successful SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; diff --git a/mysql-test/suite/innodb_fts/t/stopword.test b/mysql-test/suite/innodb_fts/t/stopword.test index 5105a6d2fec..ca01da80734 100644 --- a/mysql-test/suite/innodb_fts/t/stopword.test +++ b/mysql-test/suite/innodb_fts/t/stopword.test @@ -38,7 +38,7 @@ SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table; set global innodb_ft_server_stopword_table = "not_defined"; set global innodb_ft_server_stopword_table = NULL; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful @@ -311,11 +311,11 @@ INSERT INTO articles (title,body) VALUES # No records expeced for select SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; -# Define a correct formated server stopword table +# Define a correct formatted server stopword table create table server_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set global innodb_ft_server_stopword_table = "test/server_stopword"; @@ -411,7 +411,7 @@ INSERT INTO articles (title,body) VALUES # No records expeced for select SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; @@ -433,7 +433,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); # set user stopword list empty set session innodb_ft_user_stopword_table = default; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table server_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set global innodb_ft_server_stopword_table = "test/server_stopword"; @@ -572,7 +572,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOL --echo "In connection 1" --connection con1 SET SESSION innodb_ft_enable_stopword = 1; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; @@ -592,7 +592,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); select @@innodb_ft_user_stopword_table; -# Define a correct formated user stopword table +# Define a correct formatted user stopword table create table user_stopword_1(value varchar(30)) engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword_1"; @@ -614,7 +614,7 @@ SET SESSION innodb_ft_enable_stopword = 1; SET SESSION innodb_ft_user_stopword_table=default; select @@innodb_ft_user_stopword_table; select @@innodb_ft_server_stopword_table; -# Define a correct formated server stopword table +# Define a correct formatted server stopword table create table server_stopword(value varchar(30)) engine = innodb; # The set operation should be successful SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; diff --git a/mysql-test/suite/parts/t/partition_repair_myisam.test b/mysql-test/suite/parts/t/partition_repair_myisam.test index a21493ca03d..daa57b99211 100644 --- a/mysql-test/suite/parts/t/partition_repair_myisam.test +++ b/mysql-test/suite/parts/t/partition_repair_myisam.test @@ -150,7 +150,7 @@ while ($i) # 2 - after _mi_mark_file_changed (only marked index as opened) # 3 - after write_record (updated datafile + not closed/updated index) # 4 - after flush_cached_blocks (updated index/datafiles, not closed index) -# 5 - (Not used) after mi_state_info_write (fully uppdated/closed index file) +# 5 - (Not used) after mi_state_info_write (fully updated/closed index file) # (this was verified to be a harmless crash, since everything was written) # 6 - partly updated datafile (insert 6 small records, delete 5,3,1, # insert one larger record (2.5 X small) and break in gdb before it has -- cgit v1.2.1 From 68c437bad6b6a47ee534aac0411fae83947fbfbf Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Tue, 27 Dec 2022 00:02:01 +0300 Subject: MDEV-25004 restart_bindir option to restart server from different location Adds new parameter $restart_bindir for restart_mysqld.inc. Example: let $restart_bindir= /home/midenok/src/mariadb/10.3b/build; --source include/restart_mysqld.inc It is good to return back original server before check_mysqld will be run at the test end: let $restart_bindir=; --source include/restart_mysqld.inc --- mysql-test/include/start_mysqld.inc | 36 +++++++++++++++++++++++++++++++++--- mysql-test/mysql-test-run.pl | 13 ++++++++++--- 2 files changed, 43 insertions(+), 6 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/include/start_mysqld.inc b/mysql-test/include/start_mysqld.inc index e31f26aad8c..14a0b088fc0 100644 --- a/mysql-test/include/start_mysqld.inc +++ b/mysql-test/include/start_mysqld.inc @@ -1,13 +1,44 @@ # Include this script only after using shutdown_mysqld.inc # where $_expect_file_name was initialized. # Write file to make mysql-test-run.pl start up the server again + +# restart_noprint defines how much is printed to the .result file +# if 0 (default) then '# result' and restart_parameters are printed +# if 1 then print #result but not the content of restart_parameters +# if 2 then nothing is printed + +if (!$restart_noprint) +{ + --let $restart_noprint= 2 +} + +--let $restart_cmd= restart + +if ($restart_bindir) +{ + --let $restart_cmd= restart_bindir $restart_bindir +} + if ($restart_parameters) { - --exec echo "restart: $restart_parameters" > $_expect_file_name + --exec echo "$restart_cmd: $restart_parameters" > $_expect_file_name + if (!$restart_noprint) + { + --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR $MYSQLTEST_VARDIR MYSQLTEST_VARDIR + --exec echo "# $restart_cmd: $restart_parameters" + } + if ($restart_noprint == 1) + { + --exec echo "# $restart_cmd: with restart_parameters" + } } if (!$restart_parameters) { - --exec echo "restart" > $_expect_file_name + --exec echo "$restart_cmd" > $_expect_file_name + if ($restart_noprint < 2) + { + --exec echo "# $restart_cmd" + } } # Turn on reconnect @@ -18,4 +49,3 @@ if (!$restart_parameters) # Turn off reconnect again --disable_reconnect - diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 12afab4d28c..af2e71ea17d 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1963,7 +1963,7 @@ sub collect_mysqld_features_from_running_server () sub find_mysqld { - my ($mysqld_basedir)= $ENV{MTR_BINDIR}|| @_; + my ($mysqld_basedir)= $ENV{MTR_BINDIR_FORCED} || $ENV{MTR_BINDIR} || @_; my @mysqld_names= ("mysqld", "mysqld-max-nt", "mysqld-max", "mysqld-nt"); @@ -1974,7 +1974,7 @@ sub find_mysqld { unshift(@mysqld_names, "mysqld-debug"); } - return my_find_bin($bindir, + return my_find_bin($mysqld_basedir, ["sql", "libexec", "sbin", "bin"], [@mysqld_names]); } @@ -4789,6 +4789,7 @@ sub check_expected_crash_and_restart { mtr_verbose("Test says wait before restart") if $waits == 0; next; } + delete $ENV{MTR_BINDIR_FORCED}; # Ignore any partial or unknown command next unless $last_line =~ /^restart/; @@ -4796,7 +4797,13 @@ sub check_expected_crash_and_restart { # extra command line options to add to the restarted mysqld. # Anything other than 'wait' or 'restart:' (with a colon) will # result in a restart with original mysqld options. - if ($last_line =~ /restart:(.+)/) { + if ($last_line =~ /restart_bindir\s+(\S+)(:.+)?/) { + $ENV{MTR_BINDIR_FORCED}= $1; + if ($2) { + my @rest_opt= split(' ', $2); + $mysqld->{'restart_opts'}= \@rest_opt; + } + } elsif ($last_line =~ /restart:(.+)/) { my @rest_opt= split(' ', $1); $mysqld->{'restart_opts'}= \@rest_opt; } else { -- cgit v1.2.1 From e056efdd6cfa62cc4c978fce5730af0b8d4c3c6b Mon Sep 17 00:00:00 2001 From: Aleksey Midenkov Date: Tue, 27 Dec 2022 00:02:02 +0300 Subject: MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY 1. In case of system-versioned table add row_end into FTS_DOC_ID index in fts_create_common_tables() and innobase_create_key_defs(). fts_n_uniq() returns 1 or 2 depending on whether the table is system-versioned. After this patch recreate of FTS_DOC_ID index is required for existing system-versioned tables. If you see this message in error log or server warnings: "InnoDB: Table db/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB" use this command to fix the table: ALTER TABLE db.t1 FORCE; 2. Fix duplicate history for secondary unique index like it was done in MDEV-23644 for clustered index (932ec586aad). In case of existing history row which conflicts with currently inseted row we check in row_ins_scan_sec_index_for_duplicate() whether that row was inserted as part of current transaction. In that case we indicate with DB_FOREIGN_DUPLICATE_KEY that new history row is not needed and should be silently skipped. 3. Some parts of MDEV-21138 (7410ff436e9) reverted. Skipping of FTS_DOC_ID index for history rows made problems with purge system. Now this is fixed differently by p.2. 4. wait_all_purged.inc checks that we didn't affect non-history rows so they are deleted and purged correctly. Additional FTS fixes fts_init_get_doc_id(): exclude history rows from max_doc_id calculation. fts_init_get_doc_id() callback is used only for crash recovery. fts_add_doc_by_id(): set max value for row_end field. fts_read_stopword(): stopwords table can be system-versioned too. We now read stopwords only for current data. row_insert_for_mysql(): exclude history rows from doc_id validation. row_merge_read_clustered_index(): exclude history_rows from doc_id processing. fts_load_user_stopword(): for versioned table retrieve row_end field and skip history rows. For non-versioned table we retrieve 'value' field twice (just for uniformity). FTS tests for System Versioning now include maybe_versioning.inc which adds 3 combinations: 'vers' for debug build sets sysvers_force and sysvers_hide. sysvers_force makes every created table system-versioned, sysvers_hide hides WITH SYSTEM VERSIONING for SHOW CREATE. Note: basic.test, stopword.test and versioning.test do not require debug for 'vers' combination. This is controlled by $modify_create_table in maybe_versioning.inc and these tests run WITH SYSTEM VERSIONING explicitly which allows to test 'vers' combination on non-debug builds. 'vers_trx' like 'vers' sets sysvers_force_trx and sysvers_hide. That tests FTS with trx_id-based System Versioning. 'orig' works like before: no System Versioning is added, no debug is required. Upgrade/downgrade test for System Versioning is done by innodb_fts.versioning. It has 2 combinations: 'prepare' makes binaries in std_data (requires old server and OLD_BINDIR). It tests upgrade/downgrade against old server as well. 'upgrade' tests upgrade against binaries in std_data. Cleanups: Removed innodb-fts-stopword.test as it duplicates stopword.test --- mysql-test/include/have_gzip.inc | 6 + mysql-test/include/maybe_versioning.combinations | 7 + mysql-test/include/maybe_versioning.inc | 47 ++ mysql-test/std_data/versioning/articles.frm.gz | Bin 0 -> 287 bytes mysql-test/std_data/versioning/articles2.frm.gz | Bin 0 -> 291 bytes mysql-test/std_data/versioning/ibdata1.gz | Bin 0 -> 51165 bytes .../std_data/versioning/user_stopword.frm.gz | Bin 0 -> 199 bytes mysql-test/suite/innodb_fts/r/basic.result | 6 - .../suite/innodb_fts/r/innodb-fts-stopword.result | 757 --------------------- mysql-test/suite/innodb_fts/r/stopword,vers.rdiff | 192 ++++++ mysql-test/suite/innodb_fts/r/stopword.result | 12 +- .../suite/innodb_fts/r/versioning,prepare.result | 695 +++++++++++++++++++ mysql-test/suite/innodb_fts/r/versioning.result | 303 +++++++++ mysql-test/suite/innodb_fts/t/basic.inc | 264 +++++++ mysql-test/suite/innodb_fts/t/basic.test | 251 +------ mysql-test/suite/innodb_fts/t/crash_recovery.test | 34 + mysql-test/suite/innodb_fts/t/create.test | 1 + mysql-test/suite/innodb_fts/t/fulltext2.test | 1 + mysql-test/suite/innodb_fts/t/fulltext3.test | 1 + mysql-test/suite/innodb_fts/t/fulltext_cache.test | 1 + .../suite/innodb_fts/t/fulltext_distinct.test | 1 + .../suite/innodb_fts/t/fulltext_left_join.test | 1 + mysql-test/suite/innodb_fts/t/fulltext_multi.test | 1 + .../suite/innodb_fts/t/fulltext_order_by.test | 1 + mysql-test/suite/innodb_fts/t/fulltext_update.test | 1 + mysql-test/suite/innodb_fts/t/fulltext_var.test | 1 + mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test | 1 + mysql-test/suite/innodb_fts/t/innodb-fts-fic.test | 1 + .../suite/innodb_fts/t/innodb-fts-stopword.opt | 1 - .../suite/innodb_fts/t/innodb-fts-stopword.test | 664 ------------------ .../suite/innodb_fts/t/innodb_ft_aux_table.test | 1 + .../innodb_fts/t/innodb_fts_large_records.test | 1 + .../innodb_fts/t/innodb_fts_multiple_index.test | 1 + .../suite/innodb_fts/t/innodb_fts_proximity.test | 1 + .../t/innodb_fts_result_cache_limit.test | 1 + .../innodb_fts/t/innodb_fts_stopword_charset.test | 1 + .../suite/innodb_fts/t/innodb_fts_transaction.test | 1 + mysql-test/suite/innodb_fts/t/misc_debug.test | 1 + mysql-test/suite/innodb_fts/t/stopword.inc | 55 ++ mysql-test/suite/innodb_fts/t/stopword.test | 100 +-- mysql-test/suite/innodb_fts/t/sync.test | 1 + mysql-test/suite/innodb_fts/t/sync_block.test | 1 + mysql-test/suite/innodb_fts/t/sync_ddl.test | 1 + .../suite/innodb_fts/t/versioning.combinations | 2 + mysql-test/suite/innodb_fts/t/versioning.opt | 2 + mysql-test/suite/innodb_fts/t/versioning.test | 126 ++++ mysql-test/suite/versioning/r/alter.result | 5 + mysql-test/suite/versioning/r/debug.result | 4 +- mysql-test/suite/versioning/r/delete.result | 1 - .../suite/versioning/r/delete_history.result | 22 + mysql-test/suite/versioning/r/foreign.result | 37 + mysql-test/suite/versioning/t/alter.test | 2 + mysql-test/suite/versioning/t/delete_history.test | 25 + mysql-test/suite/versioning/t/foreign.test | 33 + 54 files changed, 1942 insertions(+), 1734 deletions(-) create mode 100644 mysql-test/include/have_gzip.inc create mode 100644 mysql-test/include/maybe_versioning.combinations create mode 100644 mysql-test/include/maybe_versioning.inc create mode 100644 mysql-test/std_data/versioning/articles.frm.gz create mode 100644 mysql-test/std_data/versioning/articles2.frm.gz create mode 100644 mysql-test/std_data/versioning/ibdata1.gz create mode 100644 mysql-test/std_data/versioning/user_stopword.frm.gz delete mode 100644 mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result create mode 100644 mysql-test/suite/innodb_fts/r/stopword,vers.rdiff create mode 100644 mysql-test/suite/innodb_fts/r/versioning,prepare.result create mode 100644 mysql-test/suite/innodb_fts/r/versioning.result create mode 100644 mysql-test/suite/innodb_fts/t/basic.inc delete mode 100644 mysql-test/suite/innodb_fts/t/innodb-fts-stopword.opt delete mode 100644 mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test create mode 100644 mysql-test/suite/innodb_fts/t/stopword.inc create mode 100644 mysql-test/suite/innodb_fts/t/versioning.combinations create mode 100644 mysql-test/suite/innodb_fts/t/versioning.opt create mode 100644 mysql-test/suite/innodb_fts/t/versioning.test (limited to 'mysql-test') diff --git a/mysql-test/include/have_gzip.inc b/mysql-test/include/have_gzip.inc new file mode 100644 index 00000000000..09f282b73de --- /dev/null +++ b/mysql-test/include/have_gzip.inc @@ -0,0 +1,6 @@ +--error 0,1,127 +--exec gzip --version > /dev/null 2> /dev/null +if ($sys_errno) +{ + --skip Requires gzip executable +} diff --git a/mysql-test/include/maybe_versioning.combinations b/mysql-test/include/maybe_versioning.combinations new file mode 100644 index 00000000000..246ad30ce7e --- /dev/null +++ b/mysql-test/include/maybe_versioning.combinations @@ -0,0 +1,7 @@ +[orig] + +[vers] +system_versioning_alter_history=keep + +[vers_trx] +system_versioning_alter_history=keep diff --git a/mysql-test/include/maybe_versioning.inc b/mysql-test/include/maybe_versioning.inc new file mode 100644 index 00000000000..8a7d7dad44f --- /dev/null +++ b/mysql-test/include/maybe_versioning.inc @@ -0,0 +1,47 @@ +# include file for test files that can be run with and without debug +# having debug and non-debug tests. + +# If $modify_create_table is true CREATE statement must be evaluated with +# $create_options that adds WITH SYSTEM VERSIONING to the statement. Otherwise +# system versioning is added implicitly via debug options. The second variant +# can easily be added to any test but works only for debug builds. + +if ($modify_create_table) +{ + if ($MTR_COMBINATION_VERS) + { + let $create_options= `select ' WITH SYSTEM VERSIONING'`; + } + + if ($MTR_COMBINATION_VERS_TRX) + { + --skip Not tested + } +} + +if (!$modify_create_table) +{ + let $have_debug=`select version() like '%debug%'`; + + if ($MTR_COMBINATION_VERS) + { + if (!$have_debug) + { + --skip Requires debug + } + --disable_query_log + set debug_dbug="d,sysvers_force_trx,sysvers_hide"; + --enable_query_log + } + + if ($MTR_COMBINATION_VERS_TRX) + { + if (!$have_debug) + { + --skip Requires debug + } + --disable_query_log + set debug_dbug="d,sysvers_force,sysvers_hide"; + --enable_query_log + } +} diff --git a/mysql-test/std_data/versioning/articles.frm.gz b/mysql-test/std_data/versioning/articles.frm.gz new file mode 100644 index 00000000000..fd3a0a8c0d4 Binary files /dev/null and b/mysql-test/std_data/versioning/articles.frm.gz differ diff --git a/mysql-test/std_data/versioning/articles2.frm.gz b/mysql-test/std_data/versioning/articles2.frm.gz new file mode 100644 index 00000000000..70f20c2e081 Binary files /dev/null and b/mysql-test/std_data/versioning/articles2.frm.gz differ diff --git a/mysql-test/std_data/versioning/ibdata1.gz b/mysql-test/std_data/versioning/ibdata1.gz new file mode 100644 index 00000000000..bddd2307181 Binary files /dev/null and b/mysql-test/std_data/versioning/ibdata1.gz differ diff --git a/mysql-test/std_data/versioning/user_stopword.frm.gz b/mysql-test/std_data/versioning/user_stopword.frm.gz new file mode 100644 index 00000000000..4a17265fabb Binary files /dev/null and b/mysql-test/std_data/versioning/user_stopword.frm.gz differ diff --git a/mysql-test/suite/innodb_fts/r/basic.result b/mysql-test/suite/innodb_fts/r/basic.result index a98de60674a..a8ab0c043e4 100644 --- a/mysql-test/suite/innodb_fts/r/basic.result +++ b/mysql-test/suite/innodb_fts/r/basic.result @@ -5,12 +5,6 @@ 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), -body TEXT, -FULLTEXT (title,body) -) ENGINE=InnoDB; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('How To Use MySQL Well','After you went through a ...'), diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result b/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result deleted file mode 100644 index 8cc3cbe5ae3..00000000000 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result +++ /dev/null @@ -1,757 +0,0 @@ -select * from information_schema.innodb_ft_default_stopword; -value -a -about -an -are -as -at -be -by -com -de -en -for -from -how -i -in -is -it -la -of -on -or -that -the -this -to -was -what -when -where -who -will -with -und -the -www -CREATE TABLE articles ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT (title,body) -) ENGINE=InnoDB; -INSERT INTO articles (title,body) VALUES -('MySQL Tutorial','DBMS stands for DataBase ...') , -('How To Use MySQL Well','After you went through a ...'), -('Optimizing MySQL','In this tutorial we will show ...'), -('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -('MySQL vs. YourSQL','In the following database comparison ...'), -('MySQL Security','When configured properly, MySQL ...'); -SELECT * FROM articles WHERE MATCH (title,body) -AGAINST ('the' IN NATURAL LANGUAGE MODE); -id title body -select @@innodb_ft_server_stopword_table; -@@innodb_ft_server_stopword_table -NULL -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -1 -select @@innodb_ft_user_stopword_table; -@@innodb_ft_user_stopword_table -NULL -set global innodb_ft_server_stopword_table = "not_defined"; -ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'not_defined' -create table user_stopword(value varchar(30)) engine = innodb; -set global innodb_ft_server_stopword_table = "test/user_stopword"; -drop index title on articles; -create fulltext index idx on articles(title, body); -SELECT * FROM articles WHERE MATCH (title,body) -AGAINST ('the' IN NATURAL LANGUAGE MODE); -id title body -5 MySQL vs. YourSQL In the following database comparison ... -CREATE TABLE articles_2 ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT (title,body) -) ENGINE=InnoDB; -INSERT INTO articles_2 (title, body) -VALUES ('test for stopwords','this is it...'); -SELECT * FROM articles_2 WHERE MATCH (title,body) -AGAINST ('this' IN NATURAL LANGUAGE MODE); -id title body -1 test for stopwords this is it... -insert into user_stopword values("this"); -CREATE TABLE articles_3 ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT (title,body) -) ENGINE=InnoDB; -INSERT INTO articles_3 (title, body) -VALUES ('test for stopwords','this is it...'); -SELECT * FROM articles_3 WHERE MATCH (title,body) -AGAINST ('this' IN NATURAL LANGUAGE MODE); -id title body -create table user_stopword_session(value varchar(30)) engine = innodb; -insert into user_stopword_session values("session"); -set session innodb_ft_user_stopword_table="test/user_stopword_session"; -CREATE TABLE articles_4 ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT (title,body) -) ENGINE=InnoDB; -INSERT INTO articles_4 (title, body) -VALUES ('test for session stopwords','this should also be excluded...'); -SELECT * FROM articles_4 WHERE MATCH (title,body) -AGAINST ('session' IN NATURAL LANGUAGE MODE); -id title body -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), -body TEXT, -FULLTEXT (title,body) -) ENGINE=InnoDB; -INSERT INTO articles_5 (title, body) -VALUES ('test for session stopwords','this should also be excluded...'); -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; -drop table articles_4; -drop table articles_5; -drop table user_stopword; -drop table user_stopword_session; -SET GLOBAL innodb_ft_enable_stopword=1; -SET GLOBAL innodb_ft_server_stopword_table=default; -CREATE TABLE articles ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT `idx` (title,body) -) ENGINE=InnoDB; -SHOW CREATE TABLE articles; -Table Create Table -articles CREATE TABLE `articles` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `title` varchar(200) DEFAULT NULL, - `body` text DEFAULT NULL, - PRIMARY KEY (`id`), - FULLTEXT KEY `idx` (`title`,`body`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -INSERT INTO articles (title,body) VALUES -('MySQL from Tutorial','DBMS stands for DataBase ...') , -('when To Use MySQL Well','After that you went through a ...'), -('where will Optimizing MySQL','In what tutorial we will show ...'), -('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -('MySQL vs. YourSQL','In the following database comparison ...'), -('MySQL Security','When configured properly, MySQL ...'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); -id title body -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -id title body -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE id = 7; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -id title body -DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE id = 7; -id title body -7 update the record to see will is indexed or not -DELETE FROM articles WHERE id = 7; -SET SESSION innodb_ft_enable_stopword = 0; -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -0 -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); -id title body -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -id title body -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE id = 8; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -id title body -SELECT * FROM articles WHERE id = 8; -id title body -8 update the record to see will is indexed or not -DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE id = 8; -id title body -8 update the record to see will is indexed or not -DELETE FROM articles WHERE id = 8; -ALTER TABLE articles DROP INDEX idx; -SHOW CREATE TABLE articles; -Table Create Table -articles CREATE TABLE `articles` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `title` varchar(200) DEFAULT NULL, - `body` text DEFAULT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -ANALYZE TABLE articles; -Table Op Msg_type Msg_text -test.articles analyze status OK -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -id title body -2 when To Use MySQL Well After that you went through a ... -6 MySQL Security When configured properly, MySQL ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -1 MySQL from Tutorial DBMS stands for DataBase ... -6 MySQL Security When configured properly, MySQL ... -2 when To Use MySQL Well After that you went through a ... -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -5 MySQL vs. YourSQL In the following database comparison ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -id title body -2 when To Use MySQL Well After that you went through a ... -3 where will Optimizing MySQL In what tutorial we will show ... -6 MySQL Security When configured properly, MySQL ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -id title body -1 MySQL from Tutorial DBMS stands for DataBase ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -9 the record will not index the , will words -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -id title body -9 the record will not index the , will words -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT COUNT(*),max(id) FROM articles; -COUNT(*) max(id) -7 9 -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -9 update the record to see will is indexed or not -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -9 update the record to see will is indexed or not -DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE id = 9; -id title body -DROP TABLE articles; -SET SESSION innodb_ft_enable_stopword=1; -SET GLOBAL innodb_ft_server_stopword_table=default; -SET SESSION innodb_ft_user_stopword_table=default; -select @@innodb_ft_server_stopword_table; -@@innodb_ft_server_stopword_table -NULL -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -1 -select @@innodb_ft_user_stopword_table; -@@innodb_ft_user_stopword_table -NULL -CREATE TABLE articles ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT `idx` (title,body) -) ENGINE=InnoDB; -INSERT INTO articles (title,body) VALUES -('MySQL from Tutorial','DBMS stands for DataBase ...') , -('when To Use MySQL Well','After that you went through a ...'), -('where will Optimizing MySQL','In what tutorial we will show ...'), -('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -('MySQL vs. YourSQL','In the following database comparison ...'), -('MySQL Security','When configured properly, MySQL ...'); -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'); -id title body -create table user_stopword(value varchar(30)) engine = innodb; -set session innodb_ft_user_stopword_table = "test/user_stopword"; -create table server_stopword(value varchar(30)) engine = innodb; -set global innodb_ft_server_stopword_table = "test/server_stopword"; -insert into user_stopword values("this"),("will"),("the"); -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -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'); -id title body -insert into server_stopword values("what"),("where"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -DELETE FROM user_stopword; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -insert into user_stopword values("this"),("will"),("the"); -ALTER TABLE articles DROP INDEX idx; -SET SESSION innodb_ft_enable_stopword = 0; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SET SESSION innodb_ft_enable_stopword = 1; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -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'); -id title body -SET SESSION innodb_ft_enable_stopword = 1; -SET SESSION innodb_ft_user_stopword_table = default; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -DROP TABLE articles,user_stopword,server_stopword; -SET SESSION innodb_ft_enable_stopword=1; -SET GLOBAL innodb_ft_server_stopword_table=default; -SET SESSION innodb_ft_user_stopword_table=default; -select @@innodb_ft_server_stopword_table; -@@innodb_ft_server_stopword_table -NULL -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -1 -select @@innodb_ft_user_stopword_table; -@@innodb_ft_user_stopword_table -NULL -CREATE TABLE articles ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT `idx` (title,body) -) ENGINE=InnoDB; -SHOW CREATE TABLE articles; -Table Create Table -articles CREATE TABLE `articles` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `title` varchar(200) DEFAULT NULL, - `body` text DEFAULT NULL, - PRIMARY KEY (`id`), - FULLTEXT KEY `idx` (`title`,`body`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -INSERT INTO articles (title,body) VALUES -('MySQL from Tutorial','DBMS stands for DataBase ...') , -('when To Use MySQL Well','After that you went through a ...'), -('where will Optimizing MySQL','In what tutorial we will show ...'), -('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -('MySQL vs. YourSQL','In the following database comparison ...'), -('MySQL Security','When configured properly, MySQL ...'); -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'); -id title body -create table user_stopword(value varchar(30)) engine = innodb; -set session innodb_ft_user_stopword_table = "test/user_stopword"; -insert into user_stopword values("mysqld"),("DBMS"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); -id title body -1 MySQL from Tutorial DBMS stands for DataBase ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); -id title body -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); -id title body -set session innodb_ft_user_stopword_table = default; -create table server_stopword(value varchar(30)) engine = innodb; -set global innodb_ft_server_stopword_table = "test/server_stopword"; -insert into server_stopword values("root"),("properly"); -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); -id title body -set session innodb_ft_user_stopword_table = "test/user_stopword"; -set global innodb_ft_server_stopword_table = "test/server_stopword"; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); -id title body -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); -id title body -6 MySQL Security When configured properly, MySQL ... -set session innodb_ft_user_stopword_table = "test/user_stopword"; -DELETE FROM user_stopword; -set global innodb_ft_server_stopword_table = "test/server_stopword"; -DELETE FROM server_stopword; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); -id title body -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); -id title body -6 MySQL Security When configured properly, MySQL ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); -id title body -1 MySQL from Tutorial DBMS stands for DataBase ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); -id title body -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -DROP TABLE articles,user_stopword,server_stopword; -SET SESSION innodb_ft_enable_stopword=1; -SET GLOBAL innodb_ft_server_stopword_table=default; -SET SESSION innodb_ft_user_stopword_table=default; -CREATE TABLE articles ( -id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -title VARCHAR(200), -body TEXT, -FULLTEXT `idx` (title,body) -) ENGINE=InnoDB; -SHOW CREATE TABLE articles; -Table Create Table -articles CREATE TABLE `articles` ( - `id` int(10) unsigned NOT NULL AUTO_INCREMENT, - `title` varchar(200) DEFAULT NULL, - `body` text DEFAULT NULL, - PRIMARY KEY (`id`), - FULLTEXT KEY `idx` (`title`,`body`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -INSERT INTO articles (title,body) VALUES -('MySQL from Tutorial','DBMS stands for DataBase ...') , -('when To Use MySQL Well','After that you went through a ...'), -('where will Optimizing MySQL','In what tutorial we will show ...'), -('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -('MySQL vs. YourSQL','In the following database comparison ...'), -('MySQL Security','When configured properly, MySQL ...'); -SET SESSION innodb_ft_enable_stopword = 0; -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -0 -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -connection con1; -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -1 -ANALYZE TABLE articles; -Table Op Msg_type Msg_text -test.articles analyze status OK -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -id title body -2 when To Use MySQL Well After that you went through a ... -6 MySQL Security When configured properly, MySQL ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -1 MySQL from Tutorial DBMS stands for DataBase ... -6 MySQL Security When configured properly, MySQL ... -2 when To Use MySQL Well After that you went through a ... -4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... -5 MySQL vs. YourSQL In the following database comparison ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -id title body -2 when To Use MySQL Well After that you went through a ... -3 where will Optimizing MySQL In what tutorial we will show ... -6 MySQL Security When configured properly, MySQL ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -id title body -1 MySQL from Tutorial DBMS stands for DataBase ... -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SET SESSION innodb_ft_enable_stopword = 1; -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -1 -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); -id title body -connection default; -select @@innodb_ft_enable_stopword; -@@innodb_ft_enable_stopword -0 -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); -id title body -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -id title body -SET SESSION innodb_ft_enable_stopword = 1; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -id title body -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"; -insert into user_stopword values("this"),("will"),("the"); -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -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'); -id title body -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'); -id title body -select @@innodb_ft_user_stopword_table; -@@innodb_ft_user_stopword_table -NULL -create table user_stopword_1(value varchar(30)) engine = innodb; -set session innodb_ft_user_stopword_table = "test/user_stopword_1"; -insert into user_stopword_1 values("when"); -SET SESSION innodb_ft_enable_stopword = 1; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); -id title body -2 when To Use MySQL Well After that you went through a ... -6 MySQL Security When configured properly, MySQL ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); -id title body -2 when To Use MySQL Well After that you went through a ... -6 MySQL Security When configured properly, MySQL ... -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -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 -connection con1; -SET SESSION innodb_ft_enable_stopword = 1; -SET SESSION innodb_ft_user_stopword_table=default; -select @@innodb_ft_user_stopword_table; -@@innodb_ft_user_stopword_table -NULL -select @@innodb_ft_server_stopword_table; -@@innodb_ft_server_stopword_table -NULL -create table server_stopword(value varchar(30)) engine = innodb; -SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; -select @@innodb_ft_server_stopword_table; -@@innodb_ft_server_stopword_table -test/server_stopword -insert into server_stopword values("when"),("the"); -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -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 -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; -@@innodb_ft_server_stopword_table -test/server_stopword -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -insert into server_stopword values("where"),("will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); -id title body -3 where will Optimizing MySQL In what tutorial we will show ... -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -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 -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); -id title body -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); -id title body -DROP TABLE articles,user_stopword,user_stopword_1,server_stopword; -SET SESSION innodb_ft_enable_stopword=1; -SET GLOBAL innodb_ft_server_stopword_table=default; -SET SESSION innodb_ft_user_stopword_table=default; diff --git a/mysql-test/suite/innodb_fts/r/stopword,vers.rdiff b/mysql-test/suite/innodb_fts/r/stopword,vers.rdiff new file mode 100644 index 00000000000..7405c47c41d --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/stopword,vers.rdiff @@ -0,0 +1,192 @@ +--- stopword.result ++++ stopword,vers.reject +@@ -46,7 +46,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + INSERT INTO articles (title,body) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ...') , + ('How To Use MySQL Well','After you went through a ...'), +@@ -60,7 +60,7 @@ + set global innodb_ft_server_stopword_table = "not_defined"; + ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'not_defined' + set global innodb_ft_server_stopword_table = NULL; +-create table user_stopword(value varchar(30)) engine = innodb; ++create table user_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set global innodb_ft_server_stopword_table = "test/user_stopword"; + drop index title on articles; + create fulltext index idx on articles(title, body); +@@ -73,7 +73,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + INSERT INTO articles_2 (title, body) + VALUES ('test for stopwords','this is it...'); + SELECT * FROM articles_2 WHERE MATCH (title,body) +@@ -88,13 +88,13 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + INSERT INTO articles_3 (title, body) + VALUES ('test for stopwords','this is it...'); + SELECT * FROM articles_3 WHERE MATCH (title,body) + AGAINST ('this' IN NATURAL LANGUAGE MODE); + id title body +-create table user_stopword_session(value varchar(30)) engine = innodb; ++create table user_stopword_session(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + insert into user_stopword values("this"); + delete from user_stopword; + insert into user_stopword_session values("session"); +@@ -104,7 +104,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + INSERT INTO articles_4 (title, body) + VALUES ('test for session stopwords','this should also be excluded...'); + SELECT * FROM articles_4 WHERE MATCH (title,body) +@@ -120,7 +120,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + INSERT INTO articles_5 (title, body) + VALUES ('test for session stopwords','this should also be excluded...'); + SELECT * FROM articles_5 WHERE MATCH (title,body) +@@ -142,7 +142,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + SHOW CREATE TABLE articles; + Table Create Table + articles CREATE TABLE `articles` ( +@@ -151,7 +151,7 @@ + `body` text DEFAULT NULL, + PRIMARY KEY (`id`), + FULLTEXT KEY `idx` (`title`,`body`) +-) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ++) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), +@@ -248,7 +248,7 @@ + `title` varchar(200) DEFAULT NULL, + `body` text DEFAULT NULL, + PRIMARY KEY (`id`) +-) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ++) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); + ANALYZE TABLE articles; + Table Op Msg_type Msg_text +@@ -320,7 +320,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), +@@ -332,9 +332,9 @@ + id title body + SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + id title body +-create table user_stopword(value varchar(30)) engine = innodb; ++create table user_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set session innodb_ft_user_stopword_table = "test/user_stopword"; +-create table server_stopword(value varchar(30)) engine = innodb; ++create table server_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set global innodb_ft_server_stopword_table = "test/server_stopword"; + insert into user_stopword values("when"),("where"); + delete from user_stopword; +@@ -419,7 +419,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + SHOW CREATE TABLE articles; + Table Create Table + articles CREATE TABLE `articles` ( +@@ -428,7 +428,7 @@ + `body` text DEFAULT NULL, + PRIMARY KEY (`id`), + FULLTEXT KEY `idx` (`title`,`body`) +-) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ++) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), +@@ -440,7 +440,7 @@ + id title body + SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + id title body +-create table user_stopword(value varchar(30)) engine = innodb; ++create table user_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set session innodb_ft_user_stopword_table = "test/user_stopword"; + insert into user_stopword values("mysqld"),("DBMS"); + SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +@@ -466,7 +466,7 @@ + SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); + id title body + set session innodb_ft_user_stopword_table = default; +-create table server_stopword(value varchar(30)) engine = innodb; ++create table server_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set global innodb_ft_server_stopword_table = "test/server_stopword"; + insert into server_stopword values("root"),("properly"); + ALTER TABLE articles DROP INDEX idx; +@@ -530,7 +530,7 @@ + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) +-) ENGINE=InnoDB; ++) WITH SYSTEM VERSIONING ENGINE=InnoDB; + SHOW CREATE TABLE articles; + Table Create Table + articles CREATE TABLE `articles` ( +@@ -539,7 +539,7 @@ + `body` text DEFAULT NULL, + PRIMARY KEY (`id`), + FULLTEXT KEY `idx` (`title`,`body`) +-) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ++) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING + INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), +@@ -656,7 +656,7 @@ + "In connection 1" + connection con1; + SET SESSION innodb_ft_enable_stopword = 1; +-create table user_stopword(value varchar(30)) engine = innodb; ++create table user_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set session innodb_ft_user_stopword_table = "test/user_stopword"; + insert into user_stopword values("this"),("will"),("the"); + ALTER TABLE articles DROP INDEX idx; +@@ -674,7 +674,7 @@ + select @@innodb_ft_user_stopword_table; + @@innodb_ft_user_stopword_table + NULL +-create table user_stopword_1(value varchar(30)) engine = innodb; ++create table user_stopword_1(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + set session innodb_ft_user_stopword_table = "test/user_stopword_1"; + insert into user_stopword_1 values("when"); + SET SESSION innodb_ft_enable_stopword = 1; +@@ -702,7 +702,7 @@ + select @@innodb_ft_server_stopword_table; + @@innodb_ft_server_stopword_table + NULL +-create table server_stopword(value varchar(30)) engine = innodb; ++create table server_stopword(value varchar(30)) WITH SYSTEM VERSIONING engine = innodb; + SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; + select @@innodb_ft_server_stopword_table; + @@innodb_ft_server_stopword_table diff --git a/mysql-test/suite/innodb_fts/r/stopword.result b/mysql-test/suite/innodb_fts/r/stopword.result index 1465e1713fd..8f3cf9d6a20 100644 --- a/mysql-test/suite/innodb_fts/r/stopword.result +++ b/mysql-test/suite/innodb_fts/r/stopword.result @@ -1,3 +1,6 @@ +SET @innodb_ft_server_stopword_table_orig=@@innodb_ft_server_stopword_table; +SET @innodb_ft_enable_stopword_orig=@@innodb_ft_enable_stopword; +SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table; call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table not_defined does not exist."); call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table test/user_stopword_session does not exist."); select * from information_schema.innodb_ft_default_stopword; @@ -54,9 +57,6 @@ INSERT INTO articles (title,body) VALUES SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the' IN NATURAL LANGUAGE MODE); id title body -SET @innodb_ft_server_stopword_table_orig=@@innodb_ft_server_stopword_table; -SET @innodb_ft_enable_stopword_orig=@@innodb_ft_enable_stopword; -SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table; set global innodb_ft_server_stopword_table = "not_defined"; ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'not_defined' set global innodb_ft_server_stopword_table = NULL; @@ -80,6 +80,8 @@ SELECT * FROM articles_2 WHERE MATCH (title,body) AGAINST ('this' IN NATURAL LANGUAGE MODE); id title body 1 test for stopwords this is it... +insert into user_stopword values("the"); +delete from user_stopword; insert into user_stopword values("this"); CREATE TABLE articles_3 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -93,6 +95,8 @@ SELECT * FROM articles_3 WHERE MATCH (title,body) AGAINST ('this' IN NATURAL LANGUAGE MODE); id title body create table user_stopword_session(value varchar(30)) engine = innodb; +insert into user_stopword values("this"); +delete from user_stopword; insert into user_stopword_session values("session"); set session innodb_ft_user_stopword_table="test/user_stopword_session"; CREATE TABLE articles_4 ( @@ -332,6 +336,8 @@ create table user_stopword(value varchar(30)) engine = innodb; set session innodb_ft_user_stopword_table = "test/user_stopword"; create table server_stopword(value varchar(30)) engine = innodb; set global innodb_ft_server_stopword_table = "test/server_stopword"; +insert into user_stopword values("when"),("where"); +delete from user_stopword; insert into user_stopword values("this"),("will"),("the"); ALTER TABLE articles DROP INDEX idx; ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); diff --git a/mysql-test/suite/innodb_fts/r/versioning,prepare.result b/mysql-test/suite/innodb_fts/r/versioning,prepare.result new file mode 100644 index 00000000000..ada4f30d80c --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/versioning,prepare.result @@ -0,0 +1,695 @@ +# Upgrade test +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), +body TEXT, +FULLTEXT (title,body) +)with system versioning ENGINE=InnoDB; +INSERT INTO articles (title,body) VALUES +('MySQL Tutorial','DBMS stands for DataBase ...') , +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), +('MySQL vs. YourSQL','In the following database comparison ...'), +('MySQL Security','When configured properly, MySQL ...'); +INSERT INTO articles (title,body) VALUES +('test query expansion','for database ...'); +INSERT INTO articles (title,body) VALUES +('test proximity search, test, proximity and phrase', +'search, with proximity innodb'); +INSERT INTO articles (title,body) VALUES +('test my proximity fts new search, test, proximity and phrase', +'search, with proximity innodb'); +INSERT INTO articles (title,body) VALUES +('test more of proximity fts search, test, more proximity and phrase', +'search, with proximity innodb'); +call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table not_defined does not exist."); +call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table test/user_stopword_session does not exist."); +select * from information_schema.innodb_ft_default_stopword; +value +a +about +an +are +as +at +be +by +com +de +en +for +from +how +i +in +is +it +la +of +on +or +that +the +this +to +was +what +when +where +who +will +with +und +the +www +CREATE TABLE articles2 ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title,body) +)with system versioning ENGINE=InnoDB; +INSERT INTO articles2 (title,body) VALUES +('MySQL Tutorial','DBMS stands for DataBase ...') , +('How To Use MySQL Well','After you went through a ...'), +('Optimizing MySQL','In this tutorial we will show ...'), +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), +('MySQL vs. YourSQL','In the following database comparison ...'), +('MySQL Security','When configured properly, MySQL ...'); +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('the' IN NATURAL LANGUAGE MODE); +id title body +set global innodb_ft_server_stopword_table = "not_defined"; +ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'not_defined' +set global innodb_ft_server_stopword_table = NULL; +create table user_stopword(value varchar(30)) engine = innodb; +set global innodb_ft_server_stopword_table = "test/user_stopword"; +drop index title on articles2; +create fulltext index idx on articles2(title, body); +insert into articles2 (title, body) +values ('test for stopwords','this is it...'); +insert into user_stopword values("the"); +delete from user_stopword; +insert into user_stopword values("this"); +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE); +ERROR HY000: Index articles is corrupted +call mtr.add_suppression("test/articles.? contains 3 indexes inside InnoDB"); +alter table articles force; +Warnings: +Warning 1082 InnoDB: Table test/articles contains 3 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB +flush tables; +show create table articles; +Table Create Table +articles CREATE TABLE `articles` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `title` varchar(200) DEFAULT NULL, + `body` text DEFAULT NULL, + PRIMARY KEY (`id`), + FULLTEXT KEY `title` (`title`,`body`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +5 MySQL vs. YourSQL In the following database comparison ... +7 test query expansion for database ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title,body) +AGAINST ('database' IN NATURAL LANGUAGE MODE); +COUNT(*) +3 +SELECT * FROM articles +WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT COUNT(IF(MATCH (title,body) +AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) +AS count FROM articles; +count +3 +SELECT id, body, MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score +FROM articles; +id body score +1 DBMS stands for DataBase ... 0.2734021842479706 +2 After you went through a ... 0 +3 In this tutorial we will show ... 0 +4 1. Never run mysqld as root. 2. ... 0 +5 In the following database comparison ... 0.2734021842479706 +6 When configured properly, MySQL ... 0 +7 for database ... 0.2734021842479706 +8 search, with proximity innodb 0 +9 search, with proximity innodb 0 +10 search, with proximity innodb 0 +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('DBMS Security' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('t*' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +7 test query expansion for database ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('MY*' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('ru*' IN BOOLEAN MODE); +id title body +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE); +id title body +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 ... +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 ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR HY000: Table handler out of memory +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))' + IN BOOLEAN MODE); +id title body +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR 42000: syntax error, unexpected $end +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR 42000: syntax error, unexpected $end +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE); +id title body +2 How To Use MySQL Well After you went through a ... +1 MySQL Tutorial DBMS stands for DataBase ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE); +id title body +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE); +id title body +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title,body) +AGAINST ('database' WITH QUERY EXPANSION); +COUNT(*) +10 +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('test' WITH QUERY EXPANSION); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +8 test proximity search, test, proximity and phrase search, with proximity innodb +7 test query expansion for database ... +1 MySQL Tutorial DBMS stands for DataBase ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following comparison"@3' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following comparison"@2' IN BOOLEAN MODE); +id title body +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following database"' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@3' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@2' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@5' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@5' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@1' IN BOOLEAN MODE); +id title body +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@4' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@3' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"@4' IN BOOLEAN MODE); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"' IN BOOLEAN MODE); +id title body +set global innodb_ft_server_stopword_table= "test/user_stopword"; +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('the' IN NATURAL LANGUAGE MODE); +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('this' IN NATURAL LANGUAGE MODE); +drop index idx on articles2; +Warnings: +Warning 1082 InnoDB: Table test/articles2 contains 3 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB +create fulltext index idx on articles2(title, body); +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('the' IN NATURAL LANGUAGE MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('this' IN NATURAL LANGUAGE MODE); +id title body +# Downgrade test +alter table articles force; +Warnings: +Warning 1082 InnoDB: Table test/articles contains 3 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB +Warning 1082 InnoDB: Table test/articles contains 3 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB +flush tables; +show create table articles; +Table Create Table +articles CREATE TABLE `articles` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `title` varchar(200) DEFAULT NULL, + `body` text DEFAULT NULL, + PRIMARY KEY (`id`), + FULLTEXT KEY `title` (`title`,`body`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +5 MySQL vs. YourSQL In the following database comparison ... +7 test query expansion for database ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title,body) +AGAINST ('database' IN NATURAL LANGUAGE MODE); +COUNT(*) +3 +SELECT * FROM articles +WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT COUNT(IF(MATCH (title,body) +AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) +AS count FROM articles; +count +3 +SELECT id, body, MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score +FROM articles; +id body score +1 DBMS stands for DataBase ... 0.2734021842479706 +2 After you went through a ... 0 +3 In this tutorial we will show ... 0 +4 1. Never run mysqld as root. 2. ... 0 +5 In the following database comparison ... 0.2734021842479706 +6 When configured properly, MySQL ... 0 +7 for database ... 0.2734021842479706 +8 search, with proximity innodb 0 +9 search, with proximity innodb 0 +10 search, with proximity innodb 0 +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('DBMS Security' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('t*' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +7 test query expansion for database ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('MY*' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('ru*' IN BOOLEAN MODE); +id title body +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE); +id title body +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 ... +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 ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR HY000: Table handler out of memory +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))' + IN BOOLEAN MODE); +id title body +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR 42000: syntax error, unexpected $end +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR 42000: syntax error, unexpected $end +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE); +id title body +2 How To Use MySQL Well After you went through a ... +1 MySQL Tutorial DBMS stands for DataBase ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE); +id title body +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE); +id title body +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title,body) +AGAINST ('database' WITH QUERY EXPANSION); +COUNT(*) +10 +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('test' WITH QUERY EXPANSION); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +8 test proximity search, test, proximity and phrase search, with proximity innodb +7 test query expansion for database ... +1 MySQL Tutorial DBMS stands for DataBase ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following comparison"@3' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following comparison"@2' IN BOOLEAN MODE); +id title body +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following database"' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@3' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@2' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@5' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@5' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@1' IN BOOLEAN MODE); +id title body +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@4' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@3' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"@4' IN BOOLEAN MODE); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"' IN BOOLEAN MODE); +id title body +set global innodb_ft_server_stopword_table= "test/user_stopword"; +drop index idx on articles2; +Warnings: +Warning 1082 InnoDB: Table test/articles2 contains 3 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB +Warning 1082 InnoDB: Table test/articles2 contains 3 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB +create fulltext index idx on articles2(title, body); +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('the' IN NATURAL LANGUAGE MODE); +id title body +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('this' IN NATURAL LANGUAGE MODE); +id title body +# Cleanup +drop tables articles, articles2, user_stopword; +set global innodb_ft_server_stopword_table= default; diff --git a/mysql-test/suite/innodb_fts/r/versioning.result b/mysql-test/suite/innodb_fts/r/versioning.result new file mode 100644 index 00000000000..73ce8f838fd --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/versioning.result @@ -0,0 +1,303 @@ +# Upgrade test +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE); +ERROR HY000: Index articles is corrupted +call mtr.add_suppression("test/articles.? contains 3 indexes inside InnoDB"); +alter table articles force; +Warnings: +Warning 1082 InnoDB: Table test/articles contains 3 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB +flush tables; +show create table articles; +Table Create Table +articles CREATE TABLE `articles` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `title` varchar(200) DEFAULT NULL, + `body` text DEFAULT NULL, + PRIMARY KEY (`id`), + FULLTEXT KEY `title` (`title`,`body`) +) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +5 MySQL vs. YourSQL In the following database comparison ... +7 test query expansion for database ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title,body) +AGAINST ('database' IN NATURAL LANGUAGE MODE); +COUNT(*) +3 +SELECT * FROM articles +WHERE MATCH (title, body) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT COUNT(IF(MATCH (title,body) +AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) +AS count FROM articles; +count +3 +SELECT id, body, MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score +FROM articles; +id body score +1 DBMS stands for DataBase ... 0.2734021842479706 +2 After you went through a ... 0 +3 In this tutorial we will show ... 0 +4 1. Never run mysqld as root. 2. ... 0 +5 In the following database comparison ... 0.2734021842479706 +6 When configured properly, MySQL ... 0 +7 for database ... 0.2734021842479706 +8 search, with proximity innodb 0 +9 search, with proximity innodb 0 +10 search, with proximity innodb 0 +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('DBMS Security' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('t*' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +7 test query expansion for database ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('MY*' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +1 MySQL Tutorial DBMS stands for 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 1. Never run mysqld as root. 2. ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('ru*' IN BOOLEAN MODE); +id title body +4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE); +id title body +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 ... +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 ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR HY000: Table handler out of memory +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +id title body +6 MySQL Security When configured properly, MySQL ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))' + IN BOOLEAN MODE); +id title body +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR 42000: syntax error, unexpected $end +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +ERROR 42000: syntax error, unexpected $end +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE); +id title body +2 How To Use MySQL Well After you went through a ... +1 MySQL Tutorial DBMS stands for DataBase ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE); +id title body +1 MySQL Tutorial DBMS stands for DataBase ... +2 How To Use MySQL Well After you went through a ... +3 Optimizing MySQL In this tutorial we will show ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE); +id title body +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE); +id title body +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 ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT COUNT(*) FROM articles +WHERE MATCH (title,body) +AGAINST ('database' WITH QUERY EXPANSION); +COUNT(*) +10 +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('test' WITH QUERY EXPANSION); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +8 test proximity search, test, proximity and phrase search, with proximity innodb +7 test query expansion for database ... +1 MySQL Tutorial DBMS stands for DataBase ... +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following comparison"@3' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following comparison"@2' IN BOOLEAN MODE); +id title body +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"following database"' IN BOOLEAN MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@3' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@2' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"proximity search"@5' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@5' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@1' IN BOOLEAN MODE); +id title body +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@4' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"test proximity"@3' IN BOOLEAN MODE); +id title body +8 test proximity search, test, proximity and phrase search, with proximity innodb +9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"@4' IN BOOLEAN MODE); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); +id title body +10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb +SELECT * FROM articles +WHERE MATCH (title,body) +AGAINST ('"more test proximity"' IN BOOLEAN MODE); +id title body +set global innodb_ft_server_stopword_table= "test/user_stopword"; +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('the' IN NATURAL LANGUAGE MODE); +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('this' IN NATURAL LANGUAGE MODE); +drop index idx on articles2; +Warnings: +Warning 1082 InnoDB: Table test/articles2 contains 3 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB +create fulltext index idx on articles2(title, body); +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('the' IN NATURAL LANGUAGE MODE); +id title body +5 MySQL vs. YourSQL In the following database comparison ... +SELECT * FROM articles2 WHERE MATCH (title,body) +AGAINST ('this' IN NATURAL LANGUAGE MODE); +id title body +# Cleanup +drop tables articles, articles2, user_stopword; +set global innodb_ft_server_stopword_table= default; diff --git a/mysql-test/suite/innodb_fts/t/basic.inc b/mysql-test/suite/innodb_fts/t/basic.inc new file mode 100644 index 00000000000..fd7d09208bc --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/basic.inc @@ -0,0 +1,264 @@ +if ($basic_stage == create_table) +{ +# 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; + +--disable_query_log +eval CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + )$create_options ENGINE=InnoDB; +--enable_query_log +} + +if ($basic_stage == insert_1) +{ +# Insert six rows +INSERT INTO articles (title,body) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); +} + +if ($basic_stage == select_1) +{ +# Look for 'Database' in table article +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Database' IN NATURAL LANGUAGE MODE); + +SELECT COUNT(*) FROM articles + WHERE MATCH (title,body) + AGAINST ('database' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles + WHERE MATCH (title, body) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + + +SELECT COUNT(IF(MATCH (title,body) + AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) + AS count FROM articles; + +# Select Relevance Ranking +SELECT id, body, MATCH (title,body) + AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score + FROM articles; + +# 'MySQL' treated as stopword (stopword functionality not yet supported) +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); + +# Boolean search +# Select rows contain "MySQL" but not "YourSQL" +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); + +# Select rows contain at least one of the two words +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('DBMS Security' IN BOOLEAN MODE); + +# Select rows contain both "MySQL" and "YourSQL" +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE); + +# Select rows contain "MySQL" but rank rows with "YourSQL" higher +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE); + +# Test negation operator. Select rows contain MySQL, +# if the row contains "YourSQL", rank it lower +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE); + +# Test wild card search operator +# Notice row with "the" will not get fetched due to +# stopword filtering +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('t*' IN BOOLEAN MODE); + +# Test wild card search, notice row 6 with 2 "MySQL" rank first +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('MY*' IN BOOLEAN MODE); + +# Another wild card search +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('ru*' IN BOOLEAN MODE); + +# Test ">" and "<" Operator, the ">" operator increases +# the word relevance rank and the "<" operator decreases it +# Following test puts rows with "Well" on top and rows +# with "stands" at the bottom +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE); + +# Test sub-expression boolean search. Find rows contain +# "MySQL" but not "Well" or "stands". +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE); + +--error 128 +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))' + IN BOOLEAN MODE); + +--error ER_PARSE_ERROR +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); +--error ER_PARSE_ERROR +SELECT * FROM articles WHERE MATCH (title,body) AGAINST +('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' + IN BOOLEAN MODE); + +# Test sub-expression boolean search. Find rows contain +# "MySQL" and "Well" or "MySQL" and "stands". But rank the +# doc with "Well" higher, and doc with "stands" lower. +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE); + +# Test nested sub-expression. +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE); + +# Find rows with "MySQL" but not "Tricks", "Security" nor "YourSQL" +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE); + +# Test non-word delimiter combined with negate "-" operator +# This should return the same result as 'mysql - (Security DBMS)' +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE); + +# Again, the operator sequence should not matter +SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE); + +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE); + +# Test query expansion +SELECT COUNT(*) FROM articles + WHERE MATCH (title,body) + AGAINST ('database' WITH QUERY EXPANSION); +} + +if ($basic_stage == insert_2) +{ +INSERT INTO articles (title,body) VALUES + ('test query expansion','for database ...'); +} + +if ($basic_stage == select_2) +{ +# This query will return result containing word "database" as +# the query expand from "test" to words in document just +# inserted above +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('test' WITH QUERY EXPANSION); + +# This is to test the proximity search, search two word +# "following" and "comparison" within 19 character space +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"following comparison"@3' IN BOOLEAN MODE); + +# This is to test the proximity search, search two word +# "following" and "comparison" within 19 character space +# This search should come with no return result +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"following comparison"@2' IN BOOLEAN MODE); + +# This is to test the phrase search, searching phrase +# "following database" +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"following database"' IN BOOLEAN MODE); +} + +if ($basic_stage == insert_3) +{ +# Insert into table with similar word of different distances +INSERT INTO articles (title,body) VALUES + ('test proximity search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO articles (title,body) VALUES + ('test my proximity fts new search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO articles (title,body) VALUES + ('test more of proximity fts search, test, more proximity and phrase', + 'search, with proximity innodb'); +} + +if ($basic_stage == select_3) +{ +# This should only return the first document +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"proximity search"@3' IN BOOLEAN MODE); + +# This would return no document +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"proximity search"@2' IN BOOLEAN MODE); + +# This give you all three documents +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"proximity search"@5' IN BOOLEAN MODE); + +# Similar boundary testing for the words +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"test proximity"@5' IN BOOLEAN MODE); + +# No document will be returned +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"test proximity"@1' IN BOOLEAN MODE); + +# All three documents will be returned +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"test proximity"@4' IN BOOLEAN MODE); + +# Only two document will be returned. +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"test proximity"@3' IN BOOLEAN MODE); + +# Test with more word The last document will return, please notice there +# is no ordering requirement for proximity search. +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"more test proximity"@4' IN BOOLEAN MODE); + +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); + +# The phrase search will not require exact word ordering +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"more test proximity"' IN BOOLEAN MODE); +} diff --git a/mysql-test/suite/innodb_fts/t/basic.test b/mysql-test/suite/innodb_fts/t/basic.test index 53ad978a5b1..df2e24fae8e 100644 --- a/mysql-test/suite/innodb_fts/t/basic.test +++ b/mysql-test/suite/innodb_fts/t/basic.test @@ -1,252 +1,33 @@ # This is the basic function tests for innodb FTS -- source include/have_innodb.inc +--let $modify_create_table= 1 +-- source include/maybe_versioning.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; +let $basic_stage= create_table; +--source basic.inc -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL Tutorial','DBMS stands for DataBase ...') , - ('How To Use MySQL Well','After you went through a ...'), - ('Optimizing MySQL','In this tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); +let $basic_stage= insert_1; +--source basic.inc -- disable_result_log ANALYZE TABLE articles; -- enable_result_log -# Look for 'Database' in table article -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('Database' IN NATURAL LANGUAGE MODE); - -SELECT COUNT(*) FROM articles - WHERE MATCH (title,body) - AGAINST ('database' IN NATURAL LANGUAGE MODE); - -SELECT * FROM articles - WHERE MATCH (title, body) - AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); - - -SELECT COUNT(IF(MATCH (title,body) - AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) - AS count FROM articles; - -# Select Relevance Ranking -SELECT id, body, MATCH (title,body) - AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score - FROM articles; - -# 'MySQL' treated as stopword (stopword functionality not yet supported) -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); - -# Boolean search -# Select rows contain "MySQL" but not "YourSQL" -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); - -# Select rows contain at least one of the two words -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('DBMS Security' IN BOOLEAN MODE); - -# Select rows contain both "MySQL" and "YourSQL" -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE); - -# Select rows contain "MySQL" but rank rows with "YourSQL" higher -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE); - -# Test negation operator. Select rows contain MySQL, -# if the row contains "YourSQL", rank it lower -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE); - -# Test wild card search operator -# Notice row with "the" will not get fetched due to -# stopword filtering -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('t*' IN BOOLEAN MODE); - -# Test wild card search, notice row 6 with 2 "MySQL" rank first -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('MY*' IN BOOLEAN MODE); - -# Another wild card search -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('ru*' IN BOOLEAN MODE); - -# Test ">" and "<" Operator, the ">" operator increases -# the word relevance rank and the "<" operator decreases it -# Following test puts rows with "Well" on top and rows -# with "stands" at the bottom -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE); - -# Test sub-expression boolean search. Find rows contain -# "MySQL" but not "Well" or "stands". -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE); - ---error 128 -SELECT * FROM articles WHERE MATCH (title,body) AGAINST -('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))' - IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST -('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' - IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST -('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))' - IN BOOLEAN MODE); - ---error ER_PARSE_ERROR -SELECT * FROM articles WHERE MATCH (title,body) AGAINST -('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))' - IN BOOLEAN MODE); ---error ER_PARSE_ERROR -SELECT * FROM articles WHERE MATCH (title,body) AGAINST -('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))' - IN BOOLEAN MODE); - -# Test sub-expression boolean search. Find rows contain -# "MySQL" and "Well" or "MySQL" and "stands". But rank the -# doc with "Well" higher, and doc with "stands" lower. -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE); - -# Test nested sub-expression. -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE); - -# Find rows with "MySQL" but not "Tricks", "Security" nor "YourSQL" -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE); - -# Test non-word delimiter combined with negate "-" operator -# This should return the same result as 'mysql - (Security DBMS)' -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE); - -# Again, the operator sequence should not matter -SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE); - -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE); - -# Test query expansion -SELECT COUNT(*) FROM articles - WHERE MATCH (title,body) - AGAINST ('database' WITH QUERY EXPANSION); - -INSERT INTO articles (title,body) VALUES - ('test query expansion','for database ...'); - -# This query will return result containing word "database" as -# the query expand from "test" to words in document just -# inserted above -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('test' WITH QUERY EXPANSION); - -# This is to test the proximity search, search two word -# "following" and "comparison" within 19 character space -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"following comparison"@3' IN BOOLEAN MODE); - -# This is to test the proximity search, search two word -# "following" and "comparison" within 19 character space -# This search should come with no return result -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"following comparison"@2' IN BOOLEAN MODE); - -# This is to test the phrase search, searching phrase -# "following database" -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"following database"' IN BOOLEAN MODE); - -# Insert into table with similar word of different distances -INSERT INTO articles (title,body) VALUES - ('test proximity search, test, proximity and phrase', - 'search, with proximity innodb'); - -INSERT INTO articles (title,body) VALUES - ('test my proximity fts new search, test, proximity and phrase', - 'search, with proximity innodb'); - -INSERT INTO articles (title,body) VALUES - ('test more of proximity fts search, test, more proximity and phrase', - 'search, with proximity innodb'); - -# This should only return the first document -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"proximity search"@3' IN BOOLEAN MODE); - -# This would return no document -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"proximity search"@2' IN BOOLEAN MODE); - -# This give you all three documents -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"proximity search"@5' IN BOOLEAN MODE); - -# Similar boundary testing for the words -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"test proximity"@5' IN BOOLEAN MODE); - -# No document will be returned -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"test proximity"@1' IN BOOLEAN MODE); - -# All three documents will be returned -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"test proximity"@4' IN BOOLEAN MODE); +let $basic_stage= select_1; +--source basic.inc -# Only two document will be returned. -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"test proximity"@3' IN BOOLEAN MODE); +let $basic_stage= insert_2; +--source basic.inc -# Test with more word The last document will return, please notice there -# is no ordering requirement for proximity search. -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"more test proximity"@4' IN BOOLEAN MODE); +let $basic_stage= select_2; +--source basic.inc -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); +let $basic_stage= insert_3; +--source basic.inc -# The phrase search will not require exact word ordering -SELECT * FROM articles - WHERE MATCH (title,body) - AGAINST ('"more test proximity"' IN BOOLEAN MODE); +let $basic_stage= select_3; +--source basic.inc drop table articles; diff --git a/mysql-test/suite/innodb_fts/t/crash_recovery.test b/mysql-test/suite/innodb_fts/t/crash_recovery.test index 0e32608a81a..7bece572827 100644 --- a/mysql-test/suite/innodb_fts/t/crash_recovery.test +++ b/mysql-test/suite/innodb_fts/t/crash_recovery.test @@ -7,6 +7,7 @@ # The embedded server tests do not support restarting. --source include/not_embedded.inc --source include/maybe_debug.inc +--source include/maybe_versioning.inc FLUSH TABLES; # Following are test for crash recovery on FTS index, the first scenario @@ -22,6 +23,16 @@ CREATE TABLE articles ( FULLTEXT (title,body) ) ENGINE=InnoDB; +let $vers= $MTR_COMBINATION_VERS + $MTR_COMBINATION_VERS_TRX; +if ($vers) +{ + --disable_query_log + INSERT INTO articles (title,body) VALUES + ('history','Deleted row ...'); + DELETE FROM articles; + --enable_query_log +} + # Drop the FTS index before more insertion. The FTS_DOC_ID should # be kept DROP INDEX title ON articles; @@ -59,6 +70,13 @@ INSERT INTO articles (title,body) VALUES # Recreate fulltext index to see if everything is OK CREATE FULLTEXT INDEX idx ON articles (title,body); +if ($vers) +{ + --disable_query_log + UPDATE articles SET id= id - 1; + --enable_query_log +} + # Should return 3 rows SELECT * FROM articles WHERE MATCH (title,body) @@ -98,6 +116,13 @@ disconnect dml; INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'); +if ($vers) +{ + --disable_query_log + UPDATE articles SET id= id - 1 WHERE id > 8; + --enable_query_log +} + # Should return 6 rows SELECT * FROM articles WHERE MATCH (title,body) @@ -134,6 +159,15 @@ BEGIN; INSERT INTO articles VALUES (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...'); +if ($vers) +{ + --disable_query_log + DELETE FROM articles WHERE id = 100; + INSERT INTO articles VALUES + (100, 200, 'MySQL Tutorial','DBMS stands for DataBase ...'); + --enable_query_log +} + connect(dml2, localhost, root,,); --echo # diff --git a/mysql-test/suite/innodb_fts/t/create.test b/mysql-test/suite/innodb_fts/t/create.test index 38c93de4982..710fd9cb99b 100644 --- a/mysql-test/suite/innodb_fts/t/create.test +++ b/mysql-test/suite/innodb_fts/t/create.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/maybe_versioning.inc SET NAMES utf8mb4; --echo # diff --git a/mysql-test/suite/innodb_fts/t/fulltext2.test b/mysql-test/suite/innodb_fts/t/fulltext2.test index 25a4d5b24f9..a66f804b7ff 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext2.test +++ b/mysql-test/suite/innodb_fts/t/fulltext2.test @@ -7,6 +7,7 @@ # --source include/have_innodb.inc +--source include/maybe_versioning.inc CREATE TABLE t1 ( i int(10) unsigned not null auto_increment primary key, diff --git a/mysql-test/suite/innodb_fts/t/fulltext3.test b/mysql-test/suite/innodb_fts/t/fulltext3.test index 9c7941d7b5c..f28ca2c7d77 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext3.test +++ b/mysql-test/suite/innodb_fts/t/fulltext3.test @@ -3,6 +3,7 @@ # test of new fulltext search features # --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings DROP TABLE IF EXISTS t1; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_cache.test b/mysql-test/suite/innodb_fts/t/fulltext_cache.test index fa7ad49e881..37926c1e7f1 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_cache.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_cache.test @@ -2,6 +2,7 @@ # Bugreport due to Roy Nasser # --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings drop table if exists t1, t2; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_distinct.test b/mysql-test/suite/innodb_fts/t/fulltext_distinct.test index f6232704543..bb390a08384 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_distinct.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_distinct.test @@ -3,6 +3,7 @@ # bug reported by Tibor Simko # --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings DROP TABLE IF EXISTS t1, t2; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_left_join.test b/mysql-test/suite/innodb_fts/t/fulltext_left_join.test index 23bbd5ddc10..0a1e1748769 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_left_join.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_left_join.test @@ -2,6 +2,7 @@ # Test for bug from Jean-Cédric COSTA # --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings drop table if exists t1, t2; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_multi.test b/mysql-test/suite/innodb_fts/t/fulltext_multi.test index 274027ea10b..81ab7e1b071 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_multi.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_multi.test @@ -1,5 +1,6 @@ # several FULLTEXT indexes in one table test --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings DROP TABLE IF EXISTS t1; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_order_by.test b/mysql-test/suite/innodb_fts/t/fulltext_order_by.test index d2194f22e2a..f14681b934d 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_order_by.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_order_by.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings DROP TABLE IF EXISTS t1,t2,t3; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_update.test b/mysql-test/suite/innodb_fts/t/fulltext_update.test index 336e8de1d1b..bda97cd4a21 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_update.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_update.test @@ -2,6 +2,7 @@ # Test for bug by voi@ims.at # --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings drop table if exists test; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_var.test b/mysql-test/suite/innodb_fts/t/fulltext_var.test index 2b94aa58424..e8e4bf93303 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_var.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_var.test @@ -2,6 +2,7 @@ # Fulltext configurable parameters # --source include/have_innodb.inc +--source include/maybe_versioning.inc # Save ft_boolean_syntax variable let $saved_ft_boolean_syntax=`select @@global.ft_boolean_syntax`; diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test index 1ed164492d5..17a800c7663 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test @@ -1,6 +1,7 @@ # This is the DDL function tests for innodb FTS -- source include/have_innodb.inc +-- source include/maybe_versioning.inc # Create FTS table CREATE TABLE fts_test ( 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 669aa69e835..2d94c21398c 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-fic.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-fic.test @@ -1,6 +1,7 @@ # This is the basic function tests for innodb FTS -- source include/have_innodb.inc +-- source include/maybe_versioning.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`"); diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.opt b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.opt deleted file mode 100644 index 2b0652d08c3..00000000000 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-innodb-ft-default-stopword diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test deleted file mode 100644 index de14deab328..00000000000 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test +++ /dev/null @@ -1,664 +0,0 @@ -# This is the basic function tests for innodb FTS - --- source include/have_innodb.inc - - -select * from information_schema.innodb_ft_default_stopword; - -# Create FTS table -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL Tutorial','DBMS stands for DataBase ...') , - ('How To Use MySQL Well','After you went through a ...'), - ('Optimizing MySQL','In this tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); - -# "the" is in the default stopword, it would not be selected -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('the' IN NATURAL LANGUAGE MODE); - -let $innodb_ft_server_stopword_table_orig=`select @@innodb_ft_server_stopword_table`; -let $innodb_ft_enable_stopword_orig=`select @@innodb_ft_enable_stopword`; -let $innodb_ft_user_stopword_table_orig=`select @@innodb_ft_user_stopword_table`; - -select @@innodb_ft_server_stopword_table; -select @@innodb_ft_enable_stopword; -select @@innodb_ft_user_stopword_table; - -# Provide user defined stopword table, if not (correctly) defined, -# it will be rejected ---error 1231 -set global innodb_ft_server_stopword_table = "not_defined"; - -# Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; - -# The set operation should be successful -set global innodb_ft_server_stopword_table = "test/user_stopword"; - -drop index title on articles; - -create fulltext index idx on articles(title, body); - -# Now we should be able to find "the" -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('the' IN NATURAL LANGUAGE MODE); - -# Nothing inserted into the default stopword, so essentially -# nothing get screened. The new stopword could only be -# effective for table created thereafter -CREATE TABLE articles_2 ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -INSERT INTO articles_2 (title, body) - VALUES ('test for stopwords','this is it...'); - -# Now we can find record with "this" -SELECT * FROM articles_2 WHERE MATCH (title,body) - AGAINST ('this' IN NATURAL LANGUAGE MODE); - -# Ok, let's instantiate some value into user supplied stop word -# table -insert into user_stopword values("this"); - -# Ok, let's repeat with the new table again. -CREATE TABLE articles_3 ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -INSERT INTO articles_3 (title, body) - VALUES ('test for stopwords','this is it...'); - -# Now we should NOT find record with "this" -SELECT * FROM articles_3 WHERE MATCH (title,body) - AGAINST ('this' IN NATURAL LANGUAGE MODE); - -# Test session level stopword control "innodb_user_stopword_table" -create table user_stopword_session(value varchar(30)) engine = innodb; - -insert into user_stopword_session values("session"); - -set session innodb_ft_user_stopword_table="test/user_stopword_session"; - -CREATE TABLE articles_4 ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -INSERT INTO articles_4 (title, body) - VALUES ('test for session stopwords','this should also be excluded...'); - -# "session" is excluded -SELECT * FROM articles_4 WHERE MATCH (title,body) - AGAINST ('session' IN NATURAL LANGUAGE MODE); - -# But we can find record with "this" -SELECT * FROM articles_4 WHERE MATCH (title,body) - AGAINST ('this' IN NATURAL LANGUAGE MODE); - ---connect (con1,localhost,root,,) -CREATE TABLE articles_5 ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -INSERT INTO articles_5 (title, body) - VALUES ('test for session stopwords','this should also be excluded...'); - -# "session" should be found since the stopword table is session specific -SELECT * FROM articles_5 WHERE MATCH (title,body) - AGAINST ('session' IN NATURAL LANGUAGE MODE); - ---connection default -drop table articles; -drop table articles_2; -drop table articles_3; -drop table articles_4; -drop table articles_5; -drop table user_stopword; -drop table user_stopword_session; - -eval SET GLOBAL innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; -eval SET GLOBAL innodb_ft_server_stopword_table=default; - -#--------------------------------------------------------------------------------------- -# Behavior : -# The stopword is loaded into memory at -# 1) create fulltext index time, -# 2) boot server, -# 3) first time FTs is used -# So if you already created a FTS index, and then turn off stopword -# or change stopword table content it won't affect the FTS -# that already created since the stopword list are already loaded. -# It will only affect the new FTS index created after you changed -# the settings. - -# Create FTS table -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; - -SHOW CREATE TABLE articles; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL from Tutorial','DBMS stands for DataBase ...') , - ('when To Use MySQL Well','After that you went through a ...'), - ('where will Optimizing MySQL','In what tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); - -# Case : server_stopword=default -# Try to Search default stopword from innodb, "where", "will", "what" -# and "when" are all stopwords -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -# boolean No result expected -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -# no result expected -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -# no result expected -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); - -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -# Not going to update as where condition can not find record -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -# Update the record -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE id = 7; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Delete will not work as where condition do not return -DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE id = 7; -DELETE FROM articles WHERE id = 7; - - - -# Case : Turn OFF stopword list variable and search stopword on OLD index. -# disable stopword list -#SET global innodb_ft_server_stopword_table = ""; -SET SESSION innodb_ft_enable_stopword = 0; -select @@innodb_ft_enable_stopword; -#SET global innodb_ft_user_stopword_table = ""; - -# search default stopword with innodb_ft_enable_stopword is OFF. -# No records expected even though we turned OFF stopwod filtering -# (refer Behavior (at the top of the test) for explanation ) -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); - -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -# Not going to update as where condition can not find record -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -# Update the record -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE id = 8; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -SELECT * FROM articles WHERE id = 8; -# Delete will not work as where condition do not return -DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE id = 8; -DELETE FROM articles WHERE id = 8; - -# Case : Turn OFF stopword list variable and search stopword on NEW index. -# Drop index -ALTER TABLE articles DROP INDEX idx; -SHOW CREATE TABLE articles; - -# Create the FTS index Using Alter Table. -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); - -ANALYZE TABLE articles; - -# search default stopword with innodb_ft_enable_stopword is OFF. -# All records expected as stopwod filtering is OFF and we created -# new FTS index. -# (refer Behavior (at the top of the test) for explanation ) -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); - -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); -# Update will succeed. -UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); - -SELECT COUNT(*),max(id) FROM articles; -# Update the record - uncommet on fix -#UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' -#WHERE id = 9; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Delete will succeed. -DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE id = 9; - - -DROP TABLE articles; - -eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; -#eval SET GLOBAL innodb_ft_server_stopword_table=$innodb_ft_server_stopword_table_orig; -eval SET GLOBAL innodb_ft_server_stopword_table=default; -#eval SET GLOBAL innodb_ft_user_stopword_table=$innodb_ft_user_stopword_table_orig; -eval SET SESSION innodb_ft_user_stopword_table=default; - -#--------------------------------------------------------------------------------------- - -select @@innodb_ft_server_stopword_table; -select @@innodb_ft_enable_stopword; -select @@innodb_ft_user_stopword_table; - -# Create FTS table -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL from Tutorial','DBMS stands for DataBase ...') , - ('when To Use MySQL Well','After that you went through a ...'), - ('where will Optimizing MySQL','In what tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); - -# No records expeced for select -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; -# The set operation should be successful -set session innodb_ft_user_stopword_table = "test/user_stopword"; -# Define a correct formatted server stopword table -create table server_stopword(value varchar(30)) engine = innodb; -# The set operation should be successful -set global innodb_ft_server_stopword_table = "test/server_stopword"; -# Add values into user supplied stop word table -insert into user_stopword values("this"),("will"),("the"); - -# Drop existing index and create the FTS index Using Alter Table. -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); - -# Add values into server supplied stop word table -insert into server_stopword values("what"),("where"); -# Follwoing should return result as server stopword list was empty at create index time -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); - -# Delete stopword from user list -DELETE FROM user_stopword; -# Drop existing index and create the FTS index Using Alter Table. -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -# Follwoing should return result even though to server stopword list -# conatin these words. Session level stopword list takes priority -# Here user_stopword is set using innodb_ft_user_stopword_table -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); - -# Follwoing should return result as user stopword list was empty at create index time -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); - -# Add values into user supplied stop word table -insert into user_stopword values("this"),("will"),("the"); - -# Drop existing index and create the FTS index Using Alter Table. -ALTER TABLE articles DROP INDEX idx; -SET SESSION innodb_ft_enable_stopword = 0; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); - -# Session level stopword list takes priority -SET SESSION innodb_ft_enable_stopword = 1; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); - -# Make user stopword list deafult so as to server stopword list takes priority -SET SESSION innodb_ft_enable_stopword = 1; -SET SESSION innodb_ft_user_stopword_table = default; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); - - -DROP TABLE articles,user_stopword,server_stopword; - -# Restore Defaults -eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; -eval SET GLOBAL innodb_ft_server_stopword_table=default; -eval SET SESSION innodb_ft_user_stopword_table=default; -select @@innodb_ft_server_stopword_table; -select @@innodb_ft_enable_stopword; -select @@innodb_ft_user_stopword_table; - -#--------------------------------------------------------------------------------------- -# Create FTS table -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; - -SHOW CREATE TABLE articles; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL from Tutorial','DBMS stands for DataBase ...') , - ('when To Use MySQL Well','After that you went through a ...'), - ('where will Optimizing MySQL','In what tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); - -# No records expeced for select -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -# Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; -# The set operation should be successful -set session innodb_ft_user_stopword_table = "test/user_stopword"; -insert into user_stopword values("mysqld"),("DBMS"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); - - -# Drop existing index and create the FTS index Using Alter Table. -# user stopword list will take effect. -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); - -# set user stopword list empty -set session innodb_ft_user_stopword_table = default; -# Define a correct formatted user stopword table -create table server_stopword(value varchar(30)) engine = innodb; -# The set operation should be successful -set global innodb_ft_server_stopword_table = "test/server_stopword"; -insert into server_stopword values("root"),("properly"); -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); - - -# set user stopword list empty -set session innodb_ft_user_stopword_table = "test/user_stopword"; -# The set operation should be successful -set global innodb_ft_server_stopword_table = "test/server_stopword"; -# user stopword list take effect as its session level -# Result expected for select -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); - -# set user stopword list -set session innodb_ft_user_stopword_table = "test/user_stopword"; -DELETE FROM user_stopword; -# The set operation should be successful -set global innodb_ft_server_stopword_table = "test/server_stopword"; -DELETE FROM server_stopword; -# user stopword list take affect as its session level -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); - -DROP TABLE articles,user_stopword,server_stopword; - -# Restore Values -eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; -eval SET GLOBAL innodb_ft_server_stopword_table=default; -eval SET SESSION innodb_ft_user_stopword_table=default; - - -#------------------------------------------------------------------------------ -# FTS stopword list test - check varaibles across sessions - -# Create FTS table -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; - -SHOW CREATE TABLE articles; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL from Tutorial','DBMS stands for DataBase ...') , - ('when To Use MySQL Well','After that you went through a ...'), - ('where will Optimizing MySQL','In what tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); - -# session varaible innodb_ft_enable_stopword=0 will take effect for new FTS index -SET SESSION innodb_ft_enable_stopword = 0; -select @@innodb_ft_enable_stopword; - -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); - - ---connection con1 -select @@innodb_ft_enable_stopword; - -ANALYZE TABLE articles; - -# result expected as index created before setting innodb_ft_enable_stopword varaible off -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); - -SET SESSION innodb_ft_enable_stopword = 1; -select @@innodb_ft_enable_stopword; -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -# no result expected turned innodb_ft_enable_stopword is ON -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); - - ---connection default -select @@innodb_ft_enable_stopword; -# no result expected as word not indexed from connection 1 -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); - -INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); - -SET SESSION innodb_ft_enable_stopword = 1; -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); - - ---connection con1 -SET SESSION innodb_ft_enable_stopword = 1; -# Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; -# The set operation should be successful -set session innodb_ft_user_stopword_table = "test/user_stopword"; -# Add values into user supplied stop word table -insert into user_stopword values("this"),("will"),("the"); -# Drop existing index and create the FTS index Using Alter Table. -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -# no result expected as innodb_ft_user_stopword_table filter it -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); - - ---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); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); -select @@innodb_ft_user_stopword_table; -# Define a correct formatted user stopword table -create table user_stopword_1(value varchar(30)) engine = innodb; -# The set operation should be successful -set session innodb_ft_user_stopword_table = "test/user_stopword_1"; -insert into user_stopword_1 values("when"); -SET SESSION innodb_ft_enable_stopword = 1; -# result expected -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); -# Drop existing index and create the FTS index Using Alter Table. -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -# no result expected -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); - ---connection con1 -SET SESSION innodb_ft_enable_stopword = 1; -SET SESSION innodb_ft_user_stopword_table=default; -select @@innodb_ft_user_stopword_table; -select @@innodb_ft_server_stopword_table; -# Define a correct formatted server stopword table -create table server_stopword(value varchar(30)) engine = innodb; -# The set operation should be successful -SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; -select @@innodb_ft_server_stopword_table; -insert into server_stopword values("when"),("the"); -# Drop existing index and create the FTS index Using Alter Table. -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -# no result expected -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the'); - -disconnect con1; ---source include/wait_until_disconnected.inc - ---connection default -SET SESSION innodb_ft_enable_stopword = 1; -SET SESSION innodb_ft_user_stopword_table=default; -select @@innodb_ft_server_stopword_table; -# result expected -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); -insert into server_stopword values("where"),("will"); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); -ALTER TABLE articles DROP INDEX idx; -ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); -# no result expected -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the'); -SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); -SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); - - -DROP TABLE articles,user_stopword,user_stopword_1,server_stopword; - -# Restore Values -eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; -eval SET GLOBAL innodb_ft_server_stopword_table=default; -eval SET SESSION innodb_ft_user_stopword_table=default; - diff --git a/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test b/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test index 48964aef4fd..f9447aada60 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test +++ b/mysql-test/suite/innodb_fts/t/innodb_ft_aux_table.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/maybe_versioning.inc CREATE TABLE t1 (v VARCHAR(100), FULLTEXT INDEX (v)) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test b/mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test index e200cff6c39..c84cd0685ed 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test @@ -3,6 +3,7 @@ # b) more words across records --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings DROP TABLE IF EXISTS t1; 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 c8293655d1b..f9535c729ff 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 @@ -2,6 +2,7 @@ # Test With two FTS index on same table + alter/create/drop index + tnx #------------------------------------------------------------------------------ --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings drop table if exists t1; 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 20eee3fac23..e3d8eb0c13b 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/maybe_versioning.inc # This is the DDL function tests for innodb FTS # Functional testing with FTS proximity search using '@' diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test b/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test index 669808edbf6..1ec37532a71 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test @@ -4,6 +4,7 @@ # Must have debug code to use SET SESSION debug --source include/have_debug.inc +--source include/maybe_versioning.inc # Create FTS table CREATE TABLE t1 ( 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 16ee91c30f4..3fe99dab2c3 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 @@ -4,6 +4,7 @@ # Embedded server tests do not support restarting --source include/not_embedded.inc +--source include/maybe_versioning.inc SELECT @@innodb_ft_server_stopword_table; SELECT @@innodb_ft_enable_stopword; 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 11571f346a2..026aeb635cd 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test @@ -5,6 +5,7 @@ # 3) UNCOMMITTED RECORDS CAN BE SEEN WITH QURIES WHICH DO NOT USE FTS INDEX # this behavior do not break integratity of tables and "select" which do not use FTS still can view them. --source include/have_innodb.inc +--source include/maybe_versioning.inc --disable_warnings diff --git a/mysql-test/suite/innodb_fts/t/misc_debug.test b/mysql-test/suite/innodb_fts/t/misc_debug.test index cf3651e14a2..5e59ae6aba1 100644 --- a/mysql-test/suite/innodb_fts/t/misc_debug.test +++ b/mysql-test/suite/innodb_fts/t/misc_debug.test @@ -7,6 +7,7 @@ --source include/have_debug.inc --source include/have_debug_sync.inc --source include/count_sessions.inc +--source include/maybe_versioning.inc # Following test is for Bug 14668777 - ASSERT ON IB_VECTOR_SIZE( # TABLE->FTS->INDEXES, ALTER TABLE diff --git a/mysql-test/suite/innodb_fts/t/stopword.inc b/mysql-test/suite/innodb_fts/t/stopword.inc new file mode 100644 index 00000000000..774501ade85 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/stopword.inc @@ -0,0 +1,55 @@ +if ($stopword_stage == create_table) +{ +call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table not_defined does not exist."); +call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table test/user_stopword_session does not exist."); + +select * from information_schema.innodb_ft_default_stopword; + +# Create FTS table +eval CREATE TABLE $stopword_table ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + )$create_options ENGINE=InnoDB; + +# Insert six rows +eval INSERT INTO $stopword_table (title,body) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# "the" is in the default stopword, it would not be selected +eval SELECT * FROM $stopword_table WHERE MATCH (title,body) + AGAINST ('the' IN NATURAL LANGUAGE MODE); + +# Provide user defined stopword table, if not (correctly) defined, +# it will be rejected +--error ER_WRONG_VALUE_FOR_VAR +set global innodb_ft_server_stopword_table = "not_defined"; +set global innodb_ft_server_stopword_table = NULL; + +# Define a correct formatted user stopword table +eval create table user_stopword(value varchar(30))$create_options engine = innodb; + +# The set operation should be successful +set global innodb_ft_server_stopword_table = "test/user_stopword"; + +eval drop index title on $stopword_table; + +eval create fulltext index idx on $stopword_table(title, body); +} + +if ($stopword_stage == select_1) +{ +--error 0, ER_INDEX_CORRUPT +eval SELECT * FROM $stopword_table WHERE MATCH (title,body) + AGAINST ('the' IN NATURAL LANGUAGE MODE); +--error 0, ER_INDEX_CORRUPT +eval SELECT * FROM $stopword_table WHERE MATCH (title,body) + AGAINST ('this' IN NATURAL LANGUAGE MODE); + +} diff --git a/mysql-test/suite/innodb_fts/t/stopword.test b/mysql-test/suite/innodb_fts/t/stopword.test index ca01da80734..9f8bd89b816 100644 --- a/mysql-test/suite/innodb_fts/t/stopword.test +++ b/mysql-test/suite/innodb_fts/t/stopword.test @@ -1,52 +1,16 @@ # This is the basic function tests for innodb FTS -- source include/have_innodb.inc - -call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table not_defined does not exist."); -call mtr.add_suppression("\\[ERROR\\] InnoDB: user stopword table test/user_stopword_session does not exist."); - -select * from information_schema.innodb_ft_default_stopword; - -# Create FTS table -CREATE TABLE articles ( - id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, - title VARCHAR(200), - body TEXT, - FULLTEXT (title,body) - ) ENGINE=InnoDB; - -# Insert six rows -INSERT INTO articles (title,body) VALUES - ('MySQL Tutorial','DBMS stands for DataBase ...') , - ('How To Use MySQL Well','After you went through a ...'), - ('Optimizing MySQL','In this tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...'); - -# "the" is in the default stopword, it would not be selected -SELECT * FROM articles WHERE MATCH (title,body) - AGAINST ('the' IN NATURAL LANGUAGE MODE); +-- let $modify_create_table= 1 +-- source include/maybe_versioning.inc SET @innodb_ft_server_stopword_table_orig=@@innodb_ft_server_stopword_table; SET @innodb_ft_enable_stopword_orig=@@innodb_ft_enable_stopword; SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table; -# Provide user defined stopword table, if not (correctly) defined, -# it will be rejected ---error ER_WRONG_VALUE_FOR_VAR -set global innodb_ft_server_stopword_table = "not_defined"; -set global innodb_ft_server_stopword_table = NULL; - -# Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; - -# The set operation should be successful -set global innodb_ft_server_stopword_table = "test/user_stopword"; - -drop index title on articles; - -create fulltext index idx on articles(title, body); +let $stopword_table= articles; +let $stopword_stage= create_table; +--source stopword.inc # Now we should be able to find "the" SELECT * FROM articles WHERE MATCH (title,body) @@ -55,12 +19,12 @@ SELECT * FROM articles WHERE MATCH (title,body) # Nothing inserted into the default stopword, so essentially # nothing get screened. The new stopword could only be # effective for table created thereafter -CREATE TABLE articles_2 ( +eval CREATE TABLE articles_2 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; INSERT INTO articles_2 (title, body) VALUES ('test for stopwords','this is it...'); @@ -71,15 +35,17 @@ SELECT * FROM articles_2 WHERE MATCH (title,body) # Ok, let's instantiate some value into user supplied stop word # table +insert into user_stopword values("the"); +delete from user_stopword; insert into user_stopword values("this"); # Ok, let's repeat with the new table again. -CREATE TABLE articles_3 ( +eval CREATE TABLE articles_3 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; INSERT INTO articles_3 (title, body) VALUES ('test for stopwords','this is it...'); @@ -89,18 +55,20 @@ SELECT * FROM articles_3 WHERE MATCH (title,body) AGAINST ('this' IN NATURAL LANGUAGE MODE); # Test session level stopword control "innodb_user_stopword_table" -create table user_stopword_session(value varchar(30)) engine = innodb; +eval create table user_stopword_session(value varchar(30))$create_options engine = innodb; +insert into user_stopword values("this"); +delete from user_stopword; insert into user_stopword_session values("session"); set session innodb_ft_user_stopword_table="test/user_stopword_session"; -CREATE TABLE articles_4 ( +eval CREATE TABLE articles_4 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; INSERT INTO articles_4 (title, body) VALUES ('test for session stopwords','this should also be excluded...'); @@ -114,12 +82,12 @@ SELECT * FROM articles_4 WHERE MATCH (title,body) AGAINST ('this' IN NATURAL LANGUAGE MODE); --connect (con1,localhost,root,,) -CREATE TABLE articles_5 ( +eval CREATE TABLE articles_5 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; INSERT INTO articles_5 (title, body) VALUES ('test for session stopwords','this should also be excluded...'); @@ -153,12 +121,12 @@ SET GLOBAL innodb_ft_server_stopword_table=default; # the settings. # Create FTS table -CREATE TABLE articles ( +eval CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; SHOW CREATE TABLE articles; @@ -292,12 +260,12 @@ SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig; SET SESSION innodb_ft_user_stopword_table=default; # Create FTS table -CREATE TABLE articles ( +eval CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; # Insert six rows INSERT INTO articles (title,body) VALUES @@ -312,14 +280,16 @@ INSERT INTO articles (title,body) VALUES SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); # Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; +eval create table user_stopword(value varchar(30))$create_options engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; # Define a correct formatted server stopword table -create table server_stopword(value varchar(30)) engine = innodb; +eval create table server_stopword(value varchar(30))$create_options engine = innodb; # The set operation should be successful set global innodb_ft_server_stopword_table = "test/server_stopword"; # Add values into user supplied stop word table +insert into user_stopword values("when"),("where"); +delete from user_stopword; insert into user_stopword values("this"),("will"),("the"); # Drop existing index and create the FTS index Using Alter Table. @@ -390,12 +360,12 @@ SET SESSION innodb_ft_user_stopword_table=default; #--------------------------------------------------------------------------------------- # Create FTS table -CREATE TABLE articles ( +eval CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; SHOW CREATE TABLE articles; @@ -412,7 +382,7 @@ INSERT INTO articles (title,body) VALUES SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); # Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; +eval create table user_stopword(value varchar(30))$create_options engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; insert into user_stopword values("mysqld"),("DBMS"); @@ -434,7 +404,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); # set user stopword list empty set session innodb_ft_user_stopword_table = default; # Define a correct formatted user stopword table -create table server_stopword(value varchar(30)) engine = innodb; +eval create table server_stopword(value varchar(30))$create_options engine = innodb; # The set operation should be successful set global innodb_ft_server_stopword_table = "test/server_stopword"; insert into server_stopword values("root"),("properly"); @@ -487,12 +457,12 @@ SET SESSION innodb_ft_user_stopword_table=default; # FTS stopword list test - check varaibles across sessions # Create FTS table -CREATE TABLE articles ( +eval CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT `idx` (title,body) - ) ENGINE=InnoDB; + )$create_options ENGINE=InnoDB; SHOW CREATE TABLE articles; @@ -573,7 +543,7 @@ SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOL --connection con1 SET SESSION innodb_ft_enable_stopword = 1; # Define a correct formatted user stopword table -create table user_stopword(value varchar(30)) engine = innodb; +eval create table user_stopword(value varchar(30))$create_options engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword"; # Add values into user supplied stop word table @@ -593,7 +563,7 @@ SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); select @@innodb_ft_user_stopword_table; # Define a correct formatted user stopword table -create table user_stopword_1(value varchar(30)) engine = innodb; +eval create table user_stopword_1(value varchar(30))$create_options engine = innodb; # The set operation should be successful set session innodb_ft_user_stopword_table = "test/user_stopword_1"; insert into user_stopword_1 values("when"); @@ -615,7 +585,7 @@ SET SESSION innodb_ft_user_stopword_table=default; select @@innodb_ft_user_stopword_table; select @@innodb_ft_server_stopword_table; # Define a correct formatted server stopword table -create table server_stopword(value varchar(30)) engine = innodb; +eval create table server_stopword(value varchar(30))$create_options engine = innodb; # The set operation should be successful SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; select @@innodb_ft_server_stopword_table; diff --git a/mysql-test/suite/innodb_fts/t/sync.test b/mysql-test/suite/innodb_fts/t/sync.test index 6929dce31b8..3bd5b56a21b 100644 --- a/mysql-test/suite/innodb_fts/t/sync.test +++ b/mysql-test/suite/innodb_fts/t/sync.test @@ -7,6 +7,7 @@ --source include/not_valgrind.inc --source include/not_embedded.inc --source include/not_crashrep.inc +--source include/maybe_versioning.inc connect (con1,localhost,root,,); connection default; diff --git a/mysql-test/suite/innodb_fts/t/sync_block.test b/mysql-test/suite/innodb_fts/t/sync_block.test index 895d2ba8a59..593c8fd9176 100644 --- a/mysql-test/suite/innodb_fts/t/sync_block.test +++ b/mysql-test/suite/innodb_fts/t/sync_block.test @@ -7,6 +7,7 @@ --source include/have_debug_sync.inc --source include/have_log_bin.inc --source include/count_sessions.inc +--source include/maybe_versioning.inc SET @old_log_output = @@global.log_output; SET @old_slow_query_log = @@global.slow_query_log; diff --git a/mysql-test/suite/innodb_fts/t/sync_ddl.test b/mysql-test/suite/innodb_fts/t/sync_ddl.test index 2950297d5bb..f3919ba1c94 100644 --- a/mysql-test/suite/innodb_fts/t/sync_ddl.test +++ b/mysql-test/suite/innodb_fts/t/sync_ddl.test @@ -4,6 +4,7 @@ --source include/have_innodb.inc --source include/have_debug.inc +--source include/maybe_versioning.inc #-------------------------------------- # Check FTS_sync vs TRUNCATE (1) diff --git a/mysql-test/suite/innodb_fts/t/versioning.combinations b/mysql-test/suite/innodb_fts/t/versioning.combinations new file mode 100644 index 00000000000..42842ba51a6 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/versioning.combinations @@ -0,0 +1,2 @@ +[prepare] +[upgrade] diff --git a/mysql-test/suite/innodb_fts/t/versioning.opt b/mysql-test/suite/innodb_fts/t/versioning.opt new file mode 100644 index 00000000000..df323743314 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/versioning.opt @@ -0,0 +1,2 @@ +--innodb-file-per-table=0 +--innodb-doublewrite=0 diff --git a/mysql-test/suite/innodb_fts/t/versioning.test b/mysql-test/suite/innodb_fts/t/versioning.test new file mode 100644 index 00000000000..b492f9c9d98 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/versioning.test @@ -0,0 +1,126 @@ +--source include/have_innodb.inc +--source include/have_gzip.inc +--source include/not_embedded.inc + +# Combinations +# +# upgrade: test upgrade on prepared databases from std_data. +# prepare: requires $OLD_BINDIR, test upgrade and downgrade with old-version +# server. Also prepare std_data files during the run in the source +# directory (you just have to commit or reject them). +# +# Examples +# +# export OLD_BINDIR="/home/midenok/src/mariadb/10.3b/build" +# mtr innodb_fts.versioning,orig_stopword,prepare +# + +if ($MTR_COMBINATION_PREPARE) +{ + if (!$OLD_BINDIR) + { + --skip Requires OLD_BINDIR parameter (see test comment) + } +} + +--let $server_id= `select @@server_id` +--let $datadir= `select @@datadir` +--let $std_dir= $MYSQL_TEST_DIR/std_data/versioning +--let $restart_noprint= 3 + +--echo # Upgrade test +let $stopword_table= articles2; + +if ($MTR_COMBINATION_PREPARE) +{ + let $restart_bindir= $OLD_BINDIR; + --source include/restart_mysqld.inc + + let $create_options= with system versioning; + let $basic_stage= create_table; + --source basic.inc + let $basic_stage= insert_1; + --source basic.inc + let $basic_stage= insert_2; + --source basic.inc + let $basic_stage= insert_3; + --source basic.inc + let $stopword_stage= create_table; + --source stopword.inc + eval insert into $stopword_table (title, body) + values ('test for stopwords','this is it...'); + insert into user_stopword values("the"); + delete from user_stopword; + insert into user_stopword values("this"); + --source include/shutdown_mysqld.inc + + --exec mkdir -p $std_dir + --exec cp -af $datadir/ibdata1 $datadir/test/*.frm $std_dir + --exec gzip -9f $std_dir/ibdata1 $std_dir/*.frm +} + +if ($MTR_COMBINATION_UPGRADE) +{ + --source include/shutdown_mysqld.inc + --exec rm -f $datadir/test/*.ibd $datadir/ib* + --exec cp -af $std_dir/ibdata1.gz $datadir + --exec cp -af $std_dir/*.frm.gz $datadir/test + --exec gzip -df $datadir/ibdata1.gz $datadir/test/*.frm.gz +} +let $restart_bindir=; +--source include/start_mysqld.inc + +--error ER_INDEX_CORRUPT +SELECT * FROM articles WHERE MATCH (title,body) +AGAINST ('Database' IN NATURAL LANGUAGE MODE); + +call mtr.add_suppression("test/articles.? contains 3 indexes inside InnoDB"); +alter table articles force; +flush tables; +show create table articles; + +let $basic_stage= select_1; +source basic.inc; +let $basic_stage= select_2; +source basic.inc; +let $basic_stage= select_3; +source basic.inc; + +set global innodb_ft_server_stopword_table= "test/user_stopword"; +let $stopword_stage= select_1; +--source stopword.inc +eval drop index idx on $stopword_table; +eval create fulltext index idx on $stopword_table(title, body); +--source stopword.inc + +if ($MTR_COMBINATION_PREPARE) +{ + --echo # Downgrade test + let $restart_bindir= $OLD_BINDIR; + --source include/restart_mysqld.inc + + alter table articles force; + flush tables; + show create table articles; + let $basic_stage= select_1; + source basic.inc; + let $basic_stage= select_2; + source basic.inc; + let $basic_stage= select_3; + source basic.inc; + + set global innodb_ft_server_stopword_table= "test/user_stopword"; + let $stopword_stage= select_1; + # Downgrade faults with assertion "dict_index_get_n_unique(index) == 1" + # until we rebuilt the index: + eval drop index idx on $stopword_table; + eval create fulltext index idx on $stopword_table(title, body); + source stopword.inc; + + let $restart_bindir=; + --source include/restart_mysqld.inc +} + +--echo # Cleanup +eval drop tables articles, $stopword_table, user_stopword; +set global innodb_ft_server_stopword_table= default; diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 9242f713de3..61ea06368a5 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -375,6 +375,11 @@ a b 2 NULL 3 1 4 2 +alter table t add c int, drop system versioning; +select * from t; +a b c +3 1 NULL +4 2 NULL create or replace table t (a int) with system versioning; insert into t values (1), (2), (3); delete from t where a<3; diff --git a/mysql-test/suite/versioning/r/debug.result b/mysql-test/suite/versioning/r/debug.result index 3f1367cf3cb..27ba8ee0e12 100644 --- a/mysql-test/suite/versioning/r/debug.result +++ b/mysql-test/suite/versioning/r/debug.result @@ -19,7 +19,7 @@ show create table tt2; Table Create Table tt2 CREATE TEMPORARY TABLE `tt2` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING connect con1, localhost, root; create table t3 (a int); show create table t3; @@ -32,7 +32,7 @@ show create table tt3; Table Create Table tt3 CREATE TEMPORARY TABLE `tt3` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING disconnect con1; connection default; set debug_dbug='+d,sysvers_show'; diff --git a/mysql-test/suite/versioning/r/delete.result b/mysql-test/suite/versioning/r/delete.result index 0f9e2c22130..6f8c8921790 100644 --- a/mysql-test/suite/versioning/r/delete.result +++ b/mysql-test/suite/versioning/r/delete.result @@ -146,6 +146,5 @@ delete from t1; select f1, f3, check_row_ts(row_start, row_end) from t1 for system_time all; f1 f3 check_row_ts(row_start, row_end) 1 1 HISTORICAL ROW -1 NULL ERROR: row_end == row_start 1 1 HISTORICAL ROW drop table t1; diff --git a/mysql-test/suite/versioning/r/delete_history.result b/mysql-test/suite/versioning/r/delete_history.result index 065b1f00876..64a05ff6867 100644 --- a/mysql-test/suite/versioning/r/delete_history.result +++ b/mysql-test/suite/versioning/r/delete_history.result @@ -1,3 +1,5 @@ +set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency; +set global innodb_purge_rseg_truncate_frequency= 1; create table t (a int); delete history from t before system_time now(); ERROR HY000: Table `t` is not system-versioned @@ -165,3 +167,23 @@ x 1 drop prepare stmt; drop table t1; +# +# MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY +# +create table t1 (a integer, c0 varchar(255), fulltext key (c0)) +with system versioning engine innodb; +set system_versioning_alter_history= keep; +alter table t1 drop system versioning; +alter table t1 add system versioning; +insert into t1 values (1, 'politician'); +update t1 set c0= 'criminal'; +InnoDB 0 transactions not purged +delete history from t1; +drop table t1; +create table t1 (id int primary key, ftx varchar(255)) +with system versioning engine innodb; +insert into t1 values (1, 'c'); +delete from t1; +alter table t1 add fulltext key(ftx); +drop table t1; +set global innodb_purge_rseg_truncate_frequency= @saved_frequency; diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index d157916c60c..3eb968f1a33 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -443,6 +443,43 @@ pk f1 f2 left(f3, 4) check_row_ts(row_start, row_end) 1 8 8 SHOR HISTORICAL ROW 2 8 8 LONG HISTORICAL ROW drop table t1; +# Shorter case for clustered index (MDEV-25004) +create table t1 ( +y int primary key, r int, f int, key (r), +foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +y r f check_row_ts(row_start, row_end) +1 6 6 HISTORICAL ROW +2 6 6 HISTORICAL ROW +drop tables t1; +# Secondary unique index +create table t1 ( +y int unique null, r int, f int, key (r), +foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +y r f check_row_ts(row_start, row_end) +1 6 6 HISTORICAL ROW +2 6 6 HISTORICAL ROW +drop tables t1; +# Non-unique index cannot be fixed because it does not trigger duplicate error +create table t1 ( +y int, r int, f int, key (y), key (r), +foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +y r f check_row_ts(row_start, row_end) +1 6 6 HISTORICAL ROW +2 6 NULL ERROR: row_end == row_start +2 6 6 HISTORICAL ROW +drop tables t1; # # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table # diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index 0900e424bd0..b6562818880 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -264,6 +264,8 @@ select * from t; select * from t for system_time all; insert into t values (4, 0); select * from t for system_time all; +alter table t add c int, drop system versioning; +select * from t; create or replace table t (a int) with system versioning; insert into t values (1), (2), (3); diff --git a/mysql-test/suite/versioning/t/delete_history.test b/mysql-test/suite/versioning/t/delete_history.test index dae7ff2db9b..f636b5a22fe 100644 --- a/mysql-test/suite/versioning/t/delete_history.test +++ b/mysql-test/suite/versioning/t/delete_history.test @@ -2,6 +2,9 @@ --source include/have_partition.inc --source suite/versioning/engines.inc +set @saved_frequency= @@global.innodb_purge_rseg_truncate_frequency; +set global innodb_purge_rseg_truncate_frequency= 1; + create table t (a int); --error ER_VERS_NOT_VERSIONED delete history from t before system_time now(); @@ -164,4 +167,26 @@ select * from t1; drop prepare stmt; drop table t1; +--echo # +--echo # MDEV-25004 Missing row in FTS_DOC_ID_INDEX during DELETE HISTORY +--echo # +create table t1 (a integer, c0 varchar(255), fulltext key (c0)) +with system versioning engine innodb; +set system_versioning_alter_history= keep; +alter table t1 drop system versioning; +alter table t1 add system versioning; +insert into t1 values (1, 'politician'); +update t1 set c0= 'criminal'; +--source suite/innodb/include/wait_all_purged.inc +delete history from t1; +drop table t1; + +create table t1 (id int primary key, ftx varchar(255)) +with system versioning engine innodb; +insert into t1 values (1, 'c'); +delete from t1; +alter table t1 add fulltext key(ftx); +drop table t1; + +set global innodb_purge_rseg_truncate_frequency= @saved_frequency; --source suite/versioning/common_finish.inc diff --git a/mysql-test/suite/versioning/t/foreign.test b/mysql-test/suite/versioning/t/foreign.test index 1c834719e0f..f4e4fa7a354 100644 --- a/mysql-test/suite/versioning/t/foreign.test +++ b/mysql-test/suite/versioning/t/foreign.test @@ -476,6 +476,39 @@ select pk, f1, f2, left(f3, 4), check_row_ts(row_start, row_end) from t1 for sys # cleanup drop table t1; +--echo # Shorter case for clustered index (MDEV-25004) +create table t1 ( + y int primary key, r int, f int, key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # Secondary unique index +create table t1 ( + y int unique null, r int, f int, key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + +--echo # Non-unique index cannot be fixed because it does not trigger duplicate error +create table t1 ( + y int, r int, f int, key (y), key (r), + foreign key (f) references t1 (r) on delete set null) +with system versioning engine innodb; + +insert into t1 values (1, 6, 6), (2, 6, 6); +delete from t1; +select *, check_row_ts(row_start, row_end) from t1 for system_time all; +drop tables t1; + --echo # --echo # MDEV-21555 Assertion secondary index is out of sync on delete from versioned table --echo # -- cgit v1.2.1 From 72e2d1d2201c2da23777d8fb89e078475e0c1371 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 27 Dec 2022 00:02:02 +0300 Subject: MDEV-25004 Refactorings * Avoid some pessimization * Slightly smaller upgrade dataset * Simplify vers_row_same_trx() and its caller --- mysql-test/std_data/versioning/ibdata1.gz | Bin 51165 -> 41597 bytes mysql-test/suite/innodb_fts/t/versioning.test | 7 ++++++- 2 files changed, 6 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/std_data/versioning/ibdata1.gz b/mysql-test/std_data/versioning/ibdata1.gz index bddd2307181..345217ba593 100644 Binary files a/mysql-test/std_data/versioning/ibdata1.gz and b/mysql-test/std_data/versioning/ibdata1.gz differ diff --git a/mysql-test/suite/innodb_fts/t/versioning.test b/mysql-test/suite/innodb_fts/t/versioning.test index b492f9c9d98..286597fba39 100644 --- a/mysql-test/suite/innodb_fts/t/versioning.test +++ b/mysql-test/suite/innodb_fts/t/versioning.test @@ -12,7 +12,7 @@ # Examples # # export OLD_BINDIR="/home/midenok/src/mariadb/10.3b/build" -# mtr innodb_fts.versioning,orig_stopword,prepare +# ./mtr innodb_fts.versioning,prepare # if ($MTR_COMBINATION_PREPARE) @@ -56,11 +56,16 @@ if ($MTR_COMBINATION_PREPARE) --exec mkdir -p $std_dir --exec cp -af $datadir/ibdata1 $datadir/test/*.frm $std_dir + # zero out the doublewrite buffer + --exec dd if=/dev/zero of=$std_dir/ibdata1 bs=16k seek=64 count=128 conv=notrunc --exec gzip -9f $std_dir/ibdata1 $std_dir/*.frm } if ($MTR_COMBINATION_UPGRADE) { +--disable_query_log +call mtr.add_suppression("InnoDB: Table `mysql`.\`innodb_(table|index)_stats`"); +--enable_query_log --source include/shutdown_mysqld.inc --exec rm -f $datadir/test/*.ibd $datadir/ib* --exec cp -af $std_dir/ibdata1.gz $datadir -- cgit v1.2.1 From f8adc47b698ef8d347fd36bffff90b237491eceb Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Tue, 6 Dec 2022 15:48:13 +0100 Subject: MDEV-19071 Wrong results when using STDDEV_SAMP() and view --- mysql-test/main/func_group.result | 14 ++++++++++++++ mysql-test/main/func_group.test | 11 +++++++++++ .../suite/gcol/r/gcol_blocked_sql_funcs_innodb.result | 2 +- .../suite/gcol/r/gcol_blocked_sql_funcs_myisam.result | 2 +- mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result | 2 +- 5 files changed, 28 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 0f3169e330f..0f80d14b603 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -2552,5 +2552,19 @@ DROP TABLE t1; # SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time'; # +# MDEV-19071 Wrong results when using STDDEV_SAMP() and view +# +create table t1(i int); +insert into t1 values (1),(2),(3),(4),(5); +create view v1 as select stddev_samp(i),stddev_pop(i),stddev(i),std(i) from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select stddev_samp(`t1`.`i`) AS `stddev_samp(i)`,std(`t1`.`i`) AS `stddev_pop(i)`,std(`t1`.`i`) AS `stddev(i)`,std(`t1`.`i`) AS `std(i)` from `t1` latin1 latin1_swedish_ci +select * from v1; +stddev_samp(i) stddev_pop(i) stddev(i) std(i) +1.5811 1.4142 1.4142 1.4142 +drop view v1; +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 862ea453b46..6b3a15fd45e 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -1789,6 +1789,17 @@ DROP TABLE t1; SET STATEMENT sql_mode=ONLY_FULL_GROUP_BY FOR EXECUTE IMMEDIATE 'ALTER TABLE mysql.time_zone_transition ORDER BY Time_zone_id, Transition_time'; +--echo # +--echo # MDEV-19071 Wrong results when using STDDEV_SAMP() and view +--echo # +create table t1(i int); +insert into t1 values (1),(2),(3),(4),(5); +create view v1 as select stddev_samp(i),stddev_pop(i),stddev(i),std(i) from t1; +show create view v1; +select * from v1; +drop view v1; +drop table t1; + --echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result index b9fe877b0f2..6b7faa18da8 100644 --- a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result @@ -129,7 +129,7 @@ create table t1 (a int, b int generated always as (stddev_pop(a)) virtual); ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` # STDDEV_SAMP() create table t1 (a int, b int generated always as (stddev_samp(a)) virtual); -ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` +ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b` # STDDEV() create table t1 (a int, b int generated always as (stddev(a)) virtual); ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` diff --git a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result index 23fdea42488..99921c658b3 100644 --- a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result @@ -131,7 +131,7 @@ create table t1 (a int, b int generated always as (stddev_pop(a)) virtual); ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` # STDDEV_SAMP() create table t1 (a int, b int generated always as (stddev_samp(a)) virtual); -ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` +ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b` # STDDEV() create table t1 (a int, b int generated always as (stddev(a)) virtual); ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` diff --git a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result index 19e8efb06ad..1cf24194c5b 100644 --- a/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result +++ b/mysql-test/suite/vcol/r/vcol_blocked_sql_funcs.result @@ -191,7 +191,7 @@ create or replace table t1 (a int, b int as (stddev_pop(a))); ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` # STDDEV_SAMP() create or replace table t1 (a int, b int as (stddev_samp(a))); -ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` +ERROR HY000: Function or expression 'stddev_samp()' cannot be used in the GENERATED ALWAYS AS clause of `b` # STDDEV() create or replace table t1 (a int, b int as (stddev(a))); ERROR HY000: Function or expression 'std()' cannot be used in the GENERATED ALWAYS AS clause of `b` -- cgit v1.2.1 From eba099184e1f6704894694ea41f97f216eae5f21 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 5 Dec 2022 00:21:28 +0100 Subject: MDEV-30151 parse error 1=2 not between/in the parser couldn't parse `1=2 not between 3 and 5` after `2` it expected only NOT2_SYM, but not NOT_SYM (visible from the sql_yacc.output file), which resulted in Syntax error ... near 'not between 3 and 4' The parser was confused by a rather low NOT_SYM precedence and %prec BETWEEN_SYM didn't resolve this confusion. As a fix, let's remove any %precedence from NOT_SYM and specify %prec explicitly in the only place where it matters for NOT_SYM. In other places, such as for NOT BETWEEN, NOT_SYM won't have a precedence, so bison won't be confused about it. --- mysql-test/main/parser.result | 11 +++++++++++ mysql-test/main/parser.test | 8 ++++++++ 2 files changed, 19 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result index 0bb4e82c8b8..f44478727ae 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1866,4 +1866,15 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp EXECUTE IMMEDIATE 'CREATE PROCEDURE p() UPDATE t SET c=\'\'"abc'; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"abc' at line 1 SET @@sql_mode=@save_sql_mode; +# +# MDEV-30151 parse error 1=2 not between/in +# +select 1=2 not in (3,4); +1=2 not in (3,4) +1 +select 1=2 not between 3 and 4; +1=2 not between 3 and 4 +1 +# # End of 10.3 tests +# diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index 9df18c50ee3..cfe4f9d6f53 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1673,4 +1673,12 @@ EXECUTE IMMEDIATE 'CREATE PROCEDURE p() UPDATE t SET c=\'\'"abc'; SET @@sql_mode=@save_sql_mode; +--echo # +--echo # MDEV-30151 parse error 1=2 not between/in +--echo # +select 1=2 not in (3,4); +select 1=2 not between 3 and 4; + +--echo # --echo # End of 10.3 tests +--echo # -- cgit v1.2.1