[MDEV-29827] Clear error when --event-scheduler=ON is combined with --skip-grant-tables

When the server is started with `--event-scheduler=ON` and with
`--skip-grant-tables` (or built as embedded server which has no grant
tables at all), the event scheduler *appears* to be enabled (`SELECT
@@global.event_scheduler` returns `'ON'`), but attempting to
manipulate it in any way returns a misleading error message:

  "Cannot proceed, because event scheduler is disabled"

Possible solutions:

1. Fast-fail: fail immediately on startup if `EVENT_SCHEDULER` is set to
   any value other than `DISABLED` when starting up without grant
   tables, then prevent `SET GLOBAL event_scheduler` while running.

   Problem: there are existing setup scripts and code which start with
   this combination and assume it will not fail.

2. Warn and change value: if `EVENT_SCHEDULER` is set to any value
   other than `DISABLED` when starting, print a warning and change it
   immediately to `DISABLED`.

   Advantage: The value of the `EVENT_SCHEDULER` system variable after
   startup will be consistent with its functional unavailability.

3. Display a clear error: if `EVENT_SCHEDULER` is enabled, but grant
   tables are not enabled, then ensure error messages clearly explain
   the fact that the combination is not supported.

   Advantage: The error message encountered by the end user when
   attempting to manipulate the event scheduler (such as `CREATE
   EVENT`) is clear and explicit.

This commit implements the combination of solutions (2) and (3): it
will set `EVENT_SCHEDULER=DISABLED` on startup (reflecting the
functional reality) and it will print a startup warning, *and* it will
print a *distinct* error message each time that an end user attempts to
manipulate the event scheduler, so that the end user will clearly understand
the problem even if the startup messages are not visible at that point.

It also adds an MTR test `main.events_skip_grant_tables` to verify the
expected behavior, and the unmodified `main.events_restart` test
continues to demonstrate no change in the error message when the event
scheduler is non-functional for *different* reasons.

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

10 files changed, 53 insertions, 15 deletions

diff --git a/mysql-test/main/events_embedded.test b/mysql-test/main/events_embedded.test
index f6921f302bf..c607573b2dc 100644
--- a/mysql-test/main/events_embedded.test
+++ b/mysql-test/main/events_embedded.test
@@ -1,4 +1,4 @@
 --source include/is_embedded.inc
 
---error 1193
+--error ER_UNKNOWN_SYSTEM_VARIABLE
 set global event_scheduler=ON;
diff --git a/mysql-test/main/events_skip_grant_tables-master.opt b/mysql-test/main/events_skip_grant_tables-master.opt
new file mode 100644
index 00000000000..3cf617286a2
--- /dev/null
+++ b/mysql-test/main/events_skip_grant_tables-master.opt
@@ -0,0 +1 @@
+--event-scheduler=ON --skip-grant-tables
diff --git a/mysql-test/main/events_skip_grant_tables.result b/mysql-test/main/events_skip_grant_tables.result
new file mode 100644
index 00000000000..2bd2e383a23
--- /dev/null
+++ b/mysql-test/main/events_skip_grant_tables.result
@@ -0,0 +1,8 @@
+call mtr.add_suppression("Event Scheduler will not function when starting with --skip-grant-tables or --bootstrap.");
+CREATE EVENT test ON SCHEDULE AT CURRENT_TIMESTAMP DO DO NULL;
+ERROR HY000: Event scheduler cannot function with --skip-grant-tables, --bootstrap, or embedded build
+select (@@global.event_scheduler='DISABLED') as expect_1;
+expect_1
+1
+set global event_scheduler=1;
+ERROR HY000: The MariaDB server is running with the --event-scheduler=DISABLED or --skip-grant-tables option so it cannot execute this statement
diff --git a/mysql-test/main/events_skip_grant_tables.test b/mysql-test/main/events_skip_grant_tables.test
new file mode 100644
index 00000000000..b19178294b8
--- /dev/null
+++ b/mysql-test/main/events_skip_grant_tables.test
@@ -0,0 +1,18 @@
+# Can't test with embedded server that doesn't support grants
+-- source include/not_embedded.inc
+call mtr.add_suppression("Event Scheduler will not function when starting with --skip-grant-tables or --bootstrap.");
+
+# [MARIADB-29827] Verify that if server is started with
+# --event-scheduler=ON --skip-grant-tables, we get an error
+# with a distinct explanation that the latter disables the former.
+
+--error ER_EVENTS_NO_ACL
+CREATE EVENT test ON SCHEDULE AT CURRENT_TIMESTAMP DO DO NULL;
+
+# Although --event-scheduler=ON was specified (see -master.opt), it should
+# have been changed to 'DISABLED' at startup.
+select (@@global.event_scheduler='DISABLED') as expect_1;
+
+# Verify that we cannot (re)enable event scheduler
+--error ER_OPTION_PREVENTS_STATEMENT
+set global event_scheduler=1;
diff --git a/mysql-test/main/mysql_upgrade-6984.result b/mysql-test/main/mysql_upgrade-6984.result
index 301fdfc3bfd..dd266c2f208 100644
--- a/mysql-test/main/mysql_upgrade-6984.result
+++ b/mysql-test/main/mysql_upgrade-6984.result
@@ -167,5 +167,4 @@ OK
 connect con1,localhost,root,foo,,,;
 update mysql.global_priv set priv=json_compact(json_remove(priv, '$.plugin', '$.authentication_string'))  where user='root';
 flush privileges;
