Merge 10.8 into 10.9

17 files changed, 237 insertions, 111 deletions

diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc
index f9ee51e466b..35ac226f848 100644
--- a/storage/innobase/buf/buf0buf.cc
+++ b/storage/innobase/buf/buf0buf.cc
@@ -2136,15 +2136,17 @@ void buf_pool_t::watch_unset(const page_id_t id, buf_pool_t::hash_chain &chain)
     if (!watch_is_sentinel(*w))
     {
     no_watch:
-      ut_d(const auto s=) w->unfix();
-      ut_ad(~buf_page_t::LRU_MASK & s);
+      w->unfix();
       w= nullptr;
     }
-    const auto state= w->state();
-    ut_ad(~buf_page_t::LRU_MASK & state);
-    ut_ad(state >= buf_page_t::UNFIXED);
-    if (state != buf_page_t::UNFIXED + 1)
-      goto no_watch;
+    else
+    {
+      const auto state= w->state();
+      ut_ad(~buf_page_t::LRU_MASK & state);
+      ut_ad(state >= buf_page_t::UNFIXED + 1);
+      if (state != buf_page_t::UNFIXED + 1)
+        goto no_watch;
+    }
   }
 
   if (!w)
diff --git a/storage/innobase/dict/dict0dict.cc b/storage/innobase/dict/dict0dict.cc
index 3b4b280d1da..00ec4a219c5 100644
--- a/storage/innobase/dict/dict0dict.cc
+++ b/storage/innobase/dict/dict0dict.cc
@@ -68,6 +68,7 @@ Created 1/8/1996 Heikki Tuuri
 #include "srv0mon.h"
 #include "srv0start.h"
 #include "trx0undo.h"
+#include "trx0purge.h"
 
 #include <vector>
 #include <algorithm>
@@ -601,13 +602,13 @@ dict_index_get_nth_field_pos(
 }
 
 /** Parse the table file name into table name and database name.
-@tparam		dict_locked	whether dict_sys.lock() was called
-@param[in,out]	db_name		database name buffer
-@param[in,out]	tbl_name	table name buffer
-@param[out]	db_name_len	database name length
-@param[out]	tbl_name_len	table name length
+@tparam        dict_frozen  whether the caller holds dict_sys.latch
+@param[in,out] db_name      database name buffer
+@param[in,out] tbl_name     table name buffer
+@param[out] db_name_len     database name length
+@param[out] tbl_name_len    table name length
 @return whether the table name is visible to SQL */
-template<bool dict_locked>
+template<bool dict_frozen>
 bool dict_table_t::parse_name(char (&db_name)[NAME_LEN + 1],
                               char (&tbl_name)[NAME_LEN + 1],
                               size_t *db_name_len, size_t *tbl_name_len) const
