A set of changes aiming to make the Event Scheduler more user-friendly

when there are no up-to-date system tables to support it:
 - initialize the scheduler before reporting "Ready for connections".
   This ensures that warnings, if any, are printed before "Ready for
   connections", and this message is not mangled.
 - do not abort the scheduler if there are no system tables
 - check the tables once at start up, remember the status and disable
   the scheduler if the tables are not up to date.
   If one attempts to use the scheduler with bad tables,
   issue an error message.
 - clean up the behaviour of the module under LOCK TABLES and pre-locking
   mode
 - make sure implicit commit of Events DDL works as expected.
 - add more tests


Collateral clean ups in the events code.

This patch fixes Bug#23631 Events: SHOW VARIABLES doesn't work 
when mysql.event is damaged


mysql-test/r/events.result:
  Update results.
mysql-test/r/events_bugs.result:
  Update results.
mysql-test/r/events_restart_phase1.result:
  Update results.
mysql-test/r/events_restart_phase2.result:
  Update results.
mysql-test/r/events_restart_phase3.result:
  Update results.
mysql-test/r/events_scheduling.result:
  Update results.
mysql-test/r/events_time_zone.result:
  Update results.
mysql-test/t/events.test:
  Add new tests for tampering with mysql.event and some more
  tests for sub-statements, LOCK TABLES mode and pre-locking.
mysql-test/t/events_bugs.test:
  Move the non-concurrent part of test for Bug 16420 to this file.
mysql-test/t/events_restart_phase1.test:
  Rewrite events_restart_* tests to take into account that now
  we check mysql.event table only once, at server startup.
mysql-test/t/events_restart_phase2.test:
  Rewrite events_restart_* tests to take into account that now
  we check mysql.event table only once, at server startup.
mysql-test/t/events_restart_phase3.test:
  Rewrite events_restart_* tests to take into account that now
  we check mysql.event table only once, at server startup.
mysql-test/t/events_scheduling.test:
  Add more coverage for event_scheduler global variable.
mysql-test/t/events_time_zone.test:
  Move the non-concurrent part of the tests for Bug 16420 to 
  events_bugs.test
sql/event_data_objects.cc:
  Move update_timing_fields functionality to Event_db_repository.
  Make loading of events from a table record more robust to tampering
  with the table - now we do not check mysql.event on every table open.
sql/event_data_objects.h:
  Cleanup.
sql/event_db_repository.cc:
  Now Event_db_repository is responsible for table I/O only.
  All the logic of events DDL is handled outside, in Events class please
  refer to the added test coverage to see how this change affected
  the behavior of Event Scheduler.
  Dependency on sp_head.h and sp.h removed.
  Make this module robust to tweaks with mysql.event table.
  Move check_system_tables from events.cc to this file
sql/event_db_repository.h:
  Cleanup declarations (remove unused ones, change return type to bool
  from int).
sql/event_queue.cc:
  Update to adapt to the new start up scheme of the Event Scheduler.
sql/event_queue.h:
  Cleanup declarations.
sql/event_scheduler.cc:
  Make all the error messages uniform:
  [SEVERITY] Event Scheduler: [user][schema.event] message
  Using append_identifier for error logging was an overkill - we may 
  need it only if the system character set may have NUL (null character)
  as part of a valid identifier, this is currently never the case, 
  whereas additional quoting did not look nice in the log.
sql/event_scheduler.h:
  Cleanup the headers.
sql/events.cc:
  Use a different start up procedure of Event Scheduler:
  - at start up, try to check the system tables first.
  If they are not up-to-date, disable the scheduler.
  - try to load all the active events. In case of a load error, abort
  start up.
  - do not parse an event on start up. Parsing only gives some information
  about event validity, but far not all.
  Consolidate the business logic of Events DDL in this module.
  Now opt_event_scheduler may change after start up and thus is protected
  by LOCK_event_metadata mutex.
sql/events.h:
  Use all-static-data-members approach to implement Singleton pattern.
sql/mysqld.cc:
  New invocation scheme of Events. Move some logic to events.cc.
  Initialize the scheduler before reporting "Ready for connections".
sql/set_var.cc:
  Clean up sys_var_thd_sql_mode::symbolic_mode_representation
  to work with a LEX_STRING.
  Move more logic related to @@events_scheduler global variable to Events
  module.
