MDEV-25302: Attempt to set some system variables in PS mode results in issuing the error "ERROR 1615 (HY000): Prepared statement needs to be re-prepared"bb-10.2-MDEV-25302

Setting of the system variables gtid_slave_pos and event_scheduler
perform in PS-mode can result in issuing the error
  ERROR 1615 (HY000): Prepared statement needs to be re-prepared
on executing prepared statement. The following test can be used
to demostrate the issue:

Case 1:
MariaDB [test]> PREPARE stmt1 FROM 'set global event_scheduler=1';
Query OK, 0 rows affected (0,000 sec)
Statement prepared

MariaDB [test]> EXECUTE stmt1;
ERROR 1615 (HY000): Prepared statement needs to be re-prepared

Case 2:
MariaDB [test]> PREPARE stmt1 FROM 'SET GLOBAL gtid_slave_pos= ""'
Query OK, 0 rows affected (0,000 sec)
Statement prepared

MariaDB [test]> EXECUTE stmt1;
ERROR 1615 (HY000): Prepared statement needs to be re-prepared

For case 1 MariaDB server must be started with option --skip-grant-tables

For both use cases the actual reason of issuing the error
  "ERROR 1615 (HY000): Prepared statement needs to be re-prepared"
on executing the prepared statement is that system tables have to opened
during handling of the 'SET' statement and a table version stored in TABLE_SHARE
for a system table being opened differs from a value stored in a TABLE_LIST.

Comparing of tables versions takes place in the function
check_and_update_table_version() and the error is reported by an instance of
the class Reprepare_observer in case it is set. The main goal of using
the class Reprepare_observer is to control changes in table's metadata.
On the other case, system tables being opened during setting of the system
variables like event_scheduler and gtid_slave_posv are not subject to metadata
version tracking. Therefore the data member thd->m_reprepare_observer can be
reset safely to NULL before setting these system variables and restored
on completion.

Additionally, the directives --disable_warnings/--enable_warnings have been
placed around the statement 'SET GLOBAL event_scheduler=1' for those tests
which contain the option '--skip-grant-tables' in their '.opt' files.
It's required in order to make execution of such tests to be repeatable.
Without these directives around the statement 'SET GLOBAL event_scheduler=1'
execution of this statement could result in missing warning
  Warnings:
  Note 1408 Event Scheduler: Loaded 0 events
that takes place when multiple tests with the option --skip-grant-tables
enabled is run sequntially and every test executes the statement
'SET GLOBAL event_scheduler=1'.

7 files changed, 128 insertions, 9 deletions

diff --git a/mysql-test/r/ps_mdev_25302.result b/mysql-test/r/ps_mdev_25302.result
new file mode 100644
index 00000000000..7a039e848af
--- /dev/null
+++ b/mysql-test/r/ps_mdev_25302.result
@@ -0,0 +1,23 @@
+#
+# MDEV-25302: Attempt to set some system variables in PS mode results in issuing the error "ERROR 1615 (HY000): Prepared statement needs to be re-prepared"
+#
+SET @old_event_scheduler=@@event_scheduler;
+PREPARE stmt FROM 'set global event_scheduler=1';
+EXECUTE stmt;
+SELECT @@event_scheduler;
+@@event_scheduler
+ON
+PREPARE stmt FROM 'set global event_scheduler=0';
+EXECUTE stmt;
+SELECT @@event_scheduler;
+@@event_scheduler
+OFF
+SET GLOBAL event_scheduler=@old_event_scheduler;
+SET @old_gtid_slave_pos=@@gtid_slave_pos;
+PREPARE stmt FROM 'SET GLOBAL gtid_slave_pos= ""';
+EXECUTE stmt;
+SELECT @@gtid_slave_pos;
+@@gtid_slave_pos
+
+SET GLOBAL gtid_slave_pos=@old_gtid_slave_pos;
+DEALLOCATE PREPARE stmt;
diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result
index b058c8f001e..7d70edc47d5 100644
--- a/mysql-test/r/skip_grants.result
+++ b/mysql-test/r/skip_grants.result
@@ -52,8 +52,6 @@ DROP FUNCTION f3;
 # Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server
 #
 set global event_scheduler=1;