@@ -615,7 +616,7 @@ bool dict_table_t::parse_name(char (&db_name)[NAME_LEN + 1],
   char db_buf[MAX_DATABASE_NAME_LEN + 1];
   char tbl_buf[MAX_TABLE_NAME_LEN + 1];
 
-  if (!dict_locked)
+  if (!dict_frozen)
     dict_sys.freeze(SRW_LOCK_CALL); /* protect against renaming */
   ut_ad(dict_sys.frozen());
   const size_t db_len= name.dblen();
@@ -635,7 +636,7 @@ bool dict_table_t::parse_name(char (&db_name)[NAME_LEN + 1],
   memcpy(tbl_buf, mdl_name.m_name + db_len + 1, tbl_len);
   tbl_buf[tbl_len]= 0;
 
-  if (!dict_locked)
+  if (!dict_frozen)
     dict_sys.unfreeze();
 
   *db_name_len= filename_to_tablename(db_buf, db_name,
@@ -781,7 +782,7 @@ return_without_mdl:
 
   size_t db1_len, tbl1_len;
 
-  if (!table->parse_name<!trylock>(db_buf1, tbl_buf1, &db1_len, &tbl1_len))
+  if (!table->parse_name<true>(db_buf1, tbl_buf1, &db1_len, &tbl1_len))
   {
     /* The table was renamed to #sql prefix.
     Release MDL (if any) for the old name and return. */
@@ -819,12 +820,14 @@ template dict_table_t* dict_acquire_mdl_shared<true>
 (dict_table_t*,THD*,MDL_ticket**,dict_table_op_t);
 
 /** Look up a table by numeric identifier.
+@tparam purge_thd Whether the function is called by purge thread
 @param[in]      table_id        table identifier
 @param[in]      dict_locked     data dictionary locked
 @param[in]      table_op        operation to perform when opening
 @param[in,out]  thd             background thread, or NULL to not acquire MDL
 @param[out]     mdl             mdl ticket, or NULL
 @return table, NULL if does not exist */
+template <bool purge_thd>
 dict_table_t*
 dict_table_open_on_id(table_id_t table_id, bool dict_locked,
                       dict_table_op_t table_op, THD *thd,
@@ -837,6 +840,12 @@ dict_table_open_on_id(table_id_t table_id, bool dict_locked,
 
   if (table)
   {
+    if (purge_thd && purge_sys.must_wait_FTS())
+    {
+      table= nullptr;
+      goto func_exit;
+    }
+
     table->acquire();
     if (thd && !dict_locked)
       table= dict_acquire_mdl_shared<false>(table, thd, mdl, table_op);
@@ -853,7 +862,14 @@ dict_table_open_on_id(table_id_t table_id, bool dict_locked,
                                  ? DICT_ERR_IGNORE_RECOVER_LOCK
                                  : DICT_ERR_IGNORE_FK_NOKEY);
     if (table)
+    {
+      if (purge_thd && purge_sys.must_wait_FTS())
+      {
+        dict_sys.unlock();
+        return nullptr;
+      }
       table->acquire();
+    }
     if (!dict_locked)
     {
       dict_sys.unlock();
@@ -867,12 +883,22 @@ dict_table_open_on_id(table_id_t table_id, bool dict_locked,
     }
   }
 
+func_exit:
   if (!dict_locked)
     dict_sys.unfreeze();
 
   return table;
 }
 
+template dict_table_t* dict_table_open_on_id<false>
+(table_id_t table_id, bool dict_locked,
+ dict_table_op_t table_op, THD *thd,
+ MDL_ticket **mdl);
+template dict_table_t* dict_table_open_on_id<true>
+(table_id_t table_id, bool dict_locked,
+ dict_table_op_t table_op, THD *thd,
+ MDL_ticket **mdl);
+
 /********************************************************************//**
 Looks for column n position in the clustered index.
 @return position in internal representation of the clustered index */
diff --git a/storage/innobase/fts/fts0fts.cc b/storage/innobase/fts/fts0fts.cc
index 31c97ffcf42..5358df2271a 100644
--- a/storage/innobase/fts/fts0fts.cc
+++ b/storage/innobase/fts/fts0fts.cc
@@ -1555,12 +1555,16 @@ have any other reference count.
 static void fts_table_no_ref_count(const char *table_name)
 {
   dict_table_t *table= dict_table_open_on_name(
-    table_name, false, DICT_ERR_IGNORE_TABLESPACE);
+    table_name, true, DICT_ERR_IGNORE_TABLESPACE);
   if (!table)
     return;
 
   while (table->get_ref_count() > 1)
+  {
+    dict_sys.unlock();
     std::this_thread::sleep_for(std::chrono::milliseconds(50));
+    dict_sys.lock(SRW_LOCK_CALL);
+  }
 
   table->release();
 }
@@ -1572,8 +1576,10 @@ and common table associated with the fts table.
 			already stopped*/
 void purge_sys_t::stop_FTS(const dict_table_t &table, bool already_stopped)
 {
+  dict_sys.lock(SRW_LOCK_CALL);
   if (!already_stopped)
     purge_sys.stop_FTS();
+
   fts_table_t fts_table;
   char table_name[MAX_FULL_NAME_LEN];
 
@@ -1582,28 +1588,31 @@ void purge_sys_t::stop_FTS(const dict_table_t &table, bool already_stopped)
   for (const char **suffix= fts_common_tables; *suffix; suffix++)
   {
     fts_table.suffix= *suffix;
-    fts_get_table_name(&fts_table, table_name, false);
+    fts_get_table_name(&fts_table, table_name, true);
     fts_table_no_ref_count(table_name);
   }
 
-  if (!table.fts)
-    return;
-  auto indexes= table.fts->indexes;
-  if (!indexes)
-    return;
-  for (ulint i= 0;i < ib_vector_size(indexes); ++i)
+  if (table.fts)
   {
-    const dict_index_t *index= static_cast<const dict_index_t*>(
-      ib_vector_getp(indexes, i));
-    FTS_INIT_INDEX_TABLE(&fts_table, nullptr, FTS_INDEX_TABLE, index);
-    for (const fts_index_selector_t *s= fts_index_selector;
-         s->suffix; s++)
+    if (auto indexes= table.fts->indexes)
     {
-      fts_table.suffix= s->suffix;
-      fts_get_table_name(&fts_table, table_name, false);
-      fts_table_no_ref_count(table_name);
+      for (ulint i= 0;i < ib_vector_size(indexes); ++i)
+      {
+        const dict_index_t *index= static_cast<const dict_index_t*>(
+          ib_vector_getp(indexes, i));
+        FTS_INIT_INDEX_TABLE(&fts_table, nullptr, FTS_INDEX_TABLE, index);
+        for (const fts_index_selector_t *s= fts_index_selector;
+             s->suffix; s++)
+        {
+          fts_table.suffix= s->suffix;
+          fts_get_table_name(&fts_table, table_name, true);
+          fts_table_no_ref_count(table_name);
+        }
+      }
     }
   }
+
+  dict_sys.unlock();
 }
 
 /** Lock the internal FTS_ tables for table, before fts_drop_tables().
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 9afb0405444..546ef821806 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -15788,6 +15788,12 @@ ha_innobase::start_stmt(
 		if (!trx->bulk_insert) {
 			break;
 		}
+
+		/* Trigger could've initiated another stmt.
+		So apply all bulk operation and mark as
+		end bulk insert for all tables */
+		trx->bulk_insert_apply();
+		trx->end_bulk_insert();
 		trx->bulk_insert = false;
 		trx->last_sql_stat_start.least_undo_no = trx->undo_no;
 	}
diff --git a/storage/innobase/include/buf0buf.h b/storage/innobase/include/buf0buf.h
index f8b960910af..d8e01fa37fa 100644
--- a/storage/innobase/include/buf0buf.h
+++ b/storage/innobase/include/buf0buf.h
@@ -2003,7 +2003,8 @@ inline void buf_page_t::set_state(uint32_t s)
 {
   mysql_mutex_assert_owner(&buf_pool.mutex);
   ut_ad(s <= REMOVE_HASH || s >= UNFIXED);
-  ut_ad(s <= READ_FIX);
+  ut_ad(s < WRITE_FIX);
+  ut_ad(s <= READ_FIX || zip.fix == READ_FIX);
   zip.fix= s;
 }
 
diff --git a/storage/innobase/include/dict0dict.h b/storage/innobase/include/dict0dict.h
index 7788158dc62..244d178da3a 100644
--- a/storage/innobase/include/dict0dict.h
+++ b/storage/innobase/include/dict0dict.h
@@ -2,7 +2,7 @@
 
 Copyright (c) 1996, 2018, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2021, MariaDB Corporation.
+Copyright (c) 2013, 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
@@ -146,6 +146,7 @@ dict_acquire_mdl_shared(dict_table_t *table,
 @param[in,out]  thd             background thread, or NULL to not acquire MDL
 @param[out]     mdl             mdl ticket, or NULL
 @return table, NULL if does not exist */
+template<bool purge_thd= false>
 dict_table_t*
 dict_table_open_on_id(table_id_t table_id, bool dict_locked,
                       dict_table_op_t table_op, THD *thd= nullptr,
@@ -1566,7 +1567,7 @@ public:
     }
     else
       lock_wait(SRW_LOCK_ARGS(file, line));
-   }
+  }
 
 #ifdef UNIV_PFS_RWLOCK
   /** Unlock the data dictionary cache. */
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
index 06521c19e57..bfa3742f683 100644
--- a/storage/innobase/include/dict0mem.h
+++ b/storage/innobase/include/dict0mem.h
@@ -2,7 +2,7 @@
 
 Copyright (c) 1996, 2017, Oracle and/or its affiliates. All Rights Reserved.
 Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2021, MariaDB Corporation.
+Copyright (c) 2013, 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
@@ -1947,17 +1947,17 @@ struct dict_table_t {
 	/** For overflow fields returns potential max length stored inline */
 	inline size_t get_overflow_field_local_len() const;
 
-	/** Parse the table file name into table name and database name.
-	@tparam		dict_locked	whether dict_sys.lock() was called
-	@param[in,out]	db_name		database name buffer
-	@param[in,out]	tbl_name	table name buffer
-	@param[out]	db_name_len	database name length
-	@param[out]	tbl_name_len	table name length
-	@return whether the table name is visible to SQL */
-	template<bool dict_locked= false>
-	bool parse_name(char (&db_name)[NAME_LEN + 1],
-			char (&tbl_name)[NAME_LEN + 1],
-			size_t *db_name_len, size_t *tbl_name_len) const;
+  /** Parse the table file name into table name and database name.
+  @tparam        dict_frozen  whether the caller holds dict_sys.latch
+  @param[in,out] db_name      database name buffer
+  @param[in,out] tbl_name     table name buffer
+  @param[out] db_name_len     database name length
+  @param[out] tbl_name_len    table name length
+  @return whether the table name is visible to SQL */
+  template<bool dict_frozen= false>
+  bool parse_name(char (&db_name)[NAME_LEN + 1],
+                  char (&tbl_name)[NAME_LEN + 1],
+                  size_t *db_name_len, size_t *tbl_name_len) const;
 
   /** Clear the table when rolling back TRX_UNDO_EMPTY */
   void clear(que_thr_t *thr);
diff --git a/storage/innobase/log/log0recv.cc b/storage/innobase/log/log0recv.cc
index 27dce5fa17d..46e3b8a7548 100644
--- a/storage/innobase/log/log0recv.cc
+++ b/storage/innobase/log/log0recv.cc
@@ -1871,7 +1871,7 @@ dberr_t recv_sys_t::find_checkpoint()
     sql_print_error("%s The redo log was created with %s%s",
                     srv_operation == SRV_OPERATION_NORMAL
                     ? "InnoDB: Upgrade after a crash is not supported."
-                    : "mariadb-backup --prepare is not possible", creator,
+                    : "mariadb-backup --prepare is not possible.", creator,
                     (err == DB_ERROR ? "." : ", and it appears corrupted."));
     return err;
   }
diff --git a/storage/innobase/row/row0purge.cc b/storage/innobase/row/row0purge.cc
index f0043ab5c85..a68c8d64a78 100644
--- a/storage/innobase/row/row0purge.cc
+++ b/storage/innobase/row/row0purge.cc
@@ -1027,10 +1027,14 @@ row_purge_parse_undo_rec(
 try_again:
 	purge_sys.check_stop_FTS();
 
-	node->table = dict_table_open_on_id(
+	node->table = dict_table_open_on_id<true>(
 		table_id, false, DICT_TABLE_OP_NORMAL, node->purge_thd,
 		&node->mdl_ticket);
 
+	if (!node->table && purge_sys.must_wait_FTS()) {
+		goto try_again;
+	}
+
 	if (!node->table) {
 		/* The table has been dropped: no need to do purge and
 		release mdl happened as a part of open process itself */
diff --git a/storage/spider/ha_spider.cc b/storage/spider/ha_spider.cc
index fd5c03586b1..06606f6fd62 100644
--- a/storage/spider/ha_spider.cc
+++ b/storage/spider/ha_spider.cc
@@ -851,54 +851,83 @@ int ha_spider::external_lock(
   int error_num = 0;
   SPIDER_TRX *trx;
   backup_error_status();
+
   DBUG_ENTER("ha_spider::external_lock");
   DBUG_PRINT("info",("spider this=%p", this));
   DBUG_PRINT("info",("spider lock_type=%x", lock_type));
-  if (
-    wide_handler->stage == SPD_HND_STAGE_EXTERNAL_LOCK &&
-    wide_handler->stage_executor != this)
+  DBUG_PRINT("info", ("spider sql_command=%d", thd_sql_command(thd)));
+
+  if (wide_handler->stage == SPD_HND_STAGE_EXTERNAL_LOCK)
   {
-    DBUG_RETURN(0);
+    /* Only the stage executor deals with table locks. */
+    if (wide_handler->stage_executor != this)
+    {
+      DBUG_RETURN(0);
+    }
+  }
+  else
+  {
+    /* Update the stage executor when the stage changes */
+    wide_handler->stage= SPD_HND_STAGE_EXTERNAL_LOCK;
+    wide_handler->stage_executor= this;
   }
-  wide_handler->stage = SPD_HND_STAGE_EXTERNAL_LOCK;
-  wide_handler->stage_executor = this;
-  info_auto_called = FALSE;
 
+  info_auto_called = FALSE;
+  wide_handler->external_lock_type= lock_type;
   wide_handler->sql_command = thd_sql_command(thd);
+
+  /* We treat BEGIN as if UNLOCK TABLE. */
   if (wide_handler->sql_command == SQLCOM_BEGIN)
+  {
     wide_handler->sql_command = SQLCOM_UNLOCK_TABLES;
+  }
+  if (lock_type == F_UNLCK &&
+      wide_handler->sql_command != SQLCOM_UNLOCK_TABLES)
+  {
+    DBUG_RETURN(0);
+  }
 
   trx = spider_get_trx(thd, TRUE, &error_num);
   if (error_num)
+  {
     DBUG_RETURN(error_num);
+  }
   wide_handler->trx = trx;
 
-  DBUG_PRINT("info",("spider sql_command=%d", wide_handler->sql_command));
-  if (
-    lock_type == F_UNLCK &&
-    wide_handler->sql_command != SQLCOM_UNLOCK_TABLES
-  )
-    DBUG_RETURN(0);
+  /* Question: Why the following if block is necessary? Why here? */
   if (store_error_num)
+  {
     DBUG_RETURN(store_error_num);
-  wide_handler->external_lock_type = lock_type;
-    if (
-      /* SQLCOM_RENAME_TABLE and SQLCOM_DROP_DB don't come here */
-      wide_handler->sql_command == SQLCOM_DROP_TABLE ||
-      wide_handler->sql_command == SQLCOM_ALTER_TABLE
-    ) {
-      if (trx->locked_connections)
-      {
-        my_message(ER_SPIDER_ALTER_BEFORE_UNLOCK_NUM,
-          ER_SPIDER_ALTER_BEFORE_UNLOCK_STR, MYF(0));
-        DBUG_RETURN(ER_SPIDER_ALTER_BEFORE_UNLOCK_NUM);
-      }
-      DBUG_RETURN(0);
+  }
+
+  DBUG_ASSERT(wide_handler->sql_command != SQLCOM_RENAME_TABLE &&
+              wide_handler->sql_command != SQLCOM_DROP_DB);
+
+  if (wide_handler->sql_command == SQLCOM_DROP_TABLE ||
+      wide_handler->sql_command == SQLCOM_ALTER_TABLE)
+  {
+    if (trx->locked_connections)
+    {
+      my_message(ER_SPIDER_ALTER_BEFORE_UNLOCK_NUM,
+                 ER_SPIDER_ALTER_BEFORE_UNLOCK_STR, MYF(0));
+      DBUG_RETURN(ER_SPIDER_ALTER_BEFORE_UNLOCK_NUM);
     }
-    if (unlikely((error_num = spider_internal_start_trx(this))))
+    DBUG_RETURN(0);
+  }
+
+  if (lock_type != F_UNLCK)
+  {
+    if (unlikely((error_num= spider_internal_start_trx(this))))
     {
       DBUG_RETURN(error_num);
     }
+    if (wide_handler->sql_command != SQLCOM_SELECT &&
+        wide_handler->sql_command != SQLCOM_HA_READ)
+    {
+      trx->updated_in_this_trx= TRUE;
+      DBUG_PRINT("info", ("spider trx->updated_in_this_trx=TRUE"));
+    }
+  }
 
   if (wide_handler->lock_table_type > 0 ||
     wide_handler->sql_command == SQLCOM_UNLOCK_TABLES)
@@ -913,9 +942,8 @@ int ha_spider::external_lock(
     /* lock/unlock tables */
     if (partition_handler && partition_handler->handlers)
     {
-      uint roop_count;
-      for (roop_count = 0; roop_count < partition_handler->no_parts;
-        ++roop_count)
+      for (uint roop_count= 0; roop_count < partition_handler->no_parts;
+           ++roop_count)
       {
         if (unlikely((error_num =
           partition_handler->handlers[roop_count]->lock_tables())))
@@ -923,41 +951,13 @@ int ha_spider::external_lock(
           DBUG_RETURN(error_num);
         }
       }
-    } else {
-      if (unlikely((error_num = lock_tables())))
-      {
-        DBUG_RETURN(error_num);
-      }
     }
-  }
-
-  DBUG_PRINT("info",("spider trx_start=%s",
-    trx->trx_start ? "TRUE" : "FALSE"));
-  /* need to check after spider_internal_start_trx() */
-  if (trx->trx_start)
-  {
-    switch (wide_handler->sql_command)
+    else if (unlikely((error_num= lock_tables())))
     {
-      case SQLCOM_SELECT:
-      case SQLCOM_HA_READ:
-        /* nothing to do */
-        break;
-      case SQLCOM_UPDATE:
-      case SQLCOM_UPDATE_MULTI:
-      case SQLCOM_CREATE_TABLE:
-      case SQLCOM_INSERT:
-      case SQLCOM_INSERT_SELECT:
-      case SQLCOM_DELETE:
-      case SQLCOM_LOAD:
-      case SQLCOM_REPLACE:
-      case SQLCOM_REPLACE_SELECT:
-      case SQLCOM_DELETE_MULTI:
-      default:
-        trx->updated_in_this_trx = TRUE;
-        DBUG_PRINT("info",("spider trx->updated_in_this_trx=TRUE"));
-        break;
+      DBUG_RETURN(error_num);
     }
   }
+
   DBUG_RETURN(0);
 }
 
diff --git a/storage/spider/mysql-test/spider/bugfix/r/mdev_27239.result b/storage/spider/mysql-test/spider/bugfix/r/mdev_27239.result
new file mode 100644
index 00000000000..de135972a22
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_27239.result
@@ -0,0 +1,20 @@
+#
+# MDEV-27239 Spider: Assertion `thd->transaction->stmt.ha_list == __null || trans == &thd->transaction->stmt' failed in ha_commit_trans on BEGIN WORK after FTWRL
+#
+for master_1
+for child2
+for child3
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+CREATE TABLE tbl_a (a INT) ENGINE=SPIDER;
+FLUSH TABLE tbl_a WITH READ LOCK;
+Warnings:
+Error	1429	Unable to connect to foreign data source: localhost
+Error	1429	Unable to connect to foreign data source: localhost
+Error	1429	Unable to connect to foreign data source: localhost
+Error	1429	Unable to connect to foreign data source: localhost
+BEGIN;
+DROP DATABASE auto_test_local;
+for master_1
+for child2
+for child3
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27239.cnf b/storage/spider/mysql-test/spider/bugfix/t/mdev_27239.cnf
new file mode 100644
index 00000000000..b0853e32654
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27239.cnf
@@ -0,0 +1,2 @@
+!include include/default_mysqld.cnf
+!include ../my_1_1.cnf
diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_27239.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_27239.test
new file mode 100644
index 00000000000..3cf4bebd369
--- /dev/null
+++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_27239.test
@@ -0,0 +1,24 @@
+--echo #
+--echo # MDEV-27239 Spider: Assertion `thd->transaction->stmt.ha_list == __null || trans == &thd->transaction->stmt' failed in ha_commit_trans on BEGIN WORK after FTWRL
+--echo #
+
+--disable_query_log
+--disable_result_log
+--source ../../t/test_init.inc
+--enable_result_log
+--enable_query_log
+
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+
+CREATE TABLE tbl_a (a INT) ENGINE=SPIDER;
+FLUSH TABLE tbl_a WITH READ LOCK;
+BEGIN;
+
+DROP DATABASE auto_test_local;
+
+--disable_query_log
+--disable_result_log
+--source ../../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
diff --git a/storage/spider/mysql-test/spider/r/variable_deprecation.result b/storage/spider/mysql-test/spider/r/variable_deprecation.result
index b9c7fb17839..6d715d95b9f 100644
--- a/storage/spider/mysql-test/spider/r/variable_deprecation.result
+++ b/storage/spider/mysql-test/spider/r/variable_deprecation.result
@@ -26,6 +26,8 @@ Warning	1287	The table parameter 'uhd' is deprecated and will be removed in a fu
 CREATE TABLE tbl_b (a INT) ENGINE=Spider COMMENT='use_handler "3"';
 Warnings:
 Warning	1287	The table parameter 'use_handler' is deprecated and will be removed in a future release
+DROP TABLE tbl_a;
+DROP TABLE tbl_b;
 # MDEV-28005 Deprecate Spider plugin variables regarding UDFs
 SET GLOBAL spider_udf_ds_bulk_insert_rows = 1;
 Warnings:
@@ -57,6 +59,21 @@ Warning	1287	'@@spider_udf_ct_bulk_insert_rows' is deprecated and will be remove
 SHOW VARIABLES LIKE "spider_udf_ct_bulk_insert_rows";
 Variable_name	Value
 spider_udf_ct_bulk_insert_rows	1
+# MDEV-27981 Deprecate spider_internal_limit 
+SET spider_internal_limit = 1;
+Warnings:
+Warning	1287	'@@spider_internal_limit' is deprecated and will be removed in a future release
+SHOW VARIABLES LIKE "spider_internal_limit";
+Variable_name	Value
+spider_internal_limit	9223372032559808513
+CREATE TABLE tbl_a (a INT) ENGINE=Spider COMMENT='ilm "1"';
+Warnings:
+Warning	1287	The table parameter 'ilm' is deprecated and will be removed in a future release
+CREATE TABLE tbl_b (a INT) ENGINE=Spider COMMENT='internal_limit "1"';
+Warnings:
+Warning	1287	The table parameter 'internal_limit' is deprecated and will be removed in a future release
+DROP TABLE tbl_a;
+DROP TABLE tbl_b;
 DROP DATABASE auto_test_local;
 for master_1
 for child2
diff --git a/storage/spider/mysql-test/spider/t/variable_deprecation.test b/storage/spider/mysql-test/spider/t/variable_deprecation.test
index 23519be146e..8521f5595d1 100644
--- a/storage/spider/mysql-test/spider/t/variable_deprecation.test
+++ b/storage/spider/mysql-test/spider/t/variable_deprecation.test
@@ -18,6 +18,9 @@ SHOW VARIABLES LIKE "spider_use_handler";
 eval CREATE TABLE tbl_a (a INT) $MASTER_1_ENGINE COMMENT='uhd "3"';
 eval CREATE TABLE tbl_b (a INT) $MASTER_1_ENGINE COMMENT='use_handler "3"';
 
+DROP TABLE tbl_a;
+DROP TABLE tbl_b;
+
 --echo # MDEV-28005 Deprecate Spider plugin variables regarding UDFs
 SET GLOBAL spider_udf_ds_bulk_insert_rows = 1;
 SHOW VARIABLES LIKE "spider_udf_ds_bulk_insert_rows";
@@ -34,6 +37,15 @@ SHOW VARIABLES LIKE "spider_udf_ct_bulk_insert_interval";
 SET GLOBAL spider_udf_ct_bulk_insert_rows = 1;
 SHOW VARIABLES LIKE "spider_udf_ct_bulk_insert_rows";
 
+--echo # MDEV-27981 Deprecate spider_internal_limit 
+SET spider_internal_limit = 1;
+SHOW VARIABLES LIKE "spider_internal_limit";
+eval CREATE TABLE tbl_a (a INT) $MASTER_1_ENGINE COMMENT='ilm "1"';
+eval CREATE TABLE tbl_b (a INT) $MASTER_1_ENGINE COMMENT='internal_limit "1"';
+
+DROP TABLE tbl_a;
+DROP TABLE tbl_b;
+
 DROP DATABASE auto_test_local;
 
 --disable_query_log
diff --git a/storage/spider/spd_param.cc b/storage/spider/spd_param.cc
index d2307a02b25..f46a8ddef60 100644
--- a/storage/spider/spd_param.cc
+++ b/storage/spider/spd_param.cc
@@ -458,7 +458,7 @@ longlong spider_param_internal_offset(
  */
 static MYSQL_THDVAR_LONGLONG(
   internal_limit, /* name */
-  PLUGIN_VAR_RQCMDARG, /* opt */
+  PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_DEPRECATED, /* opt */
   "Internal limit", /* comment */
   NULL, /* check */
   spider_use_table_value_deprecated, /* update */
diff --git a/storage/spider/spd_table.cc b/storage/spider/spd_table.cc
index 9f7e054355e..e3bfe4a81a8 100644
--- a/storage/spider/spd_table.cc
+++ b/storage/spider/spd_table.cc
@@ -2257,6 +2257,7 @@ int spider_parse_connect_info(
           SPIDER_PARAM_LONGLONG("frd", first_read, 0);
           SPIDER_PARAM_INT("isa", init_sql_alloc_size, 0);
           SPIDER_PARAM_INT_WITH_MAX("idl", internal_delayed, 0, 1);
+          SPIDER_PARAM_DEPRECATED_WARNING("ilm");
           SPIDER_PARAM_LONGLONG("ilm", internal_limit, 0);
           SPIDER_PARAM_LONGLONG("ios", internal_offset, 0);
           SPIDER_PARAM_INT_WITH_MAX("iom", internal_optimize, 0, 1);
@@ -2422,6 +2423,7 @@ int spider_parse_connect_info(
           error_num = connect_string_parse.print_param_error();
           goto error;
         case 14:
+          SPIDER_PARAM_DEPRECATED_WARNING("internal_limit");
           SPIDER_PARAM_LONGLONG("internal_limit", internal_limit, 0);
           SPIDER_PARAM_LONGLONG("bgs_first_read", bgs_first_read, 0);
           SPIDER_PARAM_INT_WITH_MAX("read_only_mode", read_only_mode, 0, 1);