sql/set_var.h:
  Update declarations.
sql/share/errmsg.txt:
  If someone tampered with mysql.event table after the server has
  started we no longer give him/her a complete report what was actually 
  broken. Do not send the user to look at the error log in such case,
  as there is nothing there (check_table_intact is not executed).
sql/sp_head.cc:
  Update to a new declaration of 
  sys_var_thd_sql_mode::symbolic_mode_representation
sql/sql_db.cc:
  New invocation scheme of Events module.
sql/sql_parse.cc:
  Move more logic to Events module. Make sure that we are consistent
  in the way access rights are checked for Events DDL: always
  after committing the current transaction and checking the system tables.
sql/sql_show.cc:
  Update to the new declarations of 
  sys_var_thd_sql_mode::symbolic_mode_representation
sql/sql_test.cc:
  New invocation scheme of events.
sql/table.cc:
  mysql.event is a system table.
  Update check_table_intact to be concurrent, more verbose, and less smart.
sql/table.h:
  Add a helper method.
mysql-test/r/events_trans.result:
  New BitKeeper file ``mysql-test/r/events_trans.result''
mysql-test/t/events_trans.test:
  New BitKeeper file ``mysql-test/t/events_trans.test'':
  test cases for Event Scheduler that require a transactional
  storage engine.

1 files changed, 121 insertions, 124 deletions

diff --git a/sql/table.cc b/sql/table.cc
index 71e510bf0ac..5e901318919 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -250,7 +250,7 @@ void free_table_share(TABLE_SHARE *share)
   Currently these are:
 
   help_category, help_keyword, help_relation, help_topic,
-  proc,
+  proc, event
   time_zone, time_zone_leap_second, time_zone_name, time_zone_transition,
   time_zone_transition_type
 
@@ -283,7 +283,14 @@ inline bool is_system_table_name(const char *name, uint length)
            my_tolower(ci, name[0]) == 't' &&
            my_tolower(ci, name[1]) == 'i' &&
            my_tolower(ci, name[2]) == 'm' &&
-           my_tolower(ci, name[3]) == 'e'
+           my_tolower(ci, name[3]) == 'e' ||
+
+           /* mysql.event table */
+           my_tolower(ci, name[0]) == 'e' &&
+           my_tolower(ci, name[1]) == 'v' &&
+           my_tolower(ci, name[2]) == 'e' &&
+           my_tolower(ci, name[3]) == 'n' &&
+           my_tolower(ci, name[4]) == 't'
           )
          );
 }
@@ -2432,153 +2439,143 @@ bool check_column_name(const char *name)
 }
 
 