-set global event_scheduler=OFF;
 # restart
diff --git a/mysql-test/main/mysql_upgrade-6984.test b/mysql-test/main/mysql_upgrade-6984.test
index f46c122cf02..93888d556d4 100644
--- a/mysql-test/main/mysql_upgrade-6984.test
+++ b/mysql-test/main/mysql_upgrade-6984.test
@@ -21,8 +21,6 @@ connect(con1,localhost,root,foo,,,);
 
 update mysql.global_priv set priv=json_compact(json_remove(priv, '$.plugin', '$.authentication_string'))  where user='root';
 flush privileges;
-# Load event table
-set global event_scheduler=OFF;
 
 let MYSQLD_DATADIR= `select @@datadir`;
 --remove_file $MYSQLD_DATADIR/mariadb_upgrade_info
diff --git a/mysql-test/main/skip_grants.result b/mysql-test/main/skip_grants.result
index fdd7be41095..292358fad4c 100644
--- a/mysql-test/main/skip_grants.result
+++ b/mysql-test/main/skip_grants.result
@@ -1,3 +1,4 @@
+call mtr.add_suppression("Event Scheduler will not function when starting with --skip-grant-tables or --bootstrap.");
 use test;
 CREATE TABLE t1(c INT);
 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
@@ -52,7 +53,7 @@ DROP FUNCTION f3;
 # Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server
 #
 set global event_scheduler=1;
-set global event_scheduler=0;
+ERROR HY000: The MariaDB server is running with the --event-scheduler=DISABLED or --skip-grant-tables option so it cannot execute this statement
 #
 # Bug#26285 Selecting information_schema crahes server
 #
diff --git a/mysql-test/main/skip_grants.test b/mysql-test/main/skip_grants.test
index b74cd41b039..db2d24bc760 100644
--- a/mysql-test/main/skip_grants.test
+++ b/mysql-test/main/skip_grants.test
@@ -1,5 +1,7 @@
 # This tests not performed with embedded server
 -- source include/not_embedded.inc
+call mtr.add_suppression("Event Scheduler will not function when starting with --skip-grant-tables or --bootstrap.");
+
 -- disable_ps_protocol
 use test;
 
@@ -92,10 +94,8 @@ DROP FUNCTION f3;
 --echo #
 --echo # Bug #26807 "set global event_scheduler=1" and --skip-grant-tables crashes server
 --echo #
---disable_warnings
+--error ER_OPTION_PREVENTS_STATEMENT
 set global event_scheduler=1;
---enable_warnings
-set global event_scheduler=0;
 
 --echo #
 --echo # Bug#26285 Selecting information_schema crahes server
diff --git a/sql/events.cc b/sql/events.cc
index 01a11461655..45f99435348 100644
--- a/sql/events.cc
+++ b/sql/events.cc
@@ -119,7 +119,12 @@ bool Events::check_if_system_tables_error()
 {
   DBUG_ENTER("Events::check_if_system_tables_error");
 
-  if (!inited)
+  if (opt_noacl)
+  {
+    my_error(ER_EVENTS_NO_ACL, MYF(0));
+    DBUG_RETURN(TRUE);
+  }
+  else if (!inited)
   {
     my_error(ER_EVENTS_DB_ERROR, MYF(0));
     DBUG_RETURN(TRUE);
@@ -886,9 +891,17 @@ Events::init(THD *thd, bool opt_noacl_or_bootstrap)
   /*
     Was disabled explicitly from the command line
   */
-  if (opt_event_scheduler == Events::EVENTS_DISABLED ||
-      opt_noacl_or_bootstrap)
+  if (opt_event_scheduler == Events::EVENTS_DISABLED)
     DBUG_RETURN(FALSE);
+  else if (opt_noacl_or_bootstrap)
+  {
+    my_message(ER_STARTUP,
+               "Event Scheduler will not function when starting "
+               "with --skip-grant-tables or --bootstrap.",
+               MYF(ME_ERROR_LOG));
+    opt_event_scheduler= EVENTS_DISABLED;
+    DBUG_RETURN(FALSE);
+  }
 
   /* We need a temporary THD during boot */
   if (!thd)
@@ -934,10 +947,8 @@ Events::init(THD *thd, bool opt_noacl_or_bootstrap)
     Since we allow event DDL even if the scheduler is disabled,
     check the system tables, as we might need them.
 
-    If run with --skip-grant-tables or --bootstrap, don't try to do the
-    check of system tables and don't complain: in these modes the tables
-    are most likely not there and we're going to disable the event
-    scheduler anyway.
+    If run with --skip-grant-tables or --bootstrap, we have already
+    disabled the event scheduler anyway.
   */
   if (Event_db_repository::check_system_tables(thd))
   {
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 9cc20462371..8bd961ee140 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -10784,3 +10784,5 @@ ER_JSON_INVALID_VALUE_FOR_KEYWORD
         eng "Invalid value for keyword %s"
 ER_JSON_SCHEMA_KEYWORD_UNSUPPORTED
         eng "%s keyword is not supported"
+ER_EVENTS_NO_ACL
+        eng "Event scheduler cannot function with --skip-grant-tables, --bootstrap, or embedded build"