From 8963d64ee87e92a07f1175292f5010c3a0d03090 Mon Sep 17 00:00:00 2001 From: Brandon Nesterenko Date: Tue, 26 Apr 2022 19:51:42 -0600 Subject: MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.% Problem: ======== When replicating SET DEFAULT ROLE, the pre-update check (i.e. that in set_var_default_role::check()) tries to validate the existence of the given rules/user even when the targeted tables are ignored. When previously issued CREATE USER/ROLE commands are ignored by the replica because of the replication filtering rules, this results in an error because the targeted data does not exist. Solution: ======== Before checking that the given roles/user exist of a SET DEFAULT ROLE command, first ensure that the mysql.user and mysql.roles_mapping tables are not excluded by replication filters. Reviewed By: ============ Andrei Elkin Sergei Golubchik --- .../rpl/r/rpl_filter_set_var_missing_data.result | 55 ++++++++++++++ .../rpl/t/rpl_filter_set_var_missing_data.test | 83 ++++++++++++++++++++++ sql/sql_acl.cc | 50 +++++++++---- 3 files changed, 176 insertions(+), 12 deletions(-) create mode 100644 mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result create mode 100644 mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test diff --git a/mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result b/mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result new file mode 100644 index 00000000000..e232edae1ed --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_filter_set_var_missing_data.result @@ -0,0 +1,55 @@ +include/master-slave.inc +[connection master] +# +# Set replica to ignore system mysql tables +connection slave; +include/stop_slave.inc +SET @@GLOBAL.replicate_wild_ignore_table="mysql.%"; +include/start_slave.inc +# +# Execute grant-based commands on primary which modify mysql system +# tables +connection master; +CREATE ROLE journalist; +CREATE USER testuser@localhost IDENTIFIED by ''; +GRANT journalist to testuser@localhost; +# +# Execute SET commands which use the previous user/role data +SET DEFAULT ROLE journalist for testuser@localhost; +SET PASSWORD for testuser@localhost= PASSWORD('123'); +include/save_master_gtid.inc +# +# Verify primary's grant tables have the correct user/role data +select count(*)=1 from mysql.user where User='testuser'; +count(*)=1 +1 +select count(*)=1 from mysql.roles_mapping where User='testuser'; +count(*)=1 +1 +# +# Ensure that the replica receives all of the primary's events without +# error +connection slave; +include/sync_with_master_gtid.inc +Last_SQL_Error = +Last_SQL_Errno = 0 +# +# Verify that the replica did not execute the master's commands +select count(*)=0 from mysql.user where User='testuser'; +count(*)=0 +1 +select count(*)=0 from mysql.roles_mapping where User='testuser'; +count(*)=0 +1 +# +# Clean up +connection master; +DROP ROLE journalist; +DROP USER testuser@localhost; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/stop_slave.inc +SET @@GLOBAL.replicate_wild_ignore_table=""; +include/start_slave.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test b/mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test new file mode 100644 index 00000000000..25efb6ed662 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_filter_set_var_missing_data.test @@ -0,0 +1,83 @@ +# +# Purpose: +# This test ensures that the SET DEFAULT ROLE and SET PASSWORD commands can +# be ignored by replica filter rules. MDEV-28294 exposed a bug in which +# SET DEFAULT ROLE would check for the existence of the given roles/user even +# when the targeted tables are ignored, resulting in errors if the targeted +# data does not exist. More specifically, when previously issued +# CREATE USER/ROLE commands are ignored by the replica because of the +# replication filtering rules, SET DEFAULT ROLE would result in an error +# because the targeted data does not exist. +# +# Methodology: +# Using a replica configured with replicate_wild_ignore_table="mysql.%", +# execute SET DEFAULT ROLE and SET PASSWORD on the primary and ensure that the +# replica neither errors nor executes the commands which the primary sends. +# +# References: +# MDEV-28294: set default role bypasses Replicate_Wild_Ignore_Table: mysql.% +# + +source include/master-slave.inc; +source include/have_binlog_format_mixed.inc; + +--echo # +--echo # Set replica to ignore system mysql tables +connection slave; +let $old_filter= query_get_value(SHOW SLAVE STATUS, Replicate_Wild_Ignore_Table, 1); +source include/stop_slave.inc; +SET @@GLOBAL.replicate_wild_ignore_table="mysql.%"; +source include/start_slave.inc; + +--echo # +--echo # Execute grant-based commands on primary which modify mysql system +--echo # tables +connection master; +CREATE ROLE journalist; +CREATE USER testuser@localhost IDENTIFIED by ''; +GRANT journalist to testuser@localhost; + +--echo # +--echo # Execute SET commands which use the previous user/role data +SET DEFAULT ROLE journalist for testuser@localhost; +SET PASSWORD for testuser@localhost= PASSWORD('123'); +--source include/save_master_gtid.inc + +--echo # +--echo # Verify primary's grant tables have the correct user/role data +select count(*)=1 from mysql.user where User='testuser'; +select count(*)=1 from mysql.roles_mapping where User='testuser'; + +--echo # +--echo # Ensure that the replica receives all of the primary's events without +--echo # error +connection slave; +--source include/sync_with_master_gtid.inc +let $error= query_get_value(SHOW SLAVE STATUS, Last_SQL_Error, 1); +--echo Last_SQL_Error = $error +let $errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1); +--echo Last_SQL_Errno = $errno + +--echo # +--echo # Verify that the replica did not execute the master's commands +select count(*)=0 from mysql.user where User='testuser'; +select count(*)=0 from mysql.roles_mapping where User='testuser'; + +--echo # +--echo # Clean up + +# The master has to drop the role/user combination while the slave still has +# its filters active; otherwise, the slave would try to drop users/roles that +# were never replicated. +--connection master +DROP ROLE journalist; +DROP USER testuser@localhost; +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc +source include/stop_slave.inc; +--eval SET @@GLOBAL.replicate_wild_ignore_table="$old_filter" +source include/start_slave.inc; + +--source include/rpl_end.inc diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 3a605f8e7b7..930c107cde2 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -1249,18 +1249,8 @@ class Grant_tables DBUG_ENTER("Grant_tables::open_and_lock"); DBUG_ASSERT(first_table_in_list); #ifdef HAVE_REPLICATION - if (first_table_in_list->tl.lock_type >= TL_WRITE_ALLOW_WRITE && - thd->slave_thread && !thd->spcont) - { - /* - GRANT and REVOKE are applied the slave in/exclusion rules as they are - some kind of updates to the mysql.% tables. - */ - Rpl_filter *rpl_filter= thd->system_thread_info.rpl_sql_info->rpl_filter; - if (rpl_filter->is_on() && - !rpl_filter->tables_ok(0, &first_table_in_list->tl)) - DBUG_RETURN(1); - } + if (int ignore_ret= rpl_ignore_tables(thd)) + DBUG_RETURN(ignore_ret); #endif if (open_and_lock_tables(thd, &first_table_in_list->tl, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT)) @@ -1290,6 +1280,32 @@ class Grant_tables DBUG_RETURN(0); } +#ifdef HAVE_REPLICATION + /* Checks if the tables targeted by a grant command should be ignored because + of the configured replication filters + + @retval 1 Tables are excluded for replication + @retval 0 tables are included for replication + */ + int rpl_ignore_tables(THD *thd) + { + DBUG_ENTER("Grant_tables::rpl_ignore_tables"); + if (first_table_in_list->tl.lock_type >= TL_WRITE_ALLOW_WRITE && + thd->slave_thread && !thd->spcont) + { + /* + GRANT and REVOKE are applied the slave in/exclusion rules as they are + some kind of updates to the mysql.% tables. + */ + Rpl_filter *rpl_filter= thd->system_thread_info.rpl_sql_info->rpl_filter; + if (rpl_filter->is_on() && + !rpl_filter->tables_ok(0, &first_table_in_list->tl)) + DBUG_RETURN(1); + } + DBUG_RETURN(0); + } +#endif + inline const User_table& user_table() const { return m_user_table; @@ -3469,6 +3485,16 @@ int acl_check_set_default_role(THD *thd, const char *host, const char *user, const char *role) { DBUG_ENTER("acl_check_set_default_role"); +#ifdef HAVE_REPLICATION + /* + If the roles_mapping table is excluded by the replication filter, we return + successful without validating the user/role data because the command will + be ignored in a later call to `acl_set_default_role()` for a graceful exit. + */ + Grant_tables tables(Table_roles_mapping, TL_WRITE); + if (tables.rpl_ignore_tables(thd)) + DBUG_RETURN(0); +#endif DBUG_RETURN(check_alter_user(thd, host, user) || check_user_can_set_role(thd, user, host, NULL, role, NULL)); } -- cgit v1.2.1 From 0b805733105cd5b5f8b16146ec804e56018b43d9 Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Mon, 22 Aug 2022 18:36:30 +0530 Subject: MDEV-29319 Assertion failure size_in_header >= space.free_limit in fsp_get_available_space_in_free_extents() - Race condition between fsp_get_available_space_in_free_extents() and fsp_try_extend_data_file() while accessing space.free_limit. Before calling fsp_get_available_space_in_free_extents(), take shared lock on space->latch. --- storage/innobase/handler/ha_innodb.cc | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index e1b645c1cc3..1e9c9f03ed1 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -14202,7 +14202,7 @@ been acquired by the caller who holds it for the calculation, @param[in] space tablespace object from fil_space_acquire() @return available space in KiB */ static uintmax_t -fsp_get_available_space_in_free_extents(const fil_space_t& space) +fsp_get_available_space_in_free_extents(fil_space_t& space) { ulint size_in_header = space.size_in_header; if (size_in_header < FSP_EXTENT_SIZE) { @@ -14394,9 +14394,11 @@ ha_innobase::info_low( stats.index_file_length = ulonglong(stat_sum_of_other_index_sizes) * size; + rw_lock_s_lock(&space->latch); stats.delete_length = 1024 * fsp_get_available_space_in_free_extents( *space); + rw_lock_s_unlock(&space->latch); } stats.check_time = 0; stats.mrr_length_per_rec= (uint)ref_length + 8; // 8 = max(sizeof(void *)); -- cgit v1.2.1 From dd737d071e1508b4b1441ee7079f5e93de9d2fed Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Wed, 17 Aug 2022 18:09:06 +0530 Subject: MDEV-29291 Assertion `!table->fts' failed in dict_table_can_be_evicted on SHUTDOWN - InnoDB fts table initially added to LRU table cache while creating the table. Later, table was marked as non-evicted when we add the table to fts optimizer list. Before marking the table as non-evicted, master thread can try to evict the fts table. --- storage/innobase/dict/dict0crea.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/innobase/dict/dict0crea.cc b/storage/innobase/dict/dict0crea.cc index eba70aca6d1..f0754295aef 100644 --- a/storage/innobase/dict/dict0crea.cc +++ b/storage/innobase/dict/dict0crea.cc @@ -1271,7 +1271,7 @@ dict_create_table_step( if (node->state == TABLE_ADD_TO_CACHE) { DBUG_EXECUTE_IF("ib_ddl_crash_during_create", DBUG_SUICIDE();); - node->table->can_be_evicted = true; + node->table->can_be_evicted = !node->table->fts; node->table->add_to_cache(); err = DB_SUCCESS; -- cgit v1.2.1 From 61f456e772cc3d907a3b7881dc4dfb7edc1401d5 Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Wed, 24 Aug 2022 12:27:15 +0530 Subject: MDEV-29319 Assertion failure size_in_header >= space.free_limit in fsp_get_available_space_in_free_extents() - Don't remove the constant parameter in fsp_get_available_space_in_free_extents() --- storage/innobase/handler/ha_innodb.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 1e9c9f03ed1..9ce1915b2cd 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -14202,7 +14202,7 @@ been acquired by the caller who holds it for the calculation, @param[in] space tablespace object from fil_space_acquire() @return available space in KiB */ static uintmax_t -fsp_get_available_space_in_free_extents(fil_space_t& space) +fsp_get_available_space_in_free_extents(const fil_space_t& space) { ulint size_in_header = space.size_in_header; if (size_in_header < FSP_EXTENT_SIZE) { -- cgit v1.2.1 From a3fd9e6b064e28256f932595fb5f2ac3c33072fc Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Wed, 24 Aug 2022 10:09:27 +0200 Subject: MDEV-29367 Refactor tpool::cache Removed use std::vector's ba push_back(), pop_back() to make it more obvious that memory in the vectors won't be reallocated. Also, "borrowed" elements can be debugged a little better now, they are put into the start of the m_cache vector. --- storage/innobase/os/os0file.cc | 2 +- tpool/tpool_structs.h | 130 ++++++++++++++++++++++++++++------------- 2 files changed, 89 insertions(+), 43 deletions(-) diff --git a/storage/innobase/os/os0file.cc b/storage/innobase/os/os0file.cc index 09746085ca7..efb036a6a96 100644 --- a/storage/innobase/os/os0file.cc +++ b/storage/innobase/os/os0file.cc @@ -121,7 +121,7 @@ public: size_t pending_io_count() { - return (size_t)m_max_aio - m_cache.size(); + return m_cache.pos(); } tpool::task_group* get_task_group() diff --git a/tpool/tpool_structs.h b/tpool/tpool_structs.h index 7b0fb857695..b49204f2d75 100644 --- a/tpool/tpool_structs.h +++ b/tpool/tpool_structs.h @@ -40,79 +40,121 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02111 - 1301 USA*/ namespace tpool { -enum cache_notification_mode -{ - NOTIFY_ONE, - NOTIFY_ALL -}; - /** Generic "pointer" cache of a fixed size with fast put/get operations. - Compared to STL containers, is faster/does not - do allocations. However, put() operation will wait - if there is no free items. + Compared to STL containers,e.g stack or queue + is faster/does not do allocations. + + However, get() operation will wait if there is no free items. + + We assume that put() will only put back the elements that + were retrieved previously with get(). */ template class cache { + /** Protects updates of m_pos and m_cache members */ std::mutex m_mtx; + + /** + Notify waiting threads about "cache full" or "cache not empty" conditions + @see get() and wait() + */ std::condition_variable m_cv; - std::vector m_base; + + /** Cached items vector.Does not change after construction */ + std::vector m_base; + + /** + Pointers to cached items. Protected by m_mtx. Does not grow after + construction. Elements in position [0,m_pos-1] are "borrowed", + elements in position [m_pos,capacity()-1] are "free" + */ std::vector m_cache; - cache_notification_mode m_notification_mode; + + /** Number of threads waiting for "cache full" condition (s. wait()) + Protected by m_mtx */ int m_waiters; + /** Current cache size. Protected by m_mtx*/ + size_t m_pos; + +private: + + inline size_t capacity() + { + return m_base.size(); + } + + /** + @return true if cache is full (no items are borrowed) + */ bool is_full() { - return m_cache.size() == m_base.size(); + return m_pos == 0; + } + + /** + @return true if cache is empty (all items are borrowed) + */ + bool is_empty() + { + return m_pos == capacity(); } public: - cache(size_t count, cache_notification_mode mode= tpool::cache_notification_mode::NOTIFY_ALL): - m_mtx(), m_cv(), m_base(count),m_cache(count), m_notification_mode(mode),m_waiters() + /** + Constructor + @param size - maximum number of items in cache + */ + cache(size_t size) : m_mtx(), m_cv(), m_base(size), m_cache(size), + m_waiters(), m_pos(0) { - for(size_t i = 0 ; i < count; i++) - m_cache[i]=&m_base[i]; + for(size_t i= 0 ; i < size; i++) + m_cache[i]= &m_base[i]; } - T* get(bool blocking=true) + /** + Retrieve an item from cache. Waits for free item, if cache is + currently empty. + @return borrowed item + */ + T* get() { std::unique_lock lk(m_mtx); - if (blocking) - { - while(m_cache.empty()) - m_cv.wait(lk); - } - else - { - if(m_cache.empty()) - return nullptr; - } - T* ret = m_cache.back(); - m_cache.pop_back(); - return ret; + while(is_empty()) + m_cv.wait(lk); + assert(m_pos < capacity()); + // return last element + return m_cache[m_pos++]; } - + /** + Put back an item to cache. + @param item - item to put back + */ void put(T *ele) { std::unique_lock lk(m_mtx); - m_cache.push_back(ele); - if (m_notification_mode == NOTIFY_ONE) - m_cv.notify_one(); - else if(m_cache.size() == 1) - m_cv.notify_all(); // Signal cache is not empty - else if(m_waiters && is_full()) - m_cv.notify_all(); // Signal cache is full + assert(!is_full()); + // put element to the logical end of the array + m_cache[--m_pos] = ele; + + /* Notify waiters when the cache becomes + not empty, or when it becomes full */ + if (m_pos == 1 || (m_waiters && is_full())) + m_cv.notify_all(); } + /** Check if pointer represents cached element */ bool contains(T* ele) { - return ele >= &m_base[0] && ele <= &m_base[m_base.size() -1]; + // No locking required, m_base does not change after construction. + return ele >= &m_base[0] && ele <= &m_base[capacity() - 1]; } - /* Wait until cache is full.*/ + /** Wait until cache is full.*/ void wait() { std::unique_lock lk(m_mtx); @@ -122,9 +164,13 @@ public: m_waiters--; } - TPOOL_SUPPRESS_TSAN size_t size() + /** + @return approximate number of "borrowed" items. + A "dirty" read, not used in any critical functionality. + */ + TPOOL_SUPPRESS_TSAN size_t pos() { - return m_cache.size(); + return m_pos; } }; -- cgit v1.2.1 From f2a53b6158a9c641e5dde679e45c7ff721902e77 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 24 Aug 2022 15:00:47 +0300 Subject: btr_search_drop_page_hash_index(): Remove a racey debug check --- storage/innobase/btr/btr0sea.cc | 5 +---- 1 file changed, 1 insertion(+), 4 deletions(-) diff --git a/storage/innobase/btr/btr0sea.cc b/storage/innobase/btr/btr0sea.cc index 8cc30de0dd8..4b4bba9a941 100644 --- a/storage/innobase/btr/btr0sea.cc +++ b/storage/innobase/btr/btr0sea.cc @@ -2,7 +2,7 @@ Copyright (c) 1996, 2016, Oracle and/or its affiliates. All Rights Reserved. Copyright (c) 2008, Google Inc. -Copyright (c) 2017, 2021, MariaDB Corporation. +Copyright (c) 2017, 2022, MariaDB Corporation. Portions of this file contain modifications contributed and copyrighted by Google, Inc. Those modifications are gratefully acknowledged and are described @@ -1123,9 +1123,6 @@ void btr_search_drop_page_hash_index(buf_block_t* block, rw_lock_t* latch; retry: - /* This debug check uses a dirty read that could theoretically cause - false positives while buf_pool_clear_hash_index() is executing. */ - assert_block_ahi_valid(block); ut_ad(!btr_search_own_any(RW_LOCK_S)); ut_ad(!btr_search_own_any(RW_LOCK_X)); -- cgit v1.2.1 From 8ff10969996a5349e906fab44b45c6e3ec53eea0 Mon Sep 17 00:00:00 2001 From: Vlad Lesin Date: Wed, 24 Aug 2022 17:06:57 +0300 Subject: MDEV-29081 trx_t::lock.was_chosen_as_deadlock_victim race in lock_wait_end() MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit The issue is that trx_t::lock.was_chosen_as_deadlock_victim can be reset before the transaction check it and set trx_t::error_state. The fix is to reset trx_t::lock.was_chosen_as_deadlock_victim only in trx_t::commit_in_memory(), which is invoked on full rollback. There is also no need to have separate bit in trx_t::lock.was_chosen_as_deadlock_victim to flag transaction it was chosen as a victim of Galera conflict resolution, the same variable can be used for both cases except debug build. For debug build we need to distinguish deadlock and Galera's abort victims for debug checks. Also there is no need to check for deadlock in lock_table_enqueue_waiting() for Galera as the coresponding check presents in lock_wait(). Local variable "error_state" in lock_wait() was replaced with trx->error_state, because before the replace lock_sys_t::cancel(trx, lock) and lock_sys.deadlock_check() could change trx->error_state, which then could be overwritten with the local "error_state" variable value. The lock_wait_suspend_thread_enter DEBUG_SYNC point name is misleading, because lock_wait_suspend_thread was eliminated in e71e613. It was renamed to lock_wait_start. Reviewed by: Marko Mäkelä, Jan Lindström. --- .../r/galera_FK_duplicate_client_insert.result | 40 ++++---- .../t/galera_FK_duplicate_client_insert.test | 4 +- .../suite/innodb/r/cursor-restore-locking.result | 4 +- .../suite/innodb/r/deadlock_victim_race.result | 42 ++++++++ mysql-test/suite/innodb/r/update-cascade.result | 8 +- .../suite/innodb/t/cursor-restore-locking.test | 6 +- .../suite/innodb/t/deadlock_victim_race.test | 102 ++++++++++++++++++++ mysql-test/suite/innodb/t/update-cascade.test | 8 +- storage/innobase/dict/drop.cc | 3 +- storage/innobase/include/trx0trx.h | 30 +++--- storage/innobase/lock/lock0lock.cc | 107 ++++++++++++++------- storage/innobase/trx/trx0roll.cc | 10 +- storage/innobase/trx/trx0trx.cc | 3 +- 13 files changed, 271 insertions(+), 96 deletions(-) create mode 100644 mysql-test/suite/innodb/r/deadlock_victim_race.result create mode 100644 mysql-test/suite/innodb/t/deadlock_victim_race.test diff --git a/mysql-test/suite/galera/r/galera_FK_duplicate_client_insert.result b/mysql-test/suite/galera/r/galera_FK_duplicate_client_insert.result index 5ae577a6323..b40c57c5d90 100644 --- a/mysql-test/suite/galera/r/galera_FK_duplicate_client_insert.result +++ b/mysql-test/suite/galera/r/galera_FK_duplicate_client_insert.result @@ -14,7 +14,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -32,7 +32,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -50,7 +50,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -68,7 +68,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -86,7 +86,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -104,7 +104,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -122,7 +122,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -140,7 +140,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -158,7 +158,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -176,7 +176,7 @@ connection node_1_u; begin; update user set j = j + 1 WHERE id > 0; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; insert into user_session(id,fk1,fk2) values (2, 2, 2); connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -202,7 +202,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -220,7 +220,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -238,7 +238,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -256,7 +256,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -274,7 +274,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -292,7 +292,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -310,7 +310,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -328,7 +328,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -346,7 +346,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; @@ -364,7 +364,7 @@ connection node_1_u; begin; execute upd; connection node_1_i; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; +set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; execute ins1; connection node_1; set debug_sync='now WAIT_FOR ins_waiting'; diff --git a/mysql-test/suite/galera/t/galera_FK_duplicate_client_insert.test b/mysql-test/suite/galera/t/galera_FK_duplicate_client_insert.test index 02322fc02ec..6dd1fee1d4e 100644 --- a/mysql-test/suite/galera/t/galera_FK_duplicate_client_insert.test +++ b/mysql-test/suite/galera/t/galera_FK_duplicate_client_insert.test @@ -72,7 +72,7 @@ while($counter > 0) update user set j = j + 1 WHERE id > 0; --connection node_1_i - set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; + set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; send insert into user_session(id,fk1,fk2) values (2, 2, 2); --connection node_1 @@ -126,7 +126,7 @@ while($counter > 0) #update user set j = j + 1 WHERE id > 0; --connection node_1_i - set debug_sync='lock_wait_suspend_thread_enter SIGNAL ins_waiting WAIT_FOR cont_ins'; + set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; send execute ins1; --connection node_1 diff --git a/mysql-test/suite/innodb/r/cursor-restore-locking.result b/mysql-test/suite/innodb/r/cursor-restore-locking.result index bc1127f57b3..beeb5a87844 100644 --- a/mysql-test/suite/innodb/r/cursor-restore-locking.result +++ b/mysql-test/suite/innodb/r/cursor-restore-locking.result @@ -7,12 +7,12 @@ SET DEBUG_SYNC = 'innodb_row_search_for_mysql_exit SIGNAL first_del_row_search_m DELETE FROM t WHERE b = 20; connect con_ins_1,localhost,root,,; SET DEBUG_SYNC = 'now WAIT_FOR first_del_row_search_mvcc_finished'; -SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL first_ins_locked'; +SET DEBUG_SYNC = 'lock_wait_start 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 DEBUG_SYNC = 'now WAIT_FOR first_ins_locked'; -SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL second_del_locked'; +SET DEBUG_SYNC = 'lock_wait_start SIGNAL second_del_locked'; DELETE FROM t WHERE b = 20; connection default; SET DEBUG_SYNC = 'now WAIT_FOR second_del_locked'; diff --git a/mysql-test/suite/innodb/r/deadlock_victim_race.result b/mysql-test/suite/innodb/r/deadlock_victim_race.result new file mode 100644 index 00000000000..9a7ef51ef24 --- /dev/null +++ b/mysql-test/suite/innodb/r/deadlock_victim_race.result @@ -0,0 +1,42 @@ +CREATE TABLE t (a int PRIMARY KEY, b int) engine = InnoDB; +CREATE TABLE t2 (a int PRIMARY KEY) engine = InnoDB; +INSERT INTO t VALUES (10, 10), (20, 20), (30, 30); +INSERT INTO t2 VALUES (10), (20), (30); +BEGIN; +SELECT * FROM t WHERE a = 20 FOR UPDATE; +a b +20 20 +connect con_2,localhost,root,,; +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +SET DEBUG_SYNC = 'lock_trx_handle_wait_enter SIGNAL upd_locked WAIT_FOR upd_cont EXECUTE 2'; +UPDATE t SET b = 100; +connect con_3,localhost,root,,; +BEGIN; +UPDATE t2 SET a = a + 100; +SELECT * FROM t WHERE a = 30 FOR UPDATE; +a b +30 30 +SET DEBUG_SYNC='now WAIT_FOR upd_locked'; +SET DEBUG_SYNC = 'lock_wait_start SIGNAL sel_locked'; +SELECT * FROM t WHERE a = 20 FOR UPDATE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR sel_locked'; +ROLLBACK; +SET DEBUG_SYNC='now SIGNAL upd_cont'; +SET innodb_lock_wait_timeout=1; +SET DEBUG_SYNC="now WAIT_FOR upd_locked"; +SET DEBUG_SYNC="lock_wait_end SIGNAL upd_cont"; +SELECT * FROM t WHERE a = 10 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection con_3; +a b +20 20 +connection con_2; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +disconnect con_3; +disconnect con_2; +connection default; +SET DEBUG_SYNC = 'RESET'; +DROP TABLE t; +DROP TABLE t2; diff --git a/mysql-test/suite/innodb/r/update-cascade.result b/mysql-test/suite/innodb/r/update-cascade.result index a3c8fed931e..21d8f11e1cb 100644 --- a/mysql-test/suite/innodb/r/update-cascade.result +++ b/mysql-test/suite/innodb/r/update-cascade.result @@ -38,7 +38,7 @@ select f1, f2 from t2 for update; f1 f2 1 2 connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; update t1 set f1 = 10 where f1 = 2; connection con1; set debug_sync='now WAIT_FOR upd_waiting'; @@ -97,7 +97,7 @@ select f1, f2 from t2 for update; f1 f2 1 91 connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; update t1 set f2 = 28 where f2 = 91; connection con1; set debug_sync='now WAIT_FOR upd_waiting'; @@ -164,7 +164,7 @@ select f1 from t3 for update; f1 2 connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; update t1 set f1 = 10 where f1 = 2; connection con1; set debug_sync='now WAIT_FOR upd_waiting'; @@ -253,7 +253,7 @@ select f1 from t3 for update; f1 2 connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; update t1 set f2 = 28 where f2 = 91; connection con1; set debug_sync='now WAIT_FOR upd_waiting'; diff --git a/mysql-test/suite/innodb/t/cursor-restore-locking.test b/mysql-test/suite/innodb/t/cursor-restore-locking.test index d032d8a8def..a398768fc66 100644 --- a/mysql-test/suite/innodb/t/cursor-restore-locking.test +++ b/mysql-test/suite/innodb/t/cursor-restore-locking.test @@ -16,19 +16,19 @@ SET DEBUG_SYNC = 'innodb_row_search_for_mysql_exit SIGNAL first_del_row_search_m --connect(con_ins_1,localhost,root,,) SET DEBUG_SYNC = 'now WAIT_FOR first_del_row_search_mvcc_finished'; # It's supposed the following INSERT will be suspended just after -# lock_wait_suspend_thread_enter syncpoint, and will be awaken +# lock_wait_start syncpoint, and will be awaken # after the previous DELETE commits. ib_after_row_insert will be executed # after the INSERT is woken up. The previous DELETE will wait for # first_del_cont signal before commit, and this signal will be sent later. # So it's safe to use two signals in a row here, it's guaranted the first # signal will be received before the second signal is sent. -SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL first_ins_locked'; +SET DEBUG_SYNC = 'lock_wait_start SIGNAL first_ins_locked'; SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL first_ins_row_inserted WAIT_FOR first_ins_cont'; --send INSERT INTO t VALUES(10, 20) --connect(con_del_2,localhost,root,,) SET DEBUG_SYNC = 'now WAIT_FOR first_ins_locked'; -SET DEBUG_SYNC = 'lock_wait_suspend_thread_enter SIGNAL second_del_locked'; +SET DEBUG_SYNC = 'lock_wait_start SIGNAL second_del_locked'; ############################################################################### # This DELETE is locked by the previous DELETE, after that DELETE is # committed, it will still be locked by the next INSERT on delete-marked diff --git a/mysql-test/suite/innodb/t/deadlock_victim_race.test b/mysql-test/suite/innodb/t/deadlock_victim_race.test new file mode 100644 index 00000000000..3c9dd15fb4d --- /dev/null +++ b/mysql-test/suite/innodb/t/deadlock_victim_race.test @@ -0,0 +1,102 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/count_sessions.inc + +CREATE TABLE t (a int PRIMARY KEY, b int) engine = InnoDB; +CREATE TABLE t2 (a int PRIMARY KEY) engine = InnoDB; + +INSERT INTO t VALUES (10, 10), (20, 20), (30, 30); +INSERT INTO t2 VALUES (10), (20), (30); + +BEGIN; # trx 1 +SELECT * FROM t WHERE a = 20 FOR UPDATE; +# Locking order: +# (10,10) (20,20) (30,30) +# ^ +# trx 1 + +--connect(con_2,localhost,root,,) +# RC is neccessary to do semi-consistent read +SET TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; # trx 2 +# The first time it will be hit on trying to lock (20,20), the second hit +# will be on (30,30). +SET DEBUG_SYNC = 'lock_trx_handle_wait_enter SIGNAL upd_locked WAIT_FOR upd_cont EXECUTE 2'; +# We must not modify primary key fields to cause rr_sequential() read record +# function choosing in mysql_update(), i.e. both query_plan.using_filesort and +# query_plan.using_io_buffer must be false during init_read_record() call. +--send UPDATE t SET b = 100 + +--connect(con_3,localhost,root,,) +BEGIN; # trx 3 +# The following update is necessary to increase the transaction weight, which is +# calculated as the number of locks + the number of undo records during deadlock +# report. Victim's transaction should have minimum weight. We need trx 2 to be +# choosen as victim, that's why we need to increase the current transaction +# weight. +UPDATE t2 SET a = a + 100; +SELECT * FROM t WHERE a = 30 FOR UPDATE; +SET DEBUG_SYNC='now WAIT_FOR upd_locked'; +# Locking queue: +# (10,10) (20,20) (30,30) +# ^ ^ ^ +# trx 2 trx 1 trx 3 +# trx 2 (waiting for 1) + +SET DEBUG_SYNC = 'lock_wait_start SIGNAL sel_locked'; +--send SELECT * FROM t WHERE a = 20 FOR UPDATE +--connection default +SET DEBUG_SYNC='now WAIT_FOR sel_locked'; +# Locking queue: +# (10,10) (20,20) (30,30) +# ^ ^ ^ +# trx 2 trx 1 trx 3 +# trx 2 (waiting for 1) +# trx 3 (waiting for 1) +# +# Note trx 1 must grant lock to trx2 before trx 2 checks the lock state in +# lock_trx_handle_wait(), i.e. the function must return DB_SUCCESS, that's why +# the following ROLLBACK must be executed before sending upd_cont signal. +ROLLBACK; +SET DEBUG_SYNC='now SIGNAL upd_cont'; + +SET innodb_lock_wait_timeout=1; +SET DEBUG_SYNC="now WAIT_FOR upd_locked"; +# Locking queue: +# (10,10) (20,20) (30,30) +# ^ ^ ^ +# trx 2 trx 2 trx 3 +# trx 3 (waiting for 2) trx 2 (waiting for 3) +# +# Deadlock happened after trx 1 granted lock to trx 2, and trx2 continued +# sequential read (with rr_sequential() read record function), and requested +# lock on (30,30). But the deadlock has not been determined yet. + +SET DEBUG_SYNC="lock_wait_end SIGNAL upd_cont"; +--error ER_LOCK_WAIT_TIMEOUT +# The deadlock will be determined in lock_wait() after lock wait timeout +# expired. +SELECT * FROM t WHERE a = 10 FOR UPDATE; + +--connection con_3 +--reap + +--connection con_2 +# As lock_trx_handle_wait() wrongly returned DB_SUCCESS instead of +# DB_DEADLOCK, row_search_mvcc() of trx 2 behaves so as if (30,30) was locked. +# But the waiting(for trx 3) lock was cancelled by deadlock checker after +# trx 2 was choosen as a victim (see lock_cancel_waiting_and_release() call +# from Deadlock::report() for details). The try to update non-locked record +# will cause assertion if the bug is not fixed. +--error ER_LOCK_DEADLOCK +--reap + +--disconnect con_3 +--disconnect con_2 + +--connection default +SET DEBUG_SYNC = 'RESET'; +DROP TABLE t; +DROP TABLE t2; + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/update-cascade.test b/mysql-test/suite/innodb/t/update-cascade.test index de8294703b4..69e81ac4a2f 100644 --- a/mysql-test/suite/innodb/t/update-cascade.test +++ b/mysql-test/suite/innodb/t/update-cascade.test @@ -28,7 +28,7 @@ start transaction; select f1, f2 from t2 for update; connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; send update t1 set f1 = 10 where f1 = 2; connection con1; @@ -72,7 +72,7 @@ start transaction; select f1, f2 from t2 for update; connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; send update t1 set f2 = 28 where f2 = 91; connection con1; @@ -120,7 +120,7 @@ start transaction; select f1 from t3 for update; connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; send update t1 set f1 = 10 where f1 = 2; connection con1; @@ -183,7 +183,7 @@ start transaction; select f1 from t3 for update; connection default; -set debug_sync='lock_wait_suspend_thread_enter SIGNAL upd_waiting WAIT_FOR go_upd'; +set debug_sync='lock_wait_start SIGNAL upd_waiting WAIT_FOR go_upd'; send update t1 set f2 = 28 where f2 = 91; connection con1; diff --git a/storage/innobase/dict/drop.cc b/storage/innobase/dict/drop.cc index 4a4e10b45a8..edb6add0787 100644 --- a/storage/innobase/dict/drop.cc +++ b/storage/innobase/dict/drop.cc @@ -245,7 +245,8 @@ void trx_t::commit(std::vector &deleted) ut_ad(ib_vector_is_empty(autoinc_locks)); mem_heap_empty(lock.lock_heap); lock.table_locks.clear(); - lock.was_chosen_as_deadlock_victim= false; + /* commit_persist() already reset this. */ + ut_ad(!lock.was_chosen_as_deadlock_victim); lock.n_rec_locks= 0; while (dict_table_t *table= UT_LIST_GET_FIRST(lock.evicted_tables)) { diff --git a/storage/innobase/include/trx0trx.h b/storage/innobase/include/trx0trx.h index a45bbac0334..91d1ec94e1b 100644 --- a/storage/innobase/include/trx0trx.h +++ b/storage/innobase/include/trx0trx.h @@ -338,27 +338,11 @@ struct trx_lock_t /** lock wait start time */ Atomic_relaxed suspend_time; +#if defined(UNIV_DEBUG) || !defined(DBUG_OFF) /** 2=high priority WSREP thread has marked this trx to abort; 1=another transaction chose this as a victim in deadlock resolution. */ Atomic_relaxed was_chosen_as_deadlock_victim; - /** Clear the deadlock victim status. */ - void clear_deadlock_victim() - { -#ifndef WITH_WSREP - was_chosen_as_deadlock_victim= false; -#elif defined __GNUC__ && (defined __i386__ || defined __x86_64__) - /* There is no 8-bit version of the 80386 BTR instruction. - Technically, this is the wrong addressing mode (16-bit), but - there are other data members stored after the byte. */ - __asm__ __volatile__("lock btrw $0, %0" - : "+m" (was_chosen_as_deadlock_victim)); -#else - was_chosen_as_deadlock_victim.fetch_and(byte(~1)); -#endif - } - -#ifdef WITH_WSREP /** Flag the lock owner as a victim in Galera conflict resolution. */ void set_wsrep_victim() { @@ -372,7 +356,17 @@ struct trx_lock_t was_chosen_as_deadlock_victim.fetch_or(2); # endif } -#endif +#else /* defined(UNIV_DEBUG) || !defined(DBUG_OFF) */ + + /** High priority WSREP thread has marked this trx to abort or + another transaction chose this as a victim in deadlock resolution. */ + Atomic_relaxed was_chosen_as_deadlock_victim; + + /** Flag the lock owner as a victim in Galera conflict resolution. */ + void set_wsrep_victim() { + was_chosen_as_deadlock_victim= true; + } +#endif /* defined(UNIV_DEBUG) || !defined(DBUG_OFF) */ /** Next available rec_pool[] entry */ byte rec_cached; diff --git a/storage/innobase/lock/lock0lock.cc b/storage/innobase/lock/lock0lock.cc index 76ad538ea0e..1c11efafc7a 100644 --- a/storage/innobase/lock/lock0lock.cc +++ b/storage/innobase/lock/lock0lock.cc @@ -44,6 +44,7 @@ Created 5/7/1996 Heikki Tuuri #include "row0vers.h" #include "pars0pars.h" #include "srv0mon.h" +#include "scope.h" #include @@ -1275,6 +1276,14 @@ lock_rec_enqueue_waiting( trx_t* trx = thr_get_trx(thr); ut_ad(xtest() || trx->mutex_is_owner()); ut_ad(!trx->dict_operation_lock_mode); + /* Apart from Galera, only transactions that have waiting lock can be + chosen as deadlock victim. Only one lock can be waited for at a time, + and a transaction is associated with a single thread. That is why there + must not be waiting lock requests if the transaction is deadlock victim + and it is not WSREP. Galera transaction abort can be invoked from MDL + acquisition code when the transaction does not have waiting record + lock, that's why we check only deadlock victim bit here. */ + ut_ad(!(trx->lock.was_chosen_as_deadlock_victim & 1)); if (trx->mysql_thd && thd_lock_wait_timeout(trx->mysql_thd) == 0) { trx->error_state = DB_LOCK_WAIT_TIMEOUT; @@ -1292,7 +1301,6 @@ lock_rec_enqueue_waiting( } trx->lock.wait_thr = thr; - trx->lock.clear_deadlock_victim(); DBUG_LOG("ib_lock", "trx " << ib::hex(trx->id) << " waits for lock in index " << index->name @@ -1475,7 +1483,14 @@ lock_rec_lock( que_thr_t* thr) /*!< in: query thread */ { trx_t *trx= thr_get_trx(thr); - + /* There must not be lock requests for reads or updates if transaction was + chosen as deadlock victim. Apart from Galera, only transactions that have + waiting lock may be chosen as deadlock victims. Only one lock can be waited + for at a time, and a transaction is associated with a single thread. Galera + transaction abort can be invoked from MDL acquisition code when the + transaction does not have waiting lock, that's why we check only deadlock + victim bit here. */ + ut_ad(!(trx->lock.was_chosen_as_deadlock_victim & 1)); ut_ad(!srv_read_only_mode); ut_ad(((LOCK_MODE_MASK | LOCK_TABLE) & mode) == LOCK_S || ((LOCK_MODE_MASK | LOCK_TABLE) & mode) == LOCK_X); @@ -1627,7 +1642,9 @@ void lock_sys_t::wait_resume(THD *thd, my_hrtime_t start, my_hrtime_t now) #ifdef HAVE_REPLICATION ATTRIBUTE_NOINLINE MY_ATTRIBUTE((nonnull)) -/** Report lock waits to parallel replication. +/** Report lock waits to parallel replication. Sets +trx->error_state= DB_DEADLOCK if trx->lock.was_chosen_as_deadlock_victim was +set when lock_sys.wait_mutex was unlocked. @param trx transaction that may be waiting for a lock @param wait_lock lock that is being waited for */ static void lock_wait_rpl_report(trx_t *trx) @@ -1642,7 +1659,8 @@ static void lock_wait_rpl_report(trx_t *trx) ut_ad(!(wait_lock->type_mode & LOCK_AUTO_INC)); /* This would likely be too large to attempt to use a memory transaction, even for wait_lock->is_table(). */ - if (!lock_sys.wr_lock_try()) + const bool nowait= lock_sys.wr_lock_try(); + if (!nowait) { mysql_mutex_unlock(&lock_sys.wait_mutex); lock_sys.wr_lock(SRW_LOCK_CALL); @@ -1652,6 +1670,10 @@ static void lock_wait_rpl_report(trx_t *trx) { func_exit: lock_sys.wr_unlock(); + /* trx->lock.was_chosen_as_deadlock_victim can be set when + lock_sys.wait_mutex was unlocked, let's check it. */ + if (!nowait && trx->lock.was_chosen_as_deadlock_victim) + trx->error_state= DB_DEADLOCK; return; } ut_ad(wait_lock->is_waiting()); @@ -1700,7 +1722,13 @@ dberr_t lock_wait(que_thr_t *thr) trx_t *trx= thr_get_trx(thr); if (trx->mysql_thd) - DEBUG_SYNC_C("lock_wait_suspend_thread_enter"); + DEBUG_SYNC_C("lock_wait_start"); + + /* Create the sync point for any quit from the function. */ + ut_d(SCOPE_EXIT([trx]() { + if (trx->mysql_thd) + DEBUG_SYNC_C("lock_wait_end"); + })); /* InnoDB system transactions may use the global value of innodb_lock_wait_timeout, because trx->mysql_thd == NULL. */ @@ -1731,11 +1759,8 @@ dberr_t lock_wait(que_thr_t *thr) { /* The lock has already been released or this transaction was chosen as a deadlock victim: no need to wait */ - if (trx->lock.was_chosen_as_deadlock_victim.fetch_and(byte(~1))) - trx->error_state= DB_DEADLOCK; - else - trx->error_state= DB_SUCCESS; - + trx->error_state= + trx->lock.was_chosen_as_deadlock_victim ? DB_DEADLOCK : DB_SUCCESS; return trx->error_state; } @@ -1770,7 +1795,7 @@ dberr_t lock_wait(que_thr_t *thr) wait_lock->un_member.tab_lock.table->id <= DICT_FIELDS_ID); thd_wait_begin(trx->mysql_thd, (type_mode & LOCK_TABLE) ? THD_WAIT_TABLE_LOCK : THD_WAIT_ROW_LOCK); - dberr_t error_state= DB_SUCCESS; + trx->error_state= DB_SUCCESS; mysql_mutex_lock(&lock_sys.wait_mutex); if (trx->lock.wait_lock) @@ -1778,23 +1803,28 @@ dberr_t lock_wait(que_thr_t *thr) if (Deadlock::check_and_resolve(trx)) { ut_ad(!trx->lock.wait_lock); - error_state= DB_DEADLOCK; + trx->error_state= DB_DEADLOCK; goto end_wait; } } else + { + /* trx->lock.was_chosen_as_deadlock_victim can be changed before + lock_sys.wait_mutex is acquired, so let's check it once more. */ + trx->error_state= + trx->lock.was_chosen_as_deadlock_victim ? DB_DEADLOCK : DB_SUCCESS; goto end_wait; - + } if (row_lock_wait) lock_sys.wait_start(); + trx->error_state= DB_SUCCESS; + #ifdef HAVE_REPLICATION if (rpl) lock_wait_rpl_report(trx); #endif - trx->error_state= DB_SUCCESS; - while (trx->lock.wait_lock) { int err; @@ -1807,20 +1837,19 @@ dberr_t lock_wait(que_thr_t *thr) else err= my_cond_timedwait(&trx->lock.cond, &lock_sys.wait_mutex.m_mutex, &abstime); - error_state= trx->error_state; - switch (error_state) { + switch (trx->error_state) { case DB_DEADLOCK: case DB_INTERRUPTED: break; default: - ut_ad(error_state != DB_LOCK_WAIT_TIMEOUT); + ut_ad(trx->error_state != DB_LOCK_WAIT_TIMEOUT); /* Dictionary transactions must ignore KILL, because they could be executed as part of a multi-transaction DDL operation, such as rollback_inplace_alter_table() or ha_innobase::delete_table(). */ if (!trx->dict_operation && trx_is_interrupted(trx)) /* innobase_kill_query() can only set trx->error_state=DB_INTERRUPTED for any transaction that is attached to a connection. */ - error_state= DB_INTERRUPTED; + trx->error_state= DB_INTERRUPTED; else if (!err) continue; #ifdef WITH_WSREP @@ -1828,7 +1857,7 @@ dberr_t lock_wait(que_thr_t *thr) #endif else { - error_state= DB_LOCK_WAIT_TIMEOUT; + trx->error_state= DB_LOCK_WAIT_TIMEOUT; lock_sys.timeouts++; } } @@ -1848,8 +1877,7 @@ end_wait: mysql_mutex_unlock(&lock_sys.wait_mutex); thd_wait_end(trx->mysql_thd); - trx->error_state= error_state; - return error_state; + return trx->error_state; } @@ -1862,7 +1890,7 @@ static void lock_wait_end(trx_t *trx) ut_ad(state == TRX_STATE_ACTIVE || state == TRX_STATE_PREPARED); ut_ad(trx->lock.wait_thr); - if (trx->lock.was_chosen_as_deadlock_victim.fetch_and(byte(~1))) + if (trx->lock.was_chosen_as_deadlock_victim) { ut_ad(state == TRX_STATE_ACTIVE); trx->error_state= DB_DEADLOCK; @@ -3401,17 +3429,18 @@ lock_table_enqueue_waiting( ut_ad(trx->mutex_is_owner()); ut_ad(!trx->dict_operation_lock_mode); -#ifdef WITH_WSREP - if (trx->is_wsrep() && trx->lock.was_chosen_as_deadlock_victim) { - return(DB_DEADLOCK); - } -#endif /* WITH_WSREP */ - /* Enqueue the lock request that will wait to be granted */ lock_table_create(table, mode | LOCK_WAIT, trx, c_lock); trx->lock.wait_thr = thr; - trx->lock.clear_deadlock_victim(); + /* Apart from Galera, only transactions that have waiting lock + may be chosen as deadlock victims. Only one lock can be waited for at a + time, and a transaction is associated with a single thread. That is why + there must not be waiting lock requests if the transaction is deadlock + victim and it is not WSREP. Galera transaction abort can be invoked + from MDL acquisition code when the transaction does not have waiting + lock, that's why we check only deadlock victim bit here. */ + ut_ad(!(trx->lock.was_chosen_as_deadlock_victim & 1)); MONITOR_INC(MONITOR_TABLELOCK_WAIT); return(DB_LOCK_WAIT); @@ -3949,7 +3978,6 @@ released: mysql_mutex_unlock(&lock_sys.wait_mutex); } - trx->lock.was_chosen_as_deadlock_victim= false; trx->lock.n_rec_locks= 0; #ifdef UNIV_DEBUG @@ -5718,10 +5746,12 @@ dberr_t lock_sys_t::cancel(trx_t *trx, lock_t *lock) lock_sys.rd_lock(SRW_LOCK_CALL); mysql_mutex_lock(&lock_sys.wait_mutex); lock= trx->lock.wait_lock; - if (!lock); - else if (check_victim && trx->lock.was_chosen_as_deadlock_victim) + /* Even if waiting lock was cancelled while lock_sys.wait_mutex was + unlocked, we need to return deadlock error if transaction was chosen + as deadlock victim to rollback it */ + if (check_victim && trx->lock.was_chosen_as_deadlock_victim) err= DB_DEADLOCK; - else + else if (lock) goto resolve_table_lock; } else @@ -5769,10 +5799,12 @@ retreat: lock_sys.wr_lock(SRW_LOCK_CALL); mysql_mutex_lock(&lock_sys.wait_mutex); lock= trx->lock.wait_lock; - if (!lock); - else if (check_victim && trx->lock.was_chosen_as_deadlock_victim) + /* Even if waiting lock was cancelled while lock_sys.wait_mutex was + unlocked, we need to return deadlock error if transaction was chosen + as deadlock victim to rollback it */ + if (check_victim && trx->lock.was_chosen_as_deadlock_victim) err= DB_DEADLOCK; - else + else if (lock) goto resolve_record_lock; } else @@ -5850,6 +5882,7 @@ while holding a clustered index leaf page latch. lock request was released */ dberr_t lock_trx_handle_wait(trx_t *trx) { + DEBUG_SYNC_C("lock_trx_handle_wait_enter"); if (trx->lock.was_chosen_as_deadlock_victim) return DB_DEADLOCK; if (!trx->lock.wait_lock) diff --git a/storage/innobase/trx/trx0roll.cc b/storage/innobase/trx/trx0roll.cc index 59ea0bdcd8f..59c9a319330 100644 --- a/storage/innobase/trx/trx0roll.cc +++ b/storage/innobase/trx/trx0roll.cc @@ -135,6 +135,9 @@ inline void trx_t::rollback_low(trx_savept_t *savept) } else { + /* There must not be partial rollback if transaction was chosen as deadlock + victim. Galera transaction abort can be invoked during partial rollback. */ + ut_ad(!(lock.was_chosen_as_deadlock_victim & 1)); ut_a(error_state == DB_SUCCESS); const undo_no_t limit= savept->least_undo_no; apply_online_log= false; @@ -211,6 +214,10 @@ dberr_t trx_rollback_for_mysql(trx_t* trx) case TRX_STATE_NOT_STARTED: trx->will_lock = false; ut_ad(trx->mysql_thd); + /* Galera transaction abort can be invoked from MDL acquision + code, so trx->lock.was_chosen_as_deadlock_victim can be set + even if trx->state is TRX_STATE_NOT_STARTED. */ + ut_ad(!(trx->lock.was_chosen_as_deadlock_victim & 1)); #ifdef WITH_WSREP trx->wsrep= false; trx->lock.was_chosen_as_deadlock_victim= false; @@ -418,9 +425,6 @@ trx_rollback_to_savepoint_for_mysql_low( trx_mark_sql_stat_end(trx); trx->op_info = ""; -#ifdef WITH_WSREP - trx->lock.was_chosen_as_deadlock_victim = false; -#endif return(err); } diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc index 95bc6273e48..a8f8d7dcc79 100644 --- a/storage/innobase/trx/trx0trx.cc +++ b/storage/innobase/trx/trx0trx.cc @@ -1379,9 +1379,8 @@ TRANSACTIONAL_INLINE inline void trx_t::commit_in_memory(const mtr_t *mtr) wsrep= false; wsrep_commit_ordered(mysql_thd); } - ut_ad(!(lock.was_chosen_as_deadlock_victim & byte(~2U))); - lock.was_chosen_as_deadlock_victim= false; #endif /* WITH_WSREP */ + lock.was_chosen_as_deadlock_victim= false; } void trx_t::commit_cleanup() -- cgit v1.2.1 From 2f6a728075a08c70103fa559180d0efc39f86fd4 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Wed, 10 Aug 2022 13:27:01 +0200 Subject: update a global_suppressions() list followup for "remove invalid options from warning messages" --- mysql-test/include/mtr_warnings.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index fa1ea95e5e1..9ce81703450 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -174,7 +174,7 @@ INSERT INTO global_suppressions VALUES /* Added 2009-08-XX after fixing Bug #42408 */ - ("Although a path was specified for the .* option, log tables are used"), + ("Although a .* file was specified, log tables are used. To enable logging to files "), ("Backup: Operation aborted"), ("Restore: Operation aborted"), ("Restore: The grant .* was skipped because the user does not exist"), -- cgit v1.2.1 From d1a80c42ee5b9c845ca72288d3bc58b47f5632a3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Thu, 25 Aug 2022 15:14:38 +0300 Subject: MDEV-29384 Hangs caused by innodb_adaptive_hash_index=ON buf_defer_drop_ahi(): Remove. Ever since commit c7f8cfc9e733517cff4aaa6f6eaca625a3afc098 (MDEV-27700) it is safe to invoke btr_search_drop_page_hash_index(block, true) to remove an orphan adaptive hash index. Any attempt to upgrade page latches is prone to deadlocks. Recently, we observed a few hangs that involved nothing more than a small table consisting of one clustered index page, one secondary index page and some undo pages. --- storage/innobase/buf/buf0buf.cc | 41 +---------------------------------------- 1 file changed, 1 insertion(+), 40 deletions(-) diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc index 8658b3a4a89..eb57489172c 100644 --- a/storage/innobase/buf/buf0buf.cc +++ b/storage/innobase/buf/buf0buf.cc @@ -3917,42 +3917,6 @@ buf_wait_for_read( } } -#ifdef BTR_CUR_HASH_ADAPT -/** If a stale adaptive hash index exists on the block, drop it. -Multiple executions of btr_search_drop_page_hash_index() on the -same block must be prevented by exclusive page latch. */ -ATTRIBUTE_COLD -static void buf_defer_drop_ahi(buf_block_t *block, mtr_memo_type_t fix_type) -{ - switch (fix_type) { - case MTR_MEMO_BUF_FIX: - /* We do not drop the adaptive hash index, because safely doing - so would require acquiring block->lock, and that is not safe - to acquire in some RW_NO_LATCH access paths. Those code paths - should have no business accessing the adaptive hash index anyway. */ - break; - case MTR_MEMO_PAGE_S_FIX: - /* Temporarily release our S-latch. */ - rw_lock_s_unlock(&block->lock); - rw_lock_x_lock(&block->lock); - btr_search_drop_page_hash_index(block, true); - rw_lock_x_unlock(&block->lock); - rw_lock_s_lock(&block->lock); - break; - case MTR_MEMO_PAGE_SX_FIX: - rw_lock_sx_unlock(&block->lock); - rw_lock_x_lock(&block->lock); - btr_search_drop_page_hash_index(block, true); - rw_lock_x_unlock(&block->lock); - rw_lock_sx_lock(&block->lock); - break; - default: - ut_ad(fix_type == MTR_MEMO_PAGE_X_FIX); - btr_search_drop_page_hash_index(block); - } -} -#endif /* BTR_CUR_HASH_ADAPT */ - /** Lock the page with the given latch type. @param[in,out] block block to be locked @param[in] rw_latch RW_S_LATCH, RW_X_LATCH, RW_NO_LATCH @@ -3988,10 +3952,7 @@ static buf_block_t* buf_page_mtr_lock(buf_block_t *block, } #ifdef BTR_CUR_HASH_ADAPT - { - if (block->index) - buf_defer_drop_ahi(block, fix_type); - } + btr_search_drop_page_hash_index(block, true); #endif /* BTR_CUR_HASH_ADAPT */ done: -- cgit v1.2.1 From 94e3f02db741e4a35f3aba22c258e20fe9617d75 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 24 Aug 2022 11:07:09 -0700 Subject: MDEV-29350 Crash when IN predicand is used in eliminated GROUP BY clause This bug affected some queries with an IN/ALL/ANY predicand or an EXISTS predicate whose subquery contained a GROUP BY clause that could be eliminated. If this clause used a IN/ALL/ANY predicand whose left operand was a single-value subquery then execution of the query caused a crash of the server after invokation of remove_redundant_subquery_clauses(). The crash was caused by an attempt to exclude the unit for the single-value subquery from the query tree for the second time by the function Item_subselect::eliminate_subselect_processor(). This bug had been masked by the bug MDEV-28617 until a fix for the latter that properly excluded units was pushed into 10.3. Approved by Oleksandr Byelkin --- mysql-test/main/subselect4.result | 116 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/subselect4.test | 74 ++++++++++++++++++++++++ sql/item_subselect.cc | 3 +- 3 files changed, 192 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index c374040e9e8..c794aade773 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -3038,4 +3038,120 @@ a 3 2 drop table t1,t2,t3; +# +# MDEV-29139: Redundant IN/ALL/ANY predicand in GROUP BY clause of +# IN/ALL/ANY/EXISTS subquery +# +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d int); +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t4 values (1), (7); +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +b +3 +2 +prepare stmt from "select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4))"; +execute stmt; +b +3 +2 +execute stmt; +b +3 +2 +deallocate prepare stmt; +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) >= +any (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) >= +any (select d from t4)); +b +3 +2 +explain extended select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) < +all (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where 1 +select b from t2 +where exists (select c from t3 +group by (select a from t1 where a = 1) < +all (select d from t4)); +b +3 +2 +explain extended select b from t2 +where b in (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 +1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` semi join (`test`.`t3`) where 1 +select b from t2 +where b in (select c from t3 +group by (select a from t1 where a = 1) in (select d from t4)); +b +2 +explain extended select b from t2 +where b >= any (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b`,(/* select#2 */ select min(`test`.`t3`.`c`) from `test`.`t3`) <= (`test`.`t2`.`b`))) +select b from t2 +where b >= any (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +b +3 +2 +explain extended select b from t2 +where b <= all (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` where ((`test`.`t2`.`b`,(/* select#2 */ select `test`.`t3`.`c` from `test`.`t3`) < (`test`.`t2`.`b`))) +select b from t2 +where b <= all (select c from t3 +group by (select a from t1 where a = 1) in +(select d from t4)); +b +2 +drop table t1,t2,t3,t4; # End of 10.3 tests diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index 2faede5c27e..6aedabcd3f5 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2477,4 +2477,78 @@ eval $q3; drop table t1,t2,t3; +--echo # +--echo # MDEV-29139: Redundant IN/ALL/ANY predicand in GROUP BY clause of +--echo # IN/ALL/ANY/EXISTS subquery +--echo # + +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d int); + +insert into t1 values (3), (1); +insert into t2 values (3), (2); +insert into t3 values (4), (2); +insert into t4 values (1), (7); + +let $q1= +select b from t2 + where exists (select c from t3 + group by (select a from t1 where a = 1) in (select d from t4)); + +eval explain extended $q1; +eval $q1; + +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select b from t2 + where exists (select c from t3 + group by (select a from t1 where a = 1) >= + any (select d from t4)); + +eval explain extended $q2; +eval $q2; + +let $q3= +select b from t2 + where exists (select c from t3 + group by (select a from t1 where a = 1) < + all (select d from t4)); + +eval explain extended $q3; +eval $q3; + +let $q4= +select b from t2 + where b in (select c from t3 + group by (select a from t1 where a = 1) in (select d from t4)); + +eval explain extended $q4; +eval $q4; + +let $q5= +select b from t2 + where b >= any (select c from t3 + group by (select a from t1 where a = 1) in + (select d from t4)); + +eval explain extended $q5; +eval $q5; + +let $q6= +select b from t2 + where b <= all (select c from t3 + group by (select a from t1 where a = 1) in + (select d from t4)); + +eval explain extended $q6; +eval $q6; + +drop table t1,t2,t3,t4; + --echo # End of 10.3 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 2e33a71b8f9..ee36ee2fae9 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -379,7 +379,8 @@ bool Item_subselect::mark_as_eliminated_processor(void *arg) bool Item_subselect::eliminate_subselect_processor(void *arg) { unit->item= NULL; - unit->exclude(); + if (!unit->is_excluded()) + unit->exclude(); eliminated= TRUE; return FALSE; } -- cgit v1.2.1 From 79b58f1ca893b8affc5075d0a548563d376cd481 Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Sat, 30 Jul 2022 00:11:08 +1000 Subject: MDEV-23607 MariaBackup - align required GRANTS to cmd options Since the 10.5 split of the privileges, the required GRANTs for various mariabackup operations has changed. In the addition of tests, a number of mappings where incorrect: The option --lock-ddl-per-table didn't require connection admin. The option --safe-slave-backup requires SLAVE MONITOR even without the --no-lock option. --- extra/mariabackup/xtrabackup.cc | 20 +++++--- mysql-test/suite/mariabackup/backup_grants.result | 8 +++ mysql-test/suite/mariabackup/backup_grants.test | 59 ++++++++++++++++++++++- 3 files changed, 78 insertions(+), 9 deletions(-) diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc index da2ae65447b..2c4da11c860 100644 --- a/extra/mariabackup/xtrabackup.cc +++ b/extra/mariabackup/xtrabackup.cc @@ -6200,22 +6200,28 @@ static bool check_all_privileges() } /* KILL ... */ - if ((!opt_no_lock && (opt_kill_long_queries_timeout || opt_lock_ddl_per_table)) - /* START SLAVE SQL_THREAD */ - /* STOP SLAVE SQL_THREAD */ - || opt_safe_slave_backup) { + if (!opt_no_lock && (opt_kill_long_queries_timeout || opt_kill_long_query_type)) { check_result |= check_privilege( granted_privileges, - "SUPER", "*", "*", + "CONNECTION ADMIN", "*", "*", + PRIVILEGE_WARNING); + } + + /* START SLAVE SQL_THREAD */ + /* STOP SLAVE SQL_THREAD */ + if (opt_safe_slave_backup) { + check_result |= check_privilege( + granted_privileges, + "REPLICATION SLAVE ADMIN", "*", "*", PRIVILEGE_WARNING); } /* SHOW MASTER STATUS */ /* SHOW SLAVE STATUS */ if (opt_galera_info || opt_slave_info - || (opt_no_lock && opt_safe_slave_backup)) { + || opt_safe_slave_backup) { check_result |= check_privilege(granted_privileges, - "REPLICATION CLIENT", "*", "*", + "SLAVE MONITOR", "*", "*", PRIVILEGE_WARNING); } diff --git a/mysql-test/suite/mariabackup/backup_grants.result b/mysql-test/suite/mariabackup/backup_grants.result index ed793e7ff1a..56899f8d9c0 100644 --- a/mysql-test/suite/mariabackup/backup_grants.result +++ b/mysql-test/suite/mariabackup/backup_grants.result @@ -3,4 +3,12 @@ FOUND 1 /missing required privilege RELOAD/ in backup.log FOUND 1 /missing required privilege PROCESS/ in backup.log FOUND 1 /GRANT USAGE ON/ in backup.log GRANT RELOAD, PROCESS on *.* to backup@localhost; +NOT FOUND /missing required privilege REPLICA MONITOR/ in backup.log +GRANT REPLICA MONITOR ON *.* TO backup@localhost; +REVOKE REPLICA MONITOR ON *.* FROM backup@localhost; +GRANT CONNECTION ADMIN ON *.* TO backup@localhost; +FOUND 1 /missing required privilege REPLICATION SLAVE ADMIN/ in backup.log +NOT FOUND /missing required privilege REPLICA MONITOR/ in backup.log +GRANT REPLICATION SLAVE ADMIN ON *.* TO backup@localhost; +GRANT REPLICA MONITOR ON *.* TO backup@localhost; DROP USER backup@localhost; diff --git a/mysql-test/suite/mariabackup/backup_grants.test b/mysql-test/suite/mariabackup/backup_grants.test index eadeedd9b5f..894ae73aeb9 100644 --- a/mysql-test/suite/mariabackup/backup_grants.test +++ b/mysql-test/suite/mariabackup/backup_grants.test @@ -25,7 +25,62 @@ GRANT RELOAD, PROCESS on *.* to backup@localhost; --disable_result_log exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --target-dir=$targetdir; --enable_result_log +rmdir $targetdir; -DROP USER backup@localhost; -# Cleanup +# MDEV-23607 Warning: missing required privilege REPLICATION CLIENT +# --slave-info and galera info require REPLICA MONITOR +--disable_result_log +error 1; +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --slave-info --target-dir=$targetdir > $MYSQLTEST_VARDIR/tmp/backup.log; +--enable_result_log +rmdir $targetdir; + +--let SEARCH_PATTERN= missing required privilege REPLICA MONITOR +--source include/search_pattern_in_file.inc + +GRANT REPLICA MONITOR ON *.* TO backup@localhost; +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --slave-info --target-dir=$targetdir; +--enable_result_log +rmdir $targetdir; +REVOKE REPLICA MONITOR ON *.* FROM backup@localhost; + +# TODO need a query that would delay a BACKUP STAGE START/ BACKUP STAGE BLOCK_COMMIT longer than the kill-long-queries-timeout +#--send SELECT SLEEP(9) kill_me +## kill-long-query-type=(not empty) requires CONNECTION ADMIN +#--disable_result_log +#error 1; +#--exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --kill-long-query-type=all --kill-long-queries-timeout=4 --target-dir=$targetdir > $MYSQLTEST_VARDIR/tmp/backup.log; +#--reap +#--enable_result_log +#rmdir $targetdir; +# +#--let SEARCH_PATTERN= missing required privilege CONNECTION ADMIN +#--source include/search_pattern_in_file.inc + +GRANT CONNECTION ADMIN ON *.* TO backup@localhost; +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --kill-long-query-type=all --kill-long-queries-timeout=1 --target-dir=$targetdir; +--enable_result_log +rmdir $targetdir; + +# --safe-slave-backup requires REPLICATION SLAVE ADMIN, and REPLICA MONITOR +--disable_result_log +error 1; +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --safe-slave-backup --target-dir=$targetdir > $MYSQLTEST_VARDIR/tmp/backup.log; +--enable_result_log rmdir $targetdir; + +--let SEARCH_PATTERN= missing required privilege REPLICATION SLAVE ADMIN +--source include/search_pattern_in_file.inc +--let SEARCH_PATTERN= missing required privilege REPLICA MONITOR +--source include/search_pattern_in_file.inc + +GRANT REPLICATION SLAVE ADMIN ON *.* TO backup@localhost; +GRANT REPLICA MONITOR ON *.* TO backup@localhost; +--disable_result_log +exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup -ubackup --safe-slave-backup --target-dir=$targetdir; +--enable_result_log +rmdir $targetdir; + +DROP USER backup@localhost; -- cgit v1.2.1 From 0324bde8469f37ecf0ca8aa3357c436d04d44316 Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Fri, 26 Aug 2022 10:20:26 +1000 Subject: mariabackup: remove MySQL wording --- extra/mariabackup/backup_mysql.cc | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) diff --git a/extra/mariabackup/backup_mysql.cc b/extra/mariabackup/backup_mysql.cc index 7a629e7265c..3cfbf5fbfb6 100644 --- a/extra/mariabackup/backup_mysql.cc +++ b/extra/mariabackup/backup_mysql.cc @@ -126,7 +126,7 @@ xb_mysql_connect() mysql_options(connection, MYSQL_OPT_PROTOCOL, &opt_protocol); mysql_options(connection,MYSQL_SET_CHARSET_NAME, "utf8"); - msg("Connecting to MySQL server host: %s, user: %s, password: %s, " + msg("Connecting to server host: %s, user: %s, password: %s, " "port: %s, socket: %s", opt_host ? opt_host : "localhost", opt_user ? opt_user : "not set", opt_password ? "set" : "not set", @@ -153,7 +153,7 @@ xb_mysql_connect() opt_password, "" /*database*/, opt_port, opt_socket, 0)) { - msg("Failed to connect to MySQL server: %s.", mysql_error(connection)); + msg("Failed to connect to server: %s.", mysql_error(connection)); mysql_close(connection); return(NULL); } @@ -342,7 +342,7 @@ check_server_version(unsigned long version_number, } /*********************************************************************//** -Receive options important for XtraBackup from MySQL server. +Receive options important for XtraBackup from server. @return true on success. */ bool get_mysql_vars(MYSQL *connection) { @@ -1837,8 +1837,8 @@ static std::string make_local_paths(const char *data_file_path) bool write_backup_config_file() { int rc= backup_file_printf("backup-my.cnf", - "# This MySQL options file was generated by innobackupex.\n\n" - "# The MySQL server\n" + "# This options file was generated by innobackupex.\n\n" + "# The server\n" "[mysqld]\n" "innodb_checksum_algorithm=%s\n" "innodb_data_file_path=%s\n" @@ -1923,7 +1923,7 @@ flush_changed_page_bitmaps() /*********************************************************************//** -Deallocate memory, disconnect from MySQL server, etc. +Deallocate memory, disconnect from server, etc. @return true on success. */ void backup_cleanup() -- cgit v1.2.1 From 0fbcb0a2b87d8807b85fec85507074bcda3d4da9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 26 Aug 2022 11:41:43 +0300 Subject: MDEV-29383 Assertion mysql_mutex_assert_owner(&log_sys.flush_order_mutex) failed in mtr_t::commit() In commit 0b47c126e31cddda1e94588799599e138400bcf8 (MDEV-13542) a few calls to mtr_t::memo_push() were moved before a write latch on the page was acquired. This introduced a race condition: 1. is_block_dirtied() returned false to mtr_t::memo_push() 2. buf_page_t::write_complete() was executed, the block marked clean, and a page latch released 3. The page latch was acquired by the caller of mtr_t::memo_push(), and mtr_t::m_made_dirty was not set even though the block is in a clean state. The impact of this race condition is that crash recovery and backups may fail. btr_cur_latch_leaves(), btr_store_big_rec_extern_fields(), btr_free_externally_stored_field(), trx_purge_free_segment(): Acquire the page latch before invoking mtr_t::memo_push(). This fixes the regression caused by MDEV-13542. Side note: It would suffice to set mtr_t::m_made_dirty at the time we set the MTR_MEMO_MODIFY flag for a block. Currently that flag is unnecessarily set if a mini-transaction acquires a page latch on a page that is in a clean state, and will not actually modify the block. This may cause unnecessary acquisitions of log_sys.flush_order_mutex on mtr_t::commit(). mtr_t::free(): If the block had been exclusively latched in this mini-transaction, set the m_made_dirty flag so that the flush order mutex will be acquired during mtr_t::commit(). This should have been part of commit 4179f93d28035ea2798cb1c16feeaaef87ab4775 (MDEV-18976). It was necessary to change mtr_t::free() so that WriteOPT_PAGE_CHECKSUM::operator() would be able to avoid writing checksums for freed pages. --- storage/innobase/btr/btr0cur.cc | 10 ++++++---- storage/innobase/mtr/mtr0mtr.cc | 6 ++++-- storage/innobase/trx/trx0purge.cc | 4 ++-- 3 files changed, 12 insertions(+), 8 deletions(-) diff --git a/storage/innobase/btr/btr0cur.cc b/storage/innobase/btr/btr0cur.cc index 82430f7e7ab..e9b4e2937b9 100644 --- a/storage/innobase/btr/btr0cur.cc +++ b/storage/innobase/btr/btr0cur.cc @@ -278,10 +278,10 @@ latch_block: latch_leaves->blocks[1] = block; } - mtr->memo_push(block, MTR_MEMO_PAGE_X_FIX); - block->page.fix(); block->page.lock.x_lock(); + + mtr->memo_push(block, MTR_MEMO_PAGE_X_FIX); #ifdef BTR_CUR_HASH_ADAPT ut_ad(!btr_search_check_marked_free_index(block)); #endif @@ -7019,10 +7019,11 @@ btr_store_big_rec_extern_fields( mtr.start(); index->set_modified(mtr); mtr.set_log_mode_sub(*btr_mtr); - mtr.memo_push(rec_block, MTR_MEMO_PAGE_X_FIX); rec_block->page.fix(); rec_block->page.lock.x_lock(); + + mtr.memo_push(rec_block, MTR_MEMO_PAGE_X_FIX); #ifdef BTR_CUR_HASH_ADAPT ut_ad(!btr_search_check_marked_free_index(rec_block)); #endif @@ -7397,9 +7398,10 @@ skip_free: /* The buffer pool block containing the BLOB pointer is exclusively latched by local_mtr. To satisfy some design constraints, we must recursively latch it in mtr as well. */ - mtr.memo_push(block, MTR_MEMO_PAGE_X_FIX); block->fix(); block->page.lock.x_lock(); + + mtr.memo_push(block, MTR_MEMO_PAGE_X_FIX); #ifdef BTR_CUR_HASH_ADAPT ut_ad(!btr_search_check_marked_free_index(block)); #endif diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc index 062eb650871..02965821ced 100644 --- a/storage/innobase/mtr/mtr0mtr.cc +++ b/storage/innobase/mtr/mtr0mtr.cc @@ -1599,8 +1599,10 @@ void mtr_t::free(const fil_space_t &space, uint32_t offset) if (is_logged()) { - m_memo.for_each_block_in_reverse - (CIterate((MarkFreed{{space.id, offset}}))); + CIterate mf{MarkFreed{{space.id, offset}}}; + m_memo.for_each_block_in_reverse(mf); + if (mf.functor.freed && !m_made_dirty) + m_made_dirty= is_block_dirtied(mf.functor.freed); m_log.close(log_write({space.id, offset}, nullptr)); } } diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc index 31153c8e966..c0fafe1ec6b 100644 --- a/storage/innobase/trx/trx0purge.cc +++ b/storage/innobase/trx/trx0purge.cc @@ -399,11 +399,11 @@ static dberr_t trx_purge_free_segment(trx_rseg_t *rseg, fil_addr_t hdr_addr) mtr.commit(); mtr.start(); mtr.flag_modified(); - mtr.memo_push(rseg_hdr, MTR_MEMO_PAGE_X_FIX); - mtr.memo_push(block, MTR_MEMO_PAGE_X_MODIFY); rseg->latch.wr_lock(SRW_LOCK_CALL); rseg_hdr->page.lock.x_lock(); block->page.lock.x_lock(); + mtr.memo_push(rseg_hdr, MTR_MEMO_PAGE_X_FIX); + mtr.memo_push(block, MTR_MEMO_PAGE_X_MODIFY); } /* The page list may now be inconsistent, but the length field -- cgit v1.2.1 From 827b049e1e7df204feb744a270b4dca619a61de1 Mon Sep 17 00:00:00 2001 From: tmokmss Date: Sun, 5 Jun 2022 08:04:18 +0000 Subject: MDEV-18873 Server crashes in Compare_identifiers::operator or in my_strcasecmp_utf8 upon ADD PERIOD IF NOT EXISTS with empty name empty identifier specified as `` ends up with a NULL LEX_CSTRING::str in lexer. This is not considered correct in upper layers, for example in Compare_identifiers::operator(). Empty column name is usually avoided by a check_column_name() call while parsing, and period name matches the column name completely. Hence, this fix uses the mentioned call for verification, too. --- mysql-test/suite/period/r/alter.result | 14 ++++++++++++++ mysql-test/suite/period/t/alter.test | 23 +++++++++++++++++++++++ sql/sql_lex.h | 6 ++++++ sql/table.cc | 6 ++++++ sql/table.h | 1 + sql/vers_string.h | 2 ++ 6 files changed, 52 insertions(+) diff --git a/mysql-test/suite/period/r/alter.result b/mysql-test/suite/period/r/alter.result index a6466c8944b..875dab93b68 100644 --- a/mysql-test/suite/period/r/alter.result +++ b/mysql-test/suite/period/r/alter.result @@ -190,3 +190,17 @@ alter table t1 add primary key(x, s, e); ERROR 23000: Duplicate entry '1-2020-03-01-2020-03-02' for key 'PRIMARY' alter table t1 add system versioning; drop table t1; +# +# MDEV-18873 Server crashes in Compare_identifiers::operator or in +# my_strcasecmp_utf8 upon ADD PERIOD IF NOT EXISTS with empty name +# +alter table t add period if not exists for `` (s,e); +ERROR 42000: Incorrect column name '' +create table t(s DATE, e DATE); +alter table t add period if not exists for `` (s,e); +ERROR 42000: Incorrect column name '' +alter table t add period if not exists for ` ` (s,e); +ERROR 42000: Incorrect column name ' ' +create table t2 (period for `` (s,e)) select * from t; +ERROR 42000: Incorrect column name '' +drop table t; diff --git a/mysql-test/suite/period/t/alter.test b/mysql-test/suite/period/t/alter.test index 3fa3c5c87d5..2a82f74b670 100644 --- a/mysql-test/suite/period/t/alter.test +++ b/mysql-test/suite/period/t/alter.test @@ -151,3 +151,26 @@ alter table t1 add system versioning; # cleanup drop table t1; + +--echo # +--echo # MDEV-18873 Server crashes in Compare_identifiers::operator or in +--echo # my_strcasecmp_utf8 upon ADD PERIOD IF NOT EXISTS with empty name +--echo # + +# When there is no table defined. +--error ER_WRONG_COLUMN_NAME +alter table t add period if not exists for `` (s,e); + +# When there is an actual table. +create table t(s DATE, e DATE); +--error ER_WRONG_COLUMN_NAME +alter table t add period if not exists for `` (s,e); + +# When the last character is space +--error ER_WRONG_COLUMN_NAME +alter table t add period if not exists for ` ` (s,e); + +# Create table with an empty period name +--error ER_WRONG_COLUMN_NAME +create table t2 (period for `` (s,e)) select * from t; +drop table t; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index be41b45c335..976d1b79cad 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -33,6 +33,7 @@ #include "sql_tvc.h" #include "item.h" #include "sql_schema.h" +#include "table.h" /* Used for flags of nesting constructs */ #define SELECT_NESTING_MAP_SIZE 64 @@ -4449,6 +4450,11 @@ public: int add_period(Lex_ident name, Lex_ident_sys_st start, Lex_ident_sys_st end) { + if (check_period_name(name.str)) { + my_error(ER_WRONG_COLUMN_NAME, MYF(0), name.str); + return 1; + } + if (lex_string_cmp(system_charset_info, &start, &end) == 0) { my_error(ER_FIELD_SPECIFIED_TWICE, MYF(0), start.str); diff --git a/sql/table.cc b/sql/table.cc index 7957f2da593..1a30809cde9 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4918,6 +4918,12 @@ bool check_column_name(const char *name) } +bool check_period_name(const char *name) +{ + return check_column_name(name); +} + + /** Checks whether a table is intact. Should be done *just* after the table has been opened. diff --git a/sql/table.h b/sql/table.h index c9503b54934..3f750e7b221 100644 --- a/sql/table.h +++ b/sql/table.h @@ -3231,6 +3231,7 @@ void open_table_error(TABLE_SHARE *share, enum open_frm_error error, void update_create_info_from_table(HA_CREATE_INFO *info, TABLE *form); bool check_db_name(LEX_STRING *db); bool check_column_name(const char *name); +bool check_period_name(const char *name); bool check_table_name(const char *name, size_t length, bool check_for_path_chars); int rename_file_ext(const char * from,const char * to,const char * ext); char *get_field(MEM_ROOT *mem, Field *field); diff --git a/sql/vers_string.h b/sql/vers_string.h index 2349cc0cac1..cfadc890dcd 100644 --- a/sql/vers_string.h +++ b/sql/vers_string.h @@ -39,6 +39,8 @@ struct Compare_identifiers { int operator()(const LEX_CSTRING& a, const LEX_CSTRING& b) const { + DBUG_ASSERT(a.str != NULL); + DBUG_ASSERT(b.str != NULL); DBUG_ASSERT(a.str[a.length] == 0); DBUG_ASSERT(b.str[b.length] == 0); return my_strcasecmp(system_charset_info, a.str, b.str); -- cgit v1.2.1 From 0d1de5e1d19f1e96058ab5948e184f22e7bdd908 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sun, 28 Aug 2022 21:23:28 +0300 Subject: MDEV-29403 innodb.innodb_sys_semaphore_waits fails with wrong errno 5014 take into account C/C specific CR_ERR_NET_WRITE error --- mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test b/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test index a0b9fc626f3..29ff3f69ed7 100644 --- a/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test +++ b/mysql-test/suite/innodb/t/innodb_sys_semaphore_waits.test @@ -78,10 +78,10 @@ let $counter= 80; let $mysql_errno= 0; while (!$mysql_errno) { - --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013 + --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,5014 show status; - --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013 + --error 0,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,2002,2006,2013,5014 select * from information_schema.innodb_sys_semaphore_waits; dec $counter; -- cgit v1.2.1 From b260903832456dcad882e01a10cdcd48dfd2b0dc Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 30 Aug 2022 10:59:31 +0300 Subject: MDEV-29258 Failing assertion for name length on RENAME TABLE trx_undo_page_report_rename(): Use the correct maximum length of a table name. Both the database name and the table name can be up to NAME_CHAR_LEN (64 characters) times 5 bytes per character in the my_charset_filename encoding. They are not encoded in UTF-8! fil_op_write_log(): Reserve the correct amount of log buffer for a rename operation. The file name will be appended by mlog_catenate_string(). rename_file_ext(): Reserve a large enough buffer for the file names. --- .../suite/innodb/r/foreign_key_not_windows.result | 9 ++++++++- mysql-test/suite/innodb/t/foreign_key_not_windows.test | 17 ++++++++++++++++- sql/table.cc | 5 +++-- storage/innobase/fil/fil0fil.cc | 2 +- storage/innobase/trx/trx0rec.cc | 6 +++--- 5 files changed, 31 insertions(+), 8 deletions(-) diff --git a/mysql-test/suite/innodb/r/foreign_key_not_windows.result b/mysql-test/suite/innodb/r/foreign_key_not_windows.result index 764ba911214..0dcc3d46bb1 100644 --- a/mysql-test/suite/innodb/r/foreign_key_not_windows.result +++ b/mysql-test/suite/innodb/r/foreign_key_not_windows.result @@ -11,6 +11,13 @@ CREATE TABLE `d255`.`_##################################################` ERROR HY000: Long database name and identifier for object resulted in path length exceeding 512 characters. Path: './@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023/_@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023@0023 CREATE TABLE `d255`.`##################################################` (a INT PRIMARY KEY, FOREIGN KEY(a) REFERENCES test.t(a)) ENGINE=InnoDB; +# +# MDEV-29258 Failing assertion for name length on RENAME TABLE +# +CREATE TABLE `d255`.`d245` (x INT) ENGINE=InnoDB; +DROP TABLE `d255`.`d250`; +RENAME TABLE `d250#`.`d245` TO `d250#`.`d250`; +RENAME TABLE `d255`.`d250` TO a; DROP DATABASE `d255`; -DROP TABLE t; +DROP TABLE a,t; # End of 10.3 tests diff --git a/mysql-test/suite/innodb/t/foreign_key_not_windows.test b/mysql-test/suite/innodb/t/foreign_key_not_windows.test index 7ad3723d5de..0e1e25d64b3 100644 --- a/mysql-test/suite/innodb/t/foreign_key_not_windows.test +++ b/mysql-test/suite/innodb/t/foreign_key_not_windows.test @@ -38,8 +38,23 @@ eval CREATE TABLE `$d255`.`_$d250` --replace_result $d255 d255 eval CREATE TABLE `$d255`.`$d250` (a INT PRIMARY KEY, FOREIGN KEY(a) REFERENCES test.t(a)) ENGINE=InnoDB; + +--echo # +--echo # MDEV-29258 Failing assertion for name length on RENAME TABLE +--echo # + +let $d245=-------------------------------------------------; +--replace_result $d245 d245 $d255 d255 +eval CREATE TABLE `$d255`.`$d245` (x INT) ENGINE=InnoDB; +--replace_result $d250 d250 $d255 d255 +eval DROP TABLE `$d255`.`$d250`; + +--replace_result $d245 d245 $d250 d250 d255 d255 +eval RENAME TABLE `$d255`.`$d245` TO `$d255`.`$d250`; +--replace_result $d250 d250 $d255 d255 +eval RENAME TABLE `$d255`.`$d250` TO a; --replace_result $d255 d255 eval DROP DATABASE `$d255`; -DROP TABLE t; +DROP TABLE a,t; --echo # End of 10.3 tests diff --git a/sql/table.cc b/sql/table.cc index 64fb3150a39..506195127b2 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1,5 +1,5 @@ /* Copyright (c) 2000, 2017, Oracle and/or its affiliates. - Copyright (c) 2008, 2021, MariaDB + Copyright (c) 2008, 2022, 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 @@ -4197,7 +4197,8 @@ void update_create_info_from_table(HA_CREATE_INFO *create_info, TABLE *table) int rename_file_ext(const char * from,const char * to,const char * ext) { - char from_b[FN_REFLEN],to_b[FN_REFLEN]; + /* Reserve space for ./databasename/tablename.frm + NUL byte */ + char from_b[2 + FN_REFLEN + 4 + 1], to_b[2 + FN_REFLEN + 4 + 1]; (void) strxmov(from_b,from,ext,NullS); (void) strxmov(to_b,to,ext,NullS); return mysql_file_rename(key_file_frm, from_b, to_b, MYF(0)); diff --git a/storage/innobase/fil/fil0fil.cc b/storage/innobase/fil/fil0fil.cc index a196303c39f..5d9f80eda70 100644 --- a/storage/innobase/fil/fil0fil.cc +++ b/storage/innobase/fil/fil0fil.cc @@ -2105,7 +2105,7 @@ fil_op_write_log( case MLOG_FILE_RENAME2: ut_ad(strchr(new_path, OS_PATH_SEPARATOR) != NULL); len = strlen(new_path) + 1; - log_ptr = mlog_open(mtr, 2 + len); + log_ptr = mlog_open(mtr, 2); ut_a(log_ptr); mach_write_to_2(log_ptr, len); log_ptr += 2; diff --git a/storage/innobase/trx/trx0rec.cc b/storage/innobase/trx/trx0rec.cc index e011b3f5d8e..d7ec2a38f19 100644 --- a/storage/innobase/trx/trx0rec.cc +++ b/storage/innobase/trx/trx0rec.cc @@ -1,7 +1,7 @@ /***************************************************************************** Copyright (c) 1996, 2019, Oracle and/or its affiliates. All Rights Reserved. -Copyright (c) 2017, 2021, MariaDB Corporation. +Copyright (c) 2017, 2022, 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 @@ -1867,9 +1867,9 @@ trx_undo_page_report_rename(trx_t* trx, const dict_table_t* table, byte* start = block->frame + first_free; size_t len = strlen(table->name.m_name); const size_t fixed = 2 + 1 + 11 + 11 + 2; - ut_ad(len <= NAME_LEN * 2 + 1); + ut_ad(len <= NAME_CHAR_LEN * 5 * 2 + 1); /* The -10 is used in trx_undo_left() */ - compile_time_assert((NAME_LEN * 1) * 2 + fixed + compile_time_assert(NAME_CHAR_LEN * 5 * 2 + fixed + TRX_UNDO_PAGE_HDR + TRX_UNDO_PAGE_HDR_SIZE < UNIV_PAGE_SIZE_MIN - 10 - FIL_PAGE_DATA_END); -- cgit v1.2.1 From 966d22b715f00efaca358a8a798b20213a72ae09 Mon Sep 17 00:00:00 2001 From: anson1014 <56494179+anson1014@users.noreply.github.com> Date: Tue, 30 Aug 2022 04:21:40 -0400 Subject: Ensure that source files contain only valid UTF8 encodings (#2188) Modern software (including text editors, static analysis software, and web-based code review interfaces) often requires source code files to be interpretable via a consistent character encoding, with UTF-8 or ASCII (a strict subset of UTF-8) as the default. Several of the MariaDB source files contain bytes that are not valid in either the UTF-8 or ASCII encodings, but instead represent strings encoded in the ISO-8859-1/Latin-1 or ISO-8859-2/Latin-2 encodings. These inconsistent encodings may prevent software from correctly presenting or processing such files. Converting all source files to valid UTF8 characters will ensure correct handling. Comments written in Czech were replaced with lightly-corrected translations from Google Translate. Additionally, comments describing the proper handling of special characters were changed so that the comments are now purely UTF8. All new code of the whole pull request, including one or several files that are either new files or modified ones, are contributed under the BSD-new license. I am contributing on behalf of my employer Amazon Web Services, Inc. Co-authored-by: Andrew Hutchings --- mysys/my_win_popen.cc | 2 +- storage/connect/domdoc.cpp | 1 - strings/ctype-czech.c | 75 ++++++++++++++++------------------------------ strings/ctype-latin1.c | 16 +++++----- 4 files changed, 34 insertions(+), 60 deletions(-) diff --git a/mysys/my_win_popen.cc b/mysys/my_win_popen.cc index f41f54100f1..cceb77e9019 100644 --- a/mysys/my_win_popen.cc +++ b/mysys/my_win_popen.cc @@ -92,7 +92,7 @@ extern "C" FILE *my_win_popen(const char *cmd, const char *mode) goto error; break; default: - /* Unknown mode, xpected "r", "rt", "w", "wt" */ + /* Unknown mode, expected "r", "rt", "w", "wt" */ abort(); } if (!SetHandleInformation(parent_pipe_end, HANDLE_FLAG_INHERIT, 0)) diff --git a/storage/connect/domdoc.cpp b/storage/connect/domdoc.cpp index 7d5b87a2640..268ad771ef9 100644 --- a/storage/connect/domdoc.cpp +++ b/storage/connect/domdoc.cpp @@ -642,7 +642,6 @@ bool DOMNODELIST::DropItem(PGLOBAL g, int n) if (Listp == NULL || Listp->length < n) return true; -//Listp->item[n] = NULL; La proprit n'a pas de mthode 'set' return false; } // end of DeleteItem diff --git a/strings/ctype-czech.c b/strings/ctype-czech.c index 058e1e36b3a..8a3db7aba67 100644 --- a/strings/ctype-czech.c +++ b/strings/ctype-czech.c @@ -23,13 +23,13 @@ solution was needed than the one-to-one conversion table. To note a few, here is an example of a Czech sorting sequence: - co < hlaska < hlska < hlava < chlapec < krtek + co < hlaska < hláska < hlava < chlapec < krtek It because some of the rules are: double char 'ch' is sorted - between 'h' and 'i'. Accented character '' (a with acute) is + between 'h' and 'i'. Accented character 'á' (a with acute) is sorted after 'a' and before 'b', but only if the word is otherwise the same. However, because 's' is sorted before 'v' - in hlava, the accentness of '' is overridden. There are many + in hlava, the accentness of 'á' is overridden. There are many more rules. This file defines functions my_strxfrm and my_strcoll for @@ -42,8 +42,9 @@ passes, that's why we need four times more space for expanded string. - This file also contains the ISO-Latin-2 definitions of - characters. + The non-ASCII literal strings in this file are encoded + in the iso-8859-2 / latin-2 character set + (https://en.wikipedia.org/wiki/ISO/IEC_8859-2) Author: (c) 1997--1998 Jan Pazdziora, adelton@fi.muni.cz Jan Pazdziora has a shared copyright for this code @@ -112,7 +113,7 @@ static const struct wordvalue doubles[] = { }; /* - Unformal description of the algorithm: + Informal description of the algorithm: We walk the string left to right. @@ -127,7 +128,7 @@ static const struct wordvalue doubles[] = { End of pass is marked with value 1 on the output. - For each character, we read it's value from the table. + For each character, we read its value from the table. If the value is ignore (0), we go straight to the next character. @@ -139,31 +140,6 @@ static const struct wordvalue doubles[] = { exists behind it, find its value. We append 0 to the end. ---- - Neformln popis algoritmu: - - Prochzme etzec zleva doprava. - - Konec etzce je pedn bu jako parametr, nebo je to *p == 0. - Toto je oeteno makrem IS_END. - - Pokud jsme doli na konec etzce pi prchodu 0, nejdeme na - zatek, ale na uloenou pozici, protoe prvn a druh prchod - b souasn. - - Konec vstupu (prchodu) ozname na vstupu hodnotou 1. - - Pro kad znak etzce nateme hodnotu z tdc tabulky. - - Jde-li o hodnotu ignorovat (0), skome ihned na dal znak.. - - Jde-li o hodnotu konec slova (2) a je to prchod 0 nebo 1, - peskome vechny dal 0 -- 2 a prohodme prchody. - - Jde-li o kompozitn znak (255), otestujeme, zda nsleduje - sprvn do dvojice, dohledme sprvnou hodnotu. - - Na konci pipojme znak 0 */ #define ADD_TO_RESULT(dest, len, totlen, value) \ @@ -336,24 +312,23 @@ my_strnxfrm_czech(CHARSET_INFO *cs __attribute__((unused)), /* - Neformln popis algoritmu: - - prochzme etzec zleva doprava - konec etzce poznme podle *p == 0 - pokud jsme doli na konec etzce pi prchodu 0, nejdeme na - zatek, ale na uloenou pozici, protoe prvn a druh - prchod b souasn - konec vstupu (prchodu) ozname na vstupu hodnotou 1 - - nateme hodnotu z tdc tabulky - jde-li o hodnotu ignorovat (0), skome na dal prchod - jde-li o hodnotu konec slova (2) a je to prchod 0 nebo 1, - peskome vechny dal 0 -- 2 a prohodme - prchody - jde-li o kompozitn znak (255), otestujeme, zda nsleduje - sprvn do dvojice, dohledme sprvnou hodnotu - - na konci pipojme znak 0 + Informal description of the algorithm: + + we pass the chain from left to right + we know the end of the string by *p == 0 + if we reached the end of the string on transition 0, then we don't go to + start, but to the saved position, because the first and second + the passage runs concurrently + we mark the end of the input (transition) with the value 1 on the output + + then we load the value from the sorting table + if the value is ignore (0), we jump to the next pass + if the value is the end of the word (2) and it is a 0 or 1 transition, + we skip all the other 0 -- 2 and switch transitions + if it is a composite character (255), we test whether it follows + correct to the pair, we find the correct value + + then we add the character 0 at the end */ diff --git a/strings/ctype-latin1.c b/strings/ctype-latin1.c index 00c48d8821e..33dd369657f 100644 --- a/strings/ctype-latin1.c +++ b/strings/ctype-latin1.c @@ -504,19 +504,19 @@ struct charset_info_st my_charset_latin1_nopad= * * The modern sort order is used, where: * - * '' -> "ae" - * '' -> "oe" - * '' -> "ue" - * '' -> "ss" + * 'ä' -> "ae" + * 'ö' -> "oe" + * 'ü' -> "ue" + * 'ß' -> "ss" */ /* * This is a simple latin1 mapping table, which maps all accented * characters to their non-accented equivalents. Note: in this - * table, '' is mapped to 'A', '' is mapped to 'Y', etc. - all + * table, 'ä' is mapped to 'A', 'ÿ' is mapped to 'Y', etc. - all * accented characters except the following are treated the same way. - * , , , , , + * Ü, ü, Ö, ö, Ä, ä */ static const uchar sort_order_latin1_de[] = { @@ -582,7 +582,7 @@ static const uchar combo2map[]={ my_strnxfrm_latin_de() on both strings and compared the result strings. This means that: - must also matches E and A, because my_strxn_frm_latin_de() will convert + Ä must also matches ÁE and Aè, because my_strxn_frm_latin_de() will convert both to AE. The other option would be to not do any accent removal in @@ -708,7 +708,7 @@ void my_hash_sort_latin1_de(CHARSET_INFO *cs __attribute__((unused)), /* Remove end space. We have to do this to be able to compare - 'AE' and '' as identical + 'AE' and 'Ä' as identical */ end= skip_trailing_space(key, len); -- cgit v1.2.1 From 422f3204efbbb27be9ad95355f69636114e7f907 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 30 Aug 2022 12:02:56 +0300 Subject: MDEV-29409 Buffer overflow in my_wc_mb_filename() on RENAME TABLE dict_table_rename_in_cache(), dict_table_get_highest_foreign_id(): Reserve sufficient space for the fkid[] buffer, and ensure that the fkid[] will be NUL-terminated. The fkid[] must accommodate both the database name (which is already encoded in my_charset_filename) and the constraint name (which must be converted to my_charset_filename) so that we can check if it is in the format databasename/tablename_ibfk_1 (all encoded in my_charset_filename). --- .../suite/innodb/r/foreign_key_not_windows.result | 10 +++++++++- .../suite/innodb/t/foreign_key_not_windows.test | 19 ++++++++++++++++++- storage/innobase/dict/dict0dict.cc | 10 ++++++---- 3 files changed, 33 insertions(+), 6 deletions(-) diff --git a/mysql-test/suite/innodb/r/foreign_key_not_windows.result b/mysql-test/suite/innodb/r/foreign_key_not_windows.result index 0dcc3d46bb1..aaff06f8d68 100644 --- a/mysql-test/suite/innodb/r/foreign_key_not_windows.result +++ b/mysql-test/suite/innodb/r/foreign_key_not_windows.result @@ -18,6 +18,14 @@ CREATE TABLE `d255`.`d245` (x INT) ENGINE=InnoDB; DROP TABLE `d255`.`d250`; RENAME TABLE `d250#`.`d245` TO `d250#`.`d250`; RENAME TABLE `d255`.`d250` TO a; -DROP DATABASE `d255`; DROP TABLE a,t; +# +# MDEV-29409 Buffer overflow in my_wc_mb_filename() on RENAME TABLE +# +CREATE TABLE `d255`.t(a INT PRIMARY KEY)ENGINE=InnoDB; +CREATE TABLE `d255`.u(a INT PRIMARY KEY, +CONSTRAINT `d320` FOREIGN KEY (a) REFERENCES `d255`.t (a)) ENGINE=InnoDB; +RENAME TABLE `d255`.u TO u; +DROP TABLE u; +DROP DATABASE `d255`; # End of 10.3 tests diff --git a/mysql-test/suite/innodb/t/foreign_key_not_windows.test b/mysql-test/suite/innodb/t/foreign_key_not_windows.test index 0e1e25d64b3..e5f42a0ddab 100644 --- a/mysql-test/suite/innodb/t/foreign_key_not_windows.test +++ b/mysql-test/suite/innodb/t/foreign_key_not_windows.test @@ -54,7 +54,24 @@ eval RENAME TABLE `$d255`.`$d245` TO `$d255`.`$d250`; --replace_result $d250 d250 $d255 d255 eval RENAME TABLE `$d255`.`$d250` TO a; --replace_result $d255 d255 -eval DROP DATABASE `$d255`; DROP TABLE a,t; +--echo # +--echo # MDEV-29409 Buffer overflow in my_wc_mb_filename() on RENAME TABLE +--echo # + +let $d225=#############################################; +let $d320=################################################################; + +--replace_result $d255 d255 +eval CREATE TABLE `$d255`.t(a INT PRIMARY KEY)ENGINE=InnoDB; +--replace_result $d255 d255 $d320 d320 +eval CREATE TABLE `$d255`.u(a INT PRIMARY KEY, +CONSTRAINT `$d320` FOREIGN KEY (a) REFERENCES `$d255`.t (a)) ENGINE=InnoDB; +--replace_result $d255 d255 +eval RENAME TABLE `$d255`.u TO u; +DROP TABLE u; +--replace_result $d255 d255 +eval DROP DATABASE `$d255`; + --echo # End of 10.3 tests diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc index d714e5f815b..eb0f10d55f4 100644 --- a/storage/innobase/dict/dict0dict.cc +++ b/storage/innobase/dict/dict0dict.cc @@ -1595,7 +1595,7 @@ dict_table_rename_in_cache( in UTF-8 charset. The variable fkid here is used to store foreign key constraint name in charset my_charset_filename for comparison further below. */ - char fkid[MAX_TABLE_NAME_LEN+20]; + char fkid[MAX_TABLE_NAME_LEN * 2 + 20]; ibool on_tmp = FALSE; /* The old table name in my_charset_filename is stored @@ -1629,7 +1629,8 @@ dict_table_rename_in_cache( } } - strncpy(fkid, foreign->id, MAX_TABLE_NAME_LEN); + strncpy(fkid, foreign->id, (sizeof fkid) - 1); + fkid[(sizeof fkid) - 1] = '\0'; if (strstr(fkid, TEMP_TABLE_PATH_PREFIX) == NULL) { innobase_convert_to_filename_charset( @@ -3671,10 +3672,11 @@ dict_table_get_highest_foreign_id( for (dict_foreign_set::iterator it = table->foreign_set.begin(); it != table->foreign_set.end(); ++it) { - char fkid[MAX_TABLE_NAME_LEN+20]; + char fkid[MAX_TABLE_NAME_LEN * 2 + 20]; foreign = *it; - strcpy(fkid, foreign->id); + strncpy(fkid, foreign->id, (sizeof fkid) - 1); + fkid[(sizeof fkid) - 1] = '\0'; /* Convert foreign key identifier on dictionary memory cache to filename charset. */ innobase_convert_to_filename_charset( -- cgit v1.2.1 From 57739ae94a4af580c62bbc87d364fa002c5dbe04 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Tue, 30 Aug 2022 12:03:58 +0300 Subject: MDEV-13888: innodb_fts.innodb_fts_plugin failed Add ORDER BY to make the test deterministic. Add FLUSH TABLES to avoid crash recovery warnings about the table mysql.plugin. This tends to occur on Valgrind, where the server shutdown could presumably time out, resulting in a forced kill. --- .../suite/innodb_fts/r/innodb_fts_plugin.result | 36 ++++++++++++---------- .../suite/innodb_fts/t/innodb_fts_plugin.test | 24 +++++++++------ 2 files changed, 35 insertions(+), 25 deletions(-) diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result index cec88f14b26..e636b30852b 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result @@ -1,4 +1,5 @@ INSTALL PLUGIN simple_parser SONAME 'mypluglib'; +FLUSH TABLES; # Test Part 1: Grammar Test CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -31,7 +32,7 @@ INSERT INTO articles (title, body) VALUES ('1001 MySQL Tricks','How to use full-text search engine'), ('Go MySQL Tricks','How to use full text search engine'); SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('mysql'); +MATCH(title, body) AGAINST('mysql') ORDER BY id; id title body 1 MySQL Tutorial DBMS stands for MySQL DataBase ... 2 How To Use MySQL Well After you went through a ... @@ -68,7 +69,7 @@ INSERT INTO articles (title, body) VALUES ('Go MySQL Tricks','How to use full text search engine'); ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser; SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('mysql'); +MATCH(title, body) AGAINST('mysql') ORDER BY id; id title body 1 MySQL Tutorial DBMS stands for MySQL DataBase ... 2 How To Use MySQL Well After you went through a ... @@ -88,21 +89,23 @@ MATCH(title, body) AGAINST('full text'); id title body 5 Go MySQL Tricks How to use full text search engine SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION); +MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION) +ORDER BY id; id title body -4 1001 MySQL Tricks How to use full-text search engine -5 Go MySQL Tricks How to use full text search engine -2 How To Use MySQL Well After you went through a ... 1 MySQL Tutorial DBMS stands for MySQL DataBase ... +2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks How to use full-text search engine +5 Go MySQL Tricks How to use full text search engine SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION); +MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION) +ORDER BY id; id title body -5 Go MySQL Tricks How to use full text search engine -4 1001 MySQL Tricks How to use full-text search engine -2 How To Use MySQL Well After you went through a ... 1 MySQL Tutorial DBMS stands for MySQL DataBase ... +2 How To Use MySQL Well After you went through a ... 3 Optimizing MySQL In this tutorial we will show ... +4 1001 MySQL Tricks How to use full-text search engine +5 Go MySQL Tricks How to use full text search engine SELECT * FROM articles WHERE MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE); id title body @@ -135,27 +138,27 @@ INSERT INTO articles (title, body) VALUES ('1001 MySQL Tricks','How to use full-text search engine'), ('Go MariaDB Tricks','How to use full text search engine'); SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('MySQL'); +MATCH(title, body) AGAINST('MySQL') ORDER BY id; id title body 6 MySQL Tutorial DBMS stands for MySQL DataBase ... 7 How To Use MySQL Well After you went through a ... 8 Optimizing MySQL In this tutorial we will show ... 9 1001 MySQL Tricks How to use full-text search engine SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('tutorial'); +MATCH(title, body) AGAINST('tutorial') ORDER BY id; id title body 6 MySQL Tutorial DBMS stands for MySQL DataBase ... 8 Optimizing MySQL In this tutorial we will show ... SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('Tricks'); +MATCH(title, body) AGAINST('Tricks') ORDER BY id; id title body 9 1001 MySQL Tricks How to use full-text search engine 10 Go MariaDB Tricks How to use full text search engine SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('full text search'); +MATCH(title, body) AGAINST('full text search') ORDER BY id; id title body -10 Go MariaDB Tricks How to use full text search engine 9 1001 MySQL Tricks How to use full-text search engine +10 Go MariaDB Tricks How to use full text search engine SELECT COUNT(*) FROM articles; COUNT(*) 5 @@ -183,7 +186,8 @@ UNINSTALL PLUGIN simple_parser; Warnings: Warning 1620 Plugin is busy and will be uninstalled on shutdown SELECT * FROM articles WHERE -MATCH(title, body) AGAINST('mysql'); +MATCH(title, body) AGAINST('mysql') +ORDER BY id; id title body 1 MySQL Tutorial DBMS stands for MySQL DataBase ... 2 How To Use MySQL Well After you went through a ... diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test index b22ac456668..7279925386b 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test @@ -6,6 +6,9 @@ # Install fts parser plugin INSTALL PLUGIN simple_parser SONAME 'mypluglib'; +# Flush the table mysql.plugin in case the server shutdown would time out. +FLUSH TABLES; + -- echo # Test Part 1: Grammar Test # Create a myisam table and alter it to innodb table CREATE TABLE articles ( @@ -52,7 +55,7 @@ INSERT INTO articles (title, body) VALUES # Simple term search SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('mysql'); + MATCH(title, body) AGAINST('mysql') ORDER BY id; # Test stopword and word len less than fts_min_token_size SELECT * FROM articles WHERE @@ -90,7 +93,7 @@ ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser; # Simple term search SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('mysql'); + MATCH(title, body) AGAINST('mysql') ORDER BY id; # Test stopword and word len less than fts_min_token_size SELECT * FROM articles WHERE @@ -105,10 +108,12 @@ SELECT * FROM articles WHERE # Test query expansion SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION); + MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION) + ORDER BY id; SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION); + MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION) + ORDER BY id; # No result here, we get '"mysql' 'database"' by simple parser SELECT * FROM articles WHERE @@ -150,13 +155,13 @@ INSERT INTO articles (title, body) VALUES --source include/restart_mysqld.inc SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('MySQL'); + MATCH(title, body) AGAINST('MySQL') ORDER BY id; SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('tutorial'); + MATCH(title, body) AGAINST('tutorial') ORDER BY id; SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('Tricks'); + MATCH(title, body) AGAINST('Tricks') ORDER BY id; SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('full text search'); + MATCH(title, body) AGAINST('full text search') ORDER BY id; SELECT COUNT(*) FROM articles; INSERT INTO articles (title, body) VALUES ('111', '1234 1234 1234'); @@ -193,7 +198,8 @@ UNINSTALL PLUGIN simple_parser; # Simple term search SELECT * FROM articles WHERE - MATCH(title, body) AGAINST('mysql'); + MATCH(title, body) AGAINST('mysql') + ORDER BY id; # Test stopword and word len less than fts_min_token_size SELECT * FROM articles WHERE -- cgit v1.2.1