-/*
+/**
   Checks whether a table is intact. Should be done *just* after the table has
   been opened.
-  
-  SYNOPSIS
-    table_check_intact()
-      table             The table to check
-      table_f_count     Expected number of columns in the table
-      table_def         Expected structure of the table (column name and type)
-      last_create_time  The table->file->create_time of the table in memory
-                        we have checked last time
-      error_num         ER_XXXX from the error messages file. When 0 no error
-                        is sent to the client in case types does not match.
-                        If different col number either 
-                        ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE or 
-                        ER_COL_COUNT_DOESNT_MATCH_CORRUPTED is used
-
-  RETURNS
-    FALSE  OK
-    TRUE   There was an error
+
+  @param[in] table             The table to check
+  @param[in] table_f_count     Expected number of columns in the table
+  @param[in] table_def         Expected structure of the table (column name
+                               and type)
+
+  @retval  FALSE  OK
+  @retval  TRUE   There was an error. An error message is output
+                  to the error log.  We do not push an error
+                  message into the error stack because this
+                  function is currently only called at start up,
+                  and such errors never reach the user.
 */
 
 my_bool
 table_check_intact(TABLE *table, const uint table_f_count,
-                   const TABLE_FIELD_W_TYPE *table_def,
-                   time_t *last_create_time, int error_num)
+                   const TABLE_FIELD_W_TYPE *table_def)
 {
   uint i;
   my_bool error= FALSE;
   my_bool fields_diff_count;
   DBUG_ENTER("table_check_intact");
-  DBUG_PRINT("info",("table: %s  expected_count: %d  last_create_time: %ld",
-                     table->alias, table_f_count, *last_create_time));
-  
-  if ((fields_diff_count= (table->s->fields != table_f_count)) ||
-      (*last_create_time != table->file->stats.create_time))
+  DBUG_PRINT("info",("table: %s  expected_count: %d",
+                     table->alias, table_f_count));
+
+  fields_diff_count= (table->s->fields != table_f_count);
+  if (fields_diff_count)
   {
-    DBUG_PRINT("info", ("I am suspecting, checking table"));
-    if (fields_diff_count)
+    DBUG_PRINT("info", ("Column count has changed, checking the definition"));
+
+    /* previous MySQL version */
+    if (MYSQL_VERSION_ID > table->s->mysql_version)
     {
-      /* previous MySQL version */
-      error= TRUE;
-      if (MYSQL_VERSION_ID > table->s->mysql_version)
-      {
-        my_error(ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE, MYF(0), table->alias,
-                 table_f_count, table->s->fields, table->s->mysql_version,
-                 MYSQL_VERSION_ID);
-        sql_print_error(ER(ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE),
-                        table->alias, table_f_count, table->s->fields,
-                        table->s->mysql_version, MYSQL_VERSION_ID);
-        DBUG_RETURN(error);
+      sql_print_error(ER(ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE),
+                      table->alias, table_f_count, table->s->fields,
+                      table->s->mysql_version, MYSQL_VERSION_ID);
+      DBUG_RETURN(TRUE);
+    }
+    else if (MYSQL_VERSION_ID == table->s->mysql_version)
+    {
+      sql_print_error(ER(ER_COL_COUNT_DOESNT_MATCH_CORRUPTED), table->alias,
+                      table_f_count, table->s->fields);
+      DBUG_RETURN(TRUE);
+    }
+    /*
+      Something has definitely changed, but we're running an older
+      version of MySQL with new system tables.
+      Let's check column definitions. If a column was added at
+      the end of the table, then we don't care much since such change
+      is backward compatible.
+    */
+  }
+  char buffer[STRING_BUFFER_USUAL_SIZE];
+  for (i=0 ; i < table_f_count; i++, table_def++)
+  {
+    String sql_type(buffer, sizeof(buffer), system_charset_info);
+    sql_type.length(0);
+    if (i < table->s->fields)
+    {
+      Field *field= table->field[i];
 
-      }
-      else if (MYSQL_VERSION_ID == table->s->mysql_version)
-      {
-        my_error(ER_COL_COUNT_DOESNT_MATCH_CORRUPTED,MYF(0), table->alias,
-                 table_f_count, table->s->fields);
-        sql_print_error(ER(ER_COL_COUNT_DOESNT_MATCH_CORRUPTED), table->alias,
-                        table_f_count, table->s->fields);
-      }
-      else
+      if (strncmp(field->field_name, table_def->name.str,
+                  table_def->name.length))
       {
         /*
-          Moving from newer mysql to older one -> let's say not an error but
-          will check the definition afterwards. If a column was added at the
-          end then we don't care much since it's not in the middle.
+          Name changes are not fatal, we use ordinal numbers to access columns.
+          Still this can be a sign of a tampered table, output an error
+          to the error log.
         */
-        error= FALSE;
+        sql_print_error("Incorrect definition of table %s.%s: "
+                        "expected column '%s' at position %d, found '%s'.",
+                        table->s->db.str, table->alias, table_def->name.str, i,
+                        field->field_name);
       }
-    }
-    /* definitely something has changed */
-    char buffer[255];
-    for (i=0 ; i < table_f_count; i++, table_def++)
-    {      
-      String sql_type(buffer, sizeof(buffer), system_charset_info);
-      sql_type.length(0);
+      field->sql_type(sql_type);
       /*
-        Name changes are not fatal, we use sequence numbers => no problem
-        for us but this can show tampered table or broken table.
-      */
-      if (i < table->s->fields)
+        Generally, if column types don't match, then something is
+        wrong.
+
+        However, we only compare column definitions up to the
+        length of the original definition, since we consider the
+        following definitions compatible:
+
+        1. DATETIME and DATETIM
+        2. INT(11) and INT(11
+        3. SET('one', 'two') and SET('one', 'two', 'more')
+
+        For SETs or ENUMs, if the same prefix is there it's OK to
+        add more elements - they will get higher ordinal numbers and
+        the new table definition is backward compatible with the
+        original one.
+       */
+      if (strncmp(sql_type.c_ptr_safe(), table_def->type.str,
+                  table_def->type.length - 1))
       {
-        Field *field= table->field[i];
-        if (strncmp(field->field_name, table_def->name.str,
-                                       table_def->name.length))
-        {
-          sql_print_error("(%s) Expected field %s at position %d, found %s",
-                          table->alias, table_def->name.str, i,
-                          field->field_name);
-        }
-                        
-        /*
-          If the type does not match than something is really wrong
-          Check up to length - 1. Why?
-          1. datetime -> datetim -> the same
-          2. int(11) -> int(11  -> the same
-          3. set('one','two') -> set('one','two'  
-             so for sets if the same prefix is there it's ok if more are
-             added as part of the set. The same is valid for enum. So a new
-             table running on a old server will be valid.
-        */ 
-        field->sql_type(sql_type);
-        if (strncmp(sql_type.c_ptr_safe(), table_def->type.str,
-                    table_def->type.length - 1))
-        {
-          sql_print_error("(%s) Expected field %s at position %d to have type "
-                          "%s, found %s", table->alias, table_def->name.str,
-                          i, table_def->type.str, sql_type.c_ptr_safe()); 
-          error= TRUE;
-        }
-        else if (table_def->cset.str && !field->has_charset())
-        {
-          sql_print_error("(%s) Expected field %s at position %d to have "
-                          "character set '%s' but found no such", table->alias,
-                          table_def->name.str, i, table_def->cset.str);        
-          error= TRUE;
-        }
-        else if (table_def->cset.str && 
-                 strcmp(field->charset()->csname, table_def->cset.str))
-        {
-          sql_print_error("(%s) Expected field %s at position %d to have "
-                          "character set '%s' but found '%s'", table->alias,
-                          table_def->name.str, i, table_def->cset.str,
-                          field->charset()->csname);
-          error= TRUE;
-        }
+        sql_print_error("Incorrect definition of table %s.%s: "
+                        "expected column '%s' at position %d to have type "
+                        "%s, found type %s.", table->s->db.str, table->alias,
+                        table_def->name.str, i, table_def->type.str,
+                        sql_type.c_ptr_safe());
+        error= TRUE;
       }
-      else
+      else if (table_def->cset.str && !field->has_charset())
+      {
+        sql_print_error("Incorrect definition of table %s.%s: "
+                        "expected the type of column '%s' at position %d "
+                        "to have character set '%s' but the type has no "
+                        "character set.", table->s->db.str, table->alias,
+                        table_def->name.str, i, table_def->cset.str);
+        error= TRUE;
+      }
+      else if (table_def->cset.str &&
+               strcmp(field->charset()->csname, table_def->cset.str))
       {
-        sql_print_error("(%s) Expected field %s at position %d to have type %s "
-                        " but no field found.", table->alias,
-                        table_def->name.str, i, table_def->type.str);
-        error= TRUE;        
+        sql_print_error("Incorrect definition of table %s.%s: "
+                        "expected the type of column '%s' at position %d "
+                        "to have character set '%s' but found "
+                        "character set '%s'.", table->s->db.str, table->alias,
+                        table_def->name.str, i, table_def->cset.str,
+                        field->charset()->csname);
+        error= TRUE;
       }
     }
-    if (!error)
-      *last_create_time= table->file->stats.create_time;
-    else if (!fields_diff_count && error_num)
-      my_error(error_num,MYF(0), table->alias, table_f_count, table->s->fields);
-  }
-  else
-  {
-    DBUG_PRINT("info", ("Table seems ok without thorough checking."));
-    *last_create_time= table->file->stats.create_time;
+    else
+    {
+      sql_print_error("Incorrect definition of table %s.%s: "
+                      "expected column '%s' at position %d to have type %s "
+                      " but the column is not found.",
+                      table->s->db.str, table->alias,
+                      table_def->name.str, i, table_def->type.str);
+      error= TRUE;
+    }
   }
-   
-  DBUG_RETURN(error);  
+  DBUG_RETURN(error);
 }