-Warnings:
-Note	1408	Event Scheduler: Loaded 0 events
 set global event_scheduler=0;
 #
 # Bug#26285 Selecting information_schema crahes server
diff --git a/mysql-test/t/ps_mdev_25302.opt b/mysql-test/t/ps_mdev_25302.opt
new file mode 100644
index 00000000000..5699a3387b8
--- /dev/null
+++ b/mysql-test/t/ps_mdev_25302.opt
@@ -0,0 +1 @@
+--skip-grant-tables
diff --git a/mysql-test/t/ps_mdev_25302.test b/mysql-test/t/ps_mdev_25302.test
new file mode 100644
index 00000000000..4c188370498
--- /dev/null
+++ b/mysql-test/t/ps_mdev_25302.test
@@ -0,0 +1,26 @@
+--echo #
+--echo # MDEV-25302: Attempt to set some system variables in PS mode results in issuing the error "ERROR 1615 (HY000): Prepared statement needs to be re-prepared"
+--echo #
+
+SET @old_event_scheduler=@@event_scheduler;
+
+PREPARE stmt FROM 'set global event_scheduler=1';
+--disable_warnings
+EXECUTE stmt;
+--enable_warnings
+SELECT @@event_scheduler;
+PREPARE stmt FROM 'set global event_scheduler=0';
+EXECUTE stmt;
+SELECT @@event_scheduler;
+
+SET GLOBAL event_scheduler=@old_event_scheduler;
+
+SET @old_gtid_slave_pos=@@gtid_slave_pos;
+
+PREPARE stmt FROM 'SET GLOBAL gtid_slave_pos= ""';
+EXECUTE stmt;
+SELECT @@gtid_slave_pos;
+
+SET GLOBAL gtid_slave_pos=@old_gtid_slave_pos;
+DEALLOCATE PREPARE stmt;
+
diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test
index 0ecaa022fd4..497592cc80a 100644
--- a/mysql-test/t/skip_grants.test
+++ b/mysql-test/t/skip_grants.test
@@ -94,7 +94,9 @@ DROP FUNCTION f3;
 --echo #
 --echo # Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server
 --echo #
+--disable_warnings
 set global event_scheduler=1;
+--enable_warnings
 set global event_scheduler=0;
 
 --echo #
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index e4de3d8d0aa..b0e9188bb49 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -989,14 +989,69 @@ static bool event_scheduler_update(sys_var *self, THD *thd, enum_var_type type)
   return ret;
 }
 
