diff options
149 files changed, 2784 insertions, 1991 deletions
diff --git a/.gitmodules b/.gitmodules index 921a4782f4f..30f950b00e7 100644 --- a/.gitmodules +++ b/.gitmodules @@ -1,13 +1,17 @@ [submodule "libmariadb"] path = libmariadb url = https://github.com/MariaDB/mariadb-connector-c.git + ignore = all [submodule "storage/rocksdb/rocksdb"] path = storage/rocksdb/rocksdb url = https://github.com/facebook/rocksdb.git + ignore = all [submodule "wsrep-lib"] path = wsrep-lib url = https://github.com/codership/wsrep-lib.git branch = master + ignore = all [submodule "extra/wolfssl/wolfssl"] path = extra/wolfssl/wolfssl url = https://github.com/wolfSSL/wolfssl.git + ignore = all diff --git a/debian/additions/innotop/innotop b/debian/additions/innotop/innotop index 5ffd8180c34..15ab47c8fc6 100644 --- a/debian/additions/innotop/innotop +++ b/debian/additions/innotop/innotop @@ -4937,7 +4937,7 @@ sub noecho_password { }; if ( $EVAL_ERROR ) { - die "Cannot read respose; is Term::ReadKey installed? $EVAL_ERROR"; + die "Cannot read response; is Term::ReadKey installed? $EVAL_ERROR"; } return $response; } diff --git a/debian/additions/mysqlreport b/debian/additions/mysqlreport index 61fef3f89ee..2d9b04c7eac 100755 --- a/debian/additions/mysqlreport +++ b/debian/additions/mysqlreport @@ -705,7 +705,7 @@ sub set_myisam_vals { print "set_myisam_vals\n" if $op{debug}; -# should be moved elsewere +# should be moved elsewhere $questions = $stats{'Questions'}; $key_read_ratio = sprintf "%.2f", @@ -1366,7 +1366,7 @@ format QCACHE = __ Query Cache _________________________________________________________ Memory usage @>>>>>> of @>>>>>> %Usage: @>>>>> make_short($qc_mem_used, 1), make_short($vars{'query_cache_size'}, 1), perc($qc_mem_used, $vars{'query_cache_size'}) -Block Fragmnt @>>>>>% +Block Fragment @>>>>>% perc($stats{'Qcache_free_blocks'}, $stats{'Qcache_total_blocks'}) Hits @>>>>>> @>>>>>/s make_short($stats{'Qcache_hits'}), t($stats{'Qcache_hits'}) diff --git a/debian/mariadb-server-10.4.postinst b/debian/mariadb-server-10.4.postinst index 0a191e75ef1..cff14fcb03f 100644 --- a/debian/mariadb-server-10.4.postinst +++ b/debian/mariadb-server-10.4.postinst @@ -99,7 +99,7 @@ EOF # This is important to avoid dataloss when there is a removed # mysql-server version from Woody lying around which used the same - # data directory and then somewhen gets purged by the admin. + # data directory and then somehow gets purged by the admin. db_set mariadb-server/postrm_remove_database false || true # Clean up old flags before setting new one diff --git a/extra/mariabackup/backup_copy.cc b/extra/mariabackup/backup_copy.cc index 26bb245c760..f24b068fdf4 100644 --- a/extra/mariabackup/backup_copy.cc +++ b/extra/mariabackup/backup_copy.cc @@ -1540,8 +1540,6 @@ bool backup_start(CorruptedPages &corrupted_pages) if (!write_galera_info(mysql_connection)) { return(false); } - // copied from xtrabackup. what is it needed for here? - write_current_binlog_file(mysql_connection); } if (opt_binlog_info == BINLOG_INFO_ON) { diff --git a/extra/mariabackup/backup_mysql.cc b/extra/mariabackup/backup_mysql.cc index f814a8e13a1..b895f8c4561 100644 --- a/extra/mariabackup/backup_mysql.cc +++ b/extra/mariabackup/backup_mysql.cc @@ -1448,14 +1448,18 @@ write_galera_info(MYSQL *connection) if ((state_uuid == NULL && state_uuid55 == NULL) || (last_committed == NULL && last_committed55 == NULL)) { - msg("Failed to get master wsrep state from SHOW STATUS."); - result = false; + msg("Warning: failed to get master wsrep state from SHOW STATUS."); + result = true; goto cleanup; } result = backup_file_printf(XTRABACKUP_GALERA_INFO, "%s:%s\n", state_uuid ? state_uuid : state_uuid55, last_committed ? last_committed : last_committed55); + if (result) + { + write_current_binlog_file(connection); + } cleanup: free_mysql_variables(status); diff --git a/include/CMakeLists.txt b/include/CMakeLists.txt index a7b98a11050..9a3f820e62f 100644 --- a/include/CMakeLists.txt +++ b/include/CMakeLists.txt @@ -25,6 +25,7 @@ SET(HEADERS mysql.h mysql_com.h mysql_com_server.h + mariadb_capi_rename.h pack.h my_byteorder.h byte_order_generic.h diff --git a/include/heap.h b/include/heap.h index ca6efa48f1b..b1ce42970e0 100644 --- a/include/heap.h +++ b/include/heap.h @@ -33,12 +33,12 @@ extern "C" { #include "my_compare.h" #include "my_tree.h" - /* defines used by heap-funktions */ + /* defines used by heap-functions */ #define HP_MAX_LEVELS 4 /* 128^5 records is enough */ #define HP_PTRS_IN_NOD 128 - /* struct used with heap_funktions */ + /* struct used with heap_functions */ typedef struct st_heapinfo /* Struct from heap_info */ { @@ -107,7 +107,7 @@ typedef struct st_heap_block ulong last_allocated; /* number of records there is allocated space for */ } HP_BLOCK; -struct st_heap_info; /* For referense */ +struct st_heap_info; /* For reference */ typedef struct st_hp_keydef /* Key definition with open */ { diff --git a/include/json_lib.h b/include/json_lib.h index bb649928eaa..2ec3e9de8f8 100644 --- a/include/json_lib.h +++ b/include/json_lib.h @@ -372,7 +372,7 @@ int json_find_paths_next(json_engine_t *je, json_find_paths_t *state); /* - Converst JSON string constant into ordinary string constant + Convert JSON string constant into ordinary string constant which can involve unpacking json escapes and changing character set. Returns negative integer in the case of an error, the length of the result otherwise. @@ -383,7 +383,7 @@ int json_unescape(CHARSET_INFO *json_cs, uchar *res, uchar *res_end); /* - Converst ordinary string constant into JSON string constant. + Convert ordinary string constant into JSON string constant. which can involve appropriate escaping and changing character set. Returns negative integer in the case of an error, the length of the result otherwise. diff --git a/include/m_ctype.h b/include/m_ctype.h index 187c8710929..484cd0a657e 100644 --- a/include/m_ctype.h +++ b/include/m_ctype.h @@ -14,7 +14,7 @@ Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ /* - A better inplementation of the UNIX ctype(3) library. + A better implementation of the UNIX ctype(3) library. */ #ifndef _m_ctype_h @@ -123,7 +123,7 @@ uint16 *my_uca_contraction2_weight(const MY_CONTRACTIONS *c, my_wc_t wc1, my_wc_t wc2); -/* Collation weights on a single level (e.g. primary, secondary, tertiarty) */ +/* Collation weights on a single level (e.g. primary, secondary, tertiary) */ typedef struct my_uca_level_info_st { my_wc_t maxchar; @@ -998,7 +998,7 @@ uint32 my_convert(char *to, uint32 to_length, CHARSET_INFO *to_cs, An extended version of my_convert(), to pass non-default mb_wc() and wc_mb(). For example, String::copy_printable() which is used in Protocol::store_warning() uses this to escape control - and non-convertable characters. + and non-convertible characters. */ uint32 my_convert_using_func(char *to, size_t to_length, CHARSET_INFO *to_cs, my_charset_conv_wc_mb mb_wc, @@ -1086,7 +1086,7 @@ size_t my_convert_fix(CHARSET_INFO *dstcs, char *dst, size_t dst_length, @param str - the beginning of the string @param end - the string end (the next byte after the string) @return >0, for a multi-byte character - @rerurn 0, for a single byte character, broken sequence, empty string. + @return 0, for a single byte character, broken sequence, empty string. */ static inline uint my_ismbchar(CHARSET_INFO *cs, const char *str, const char *end) diff --git a/include/ma_dyncol.h b/include/ma_dyncol.h index 4f05b425afd..4468e23c4c4 100644 --- a/include/ma_dyncol.h +++ b/include/ma_dyncol.h @@ -78,7 +78,7 @@ enum enum_dyncol_func_result ER_DYNCOL_YES= 1, /* For functions returning 0/1 */ ER_DYNCOL_FORMAT= -1, /* Wrong format of the encoded string */ ER_DYNCOL_LIMIT= -2, /* Some limit reached */ - ER_DYNCOL_RESOURCE= -3, /* Out of resourses */ + ER_DYNCOL_RESOURCE= -3, /* Out of resources */ ER_DYNCOL_DATA= -4, /* Incorrect input data */ ER_DYNCOL_UNKNOWN_CHARSET= -5, /* Unknown character set */ ER_DYNCOL_TRUNCATED= 2 /* OK, but data was truncated */ diff --git a/include/my_base.h b/include/my_base.h index d045251778f..32ef6f7fe85 100644 --- a/include/my_base.h +++ b/include/my_base.h @@ -126,7 +126,7 @@ enum ha_extra_function { HA_EXTRA_NO_USER_CHANGE=9, /* No user is allowed to write */ HA_EXTRA_KEY_CACHE=10, HA_EXTRA_NO_KEY_CACHE=11, - HA_EXTRA_WAIT_LOCK=12, /* Wait until file is avalably (def) */ + HA_EXTRA_WAIT_LOCK=12, /* Wait until file is available (def) */ HA_EXTRA_NO_WAIT_LOCK=13, /* If file is locked, return quickly */ HA_EXTRA_WRITE_CACHE=14, /* Use write cache in ha_write() */ HA_EXTRA_FLUSH_CACHE=15, /* flush write_record_cache */ @@ -287,7 +287,7 @@ enum ha_base_keytype { This flag can be calculated -- it's based on key lengths comparison. */ #define HA_KEY_HAS_PART_KEY_SEG 65536 -/* Internal Flag Can be calcaluted */ +/* Internal Flag Can be calculated */ #define HA_INVISIBLE_KEY 2<<18 /* Automatic bits in key-flag */ @@ -434,9 +434,9 @@ enum ha_base_keytype { #define HA_ERR_FIRST 120 /* Copy of first error nr.*/ #define HA_ERR_KEY_NOT_FOUND 120 /* Didn't find key on read or update */ -#define HA_ERR_FOUND_DUPP_KEY 121 /* Dupplicate key on write */ +#define HA_ERR_FOUND_DUPP_KEY 121 /* Duplicate key on write */ #define HA_ERR_INTERNAL_ERROR 122 /* Internal error */ -#define HA_ERR_RECORD_CHANGED 123 /* Uppdate with is recoverable */ +#define HA_ERR_RECORD_CHANGED 123 /* Update with is recoverable */ #define HA_ERR_WRONG_INDEX 124 /* Wrong index given to function */ #define HA_ERR_CRASHED 126 /* Indexfile is crashed */ #define HA_ERR_WRONG_IN_RECORD 127 /* Record-file is crashed */ @@ -452,7 +452,7 @@ enum ha_base_keytype { #define HA_ERR_UNSUPPORTED 138 /* unsupported extension used */ #define HA_ERR_TO_BIG_ROW 139 /* Too big row */ #define HA_WRONG_CREATE_OPTION 140 /* Wrong create option */ -#define HA_ERR_FOUND_DUPP_UNIQUE 141 /* Dupplicate unique on write */ +#define HA_ERR_FOUND_DUPP_UNIQUE 141 /* Duplicate unique on write */ #define HA_ERR_UNKNOWN_CHARSET 142 /* Can't open charset */ #define HA_ERR_WRONG_MRG_TABLE_DEF 143 /* conflicting tables in MERGE */ #define HA_ERR_CRASHED_ON_REPAIR 144 /* Last (automatic?) repair failed */ @@ -496,7 +496,7 @@ enum ha_base_keytype { illegal data being read */ #define HA_ERR_NEW_FILE 172 /* New file format */ #define HA_ERR_ROWS_EVENT_APPLY 173 /* The event could not be processed - no other hanlder error happened */ + no other handler error happened */ #define HA_ERR_INITIALIZATION 174 /* Error during initialization */ #define HA_ERR_FILE_TOO_SHORT 175 /* File too short */ #define HA_ERR_WRONG_CRC 176 /* Wrong CRC on page */ diff --git a/include/my_decimal_limits.h b/include/my_decimal_limits.h index ac1df83bb62..5bfd54d6748 100644 --- a/include/my_decimal_limits.h +++ b/include/my_decimal_limits.h @@ -32,7 +32,7 @@ digits in one our big digit decreased by 1 (because we always put decimal point on the border of our big digits)) - With normal precession we can handle 65 digits. MariaDB can store in + With normal precision we can handle 65 digits. MariaDB can store in the .frm up to 63 digits. By default we use DECIMAL_NOT_SPECIFIED digits when converting strings to decimal, so we don't want to set this too high. To not use up all digits for the scale we limit the number of decimals to diff --git a/include/my_dir.h b/include/my_dir.h index 930d54ca72f..861599d8fc0 100644 --- a/include/my_dir.h +++ b/include/my_dir.h @@ -70,7 +70,7 @@ typedef struct my_stat dev_t st_rdev; /* more major & minor device numbers (???) */ off_t st_size; /* size of file */ time_t st_atime; /* time for last read */ - time_t st_mtime; /* time for last contens modify */ + time_t st_mtime; /* time for last contents modify */ time_t st_ctime; /* time for last inode or contents modify */ } MY_STAT; diff --git a/include/my_global.h b/include/my_global.h index b60fa59dfaa..1e1821e2f25 100644 --- a/include/my_global.h +++ b/include/my_global.h @@ -670,7 +670,7 @@ typedef SOCKET_SIZE_TYPE size_socket; the mismatch of CRT and mysys file IO usage on Windows at runtime. CRT file descriptors can be in the range 0-2047, whereas descriptors returned by my_open() will start with 2048. If a file descriptor with value less then - MY_FILE_MIN is passed to mysys IO function, chances are it stemms from + MY_FILE_MIN is passed to mysys IO function, chances are it stems from open()/fileno() and not my_open()/my_fileno. For Posix, mysys functions are light wrappers around libc, and MY_FILE_MIN @@ -891,7 +891,7 @@ typedef long long my_ptrdiff_t; #define STDCALL #endif -/* Typdefs for easyier portability */ +/* Typdefs for easier portability */ #ifndef HAVE_UCHAR typedef unsigned char uchar; /* Short for unsigned char */ diff --git a/include/my_sys.h b/include/my_sys.h index 437f2d946e6..91610f15a50 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -443,7 +443,7 @@ typedef struct st_io_cache /* Used when caching files */ /* A caller will use my_b_read() macro to read from the cache if the data is already in cache, it will be simply copied with - memcpy() and internal variables will be accordinging updated with + memcpy() and internal variables will be accordingly updated with no functions invoked. However, if the data is not fully in the cache, my_b_read() will call read_function to fetch the data. read_function must never be invoked directly. @@ -487,7 +487,7 @@ typedef struct st_io_cache /* Used when caching files */ myf myflags; /* Flags used to my_read/my_write */ /* alloced_buffer is set to the size of the buffer allocated for the IO_CACHE. - Includes the overhead(storing key to ecnrypt and decrypt) for encryption. + Includes the overhead(storing key to encrypt and decrypt) for encryption. Set to 0 if nothing is allocated. Currently READ_NET is the only one that will use a buffer allocated somewhere else @@ -997,7 +997,7 @@ static inline my_hrtime_t make_hr_time(my_time_t time, ulong time_sec_part) #define my_munmap(a,b) munmap((a),(b)) #else -/* not a complete set of mmap() flags, but only those that nesessary */ +/* not a complete set of mmap() flags, but only those that necessary */ #define PROT_READ 1 #define PROT_WRITE 2 #define MAP_NORESERVE 0 diff --git a/include/myisam.h b/include/myisam.h index f2e31bb9f60..43c5cf76222 100644 --- a/include/myisam.h +++ b/include/myisam.h @@ -15,7 +15,7 @@ along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ -/* This file should be included when using myisam_funktions */ +/* This file should be included when using myisam_functions */ #ifndef _myisam_h #define _myisam_h @@ -159,7 +159,7 @@ typedef struct st_mi_create_info my_bool with_auto_increment; } MI_CREATE_INFO; -struct st_myisam_info; /* For referense */ +struct st_myisam_info; /* For reference */ struct st_mi_isam_share; typedef struct st_myisam_info MI_INFO; struct st_mi_s_param; diff --git a/include/myisammrg.h b/include/myisammrg.h index 76286000ff5..bfe14f5e1dd 100644 --- a/include/myisammrg.h +++ b/include/myisammrg.h @@ -14,7 +14,7 @@ along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ -/* This file should be included when using merge_isam_funktions */ +/* This file should be included when using merge_isam_functions */ #ifndef _myisammrg_h #define _myisammrg_h @@ -51,7 +51,7 @@ typedef struct st_mymerge_info /* Struct from h_info */ ulonglong data_file_length; ulonglong dupp_key_pos; /* Offset of the Duplicate key in the merge table */ uint reclength; /* Recordlength */ - int errkey; /* With key was dupplicated on err */ + int errkey; /* With key was duplicated on err */ uint options; /* HA_OPTION_... used */ ulong *rec_per_key; /* for sql optimizing */ } MYMERGE_INFO; diff --git a/include/mysql.h b/include/mysql.h index 114d763e58c..2a96effcf93 100644 --- a/include/mysql.h +++ b/include/mysql.h @@ -655,7 +655,7 @@ enum enum_mysql_stmt_state length - On input: in case when lengths of input values are different for each execute, you can set this to - point at a variable containining value length. This + point at a variable containing value length. This way the value length can be different in each execute. If length is not NULL, buffer_length is not used. Note, length can even point at buffer_length if diff --git a/include/mysql/plugin_ftparser.h b/include/mysql/plugin_ftparser.h index 8db8712926f..511b34bb234 100644 --- a/include/mysql/plugin_ftparser.h +++ b/include/mysql/plugin_ftparser.h @@ -101,7 +101,7 @@ enum enum_ft_token_type <0 Must not be present 0 Neither; the word is optional but its presence increases the relevance With the default settings of the ft_boolean_syntax system variable, - >0 corresponds to the '+' operator, <0 corrresponds to the '-' operator, + >0 corresponds to the '+' operator, <0 corresponds to the '-' operator, and 0 means neither operator was used. weight_adjust: A weighting factor that determines how much a match diff --git a/include/mysql/psi/mysql_thread.h b/include/mysql/psi/mysql_thread.h index eab87402140..06db6890882 100644 --- a/include/mysql/psi/mysql_thread.h +++ b/include/mysql/psi/mysql_thread.h @@ -588,7 +588,7 @@ typedef struct st_mysql_cond mysql_cond_t; This function creates both the thread instrumentation and a thread. @c mysql_thread_create is a replacement for @c pthread_create. The parameter P4 (or, if it is NULL, P1) will be used as the - instrumented thread "indentity". + instrumented thread "identity". Providing a P1 / P4 parameter with a different value for each call will on average improve performances, since this thread identity value is used internally to randomize access to data and prevent contention. diff --git a/include/mysql/service_base64.h b/include/mysql/service_base64.h index 43b5ba323ba..fa54b4923d4 100644 --- a/include/mysql/service_base64.h +++ b/include/mysql/service_base64.h @@ -29,7 +29,7 @@ extern "C" { #include <stdlib.h> #endif -/* Allow multuple chunks 'AAA= AA== AA==', binlog uses this */ +/* Allow multiple chunks 'AAA= AA== AA==', binlog uses this */ #define MY_BASE64_DECODE_ALLOW_MULTIPLE_CHUNKS 1 extern struct base64_service_st { diff --git a/include/mysql/service_my_snprintf.h b/include/mysql/service_my_snprintf.h index 6757a658fb6..fcc832fc1f1 100644 --- a/include/mysql/service_my_snprintf.h +++ b/include/mysql/service_my_snprintf.h @@ -40,7 +40,7 @@ @post The syntax of a format string is generally the same: % <flag> <width> <precision> <length modifier> <format> - where everithing but the format is optional. + where everything but the format is optional. Three one-character flags are recognized: '0' has the standard zero-padding semantics; diff --git a/include/mysql/service_thd_timezone.h b/include/mysql/service_thd_timezone.h index 89e75274cf0..667137745f4 100644 --- a/include/mysql/service_thd_timezone.h +++ b/include/mysql/service_thd_timezone.h @@ -16,7 +16,7 @@ /** @file - This service provdes functions to convert between my_time_t and + This service provides functions to convert between my_time_t and MYSQL_TIME taking into account the current value of the time_zone session variable. diff --git a/include/queues.h b/include/queues.h index e9948857dde..9cc7c15a980 100644 --- a/include/queues.h +++ b/include/queues.h @@ -24,7 +24,7 @@ */ /* - Code for generell handling of priority Queues. + Code for general handling of priority Queues. Implementation of queues from "Algorithms in C" by Robert Sedgewick. */ diff --git a/include/waiting_threads.h b/include/waiting_threads.h index f785cc8988b..5f708f42f96 100644 --- a/include/waiting_threads.h +++ b/include/waiting_threads.h @@ -81,7 +81,7 @@ typedef struct st_wt_thd { 1. Latest Keep all weights equal. 2. Random - Assight weights at random. + Assign weights at random. (variant: modify a weight randomly before every lock request) 3. Youngest Set weight to -NOW() 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/include/start_mysqld.inc b/mysql-test/include/start_mysqld.inc index b4fe116fe86..6e448cb2efd 100644 --- a/mysql-test/include/start_mysqld.inc +++ b/mysql-test/include/start_mysqld.inc @@ -12,25 +12,32 @@ if (!$restart_noprint) --let $restart_noprint=0 } +--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: $restart_parameters" + --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: with restart_parameters" + --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" + --exec echo "# $restart_cmd" } } 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}; diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index eb50b431548..7aa87fcbc6e 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 0194caae25c..3cc3b66b9c3 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/func_group.result b/mysql-test/main/func_group.result index 5908b41c61a..3d521f2e00b 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -2553,5 +2553,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 b5e9b5d93df..ac4ca32e7f5 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -1791,5 +1791,16 @@ 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/main/gis.test b/mysql-test/main/gis.test index 6a1a02fe74d..a93ee736566 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -3092,12 +3092,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 diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 649d1292b39..6686d451dd8 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -2234,8 +2234,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 77d0df59e2f..b9ed6326350 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -1941,8 +1941,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 9345b5c6d2b..25597d46b75 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -4471,6 +4471,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 d1b0a7d8c84..78f0976ce6a 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -1922,6 +1922,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/parser.result b/mysql-test/main/parser.result index 6f05caec0bd..32cc70bf50c 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1870,8 +1870,19 @@ 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 # +# # MDEV-19540: 10.4 allow lock options with SELECT in brackets # which previous version do not # diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index e4ca1823c60..13126b77f78 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1678,7 +1678,15 @@ 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 # --echo # --echo # MDEV-19540: 10.4 allow lock options with SELECT in brackets diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 12214ca8bc8..8c31545eb84 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6735,7 +6735,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); @@ -6765,7 +6767,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 78a2c946491..0e2dce1fb70 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6438,7 +6438,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); @@ -6461,7 +6463,6 @@ drop view db1.v1; drop table db1.t1; drop database db1; -create database test; use test; --echo # diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index f7a581b728d..4d1b3cd6aa5 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1795,7 +1795,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"); @@ -1806,7 +1806,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]); } @@ -4659,6 +4659,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/; @@ -4666,7 +4667,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 { diff --git a/mysql-test/std_data/versioning/articles.frm.gz b/mysql-test/std_data/versioning/articles.frm.gz Binary files differnew file mode 100644 index 00000000000..fd3a0a8c0d4 --- /dev/null +++ b/mysql-test/std_data/versioning/articles.frm.gz diff --git a/mysql-test/std_data/versioning/articles2.frm.gz b/mysql-test/std_data/versioning/articles2.frm.gz Binary files differnew file mode 100644 index 00000000000..70f20c2e081 --- /dev/null +++ b/mysql-test/std_data/versioning/articles2.frm.gz diff --git a/mysql-test/std_data/versioning/ibdata1.gz b/mysql-test/std_data/versioning/ibdata1.gz Binary files differnew file mode 100644 index 00000000000..345217ba593 --- /dev/null +++ b/mysql-test/std_data/versioning/ibdata1.gz diff --git a/mysql-test/std_data/versioning/user_stopword.frm.gz b/mysql-test/std_data/versioning/user_stopword.frm.gz Binary files differnew file mode 100644 index 00000000000..4a17265fabb --- /dev/null +++ b/mysql-test/std_data/versioning/user_stopword.frm.gz 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/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/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/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/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 ca1d37fbb2d..b0ed4322fa0 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -671,7 +671,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/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; 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; 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 ccd2db7425a..00000000000 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result +++ /dev/null @@ -1,761 +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 Engine-independent statistics collected -test.articles analyze Warning Engine-independent statistics are not collected for column 'body' -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 Engine-independent statistics collected -test.articles analyze Warning Engine-independent statistics are not collected for column 'body' -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/misc_debug.result b/mysql-test/suite/innodb_fts/r/misc_debug.result index 022c389a47e..d582dfa53ed 100644 --- a/mysql-test/suite/innodb_fts/r/misc_debug.result +++ b/mysql-test/suite/innodb_fts/r/misc_debug.result @@ -60,7 +60,7 @@ SET @save_dbug= @@debug_dbug; CREATE TABLE t1 (b CHAR(12), FULLTEXT KEY(b)) engine=InnoDB; SET debug_dbug='+d,ib_create_table_fail_too_many_trx'; TRUNCATE t1; -ERROR HY000: Got error -1 "Internal error < 0 (Not system error)" from storage engine InnoDB +Got one of the listed errors SET debug_dbug=@save_dbug; DROP TABLE t1; # End of 10.3 tests 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 06990528b81..aa6f51aac76 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 ( @@ -334,6 +338,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..7efd9adeeac 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, @@ -212,7 +213,17 @@ delete from t1 limit 1; # # BUG#16489: utf8 + fulltext leads to corrupt index file. # +if ($MTR_COMBINATION_ORIG) +{ truncate table t1; +} +if (!$MTR_COMBINATION_ORIG) +{ +--disable_query_log +delete from t1; +--enable_query_log +--echo truncate table t1; +} insert into t1 values('ab c d'); update t1 set a='ab c d'; select * from t1 where match a against('ab c' in boolean mode); 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 <roy@vem.ca> # --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 <tibor.simko@cern.ch> # --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 <jean-cedric.costa@ensmp.fr> # --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..31f10399bc2 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 ( @@ -54,7 +55,21 @@ SELECT * FROM fts_test WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); # Truncate table +let $vers=$MTR_COMBINATION_VERS+$MTR_COMBINATION_VERS_TRX; + +if ($vers) +{ +--disable_query_log +CREATE TABLE fts_test2 LIKE fts_test; +DROP TABLE fts_test; +RENAME TABLE fts_test2 TO fts_test; +--enable_query_log +--echo TRUNCATE TABLE fts_test; +} +if (!$vers) +{ TRUNCATE TABLE fts_test; +} DROP INDEX idx ON 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 0f29d092541..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 formated 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 formated 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 -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 formated 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 formated 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 formated 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 formated 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 formated 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 eec841b22ec..f4c5f49aa73 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 @@ -89,7 +90,7 @@ SET @save_dbug= @@debug_dbug; CREATE TABLE t1 (b CHAR(12), FULLTEXT KEY(b)) engine=InnoDB; SET debug_dbug='+d,ib_create_table_fail_too_many_trx'; ---error ER_GET_ERRNO +--error ER_GET_ERRNO,ER_VERS_NOT_SUPPORTED TRUNCATE t1; SET debug_dbug=@save_dbug; DROP TABLE t1; 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 5105a6d2fec..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 formated 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 @@ -311,15 +279,17 @@ 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 -create table user_stopword(value varchar(30)) engine = innodb; +# 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 session innodb_ft_user_stopword_table = "test/user_stopword"; -# Define a correct formated server stopword table -create table server_stopword(value varchar(30)) engine = innodb; +# Define a correct formatted server stopword table +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; @@ -411,8 +381,8 @@ 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 -create table user_stopword(value varchar(30)) engine = innodb; +# 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 session innodb_ft_user_stopword_table = "test/user_stopword"; insert into user_stopword values("mysqld"),("DBMS"); @@ -433,8 +403,8 @@ 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 -create table server_stopword(value varchar(30)) engine = innodb; +# Define a correct formatted user stopword table +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; @@ -572,8 +542,8 @@ 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 -create table user_stopword(value varchar(30)) engine = innodb; +# 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 session innodb_ft_user_stopword_table = "test/user_stopword"; # Add values into user supplied stop word table @@ -592,8 +562,8 @@ 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 -create table user_stopword_1(value varchar(30)) engine = innodb; +# Define a correct formatted user stopword table +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"); @@ -614,8 +584,8 @@ 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 -create table server_stopword(value varchar(30)) engine = innodb; +# Define a correct formatted server stopword table +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..6a16ececa60 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) @@ -23,7 +24,16 @@ INSERT INTO t1 (value) VALUES ('By default or with the IN NATURAL LANGUAGE MODE modifier') ; +let $vers=$MTR_COMBINATION_VERS+$MTR_COMBINATION_VERS_TRX; + +if ($vers) +{ +--echo TRUNCATE TABLE t1; +} +if (!$vers) +{ TRUNCATE TABLE t1; +} DROP TABLE t1; @@ -54,7 +64,14 @@ INSERT INTO t1 (value) VALUES ('collation of latin1_bin to make it case sensitive') ; +if ($vers) +{ +--echo TRUNCATE TABLE t1; +} +if (!$vers) +{ TRUNCATE TABLE t1; +} DROP TABLE t1; 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..286597fba39 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/versioning.test @@ -0,0 +1,131 @@ +--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,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 + # 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 + --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/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 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` diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index 7037ddad400..a026b68a11b 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -377,6 +377,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 4ea6c6202bc..3efddd40275 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 9cbe3b1ae1f..8efe75a1ffd 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 @@ -191,6 +193,26 @@ x 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; +# # MDEV-28201 Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON # CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING; @@ -202,3 +224,4 @@ DELETE HISTORY FROM v1; ERROR HY000: The target table v1 of the DELETE is not updatable DROP VIEW v1; DROP TABLE t1; +# End of 10.4 tests diff --git a/mysql-test/suite/versioning/r/foreign.result b/mysql-test/suite/versioning/r/foreign.result index 2dbceebb539..ae53511d7e0 100644 --- a/mysql-test/suite/versioning/r/foreign.result +++ b/mysql-test/suite/versioning/r/foreign.result @@ -445,6 +445,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 f826ba470f1..311f2d97bdf 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 e57779d07d3..cc2f923da8d 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(); @@ -194,6 +197,29 @@ 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; + +--echo # --echo # MDEV-28201 Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON --echo # CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING; @@ -206,4 +232,6 @@ DELETE HISTORY FROM v1; DROP VIEW v1; DROP TABLE t1; +--echo # End of 10.4 tests + --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 20c35028565..3548a9b301b 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 # diff --git a/sql/handler.cc b/sql/handler.cc index aebfeb17b2c..20bf32f3cdc 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -7325,7 +7325,6 @@ static Create_field *vers_init_sys_field(THD *thd, const char *field_name, int f f->flags= flags | NOT_NULL_FLAG; if (integer) { - DBUG_ASSERT(0); // Not implemented yet f->set_handler(&type_handler_vers_trx_id); f->length= MY_INT64_NUM_DECIMAL_DIGITS - 1; f->flags|= UNSIGNED_FLAG; @@ -7343,10 +7342,13 @@ static Create_field *vers_init_sys_field(THD *thd, const char *field_name, int f return f; } -static bool vers_create_sys_field(THD *thd, const char *field_name, - Alter_info *alter_info, int flags) +bool Vers_parse_info::create_sys_field(THD *thd, const char *field_name, + Alter_info *alter_info, int flags) { - Create_field *f= vers_init_sys_field(thd, field_name, flags, false); + DBUG_ASSERT(can_native >= 0); /* Requires vers_check_native() called */ + Create_field *f= vers_init_sys_field(thd, field_name, flags, + DBUG_EVALUATE_IF("sysvers_force_trx", + (bool) can_native, false)); if (!f) return true; @@ -7370,8 +7372,8 @@ bool Vers_parse_info::fix_implicit(THD *thd, Alter_info *alter_info) period= start_end_t(default_start, default_end); as_row= period; - if (vers_create_sys_field(thd, default_start, alter_info, VERS_ROW_START) || - vers_create_sys_field(thd, default_end, alter_info, VERS_ROW_END)) + if (create_sys_field(thd, default_start, alter_info, VERS_ROW_START) || + create_sys_field(thd, default_end, alter_info, VERS_ROW_END)) { return true; } @@ -7379,14 +7381,25 @@ bool Vers_parse_info::fix_implicit(THD *thd, Alter_info *alter_info) } +void Table_scope_and_contents_source_st::vers_check_native() +{ + vers_info.can_native= (db_type->db_type == DB_TYPE_PARTITION_DB || + ha_check_storage_engine_flag(db_type, + HTON_NATIVE_SYS_VERSIONING)); +} + + bool Table_scope_and_contents_source_st::vers_fix_system_fields( THD *thd, Alter_info *alter_info, const TABLE_LIST &create_table) { DBUG_ASSERT(!(alter_info->flags & ALTER_DROP_SYSTEM_VERSIONING)); - DBUG_EXECUTE_IF("sysvers_force", if (!tmp_table()) { - alter_info->flags|= ALTER_ADD_SYSTEM_VERSIONING; - options|= HA_VERSIONED_TABLE; }); + if (DBUG_EVALUATE_IF("sysvers_force", true, false) || + DBUG_EVALUATE_IF("sysvers_force_trx", true, false)) + { + alter_info->flags|= ALTER_ADD_SYSTEM_VERSIONING; + options|= HA_VERSIONED_TABLE; + } if (!vers_info.need_check(alter_info)) return false; @@ -7417,7 +7430,9 @@ bool Table_scope_and_contents_source_st::vers_fix_system_fields( { f->flags|= VERS_UPDATE_UNVERSIONED_FLAG; } - } // while (Create_field *f= it++) + } // while + + vers_check_native(); if (vers_info.fix_implicit(thd, alter_info)) return true; @@ -7470,11 +7485,7 @@ bool Table_scope_and_contents_source_st::vers_check_system_fields( if (!(alter_info->flags & ALTER_ADD_SYSTEM_VERSIONING) && !versioned_fields) return false; - bool can_native= ha_check_storage_engine_flag(db_type, - HTON_NATIVE_SYS_VERSIONING) - || db_type->db_type == DB_TYPE_PARTITION_DB; - - return vers_info.check_sys_fields(table_name, db, alter_info, can_native); + return vers_info.check_sys_fields(table_name, db, alter_info); } @@ -7487,7 +7498,8 @@ bool Vers_parse_info::fix_alter_info(THD *thd, Alter_info *alter_info, if (!need_check(alter_info) && !share->versioned) return false; - if (DBUG_EVALUATE_IF("sysvers_force", 0, share->tmp_table)) + if (DBUG_EVALUATE_IF("sysvers_force", 0, share->tmp_table) || + DBUG_EVALUATE_IF("sysvers_force_trx", 0, share->tmp_table)) { my_error(ER_VERS_NOT_SUPPORTED, MYF(0), "CREATE TEMPORARY TABLE"); return true; @@ -7732,8 +7744,7 @@ bool Create_field::vers_check_bigint(const Lex_table_name &table_name) const bool Vers_parse_info::check_sys_fields(const Lex_table_name &table_name, const Lex_table_name &db, - Alter_info *alter_info, - bool can_native) const + Alter_info *alter_info) const { if (check_conditions(table_name, db)) return true; diff --git a/sql/handler.h b/sql/handler.h index 541c408178b..3f0fc5c897f 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -2,7 +2,7 @@ #define HANDLER_INCLUDED /* Copyright (c) 2000, 2019, Oracle and/or its affiliates. - Copyright (c) 2009, 2022, MariaDB + Copyright (c) 2009, 2023, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License @@ -1984,7 +1984,8 @@ struct Vers_parse_info: public Table_period_info Vers_parse_info() : Table_period_info(STRING_WITH_LEN("SYSTEM_TIME")), versioned_fields(false), - unversioned_fields(false) + unversioned_fields(false), + can_native(-1) {} Table_period_info::start_end_t as_row; @@ -2013,6 +2014,9 @@ protected: bool need_check(const Alter_info *alter_info) const; bool check_conditions(const Lex_table_name &table_name, const Lex_table_name &db) const; + bool create_sys_field(THD *thd, const char *field_name, + Alter_info *alter_info, int flags); + public: static const Lex_ident default_start; static const Lex_ident default_end; @@ -2022,8 +2026,7 @@ public: bool fix_create_like(Alter_info &alter_info, HA_CREATE_INFO &create_info, TABLE_LIST &src_table, TABLE_LIST &table); bool check_sys_fields(const Lex_table_name &table_name, - const Lex_table_name &db, Alter_info *alter_info, - bool can_native) const; + const Lex_table_name &db, Alter_info *alter_info) const; /** At least one field was specified 'WITH/WITHOUT SYSTEM VERSIONING'. @@ -2031,6 +2034,7 @@ public: */ bool versioned_fields : 1; bool unversioned_fields : 1; + int can_native; }; /** @@ -2147,6 +2151,7 @@ struct Table_scope_and_contents_source_st: int select_count= 0); bool check_period_fields(THD *thd, Alter_info *alter_info); + void vers_check_native(); bool vers_fix_system_fields(THD *thd, Alter_info *alter_info, const TABLE_LIST &create_table); @@ -2154,7 +2159,6 @@ struct Table_scope_and_contents_source_st: const Lex_table_name &table_name, const Lex_table_name &db, int select_count= 0); - }; diff --git a/sql/item_sum.h b/sql/item_sum.h index f715c80ffaf..724581fc6ec 100644 --- a/sql/item_sum.h +++ b/sql/item_sum.h @@ -1066,7 +1066,7 @@ class Item_sum_std :public Item_sum_variance enum Sumfunctype sum_func () const { return STD_FUNC; } double val_real(); Item *result_item(THD *thd, Field *field); - const char *func_name() const { return "std("; } + const char *func_name() const { return sample ? "stddev_samp(" : "std("; } Item *copy_or_same(THD* thd); Item *get_copy(THD *thd) { return get_item_copy<Item_sum_std>(thd, this); } diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 7cfa803c73a..1a55159a0e6 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -8548,12 +8548,6 @@ mysqld_get_one_option(int optid, const struct my_option *opt, char *argument) case (int) OPT_SKIP_HOST_CACHE: opt_specialflag|= SPECIAL_NO_HOST_CACHE; break; - case (int) OPT_SKIP_RESOLVE: - if ((opt_skip_name_resolve= (argument != disabled_my_option))) - opt_specialflag|= SPECIAL_NO_RESOLVE; - else - opt_specialflag&= ~SPECIAL_NO_RESOLVE; - break; case (int) OPT_WANT_CORE: test_flags |= TEST_CORE_ON_SIGNAL; break; diff --git a/sql/mysqld.h b/sql/mysqld.h index 8e33d6488e4..60afb0b9dba 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -681,7 +681,6 @@ enum options_mysqld OPT_SERVER_ID, OPT_SILENT, OPT_SKIP_HOST_CACHE, - OPT_SKIP_RESOLVE, OPT_SLAVE_PARALLEL_MODE, OPT_SSL_CA, OPT_SSL_CAPATH, diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index afb1968c73f..211114f0d19 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2018, Oracle and/or its affiliates. - Copyright (c) 2009, 2022, MariaDB + Copyright (c) 2009, 2023, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -373,9 +373,9 @@ public: update_hostname(&host, safe_strdup_root(mem, host_arg)); } - bool check_validity(bool check_no_resolve) + bool check_validity() { - if (check_no_resolve && + if (opt_skip_name_resolve && (hostname_requires_resolving(host.hostname) || hostname_requires_resolving(proxied_host.hostname))) { @@ -2398,7 +2398,6 @@ static void push_new_user(const ACL_USER &user) static bool acl_load(THD *thd, const Grant_tables& tables) { READ_RECORD read_record_info; - bool check_no_resolve= specialflag & SPECIAL_NO_RESOLVE; char tmp_name[SAFE_NAME_LEN+1]; Sql_mode_save old_mode_save(thd); DBUG_ENTER("acl_load"); @@ -2441,7 +2440,8 @@ static bool acl_load(THD *thd, const Grant_tables& tables) host.access= host_table.get_access(); host.access= fix_rights_for_db(host.access); host.sort= get_magic_sort("hd", host.host.hostname, host.db); - if (check_no_resolve && hostname_requires_resolving(host.host.hostname)) + if (opt_skip_name_resolve && + hostname_requires_resolving(host.host.hostname)) { sql_print_warning("'host' entry '%s|%s' " "ignored in --skip-name-resolve mode.", @@ -2511,7 +2511,8 @@ static bool acl_load(THD *thd, const Grant_tables& tables) } else { - if (check_no_resolve && hostname_requires_resolving(user.host.hostname)) + if (opt_skip_name_resolve && + hostname_requires_resolving(user.host.hostname)) { sql_print_warning("'user' entry '%s@%s' " "ignored in --skip-name-resolve mode.", user.user.str, @@ -2569,7 +2570,7 @@ static bool acl_load(THD *thd, const Grant_tables& tables) sql_print_warning("Found an entry in the 'db' table with empty database name; Skipped"); continue; } - if (check_no_resolve && hostname_requires_resolving(db.host.hostname)) + if (opt_skip_name_resolve && hostname_requires_resolving(db.host.hostname)) { sql_print_warning("'db' entry '%s %s@%s' " "ignored in --skip-name-resolve mode.", @@ -2624,7 +2625,7 @@ static bool acl_load(THD *thd, const Grant_tables& tables) { ACL_PROXY_USER proxy; proxy.init(proxies_priv_table, &acl_memroot); - if (proxy.check_validity(check_no_resolve)) + if (proxy.check_validity()) continue; if (push_dynamic(&acl_proxy_users, (uchar*) &proxy)) DBUG_RETURN(TRUE); @@ -7780,7 +7781,6 @@ static bool grant_load(THD *thd, { bool return_val= 1; TABLE *t_table, *c_table, *p_table; - bool check_no_resolve= specialflag & SPECIAL_NO_RESOLVE; MEM_ROOT *save_mem_root= thd->mem_root; sql_mode_t old_sql_mode= thd->variables.sql_mode; DBUG_ENTER("grant_load"); @@ -7824,7 +7824,7 @@ static bool grant_load(THD *thd, goto end_unlock; } - if (check_no_resolve) + if (opt_skip_name_resolve) { if (hostname_requires_resolving(mem_check->host.hostname)) { @@ -7868,7 +7868,7 @@ static bool grant_load(THD *thd, goto end_unlock_p; } - if (check_no_resolve) + if (opt_skip_name_resolve) { if (hostname_requires_resolving(mem_check->host.hostname)) { diff --git a/sql/sql_connect.cc b/sql/sql_connect.cc index 0346e70a588..1c267e02839 100644 --- a/sql/sql_connect.cc +++ b/sql/sql_connect.cc @@ -891,7 +891,7 @@ int thd_set_peer_addr(THD *thd, return 1; /* The error is set by my_strdup(). */ } thd->main_security_ctx.host_or_ip = thd->main_security_ctx.ip; - if (!(specialflag & SPECIAL_NO_RESOLVE)) + if (!opt_skip_name_resolve) { int rc; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index f76e16153e0..f8974bb9cc0 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5376,7 +5376,7 @@ mysql_execute_command(THD *thd) List_iterator <LEX_USER> user_list(lex->users_list); while ((user= user_list++)) { - if (specialflag & SPECIAL_NO_RESOLVE && + if (opt_skip_name_resolve && hostname_requires_resolving(user->host.str)) push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_WARN_HOSTNAME_WONT_WORK, diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 1fa0980977d..f780ed9c23a 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2501,7 +2501,7 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet, add_table_options(thd, table, create_info_arg, table_list->schema_table != 0, 0, packet); - if (table->versioned()) + if (DBUG_EVALUATE_IF("sysvers_hide", 0, table->versioned())) packet->append(STRING_WITH_LEN(" WITH SYSTEM VERSIONING")); #ifdef WITH_PARTITION_STORAGE_ENGINE diff --git a/sql/sql_table.cc b/sql/sql_table.cc index c1f7c7fee7a..725af4adb4e 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -9837,6 +9837,7 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db, if (check_engine(thd, alter_ctx.new_db.str, alter_ctx.new_name.str, create_info)) DBUG_RETURN(true); + create_info->vers_check_native(); if (create_info->vers_info.fix_alter_info(thd, alter_info, create_info, table)) { DBUG_RETURN(true); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 2682aef14d8..97bf707f7c9 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2010, 2022, MariaDB + Copyright (c) 2010, 2023, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -834,7 +834,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); /* We should not introduce any further shift/reduce conflicts. */ -%expect 72 +%expect 83 /* Comments for TOKENS. @@ -1631,7 +1631,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %left PREC_BELOW_NOT -%nonassoc NOT_SYM +%nonassoc LOW_PRIORITY_NOT %left '=' EQUAL_SYM GE '>' LE '<' NE %nonassoc IS %right BETWEEN_SYM @@ -10065,7 +10065,7 @@ expr: MYSQL_YYABORT; } } - | NOT_SYM expr %prec NOT_SYM + | NOT_SYM expr %prec LOW_PRIORITY_NOT { $$= negate_expression(thd, $2); if (unlikely($$ == NULL)) diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 71f18ec2949..91913020644 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -1,6 +1,6 @@ /* Copyright (c) 2000, 2015, Oracle and/or its affiliates. - Copyright (c) 2010, 2021, MariaDB + Copyright (c) 2010, 2023, MariaDB This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -310,7 +310,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); /* We should not introduce any further shift/reduce conflicts. */ -%expect 73 +%expect 84 /* Comments for TOKENS. @@ -1107,7 +1107,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %left PREC_BELOW_NOT -%nonassoc NOT_SYM +%nonassoc LOW_PRIORITY_NOT %left '=' EQUAL_SYM GE '>' LE '<' NE %nonassoc IS %right BETWEEN_SYM @@ -10180,7 +10180,7 @@ expr: MYSQL_YYABORT; } } - | NOT_SYM expr %prec NOT_SYM + | NOT_SYM expr %prec LOW_PRIORITY_NOT { $$= negate_expression(thd, $2); if (unlikely($$ == NULL)) diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 56f3bd92bc4..627b6191e51 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2876,7 +2876,7 @@ static Sys_var_mybool Sys_skip_name_resolve( "skip_name_resolve", "Don't resolve hostnames. All hostnames are IP's or 'localhost'.", READ_ONLY GLOBAL_VAR(opt_skip_name_resolve), - CMD_LINE(OPT_ARG, OPT_SKIP_RESOLVE), + CMD_LINE(OPT_ARG), DEFAULT(FALSE)); static Sys_var_mybool Sys_skip_show_database( diff --git a/sql/unireg.h b/sql/unireg.h index dd003cd0b3f..419cf1f17d5 100644 --- a/sql/unireg.h +++ b/sql/unireg.h @@ -62,7 +62,7 @@ #define SPECIAL_WAIT_IF_LOCKED 8 /* Wait if locked database */ #define SPECIAL_SAME_DB_NAME 16 /* form name = file name */ #define SPECIAL_ENGLISH 32 /* English error messages */ -#define SPECIAL_NO_RESOLVE 64 /* Don't use gethostname */ +#define SPECIAL_NO_RESOLVE 64 /* Obsolete */ #define SPECIAL_NO_PRIOR 128 /* Obsolete */ #define SPECIAL_BIG_SELECTS 256 /* Don't use heap tables */ #define SPECIAL_NO_HOST_CACHE 512 /* Don't cache hosts */ diff --git a/storage/connect/value.cpp b/storage/connect/value.cpp index 9586f834b8f..498ec71a87f 100644 --- a/storage/connect/value.cpp +++ b/storage/connect/value.cpp @@ -1199,7 +1199,7 @@ bool TYPVAL<TYPE>::Compall(PGLOBAL g, PVAL *vp, int np, OPVAL op) /***********************************************************************/ /* FormatValue: This function set vp (a STRING value) to the string */ -/* constructed from its own value formated using the fmt format. */ +/* constructed from its own value formatted using the fmt format. */ /* This function assumes that the format matches the value type. */ /***********************************************************************/ template <class TYPE> @@ -1711,7 +1711,7 @@ bool TYPVAL<PSZ>::Compute(PGLOBAL g, PVAL *vp, int np, OPVAL op) /***********************************************************************/ /* FormatValue: This function set vp (a STRING value) to the string */ -/* constructed from its own value formated using the fmt format. */ +/* constructed from its own value formatted using the fmt format. */ /* This function assumes that the format matches the value type. */ /***********************************************************************/ bool TYPVAL<PSZ>::FormatValue(PVAL vp, PCSZ fmt) @@ -2325,7 +2325,7 @@ bool BINVAL::IsEqual(PVAL vp, bool chktype) /***********************************************************************/ /* FormatValue: This function set vp (a STRING value) to the string */ -/* constructed from its own value formated using the fmt format. */ +/* constructed from its own value formatted using the fmt format. */ /* This function assumes that the format matches the value type. */ /***********************************************************************/ bool BINVAL::FormatValue(PVAL vp, PCSZ fmt) @@ -2865,7 +2865,7 @@ bool DTVAL::WeekNum(PGLOBAL g, int& nval) /***********************************************************************/ /* FormatValue: This function set vp (a STRING value) to the string */ -/* constructed from its own value formated using the fmt format. */ +/* constructed from its own value formatted using the fmt format. */ /* This function assumes that the format matches the value type. */ /***********************************************************************/ bool DTVAL::FormatValue(PVAL vp, PCSZ fmt) diff --git a/storage/innobase/dict/dict0mem.cc b/storage/innobase/dict/dict0mem.cc index 0bdb086a5b6..d6b9e296c5c 100644 --- a/storage/innobase/dict/dict0mem.cc +++ b/storage/innobase/dict/dict0mem.cc @@ -1370,6 +1370,20 @@ dict_index_t::vers_history_row( { ut_ad(!is_primary()); + /* + Get row_end from clustered index + + TODO (optimization): row_end can be taken from unique secondary index + as well. For that dict_index_t::vers_end member should be added and + updated at index init (dict_index_build_internal_non_clust()). + + Test case: + + create or replace table t1 (x int unique, y int unique, + foreign key r (y) references t1 (x)) + with system versioning engine innodb; + insert into t1 values (1, 1); + */ bool error = false; mem_heap_t* heap = NULL; dict_index_t* clust_index = NULL; diff --git a/storage/innobase/fts/fts0fts.cc b/storage/innobase/fts/fts0fts.cc index 64d662ca9b6..224fc9593b7 100644 --- a/storage/innobase/fts/fts0fts.cc +++ b/storage/innobase/fts/fts0fts.cc @@ -1,7 +1,7 @@ /***************************************************************************** Copyright (c) 2011, 2021, Oracle and/or its affiliates. -Copyright (c) 2016, 2022, MariaDB Corporation. +Copyright (c) 2016, 2023, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -386,8 +386,10 @@ fts_read_stopword( fts_string_t str; mem_heap_t* heap; ib_rbt_bound_t parent; + dict_table_t* table; sel_node = static_cast<sel_node_t*>(row); + table = sel_node->table_list->table; stopword_info = static_cast<fts_stopword_t*>(user_arg); stop_words = stopword_info->cached_stopword; @@ -402,6 +404,27 @@ fts_read_stopword( str.f_n_char = 0; str.f_str = static_cast<byte*>(dfield_get_data(dfield)); str.f_len = dfield_get_len(dfield); + exp = que_node_get_next(exp); + ut_ad(exp); + + if (table->versioned()) { + dfield = que_node_get_val(exp); + ut_ad(dfield_get_type(dfield)->vers_sys_end()); + void* data = dfield_get_data(dfield); + ulint len = dfield_get_len(dfield); + if (table->versioned_by_id()) { + ut_ad(len == sizeof trx_id_max_bytes); + if (0 != memcmp(data, trx_id_max_bytes, len)) { + return true; + } + } else { + ut_ad(len == sizeof timestamp_max_bytes); + if (0 != memcmp(data, timestamp_max_bytes, len)) { + return true; + } + } + } + ut_ad(!que_node_get_next(exp)); /* Only create new node if it is a value not already existed */ if (str.f_len != UNIV_SQL_NULL @@ -445,7 +468,9 @@ fts_load_user_stopword( /* Validate the user table existence in the right format */ bool ret= false; - stopword_info->charset = fts_valid_stopword_table(stopword_table_name); + const char* row_end; + stopword_info->charset = fts_valid_stopword_table(stopword_table_name, + &row_end); if (!stopword_info->charset) { cleanup: if (!fts->dict_locked) { @@ -470,6 +495,7 @@ cleanup: pars_info_t* info = pars_info_create(); pars_info_bind_id(info, "table_stopword", stopword_table_name); + pars_info_bind_id(info, "row_end", row_end); pars_info_bind_function(info, "my_func", fts_read_stopword, stopword_info); @@ -478,7 +504,7 @@ cleanup: info, "DECLARE FUNCTION my_func;\n" "DECLARE CURSOR c IS" - " SELECT value" + " SELECT value, $row_end" " FROM $table_stopword;\n" "BEGIN\n" "\n" @@ -1913,9 +1939,16 @@ fts_create_common_tables( goto func_exit; } - index = dict_mem_index_create(table, FTS_DOC_ID_INDEX_NAME, - DICT_UNIQUE, 1); - dict_mem_index_add_field(index, FTS_DOC_ID_COL_NAME, 0); + if (table->versioned()) { + index = dict_mem_index_create(table, FTS_DOC_ID_INDEX_NAME, + DICT_UNIQUE, 2); + dict_mem_index_add_field(index, FTS_DOC_ID_COL_NAME, 0); + dict_mem_index_add_field(index, table->cols[table->vers_end].name(*table), 0); + } else { + index = dict_mem_index_create(table, FTS_DOC_ID_INDEX_NAME, + DICT_UNIQUE, 1); + dict_mem_index_add_field(index, FTS_DOC_ID_COL_NAME, 0); + } op = trx_get_dict_operation(trx); @@ -3405,7 +3438,8 @@ fts_add_doc_by_id( /* Search based on Doc ID. Here, we'll need to consider the case when there is no primary index on Doc ID */ - tuple = dtuple_create(heap, 1); + const ulint n_uniq = table->fts_n_uniq(); + tuple = dtuple_create(heap, n_uniq); dfield = dtuple_get_nth_field(tuple, 0); dfield->type.mtype = DATA_INT; dfield->type.prtype = DATA_NOT_NULL | DATA_UNSIGNED | DATA_BINARY_TYPE; @@ -3413,12 +3447,27 @@ fts_add_doc_by_id( mach_write_to_8((byte*) &temp_doc_id, doc_id); dfield_set_data(dfield, &temp_doc_id, sizeof(temp_doc_id)); + if (n_uniq == 2) { + ut_ad(table->versioned()); + ut_ad(fts_id_index->fields[1].col->vers_sys_end()); + dfield = dtuple_get_nth_field(tuple, 1); + dfield->type.mtype = fts_id_index->fields[1].col->mtype; + dfield->type.prtype = fts_id_index->fields[1].col->prtype; + if (table->versioned_by_id()) { + dfield_set_data(dfield, trx_id_max_bytes, + sizeof(trx_id_max_bytes)); + } else { + dfield_set_data(dfield, timestamp_max_bytes, + sizeof(timestamp_max_bytes)); + } + } + btr_pcur_open_with_no_init( fts_id_index, tuple, PAGE_CUR_LE, BTR_SEARCH_LEAF, &pcur, &mtr); /* If we have a match, add the data to doc structure */ - if (btr_pcur_get_low_match(&pcur) == 1) { + if (btr_pcur_get_low_match(&pcur) == n_uniq) { const rec_t* rec; btr_pcur_t* doc_pcur; const rec_t* clust_rec; @@ -3614,13 +3663,34 @@ fts_get_max_doc_id( if (!page_is_empty(btr_pcur_get_page(&pcur))) { const rec_t* rec = NULL; + constexpr ulint doc_id_len= 8; do { rec = btr_pcur_get_rec(&pcur); - if (page_rec_is_user_rec(rec)) { + if (!page_rec_is_user_rec(rec)) { + continue; + } + + if (index->n_uniq == 1) { break; } + + ut_ad(table->versioned()); + ut_ad(index->n_uniq == 2); + + const byte *data = rec + doc_id_len; + if (table->versioned_by_id()) { + if (0 == memcmp(data, trx_id_max_bytes, + sizeof trx_id_max_bytes)) { + break; + } + } else { + if (0 == memcmp(data, timestamp_max_bytes, + sizeof timestamp_max_bytes)) { + break; + } + } } while (btr_pcur_move_to_prev(&pcur, &mtr)); if (!rec || rec_is_metadata(rec, *index)) { @@ -5902,12 +5972,16 @@ void fts_drop_orphaned_tables() /**********************************************************************//** Check whether user supplied stopword table is of the right format. Caller is responsible to hold dictionary locks. -@return the stopword column charset if qualifies */ +@param stopword_table_name table name +@param row_end name of the system-versioning end column, or "value" +@return the stopword column charset +@retval NULL if the table does not exist or qualify */ CHARSET_INFO* fts_valid_stopword_table( /*=====================*/ - const char* stopword_table_name) /*!< in: Stopword table + const char* stopword_table_name, /*!< in: Stopword table name */ + const char** row_end) /* row_end value of system-versioned table */ { dict_table_t* table; dict_col_t* col = NULL; @@ -5949,6 +6023,13 @@ fts_valid_stopword_table( } ut_ad(col); + ut_ad(!table->versioned() || col->ind != table->vers_end); + + if (row_end) { + *row_end = table->versioned() + ? dict_table_get_col_name(table, table->vers_end) + : "value"; /* for fts_load_user_stopword() */ + } return(fts_get_charset(col->prtype)); } @@ -6084,18 +6165,20 @@ cleanup: /**********************************************************************//** Callback function when we initialize the FTS at the start up time. It recovers the maximum Doc IDs presented in the current table. +Tested by innodb_fts.crash_recovery @return: always returns TRUE */ static ibool fts_init_get_doc_id( /*================*/ void* row, /*!< in: sel_node_t* */ - void* user_arg) /*!< in: fts cache */ + void* user_arg) /*!< in: table with fts */ { doc_id_t doc_id = FTS_NULL_DOC_ID; sel_node_t* node = static_cast<sel_node_t*>(row); que_node_t* exp = node->select_list; - fts_cache_t* cache = static_cast<fts_cache_t*>(user_arg); + dict_table_t* table = static_cast<dict_table_t *>(user_arg); + fts_cache_t* cache = table->fts->cache; ut_ad(ib_vector_is_empty(cache->get_docs)); @@ -6110,6 +6193,29 @@ fts_init_get_doc_id( doc_id = static_cast<doc_id_t>(mach_read_from_8( static_cast<const byte*>(data))); + exp = que_node_get_next(que_node_get_next(exp)); + if (exp) { + ut_ad(table->versioned()); + dfield = que_node_get_val(exp); + type = dfield_get_type(dfield); + ut_ad(type->vers_sys_end()); + data = dfield_get_data(dfield); + ulint len = dfield_get_len(dfield); + if (table->versioned_by_id()) { + ut_ad(len == sizeof trx_id_max_bytes); + if (0 != memcmp(data, trx_id_max_bytes, len)) { + return true; + } + } else { + ut_ad(len == sizeof timestamp_max_bytes); + if (0 != memcmp(data, timestamp_max_bytes, len)) { + return true; + } + } + ut_ad(!(exp = que_node_get_next(exp))); + } + ut_ad(!exp); + if (doc_id >= cache->next_doc_id) { cache->next_doc_id = doc_id + 1; } @@ -6275,7 +6381,7 @@ fts_init_index( fts_doc_fetch_by_doc_id(NULL, start_doc, index, FTS_FETCH_DOC_BY_ID_LARGE, - fts_init_get_doc_id, cache); + fts_init_get_doc_id, table); } else { if (table->fts->cache->stopword_info.status & STOPWORD_NOT_INIT) { diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 2a0de690174..c83deb72f02 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -8946,6 +8946,9 @@ ha_innobase::update_row( innobase_srv_conc_enter_innodb(m_prebuilt); + if (m_prebuilt->upd_node->is_delete) { + trx->fts_next_doc_id = 0; + } error = row_update_for_mysql(m_prebuilt); if (error == DB_SUCCESS && vers_ins_row @@ -9066,6 +9069,7 @@ ha_innobase::delete_row( && trx->id != table->vers_start_id() ? VERSIONED_DELETE : PLAIN_DELETE; + trx->fts_next_doc_id = 0; innobase_srv_conc_enter_innodb(m_prebuilt); @@ -10062,9 +10066,12 @@ ha_innobase::ft_init_ext( /*****************************************************************//** Set up search tuple for a query through FTS_DOC_ID_INDEX on supplied Doc ID. This is used by MySQL to retrieve the documents -once the search result (Doc IDs) is available */ +once the search result (Doc IDs) is available + +@return DB_SUCCESS or DB_INDEX_CORRUPT +*/ static -void +dberr_t innobase_fts_create_doc_id_key( /*===========================*/ dtuple_t* tuple, /* in/out: m_prebuilt->search_tuple */ @@ -10076,8 +10083,10 @@ innobase_fts_create_doc_id_key( { doc_id_t temp_doc_id; dfield_t* dfield = dtuple_get_nth_field(tuple, 0); + const ulint n_uniq = index->table->fts_n_uniq(); - ut_a(dict_index_get_n_unique(index) == 1); + if (dict_index_get_n_unique(index) != n_uniq) + return DB_INDEX_CORRUPT; dtuple_set_n_fields(tuple, index->n_fields); dict_index_copy_types(tuple, index, index->n_fields); @@ -10095,12 +10104,25 @@ innobase_fts_create_doc_id_key( *doc_id = temp_doc_id; dfield_set_data(dfield, doc_id, sizeof(*doc_id)); - dtuple_set_n_fields_cmp(tuple, 1); + if (n_uniq == 2) { + ut_ad(index->table->versioned()); + dfield = dtuple_get_nth_field(tuple, 1); + if (index->table->versioned_by_id()) { + dfield_set_data(dfield, trx_id_max_bytes, + sizeof(trx_id_max_bytes)); + } else { + dfield_set_data(dfield, timestamp_max_bytes, + sizeof(timestamp_max_bytes)); + } + } + + dtuple_set_n_fields_cmp(tuple, n_uniq); - for (ulint i = 1; i < index->n_fields; i++) { + for (ulint i = n_uniq; i < index->n_fields; i++) { dfield = dtuple_get_nth_field(tuple, i); dfield_set_null(dfield); } + return DB_SUCCESS; } /**********************************************************************//** @@ -10182,14 +10204,18 @@ next_record: /* We pass a pointer of search_doc_id because it will be converted to storage byte order used in the search tuple. */ - innobase_fts_create_doc_id_key(tuple, index, &search_doc_id); + dberr_t ret = innobase_fts_create_doc_id_key( + tuple, index, &search_doc_id); - innobase_srv_conc_enter_innodb(m_prebuilt); + if (ret == DB_SUCCESS) { + innobase_srv_conc_enter_innodb(m_prebuilt); - dberr_t ret = row_search_for_mysql( - (byte*) buf, PAGE_CUR_GE, m_prebuilt, ROW_SEL_EXACT, 0); + ret = row_search_for_mysql( + (byte*) buf, PAGE_CUR_GE, m_prebuilt, + ROW_SEL_EXACT, 0); - innobase_srv_conc_exit_innodb(m_prebuilt); + innobase_srv_conc_exit_innodb(m_prebuilt); + } int error; @@ -17354,7 +17380,7 @@ innodb_stopword_table_validate( /* Validate the stopword table's (if supplied) existence and of the right format */ int ret = stopword_table_name && !fts_valid_stopword_table( - stopword_table_name); + stopword_table_name, NULL); row_mysql_unlock_data_dictionary(trx); diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index cbb0cc623fe..17fe6014fea 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -1,7 +1,7 @@ /***************************************************************************** Copyright (c) 2005, 2019, Oracle and/or its affiliates. All Rights Reserved. -Copyright (c) 2013, 2022, MariaDB Corporation. +Copyright (c) 2013, 2023, MariaDB Corporation. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software @@ -2395,6 +2395,11 @@ innodb_instant_alter_column_allowed_reason: < dict_table_get_n_user_cols(m_prebuilt->table))); if (fulltext_indexes && m_prebuilt->table->fts) { + /* FTS index of versioned table has row_end, need rebuild */ + if (table->versioned() != altered_table->versioned()) { + need_rebuild= true; + } + /* FULLTEXT indexes are supposed to remain. */ /* Disallow DROP INDEX FTS_DOC_ID_INDEX */ @@ -3835,6 +3840,8 @@ innobase_fts_check_doc_id_index( /* Check if a unique index with the name of FTS_DOC_ID_INDEX_NAME is being created. */ + const ulint fts_n_uniq= altered_table->versioned() ? 2 : 1; + for (uint i = 0; i < altered_table->s->keys; i++) { const KEY& key = altered_table->key_info[i]; @@ -3844,7 +3851,7 @@ innobase_fts_check_doc_id_index( } if ((key.flags & HA_NOSAME) - && key.user_defined_key_parts == 1 + && key.user_defined_key_parts == fts_n_uniq && !strcmp(key.name.str, FTS_DOC_ID_INDEX_NAME) && !strcmp(key.key_part[0].field->field_name.str, FTS_DOC_ID_COL_NAME)) { @@ -3874,7 +3881,7 @@ innobase_fts_check_doc_id_index( } if (!dict_index_is_unique(index) - || dict_index_get_n_unique(index) > 1 + || dict_index_get_n_unique(index) != table->fts_n_uniq() || strcmp(index->name, FTS_DOC_ID_INDEX_NAME)) { return(FTS_INCORRECT_DOC_ID_INDEX); } @@ -3915,6 +3922,7 @@ innobase_fts_check_doc_id_index_in_def( { /* Check whether there is a "FTS_DOC_ID_INDEX" in the to be built index list */ + const uint fts_n_uniq= key_info->table->versioned() ? 2 : 1; for (ulint j = 0; j < n_key; j++) { const KEY* key = &key_info[j]; @@ -3925,7 +3933,7 @@ innobase_fts_check_doc_id_index_in_def( /* Do a check on FTS DOC ID_INDEX, it must be unique, named as "FTS_DOC_ID_INDEX" and on column "FTS_DOC_ID" */ if (!(key->flags & HA_NOSAME) - || key->user_defined_key_parts != 1 + || key->user_defined_key_parts != fts_n_uniq || strcmp(key->name.str, FTS_DOC_ID_INDEX_NAME) || strcmp(key->key_part[0].field->field_name.str, FTS_DOC_ID_COL_NAME)) { @@ -4117,13 +4125,22 @@ created_clustered: if (add_fts_doc_idx) { index_def_t* index = indexdef++; + uint nfields = 1; + if (altered_table->versioned()) + ++nfields; index->fields = static_cast<index_field_t*>( - mem_heap_alloc(heap, sizeof *index->fields)); - index->n_fields = 1; - index->fields->col_no = fts_doc_id_col; - index->fields->prefix_len = 0; - index->fields->is_v_col = false; + mem_heap_alloc(heap, sizeof(*index->fields) * nfields)); + index->n_fields = nfields; + index->fields[0].col_no = fts_doc_id_col; + index->fields[0].prefix_len = 0; + index->fields[0].is_v_col = false; + if (nfields == 2) { + index->fields[1].col_no + = altered_table->s->vers.end_fieldno; + index->fields[1].prefix_len = 0; + index->fields[1].is_v_col = false; + } index->ind_type = DICT_UNIQUE; ut_ad(!rebuild || !add_fts_doc_id diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h index 596800ee8d2..f0d4eb7bebf 100644 --- a/storage/innobase/include/dict0mem.h +++ b/storage/innobase/include/dict0mem.h @@ -2350,6 +2350,9 @@ public: or mysql/innodb_index_stats. @return true if the table name is same as stats table */ bool is_stats_table() const; + + /** @return number of unique columns in FTS_DOC_ID index */ + unsigned fts_n_uniq() const { return versioned() ? 2 : 1; } }; inline void dict_index_t::set_modified(mtr_t& mtr) const diff --git a/storage/innobase/include/fts0fts.h b/storage/innobase/include/fts0fts.h index d3cfa5b23df..fcf5a5da9de 100644 --- a/storage/innobase/include/fts0fts.h +++ b/storage/innobase/include/fts0fts.h @@ -830,15 +830,14 @@ fts_get_max_doc_id( /*===============*/ dict_table_t* table); /*!< in: user table */ -/******************************************************************//** -Check whether user supplied stopword table exists and is of -the right format. -@return the stopword column charset if qualifies */ -CHARSET_INFO* -fts_valid_stopword_table( -/*=====================*/ - const char* stopword_table_name); /*!< in: Stopword table - name */ +/** Check whether a stopword table is in the right format. +@param stopword_table_name table name +@param row_end name of the system-versioning end column, or "value" +@return the stopword column charset +@retval NULL if the table does not exist or qualify */ +CHARSET_INFO *fts_valid_stopword_table(const char *stopword_table_name, + const char **row_end= NULL); + /****************************************************************//** This function loads specified stopword into FTS cache @return true if success */ diff --git a/storage/innobase/include/row0ins.h b/storage/innobase/include/row0ins.h index 9a16394a052..34427dc6dc7 100644 --- a/storage/innobase/include/row0ins.h +++ b/storage/innobase/include/row0ins.h @@ -206,7 +206,6 @@ struct ins_node_t if this is NULL, entry list should be created and buffers for sys fields in row allocated */ void vers_update_end(row_prebuilt_t *prebuilt, bool history_row); - bool vers_history_row() const; /* true if 'row' is historical */ }; /** Create an insert object. diff --git a/storage/innobase/include/row0upd.h b/storage/innobase/include/row0upd.h index b500b11e95c..0287040c418 100644 --- a/storage/innobase/include/row0upd.h +++ b/storage/innobase/include/row0upd.h @@ -599,17 +599,13 @@ public: void vers_make_update(const trx_t *trx) { vers_update_fields(trx, table->vers_start); - } + } - /** Only set row_end = CURRENT_TIMESTAMP/trx->id. - Do not touch other fields at all. - @param[in] trx transaction */ - void vers_make_delete(const trx_t *trx) - { - update->n_fields = 0; - is_delete = VERSIONED_DELETE; - vers_update_fields(trx, table->vers_end); - } + /** Prepare update vector for versioned delete. + Set row_end to CURRENT_TIMESTAMP or trx->id. + Initialize fts_next_doc_id for versioned delete. + @param[in] trx transaction */ + void vers_make_delete(trx_t *trx); }; #define UPD_NODE_MAGIC_N 1579975 diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index cf9468c4997..81f662e7a15 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -2051,6 +2051,61 @@ row_ins_dupl_error_with_rec( return(!rec_get_deleted_flag(rec, rec_offs_comp(offsets))); } +/** Determine whether a history row was inserted by this transaction +(row TRX_ID is the same as current TRX_ID). +@param index secondary index +@param rec secondary index record +@param trx transaction +@return error code +@retval DB_SUCCESS on success +@retval DB_FOREIGN_DUPLICATE_KEY if a history row was inserted by trx */ +static dberr_t vers_row_same_trx(dict_index_t* index, const rec_t* rec, + const trx_t& trx) +{ + mtr_t mtr; + dberr_t ret= DB_SUCCESS; + dict_index_t *clust_index= dict_table_get_first_index(index->table); + ut_ad(index != clust_index); + + mtr.start(); + + if (const rec_t *clust_rec= + row_get_clust_rec(BTR_SEARCH_LEAF, rec, index, &clust_index, &mtr)) + { + rec_offs offsets_[REC_OFFS_NORMAL_SIZE]; + rec_offs *clust_offs= offsets_; + rec_offs_init(offsets_); + mem_heap_t *heap= NULL; + + clust_offs= + rec_get_offsets(clust_rec, clust_index, clust_offs, + clust_index->n_core_fields, ULINT_UNDEFINED, &heap); + if (clust_index->vers_history_row(clust_rec, clust_offs)) + { + ulint trx_id_len; + const byte *trx_id= rec_get_nth_field(clust_rec, clust_offs, + clust_index->n_uniq, &trx_id_len); + ut_ad(trx_id_len == DATA_TRX_ID_LEN); + + if (trx.id == trx_read_trx_id(trx_id)) + ret= DB_FOREIGN_DUPLICATE_KEY; + } + + if (UNIV_LIKELY_NULL(heap)) + mem_heap_free(heap); + } + else + { + ib::error() << "foreign constraints: secondary index " << index->name << + " of table " << index->table->name << " is out of sync"; + ut_ad("secondary index is out of sync" == 0); + ret= DB_TABLE_CORRUPT; + } + + mtr.commit(); + return ret; +} + /***************************************************************//** Scans a unique non-clustered index at a given index entry to determine whether a uniqueness violation has occurred for the key value of the entry. @@ -2074,7 +2129,6 @@ row_ins_scan_sec_index_for_duplicate( ulint n_fields_cmp; btr_pcur_t pcur; dberr_t err = DB_SUCCESS; - ulint allow_duplicates; rec_offs offsets_[REC_OFFS_SEC_INDEX_SIZE]; rec_offs* offsets = offsets_; DBUG_ENTER("row_ins_scan_sec_index_for_duplicate"); @@ -2112,7 +2166,7 @@ row_ins_scan_sec_index_for_duplicate( : BTR_SEARCH_LEAF, &pcur, mtr); - allow_duplicates = thr_get_trx(thr)->duplicates; + trx_t* const trx = thr_get_trx(thr); /* Scan index records and check if there is a duplicate */ @@ -2133,7 +2187,7 @@ row_ins_scan_sec_index_for_duplicate( if (flags & BTR_NO_LOCKING_FLAG) { /* Set no locks when applying log in online table rebuild. */ - } else if (allow_duplicates) { + } else if (trx->duplicates) { /* If the SQL-query will update or replace duplicate key we will take X-lock for @@ -2167,9 +2221,18 @@ row_ins_scan_sec_index_for_duplicate( if (cmp == 0) { if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) { + err = DB_DUPLICATE_KEY; - thr_get_trx(thr)->error_info = index; + trx->error_info = index; + + if (!index->table->versioned()) { + } else if (dberr_t e = + vers_row_same_trx(index, rec, + *trx)) { + err = e; + goto end_scan; + } /* If the duplicate is on hidden FTS_DOC_ID, state so in the error log */ @@ -3605,16 +3668,6 @@ row_ins_get_row_from_select( } } -inline -bool ins_node_t::vers_history_row() const -{ - if (!table->versioned()) - return false; - dfield_t* row_end = dtuple_get_nth_field(row, table->vers_end); - return row_end->vers_history_row(); -} - - /***********************************************************//** Inserts a row to a table. @return DB_SUCCESS if operation successfully completed, else error @@ -3653,31 +3706,12 @@ row_ins( ut_ad(node->state == INS_NODE_INSERT_ENTRIES); while (node->index != NULL) { - dict_index_t *index = node->index; - /* - We do not insert history rows into FTS_DOC_ID_INDEX because - it is unique by FTS_DOC_ID only and we do not want to add - row_end to unique key. Fulltext field works the way new - FTS_DOC_ID is created on every fulltext UPDATE, so holding only - FTS_DOC_ID for history is enough. - */ - const unsigned type = index->type; - if (index->type & DICT_FTS) { - } else if (!(type & DICT_UNIQUE) || index->n_uniq > 1 - || !node->vers_history_row()) { - + if (!(node->index->type & DICT_FTS)) { dberr_t err = row_ins_index_entry_step(node, thr); if (err != DB_SUCCESS) { DBUG_RETURN(err); } - } else { - /* Unique indexes with system versioning must contain - the version end column. The only exception is a hidden - FTS_DOC_ID_INDEX that InnoDB may create on a hidden or - user-created FTS_DOC_ID column. */ - ut_ad(!strcmp(index->name, FTS_DOC_ID_INDEX_NAME)); - ut_ad(!strcmp(index->fields[0].name, FTS_DOC_ID_COL_NAME)); } node->index = dict_table_get_next_index(node->index); diff --git a/storage/innobase/row/row0merge.cc b/storage/innobase/row/row0merge.cc index 2ba6e4e1a52..9d755ce6f1e 100644 --- a/storage/innobase/row/row0merge.cc +++ b/storage/innobase/row/row0merge.cc @@ -457,6 +457,8 @@ row_merge_buf_redundant_convert( @param[in,out] row table row @param[in] ext cache of externally stored column prefixes, or NULL +@param[in] history_fts row is historical in a system-versioned table + on which a FTS_DOC_ID_INDEX(FTS_DOC_ID) exists @param[in,out] doc_id Doc ID if we are creating FTS index @param[in,out] conv_heap memory heap where to allocate data when @@ -478,6 +480,7 @@ row_merge_buf_add( fts_psort_t* psort_info, dtuple_t* row, const row_ext_t* ext, + const bool history_fts, doc_id_t* doc_id, mem_heap_t* conv_heap, dberr_t* err, @@ -540,7 +543,7 @@ error: : NULL; /* Process the Doc ID column */ - if (!v_col && *doc_id + if (!v_col && (history_fts || *doc_id) && col->ind == index->table->fts->doc_col) { fts_write_doc_id((byte*) &write_doc_id, *doc_id); @@ -592,7 +595,7 @@ error: /* Tokenize and process data for FTS */ - if (index->type & DICT_FTS) { + if (!history_fts && (index->type & DICT_FTS)) { fts_doc_item_t* doc_item; byte* value; void* ptr; @@ -1894,6 +1897,7 @@ row_merge_read_clustered_index( dtuple_t* row; row_ext_t* ext; page_cur_t* cur = btr_pcur_get_page_cur(&pcur); + bool history_row, history_fts = false; page_cur_move_to_next(cur); @@ -2131,6 +2135,11 @@ end_of_index: row_heap); ut_ad(row); + history_row = new_table->versioned() + && dtuple_get_nth_field(row, new_table->vers_end) + ->vers_history_row(); + history_fts = history_row && new_table->fts; + for (ulint i = 0; i < n_nonnull; i++) { dfield_t* field = &row->fields[nonnull[i]]; @@ -2160,7 +2169,7 @@ end_of_index: } /* Get the next Doc ID */ - if (add_doc_id) { + if (add_doc_id && !history_fts) { doc_id++; } else { doc_id = 0; @@ -2196,13 +2205,6 @@ end_of_index: ut_ad(add_autoinc < dict_table_get_n_user_cols(new_table)); - bool history_row = false; - if (new_table->versioned()) { - const dfield_t* dfield = dtuple_get_nth_field( - row, new_table->vers_end); - history_row = dfield->vers_history_row(); - } - dfield_t* dfield = dtuple_get_nth_field(row, add_autoinc); @@ -2326,8 +2328,8 @@ write_buffers: if (UNIV_LIKELY (row && (rows_added = row_merge_buf_add( buf, fts_index, old_table, new_table, - psort_info, row, ext, &doc_id, - conv_heap, &err, + psort_info, row, ext, history_fts, + &doc_id, conv_heap, &err, &v_heap, eval_table, trx)))) { /* Set the page flush observer for the @@ -2659,9 +2661,10 @@ write_buffers: if (UNIV_UNLIKELY (!(rows_added = row_merge_buf_add( buf, fts_index, old_table, - new_table, psort_info, row, ext, - &doc_id, conv_heap, - &err, &v_heap, eval_table, trx)))) { + new_table, psort_info, + row, ext, history_fts, &doc_id, + conv_heap, &err, &v_heap, + eval_table, trx)))) { /* An empty buffer should have enough room for at least one record. */ ut_ad(err == DB_COMPUTE_VALUE_FAILED diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc index 84964970ad0..bb994a926eb 100644 --- a/storage/innobase/row/row0mysql.cc +++ b/storage/innobase/row/row0mysql.cc @@ -1425,7 +1425,10 @@ error_exit: return(err); } - if (dict_table_has_fts_index(table)) { + if (dict_table_has_fts_index(table) + && (!table->versioned() + || !node->row->fields[table->vers_end].vers_history_row())) { + doc_id_t doc_id; /* Extract the doc id from the hidden FTS column */ @@ -1639,7 +1642,7 @@ row_fts_update_or_delete( ut_a(dict_table_has_fts_index(prebuilt->table)); /* Deletes are simple; get them out of the way first. */ - if (node->is_delete == PLAIN_DELETE) { + if (node->is_delete) { /* A delete affects all FTS indexes, so we pass NULL */ fts_trx_add_op(trx, table, old_doc_id, FTS_DELETE, NULL); } else { @@ -1648,7 +1651,7 @@ row_fts_update_or_delete( if (new_doc_id == 0) { ib::error() << "InnoDB FTS: Doc ID cannot be 0"; - return(DB_FTS_INVALID_DOCID); + DBUG_RETURN(DB_FTS_INVALID_DOCID); } row_fts_do_update(trx, table, old_doc_id, new_doc_id); } @@ -2204,7 +2207,7 @@ row_update_cascade_for_mysql( return(DB_FOREIGN_EXCEED_MAX_CASCADE); } - const trx_t* trx = thr_get_trx(thr); + trx_t* trx = thr_get_trx(thr); if (table->versioned()) { if (node->is_delete == PLAIN_DELETE) { diff --git a/storage/innobase/row/row0row.cc b/storage/innobase/row/row0row.cc index f0e5385be85..acd687d2a9c 100644 --- a/storage/innobase/row/row0row.cc +++ b/storage/innobase/row/row0row.cc @@ -1360,7 +1360,7 @@ row_raw_format_int( ulint buf_size, /*!< in: output buffer size in bytes */ ibool* format_in_hex) /*!< out: should the data be - formated in hex */ + formatted in hex */ { ulint ret; @@ -1408,7 +1408,7 @@ row_raw_format_str( ulint buf_size, /*!< in: output buffer size in bytes */ ibool* format_in_hex) /*!< out: should the data be - formated in hex */ + formatted in hex */ { ulint charset_coll; diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index c817b700b65..4504fabd4e0 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -5113,8 +5113,10 @@ wrong_offs: goto no_gap_lock; } + /* Set next-key lock both for delete- and non-delete-marked + records for unique search, because non-delete-marked record can + be marked as deleted while transaction suspends. */ if (!set_also_gap_locks - || (unique_search && !rec_get_deleted_flag(rec, comp)) || dict_index_is_spatial(index)) { goto no_gap_lock; diff --git a/storage/innobase/row/row0upd.cc b/storage/innobase/row/row0upd.cc index a79ff94b61b..c7087559f47 100644 --- a/storage/innobase/row/row0upd.cc +++ b/storage/innobase/row/row0upd.cc @@ -3539,3 +3539,16 @@ skip_append: } } } + + +/** Prepare update vector for versioned delete. +Set row_end to CURRENT_TIMESTAMP or trx->id. +Initialize fts_next_doc_id for versioned delete. +@param[in] trx transaction */ +void upd_node_t::vers_make_delete(trx_t* trx) +{ + update->n_fields= 0; + is_delete= VERSIONED_DELETE; + vers_update_fields(trx, table->vers_end); + trx->fts_next_doc_id= table->fts ? UINT64_UNDEFINED : 0; +} diff --git a/storage/maria/ma_locking.c b/storage/maria/ma_locking.c index 200a728626f..a377ab13c6a 100644 --- a/storage/maria/ma_locking.c +++ b/storage/maria/ma_locking.c @@ -282,7 +282,7 @@ int _ma_readinfo(register MARIA_HA *info __attribute__ ((unused)), /* - Every isam-function that uppdates the isam-database MUST end with this + Every isam-function that updates the isam-database MUST end with this request NOTES diff --git a/storage/maria/ma_loghandler.c b/storage/maria/ma_loghandler.c index 78f211c824e..35e47583720 100644 --- a/storage/maria/ma_loghandler.c +++ b/storage/maria/ma_loghandler.c @@ -957,7 +957,7 @@ char *translog_filename_by_fileno(uint32 file_no, char *path) DBUG_ENTER("translog_filename_by_fileno"); DBUG_ASSERT(file_no <= 0xfffffff); - /* log_descriptor.directory is already formated */ + /* log_descriptor.directory is already formatted */ end= strxmov(path, log_descriptor.directory, "aria_log.0000000", NullS); length= (uint) (int10_to_str(file_no, buff, 10) - buff); strmov(end - length +1, buff); diff --git a/storage/myisam/mi_locking.c b/storage/myisam/mi_locking.c index 713ba0a3851..8c49a9e6011 100644 --- a/storage/myisam/mi_locking.c +++ b/storage/myisam/mi_locking.c @@ -508,7 +508,7 @@ int _mi_readinfo(register MI_INFO *info, int lock_type, int check_keybuffer) /* - Every isam-function that uppdates the isam-database MUST end with this + Every isam-function that updates the isam-database MUST end with this request */ diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 49d6267b181..d395f907c1f 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -7696,7 +7696,7 @@ int rdb_split_normalized_tablename(const std::string &fullname, into MyRocks Data Dictionary The method is called during create table/partition, truncate table/partition - @param table_name IN table's name formated as + @param table_name IN table's name formatted as 'dbname.tablename' @param table_arg IN sql table @param auto_increment_value IN specified table's auto increment value diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc index dd1be6ba6b3..dd23e4f1354 100644 --- a/storage/spider/ha_spider.cc +++ b/storage/spider/ha_spider.cc @@ -11616,6 +11616,15 @@ int ha_spider::create( sql_command == SQLCOM_DROP_INDEX ) DBUG_RETURN(0); + if (!is_supported_parser_charset(info->default_table_charset)) + { + String charset_option; + charset_option.append("CHARSET "); + charset_option.append(info->default_table_charset->csname); + my_error(ER_ILLEGAL_HA_CREATE_OPTION, MYF(0), "SPIDER", charset_option.c_ptr()); + error_num = ER_ILLEGAL_HA_CREATE_OPTION; + goto error_charset; + } if (!(trx = spider_get_trx(thd, TRUE, &error_num))) goto error_get_trx; if ( @@ -11790,6 +11799,7 @@ error: spider_free_share_alloc(&tmp_share); error_alter_before_unlock: error_get_trx: +error_charset: DBUG_RETURN(error_num); } diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_29562.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_29562.result new file mode 100644 index 00000000000..561ce18a603 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_29562.result @@ -0,0 +1,47 @@ +# +# MDEV-29562 Spider table with charset utf32/utf16/ucs2 tries to set client charset to unsupported value +# +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +connection child2_1; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +CREATE TABLE tbl_a ( +a INT +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +connection master_1; +CREATE DATABASE auto_test_local; +USE auto_test_local; +CREATE TABLE tbl_a ( +a INT +) ENGINE=Spider CHARSET utf32 COMMENT='table "tbl_a", srv "s_2_1"'; +ERROR HY000: Table storage engine 'SPIDER' does not support the create option 'CHARSET utf32' +ALTER DATABASE auto_test_local CHARSET="ucs2"; +CREATE TABLE tbl_a ( +a INT +) ENGINE=Spider COMMENT='table "tbl_a", srv "s_2_1"'; +ERROR HY000: Table storage engine 'SPIDER' does not support the create option 'CHARSET ucs2' +CREATE TABLE tbl_a ( +a INT +) ENGINE=Spider CHARSET utf8 COMMENT='table "tbl_a", srv "s_2_1"'; +SELECT * FROM tbl_a; +a +ALTER TABLE tbl_a CONVERT TO CHARACTER SET utf16; +ERROR HY000: Table storage engine 'SPIDER' does not support the create option 'CHARSET utf16' +ALTER TABLE tbl_a CONVERT TO CHARACTER SET utf16le; +ERROR HY000: Table storage engine 'SPIDER' does not support the create option 'CHARSET utf16le' +ALTER TABLE tbl_a CONVERT TO CHARACTER SET latin1; +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29562.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_29562.cnf new file mode 100644 index 00000000000..05dfd8a0bce --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29562.cnf @@ -0,0 +1,3 @@ +!include include/default_mysqld.cnf +!include ../my_1_1.cnf +!include ../my_2_1.cnf diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_29562.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_29562.test new file mode 100644 index 00000000000..5ddb2cef492 --- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_29562.test @@ -0,0 +1,54 @@ +--echo # +--echo # MDEV-29562 Spider table with charset utf32/utf16/ucs2 tries to set client charset to unsupported value +--echo # + +--disable_query_log +--disable_result_log +--source ../../t/test_init.inc +--enable_result_log +--enable_query_log + +--connection child2_1 +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +eval CREATE TABLE tbl_a ( + a INT +) $CHILD2_1_ENGINE $CHILD2_1_CHARSET; + +--connection master_1 +CREATE DATABASE auto_test_local; +USE auto_test_local; +--error ER_ILLEGAL_HA_CREATE_OPTION +eval CREATE TABLE tbl_a ( + a INT +) $MASTER_1_ENGINE CHARSET utf32 COMMENT='table "tbl_a", srv "s_2_1"'; + +ALTER DATABASE auto_test_local CHARSET="ucs2"; +--error ER_ILLEGAL_HA_CREATE_OPTION +eval CREATE TABLE tbl_a ( + a INT +) $MASTER_1_ENGINE COMMENT='table "tbl_a", srv "s_2_1"'; + +eval CREATE TABLE tbl_a ( + a INT +) $MASTER_1_ENGINE CHARSET utf8 COMMENT='table "tbl_a", srv "s_2_1"'; +SELECT * FROM tbl_a; + +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE tbl_a CONVERT TO CHARACTER SET utf16; +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE tbl_a CONVERT TO CHARACTER SET utf16le; + +ALTER TABLE tbl_a CONVERT TO CHARACTER SET latin1; + +--connection master_1 +DROP DATABASE IF EXISTS auto_test_local; + +--connection child2_1 +DROP DATABASE IF EXISTS auto_test_remote; + +--disable_query_log +--disable_result_log +--source ../t/test_deinit.inc +--enable_query_log +--enable_result_log |