summaryrefslogtreecommitdiff
path: root/storage
diff options
context:
space:
mode:
Diffstat (limited to 'storage')
-rw-r--r--storage/connect/CMakeLists.txt1
-rw-r--r--storage/innobase/btr/btr0defragment.cc1
-rw-r--r--storage/innobase/fsp/fsp0fsp.cc18
-rw-r--r--storage/innobase/include/log0log.h14
-rw-r--r--storage/innobase/include/mtr0mtr.h6
-rw-r--r--storage/innobase/include/mtr0mtr.ic6
-rw-r--r--storage/innobase/log/log0log.cc101
-rw-r--r--storage/innobase/mtr/mtr0mtr.cc85
-rw-r--r--storage/innobase/row/row0ftsort.cc3
-rw-r--r--storage/innobase/trx/trx0purge.cc25
-rw-r--r--storage/spider/mysql-test/spider/r/udf_pushdown.result218
-rw-r--r--storage/spider/mysql-test/spider/t/udf_pushdown.inc48
-rw-r--r--storage/spider/mysql-test/spider/t/udf_pushdown.test141
-rw-r--r--storage/spider/spd_db_mysql.cc6
-rw-r--r--storage/spider/spd_param.cc2
15 files changed, 629 insertions, 46 deletions
diff --git a/storage/connect/CMakeLists.txt b/storage/connect/CMakeLists.txt
index 0100f80415d..5c1bdef4c5c 100644
--- a/storage/connect/CMakeLists.txt
+++ b/storage/connect/CMakeLists.txt
@@ -425,6 +425,7 @@ IF(MSVC)
# Temporarily disable "conversion from size_t .."
IF(CMAKE_SIZEOF_VOID_P EQUAL 8)
SET(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} /wd4267")
+ SET(CMAKE_C_FLAGS "${CMAKE_C_FLAGS} /wd4267")
ENDIF()
SET(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} /wd4996")
string(REPLACE "/permissive-" "" CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS}")
diff --git a/storage/innobase/btr/btr0defragment.cc b/storage/innobase/btr/btr0defragment.cc
index ea58d2f5fe1..67076e7a515 100644
--- a/storage/innobase/btr/btr0defragment.cc
+++ b/storage/innobase/btr/btr0defragment.cc
@@ -309,6 +309,7 @@ btr_defragment_save_defrag_stats_if_needed(
{
if (srv_defragment_stats_accuracy != 0 // stats tracking disabled
&& index->table->space_id != 0 // do not track system tables
+ && !index->table->is_temporary()
&& index->stat_defrag_modified_counter
>= srv_defragment_stats_accuracy) {
dict_stats_defrag_pool_add(index);
diff --git a/storage/innobase/fsp/fsp0fsp.cc b/storage/innobase/fsp/fsp0fsp.cc
index 759d7084f59..8ec813b1896 100644
--- a/storage/innobase/fsp/fsp0fsp.cc
+++ b/storage/innobase/fsp/fsp0fsp.cc
@@ -862,10 +862,12 @@ fsp_try_extend_data_file(fil_space_t* space, fsp_header_t* header, mtr_t* mtr)
return(0);
}
- /* We ignore any fragments of a full megabyte when storing the size
- to the space header */
+ /* For the system tablespace, we ignore any fragments of a
+ full megabyte when storing the size to the space header */
- space->size_in_header = ut_2pow_round(space->size, (1024 * 1024) / ps);
+ space->size_in_header = space->id
+ ? space->size
+ : ut_2pow_round(space->size, (1024 * 1024) / ps);
mlog_write_ulint(
header + FSP_SIZE, space->size_in_header, MLOG_4BYTES, mtr);
@@ -1241,7 +1243,7 @@ fsp_alloc_free_page(
/* It must be that we are extending a single-table tablespace
whose size is still < 64 pages */
- ut_a(!is_system_tablespace(space_id));
+ ut_a(!is_predefined_tablespace(space_id));
if (page_no >= FSP_EXTENT_SIZE) {
ib::error() << "Trying to extend a single-table"
" tablespace " << space->name << " , by single"
@@ -2319,14 +2321,14 @@ take_hinted_page:
return(NULL);
}
- if (space->size <= ret_page && !is_system_tablespace(space_id)) {
+ if (space->size <= ret_page && !is_predefined_tablespace(space_id)) {
/* It must be that we are extending a single-table
tablespace whose size is still < 64 pages */
if (ret_page >= FSP_EXTENT_SIZE) {
- ib::error() << "Error (2): trying to extend"
- " a single-table tablespace " << space_id
- << " by single page(s) though the"
+ ib::error() << "Trying to extend '"
+ << space->chain.start->name
+ << "' by single page(s) though the"
<< " space size " << space->size
<< ". Page no " << ret_page << ".";
ut_ad(!has_done_reservation);
diff --git a/storage/innobase/include/log0log.h b/storage/innobase/include/log0log.h
index 368d912f07f..fb027e1a823 100644
--- a/storage/innobase/include/log0log.h
+++ b/storage/innobase/include/log0log.h
@@ -2,7 +2,7 @@
Copyright (c) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Copyright (c) 2009, Google Inc.
-Copyright (c) 2017, 2020, MariaDB Corporation.
+Copyright (c) 2017, 2021, MariaDB Corporation.
Portions of this file contain modifications contributed and copyrighted by
Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -171,9 +171,15 @@ void log_write_up_to(lsn_t lsn, bool flush_to_disk, bool rotate_key = false);
/** write to the log file up to the last log entry.
@param[in] sync whether we want the written log
also to be flushed to disk. */
-void
-log_buffer_flush_to_disk(
- bool sync = true);
+void log_buffer_flush_to_disk(bool sync= true);
+
+
+/** Prepare to invoke log_write_and_flush(), before acquiring log_sys.mutex. */
+#define log_write_and_flush_prepare() log_write_mutex_enter()
+
+/** Durably write the log up to log_sys.lsn and release log_sys.mutex. */
+ATTRIBUTE_COLD void log_write_and_flush();
+
/****************************************************************//**
This functions writes the log buffer to the log file and if 'flush'
is set it forces a flush of the log file as well. This is meant to be
diff --git a/storage/innobase/include/mtr0mtr.h b/storage/innobase/include/mtr0mtr.h
index b5915c934a1..08e7deb052b 100644
--- a/storage/innobase/include/mtr0mtr.h
+++ b/storage/innobase/include/mtr0mtr.h
@@ -2,7 +2,7 @@
Copyright (c) 1995, 2017, Oracle and/or its affiliates. All Rights Reserved.
Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2020, MariaDB Corporation.
+Copyright (c) 2013, 2021, 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
@@ -133,6 +133,10 @@ struct mtr_t {
/** Commit the mini-transaction. */
void commit();
+ /** Commit a mini-transaction that is shrinking a tablespace.
+ @param space tablespace that is being shrunk */
+ ATTRIBUTE_COLD void commit_shrink(fil_space_t &space);
+
/** Commit a mini-transaction that did not modify any pages,
but generated some redo log on a higher level, such as
MLOG_FILE_NAME records and a MLOG_CHECKPOINT marker.
diff --git a/storage/innobase/include/mtr0mtr.ic b/storage/innobase/include/mtr0mtr.ic
index deb1e977404..c35de2bcbf9 100644
--- a/storage/innobase/include/mtr0mtr.ic
+++ b/storage/innobase/include/mtr0mtr.ic
@@ -1,7 +1,7 @@
/*****************************************************************************
Copyright (c) 1995, 2014, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2017, 2020, MariaDB Corporation.
+Copyright (c) 2017, 2021, 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
@@ -53,8 +53,8 @@ mtr_t::memo_push(void* object, mtr_memo_type_t type)
/* If this mtr has x-fixed a clean page then we set
the made_dirty flag. This tells us if we need to
- grab log_flush_order_mutex at mtr_commit so that we
- can insert the dirtied page to the flush list. */
+ grab log_sys.flush_order_mutex at mtr_t::commit() so that we
+ can insert the dirtied page into the flush list. */
if ((type == MTR_MEMO_PAGE_X_FIX || type == MTR_MEMO_PAGE_SX_FIX)
&& !m_made_dirty) {
diff --git a/storage/innobase/log/log0log.cc b/storage/innobase/log/log0log.cc
index 130c830858b..b2e8a15fbd3 100644
--- a/storage/innobase/log/log0log.cc
+++ b/storage/innobase/log/log0log.cc
@@ -2,7 +2,7 @@
Copyright (c) 1995, 2017, Oracle and/or its affiliates. All Rights Reserved.
Copyright (c) 2009, Google Inc.
-Copyright (c) 2014, 2020, MariaDB Corporation.
+Copyright (c) 2014, 2021, MariaDB Corporation.
Portions of this file contain modifications contributed and copyrighted by
Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -1043,12 +1043,101 @@ loop:
/** write to the log file up to the last log entry.
@param[in] sync whether we want the written log
also to be flushed to disk. */
-void
-log_buffer_flush_to_disk(
- bool sync)
+void log_buffer_flush_to_disk(bool sync)
{
- ut_ad(!srv_read_only_mode);
- log_write_up_to(log_get_lsn(), sync);
+ ut_ad(!srv_read_only_mode);
+ log_write_up_to(log_get_lsn(), sync);
+}
+
+
+/** Durably write the log and release log_sys.mutex */
+ATTRIBUTE_COLD void log_write_and_flush()
+{
+ ut_ad(!srv_read_only_mode);
+ ut_ad(!recv_no_log_write);
+ ut_ad(!recv_recovery_is_on());
+
+ /* The following code is adapted from log_write_up_to(). */
+ DBUG_PRINT("ib_log", ("write " LSN_PF " to " LSN_PF,
+ log_sys.write_lsn, log_sys.lsn));
+ log_sys.n_pending_flushes++;
+ log_sys.current_flush_lsn= log_sys.lsn;
+ os_event_reset(log_sys.flush_event);
+ ut_ad(log_sys.buf_free != log_sys.buf_next_to_write);
+ ulint start_offset= log_sys.buf_next_to_write;
+ ulint end_offset= log_sys.buf_free;
+ ulint area_start= ut_2pow_round(start_offset, ulint(OS_FILE_LOG_BLOCK_SIZE));
+ ulint area_end= ut_calc_align(end_offset, ulint(OS_FILE_LOG_BLOCK_SIZE));
+ ulong write_ahead_size= srv_log_write_ahead_size;
+
+ log_block_set_flush_bit(log_sys.buf + area_start, TRUE);
+ log_block_set_checkpoint_no(log_sys.buf + area_end - OS_FILE_LOG_BLOCK_SIZE,
+ log_sys.next_checkpoint_no);
+ lsn_t write_lsn= log_sys.lsn;
+ byte *write_buf= log_sys.buf;
+
+ ut_ad(area_end - area_start > 0);
+
+ log_buffer_switch();
+
+ log_sys.log.set_fields(log_sys.write_lsn);
+
+ /* Erase the end of the last log block. */
+ memset(write_buf + end_offset, 0,
+ ~end_offset & (OS_FILE_LOG_BLOCK_SIZE - 1));
+ /* Calculate pad_size if needed. */
+ ulint pad_size= 0;
+ if (write_ahead_size > OS_FILE_LOG_BLOCK_SIZE)
+ {
+ lsn_t end_offset=
+ log_sys.log.calc_lsn_offset(ut_uint64_align_up(write_lsn,
+ OS_FILE_LOG_BLOCK_SIZE));
+ ulint end_offset_in_unit= (ulint) (end_offset % write_ahead_size);
+
+ if (end_offset_in_unit && (area_end - area_start) > end_offset_in_unit)
+ {
+ /* The first block in the unit was initialized after the last
+ writing. Needs to be written padded data once. */
+ pad_size= std::min<ulint>(ulint(write_ahead_size) - end_offset_in_unit,
+ srv_log_buffer_size - area_end);
+ memset(write_buf + area_end, 0, pad_size);
+ }
+ }
+
+ if (log_sys.is_encrypted())
+ log_crypt(write_buf + area_start, log_sys.write_lsn,
+ area_end - area_start);
+
+ /* Do the write to the log files */
+ log_write_buf(write_buf + area_start, area_end - area_start + pad_size,
+#ifdef UNIV_DEBUG
+ pad_size,
+#endif /* UNIV_DEBUG */
+ ut_uint64_align_down(log_sys.write_lsn,
+ OS_FILE_LOG_BLOCK_SIZE),
+ start_offset - area_start);
+ srv_stats.log_padded.add(pad_size);
+ log_sys.write_lsn= write_lsn;
+
+ log_write_mutex_exit();
+
+ /* Code adapted from log_write_flush_to_disk_low() */
+
+ ut_a(log_sys.n_pending_flushes == 1); /* No other threads here */
+
+ if (srv_file_flush_method != SRV_O_DSYNC)
+ fil_flush(SRV_LOG_SPACE_FIRST_ID);
+
+ log_sys.flushed_to_disk_lsn= log_sys.current_flush_lsn;
+
+ log_sys.n_pending_flushes--;
+
+ os_event_set(log_sys.flush_event);
+
+ const lsn_t flush_lsn= log_sys.flushed_to_disk_lsn;
+ log_mutex_exit();
+
+ innobase_mysql_log_notify(flush_lsn);
}
/****************************************************************//**
diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc
index 4d2deb49d46..b6e6055bbc4 100644
--- a/storage/innobase/mtr/mtr0mtr.cc
+++ b/storage/innobase/mtr/mtr0mtr.cc
@@ -1,7 +1,7 @@
/*****************************************************************************
Copyright (c) 1995, 2017, Oracle and/or its affiliates. All Rights Reserved.
-Copyright (c) 2017, 2020, MariaDB Corporation.
+Copyright (c) 2017, 2021, 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
@@ -463,6 +463,89 @@ mtr_t::commit()
release_resources();
}
+#ifdef UNIV_DEBUG
+/** Check that all pages belong to a shrunk tablespace. */
+struct Shrink
+{
+ const fil_space_t &space;
+ Shrink(const fil_space_t &space) : space(space) {}
+
+ bool operator()(const mtr_memo_slot_t *slot) const
+ {
+ if (!slot->object)
+ return true;
+ switch (slot->type) {
+ default:
+ ut_ad("invalid type" == 0);
+ return false;
+ case MTR_MEMO_MODIFY:
+ break;
+ case MTR_MEMO_SPACE_X_LOCK:
+ ut_ad(&space == slot->object);
+ return true;
+ case MTR_MEMO_PAGE_X_FIX:
+ case MTR_MEMO_PAGE_SX_FIX:
+ const buf_page_t &bpage= static_cast<buf_block_t*>(slot->object)->page;
+ const page_id_t &id= bpage.id;
+ if (id.space() == 0 && id.page_no() == TRX_SYS_PAGE_NO)
+ {
+ ut_ad(srv_is_undo_tablespace(space.id));
+ break;
+ }
+ ut_ad(id.space() == space.id);
+ ut_ad(id.page_no() < space.size);
+ ut_ad(bpage.state == BUF_BLOCK_FILE_PAGE);
+ ut_ad(!bpage.oldest_modification);
+ break;
+ }
+ return true;
+ }
+};
+#endif
+
+/** Commit a mini-transaction that is shrinking a tablespace.
+@param space tablespace that is being shrunk */
+void mtr_t::commit_shrink(fil_space_t &space)
+{
+ ut_ad(is_active());
+ ut_ad(!is_inside_ibuf());
+ ut_ad(!high_level_read_only);
+ ut_ad(m_modifications);
+ ut_ad(m_made_dirty);
+ ut_ad(!recv_recovery_is_on());
+ ut_ad(m_log_mode == MTR_LOG_ALL);
+ ut_ad(UT_LIST_GET_LEN(space.chain) == 1);
+
+ log_write_and_flush_prepare();
+
+ const lsn_t start_lsn= finish_write(prepare_write());
+
+ log_flush_order_mutex_enter();
+ /* Durably write the reduced FSP_SIZE before truncating the data file. */
+ log_write_and_flush();
+
+ os_file_truncate(space.chain.start->name, space.chain.start->handle,
+ os_offset_t(space.size) << srv_page_size_shift, true);
+
+ ut_d(m_memo.for_each_block_in_reverse(CIterate<Shrink>(space)));
+
+ m_memo.for_each_block_in_reverse(CIterate<const ReleaseBlocks>
+ (ReleaseBlocks(start_lsn, m_commit_lsn,
+ m_flush_observer)));
+ log_flush_order_mutex_exit();
+
+ mutex_enter(&fil_system.mutex);
+ ut_ad(space.is_being_truncated);
+ space.is_being_truncated= false;
+ space.set_stopping(false);
+ mutex_exit(&fil_system.mutex);
+
+ m_memo.for_each_block_in_reverse(CIterate<ReleaseLatches>());
+ srv_stats.log_write_requests.inc();
+
+ release_resources();
+}
+
/** Commit a mini-transaction that did not modify any pages,
but generated some redo log on a higher level, such as
MLOG_FILE_NAME records and a MLOG_CHECKPOINT marker.
diff --git a/storage/innobase/row/row0ftsort.cc b/storage/innobase/row/row0ftsort.cc
index 196dadc557f..f7c07ec1ca9 100644
--- a/storage/innobase/row/row0ftsort.cc
+++ b/storage/innobase/row/row0ftsort.cc
@@ -1665,7 +1665,6 @@ row_fts_merge_insert(
aux_table = dict_table_open_on_name(aux_table_name, FALSE, FALSE,
DICT_ERR_IGNORE_NONE);
ut_ad(aux_table != NULL);
- dict_table_close(aux_table, FALSE, FALSE);
aux_index = dict_table_get_first_index(aux_table);
ut_ad(!aux_index->is_instant());
@@ -1792,6 +1791,8 @@ row_fts_merge_insert(
}
exit:
+ dict_table_close(aux_table, FALSE, FALSE);
+
fts_sql_commit(trx);
trx->op_info = "";
diff --git a/storage/innobase/trx/trx0purge.cc b/storage/innobase/trx/trx0purge.cc
index f50d66041b5..8a74dbfa2bf 100644
--- a/storage/innobase/trx/trx0purge.cc
+++ b/storage/innobase/trx/trx0purge.cc
@@ -597,7 +597,7 @@ static void trx_purge_truncate_history()
return;
}
- const fil_space_t& space = *purge_sys.truncate.current;
+ fil_space_t& space = *purge_sys.truncate.current;
/* Undo tablespace always are a single file. */
ut_a(UT_LIST_GET_LEN(space.chain) == 1);
fil_node_t* file = UT_LIST_GET_FIRST(space.chain);
@@ -758,28 +758,11 @@ not_free:
rseg->needs_purge = false;
}
- mtr.commit();
- /* Write-ahead the redo log record. */
- log_write_up_to(mtr.commit_lsn(), true);
-
- /* Trim the file size. */
- os_file_truncate(file->name, file->handle,
- os_offset_t(size) << srv_page_size_shift,
- true);
-
- /* This is only executed by srv_purge_coordinator_thread. */
+ mtr.commit_shrink(space);
+ /* No mutex; this is only updated by the purge coordinator. */
export_vars.innodb_undo_truncations++;
- /* In MDEV-8319 (10.5) we will PUNCH_HOLE the garbage
- (with write-ahead logging). */
- mutex_enter(&fil_system.mutex);
- ut_ad(&space == purge_sys.truncate.current);
- ut_ad(space.is_being_truncated);
- purge_sys.truncate.current->set_stopping(false);
- purge_sys.truncate.current->is_being_truncated = false;
- mutex_exit(&fil_system.mutex);
-
- if (purge_sys.rseg != NULL
+ if (purge_sys.rseg
&& purge_sys.rseg->last_page_no == FIL_NULL) {
/* If purge_sys.rseg is pointing to rseg that
was recently truncated then move to next rseg
diff --git a/storage/spider/mysql-test/spider/r/udf_pushdown.result b/storage/spider/mysql-test/spider/r/udf_pushdown.result
new file mode 100644
index 00000000000..4ca734165e7
--- /dev/null
+++ b/storage/spider/mysql-test/spider/r/udf_pushdown.result
@@ -0,0 +1,218 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+#
+# MDEV-26545 Spider does not correctly handle UDF and stored function in where conds
+#
+
+##### enable general_log #####
+connection child2_1;
+SET @general_log_backup = @@global.general_log;
+SET @log_output_backup = @@global.log_output;
+SET @@global.general_log = 1;
+SET @@global.log_output = "TABLE";
+TRUNCATE TABLE mysql.general_log;
+
+##### create databases #####
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection child2_1;
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+##### create tables #####
+connection child2_1;
+CHILD_CREATE_TABLE
+connection master_1;
+MASTER_CREATE_TABLE
+CREATE TABLE ta_l (
+id INT NOT NULL,
+a INT,
+PRIMARY KEY(id)
+) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
+INSERT INTO ta_l VALUES
+(1, 11),
+(2, 22),
+(3, 33),
+(4, 44),
+(5, 55);
+
+##### create functions #####
+connection master_1;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+RETURN param + 1;
+END //
+connection child2_1;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+RETURN param + 1;
+END //
+
+########## spider_use_pushdown_udf=0 ##########
+connection master_1;
+SET @@spider_use_pushdown_udf = 0;
+
+##### test SELECTs #####
+connection master_1;
+SELECT * FROM ta_l WHERE id = plusone(1);
+id a
+2 22
+SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+id a
+3 33
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%";
+argument
+select `id`,`a` from `auto_test_remote`.`ta_r`
+select `id`,`a` from `auto_test_remote`.`ta_r`
+
+##### test UPDATEs #####
+connection master_1;
+UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 33
+4 44
+5 55
+UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 333
+4 44
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%";
+argument
+select `id`,`a` from `auto_test_remote`.`ta_r` for update
+update `auto_test_remote`.`ta_r` set `a` = 222 where `id` = 2 limit 1
+select `id`,`a` from `auto_test_remote`.`ta_r` for update
+update `auto_test_remote`.`ta_r` set `a` = 333 where `id` = 3 and `a` = 33 limit 1
+
+##### test DELETEs #####
+connection master_1;
+DELETE FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+4 44
+5 55
+DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%";
+argument
+select `id` from `auto_test_remote`.`ta_r` for update
+delete from `auto_test_remote`.`ta_r` where `id` = 2 limit 1
+select `id`,`a` from `auto_test_remote`.`ta_r` for update
+delete from `auto_test_remote`.`ta_r` where `id` = 4 and `a` = 44 limit 1
+
+##### reset records #####
+connection master_1;
+TRUNCATE TABLE ta_l;
+INSERT INTO ta_l VALUES
+(1, 11),
+(2, 22),
+(3, 33),
+(4, 44),
+(5, 55);
+
+########## spider_use_pushdown_udf=1 ##########
+connection master_1;
+SET @@spider_use_pushdown_udf = 1;
+
+##### test SELECTs #####
+connection master_1;
+SELECT * FROM ta_l WHERE id = plusone(1);
+id a
+2 22
+SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+id a
+3 33
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%";
+argument
+select t0.`id` `id`,t0.`a` `a` from `auto_test_remote`.`ta_r` t0 where (t0.`id` = (`plusone`(1)))
+select t0.`id` `id`,t0.`a` `a` from `auto_test_remote`.`ta_r` t0 where ((t0.`id` in( (`plusone`(1)) , (`plusone`(2)))) and (t0.`a` = (`plusone`(32))))
+
+##### test UPDATEs #####
+connection master_1;
+UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 33
+4 44
+5 55
+UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+SELECT * FROM ta_l;
+id a
+1 11
+2 222
+3 333
+4 44
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%";
+argument
+update `auto_test_remote`.`ta_r` set `a` = (`plusone`(221)) where (`id` = (`plusone`(1)))
+update `auto_test_remote`.`ta_r` set `a` = (`plusone`(332)) where ((`id` in( (`plusone`(1)) , (`plusone`(2)))) and (`a` = (`plusone`(32))))
+
+##### test DELETEs #####
+connection master_1;
+DELETE FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+4 44
+5 55
+DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43);
+SELECT * FROM ta_l;
+id a
+1 11
+3 333
+5 55
+connection child2_1;
+SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%";
+argument
+delete from `auto_test_remote`.`ta_r` where (`id` = (`plusone`(1)))
+delete from `auto_test_remote`.`ta_r` where ((`id` in( (`plusone`(1)) , (`plusone`(2)) , (`plusone`(3)))) and (`a` = (`plusone`(43))))
+
+deinit
+connection master_1;
+DROP FUNCTION `plusone`;
+DROP DATABASE IF EXISTS auto_test_local;
+connection child2_1;
+SET @@global.general_log = @general_log_backup;
+SET @@global.log_output = @log_output_backup;
+DROP FUNCTION `plusone`;
+DROP DATABASE IF EXISTS auto_test_remote;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+child3_1
+child3_2
+child3_3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/t/udf_pushdown.inc b/storage/spider/mysql-test/spider/t/udf_pushdown.inc
new file mode 100644
index 00000000000..160e8af21b2
--- /dev/null
+++ b/storage/spider/mysql-test/spider/t/udf_pushdown.inc
@@ -0,0 +1,48 @@
+--echo
+--echo ##### test SELECTs #####
+--connection master_1
+SELECT * FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SELECT argument FROM mysql.general_log WHERE argument LIKE "%select%" AND argument NOT LIKE "%argument%";
+ --disable_query_log
+ TRUNCATE TABLE mysql.general_log;
+ --enable_query_log
+}
+
+--echo
+--echo ##### test UPDATEs #####
+--connection master_1
+UPDATE ta_l SET a = plusone(221) WHERE id = plusone(1);
+SELECT * FROM ta_l;
+UPDATE ta_l SET a = plusone(332) WHERE id IN (plusone(1), plusone(2)) AND a = plusone(32);
+SELECT * FROM ta_l;
+
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SELECT argument FROM mysql.general_log WHERE argument LIKE "%update%" AND argument NOT LIKE "%argument%";
+ --disable_query_log
+ TRUNCATE TABLE mysql.general_log;
+ --enable_query_log
+}
+
+--echo
+--echo ##### test DELETEs #####
+--connection master_1
+DELETE FROM ta_l WHERE id = plusone(1);
+SELECT * FROM ta_l;
+DELETE FROM ta_l WHERE id IN (plusone(1), plusone(2), plusone(3)) AND a = plusone(43);
+SELECT * FROM ta_l;
+
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SELECT argument FROM mysql.general_log WHERE (argument LIKE "%delete%" OR argument LIKE "%update%") AND argument NOT LIKE "%argument%";
+ --disable_query_log
+ TRUNCATE TABLE mysql.general_log;
+ --enable_query_log
+}
diff --git a/storage/spider/mysql-test/spider/t/udf_pushdown.test b/storage/spider/mysql-test/spider/t/udf_pushdown.test
new file mode 100644
index 00000000000..2eadbbbb40b
--- /dev/null
+++ b/storage/spider/mysql-test/spider/t/udf_pushdown.test
@@ -0,0 +1,141 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source test_init.inc
+--enable_result_log
+--enable_query_log
+
+--echo #
+--echo # MDEV-26545 Spider does not correctly handle UDF and stored function in where conds
+--echo #
+
+let $CHILD_CREATE_TABLE=
+ CREATE TABLE ta_r (
+ id INT NOT NULL,
+ a INT,
+ PRIMARY KEY(id)
+ ) $CHILD2_1_ENGINE $CHILD2_1_CHARSET;
+
+let $MASTER_CREATE_TABLE_OUTPUT=
+ CREATE TABLE ta_l (
+ id INT NOT NULL,
+ a INT,
+ PRIMARY KEY(id)
+ ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1;
+
+let $MASTER_CREATE_TABLE=
+ CREATE TABLE ta_l (
+ id INT NOT NULL,
+ a INT,
+ PRIMARY KEY(id)
+ ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_2_1;
+
+--echo
+--echo ##### enable general_log #####
+--connection child2_1
+SET @general_log_backup = @@global.general_log;
+SET @log_output_backup = @@global.log_output;
+SET @@global.general_log = 1;
+SET @@global.log_output = "TABLE";
+TRUNCATE TABLE mysql.general_log;
+
+--echo
+--echo ##### create databases #####
+--connection master_1
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ CREATE DATABASE auto_test_remote;
+ USE auto_test_remote;
+}
+
+--echo
+--echo ##### create tables #####
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ --disable_query_log
+ echo CHILD_CREATE_TABLE;
+ eval $CHILD_CREATE_TABLE;
+ --enable_query_log
+}
+
+--connection master_1
+--disable_query_log
+echo MASTER_CREATE_TABLE;
+echo $MASTER_CREATE_TABLE_OUTPUT;
+eval $MASTER_CREATE_TABLE;
+--enable_query_log
+
+INSERT INTO ta_l VALUES
+ (1, 11),
+ (2, 22),
+ (3, 33),
+ (4, 44),
+ (5, 55);
+
+--echo
+--echo ##### create functions #####
+--connection master_1
+DELIMITER //;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+ RETURN param + 1;
+END //
+DELIMITER ;//
+
+--connection child2_1
+DELIMITER //;
+CREATE FUNCTION `plusone`( param INT ) RETURNS INT
+BEGIN
+ RETURN param + 1;
+END //
+DELIMITER ;//
+
+--echo
+--echo ########## spider_use_pushdown_udf=0 ##########
+--connection master_1
+SET @@spider_use_pushdown_udf = 0;
+--source udf_pushdown.inc
+
+--echo
+--echo ##### reset records #####
+--connection master_1
+TRUNCATE TABLE ta_l;
+INSERT INTO ta_l VALUES
+ (1, 11),
+ (2, 22),
+ (3, 33),
+ (4, 44),
+ (5, 55);
+
+--echo
+--echo ########## spider_use_pushdown_udf=1 ##########
+--connection master_1
+SET @@spider_use_pushdown_udf = 1;
+--source udf_pushdown.inc
+
+--echo
+--echo deinit
+--disable_warnings
+--connection master_1
+DROP FUNCTION `plusone`;
+DROP DATABASE IF EXISTS auto_test_local;
+if ($USE_CHILD_GROUP2)
+{
+ --connection child2_1
+ SET @@global.general_log = @general_log_backup;
+ SET @@global.log_output = @log_output_backup;
+ DROP FUNCTION `plusone`;
+ DROP DATABASE IF EXISTS auto_test_remote;
+}
+--disable_query_log
+--disable_result_log
+--source test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--echo
+--echo end of test
diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc
index cfe2fecf9d6..ee3c25d914c 100644
--- a/storage/spider/spd_db_mysql.cc
+++ b/storage/spider/spd_db_mysql.cc
@@ -6476,10 +6476,16 @@ int spider_db_mbase_util::open_item_func(
separator_str_length = SPIDER_SQL_AND_LEN;
}
break;
+ case Item_func::FUNC_SP:
case Item_func::UDF_FUNC:
use_pushdown_udf = spider_param_use_pushdown_udf(spider->trx->thd,
spider->share->use_pushdown_udf);
if (!use_pushdown_udf)
+ /*
+ This is the default behavior because the remote nodes may deal with
+ the function in an unexpected way (e.g. not having the same
+ definition). Users can turn it on if they know what they are doing.
+ */
DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM);
if (str)
{
diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc
index 446ccc22141..81540a1ef91 100644
--- a/storage/spider/spd_param.cc
+++ b/storage/spider/spd_param.cc
@@ -1978,7 +1978,7 @@ static MYSQL_THDVAR_INT(
"Remote server transmission existence when UDF is used at condition and \"engine_condition_pushdown=1\"", /* comment */
NULL, /* check */
NULL, /* update */
- -1, /* def */
+ 0, /* def */
-1, /* min */
1, /* max */
0 /* blk */
View Lorry Controller Status