-static Sys_var_enum Sys_event_scheduler(
-       "event_scheduler", "Enable the event scheduler. Possible values are "
+
+class Sys_var_event_scheduler : public Sys_var_enum
+{
+  Reprepare_observer *save_reprepare_observer;
+
+public:
+  Sys_var_event_scheduler(const char *name_arg,
+                          const char *comment, int flag_args, ptrdiff_t off,
+                          size_t size, CMD_LINE getopt)
+  : Sys_var_enum(name_arg, comment, flag_args, off, size, getopt,
+                 event_scheduler_names, DEFAULT(Events::EVENTS_OFF),
+                 NO_MUTEX_GUARD, VARIABLE_NOT_IN_BINLOG,
+                 NULL, NULL, NULL), save_reprepare_observer(NULL)
+  {
+  }
+
+  bool do_check(THD *thd, set_var *var)
+  {
+    bool st=
+      Sys_var_enum::do_check(thd, var) ||
+      event_scheduler_check(this, thd, var);
+
+    if (!st)
+    {
+      /*
+        Clear the member THD::m_reprepare_observer to avoid spurious
+        ER_NEED_REPREPARE errors that could happened on opening event-related
+        system tables (these tables are not subject to metadata version
+        tracking). The member THD::m_reprepare_observer is restored to its
+        original value on return from the method
+        Sys_var_event_scheduler::global_update().
+      */
+      save_reprepare_observer= thd->m_reprepare_observer;
+      thd->m_reprepare_observer= NULL;
+    }
+
+    return st;
+  }
+
+  bool session_update(THD *thd, set_var *var)
+  {
+    DBUG_ASSERT(false);
+    return true;
+  }
+
+  bool global_update(THD *thd, set_var *var)
+  {
+    bool ret=
+      Sys_var_enum::global_update(thd, var) ||
+      event_scheduler_update(this, thd, SHOW_OPT_GLOBAL);
+
+    thd->m_reprepare_observer= save_reprepare_observer;
+
+    return ret;
+  }
+};
+
+static Sys_var_event_scheduler Sys_event_scheduler("event_scheduler",
+       "Enable the event scheduler. Possible values are "
        "ON, OFF, and DISABLED (keep the event scheduler completely "
        "deactivated, it cannot be activated run-time)",
-       GLOBAL_VAR(Events::opt_event_scheduler), CMD_LINE(OPT_ARG),
-       event_scheduler_names, DEFAULT(Events::EVENTS_OFF),
-       NO_MUTEX_GUARD, NOT_IN_BINLOG,
-       ON_CHECK(event_scheduler_check), ON_UPDATE(event_scheduler_update));
+       GLOBAL_VAR(Events::opt_event_scheduler), CMD_LINE(OPT_ARG));
+
 #endif
 
 static Sys_var_ulong Sys_expire_logs_days(
@@ -1705,6 +1760,15 @@ Sys_var_gtid_slave_pos::do_check(THD *thd, set_var *var)
     return true;
   }
   var->save_result.string_value.length= res->length();
+  /*
+    Clear the member THD::m_reprepare_observer to avoid spurious
+    ER_NEED_REPREPARE errors that could happened on opening rpl-related system
+    tables (these tables are not subject to metadata version tracking).
+    The member THD::m_reprepare_observer is restored to its original value
+    on return from the method Sys_var_gtid_slave_pos::global_update().
+  */
+  save_reprepare_observer= thd->m_reprepare_observer;
+  thd->m_reprepare_observer= NULL;
   return false;
 }
 
@@ -1719,6 +1783,7 @@ Sys_var_gtid_slave_pos::global_update(THD *thd, set_var *var)
   if (!var->value)
   {
     my_error(ER_NO_DEFAULT, MYF(0), var->var->name.str);
+    thd->m_reprepare_observer= save_reprepare_observer;
     return true;
   }
 
@@ -1731,6 +1796,8 @@ Sys_var_gtid_slave_pos::global_update(THD *thd, set_var *var)
                              var->save_result.string_value.length);
   mysql_mutex_unlock(&LOCK_active_mi);
   mysql_mutex_lock(&LOCK_global_system_variables);
+  thd->m_reprepare_observer= save_reprepare_observer;
+
   return err;
 }
 
diff --git a/sql/sys_vars.ic b/sql/sys_vars.ic
index 3c3cfb8c8a6..b6981b8badb 100644
--- a/sql/sys_vars.ic
+++ b/sql/sys_vars.ic
@@ -2378,13 +2378,15 @@ public:
 */
 class Sys_var_gtid_slave_pos: public sys_var
 {
+  Reprepare_observer *save_reprepare_observer;
+
 public:
   Sys_var_gtid_slave_pos(const char *name_arg,
           const char *comment, int flag_args, ptrdiff_t off, size_t size,
           CMD_LINE getopt)
     : sys_var(&all_sys_vars, name_arg, comment, flag_args, off, getopt.id,
               getopt.arg_type, SHOW_CHAR, 0, NULL, VARIABLE_NOT_IN_BINLOG,
-              NULL, NULL, NULL)
+              NULL, NULL, NULL), save_reprepare_observer(NULL)
   {
     option.var_type|= GET_STR;
   }