Fixed bug#11753738 (formely known as bug#45235) - 5.1 DOES NOT SUPPORT 5.0-ONLY

SYNTAX TRIGGERS IN ANY WAY

Table with triggers which were using deprecated (5.0-only) syntax became
unavailable for any DML and DDL after upgrade to 5.1 version of server.
Attempt to execute any statement on such a table resulted in parsing
error reported. Since this included DROP TRIGGER and DROP TABLE
statements (actually, the latter was allowed but was not functioning
properly for such tables) it was impossible to fix the problem without
manual operations on .TRG and .TRN files in data directory.

The problem was that failure to parse trigger body (due to 5.0-only
syntax) when opening trigger file for a table prevented the table
from being open. This made all operations on the table impossible
(except DROP TABLE which due to peculiarity in its implementation
dropped the table but left trigger files around).

This patch solves this problem by silencing error which occurs when
we parse trigger body during table open. Error message is preserved
for the future use and table is marked as having a broken trigger.
We also try to analyze parse tree to recover trigger name, which
will be needed in order to drop the broken trigger. DML statements
which invoke triggers on the table marked as having broken trigger
are prohibited and emit saved error message. The same happens for
DDL which change triggers except DROP TRIGGER and DROP TABLE which
try their best to do what was requested. Table becomes no longer
marked as having broken trigger when last such trigger is dropped.

1 files changed, 136 insertions, 12 deletions

diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 9348feaa22a..89ee411c8da 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -19,6 +19,7 @@
 #include "sp_head.h"
 #include "sql_trigger.h"
 #include "parse_file.h"
+#include <mysys_err.h>
 
 /*************************************************************************/
 
@@ -293,6 +294,52 @@ private:
 
 
 /**
+  An error handler that catches all non-OOM errors which can occur during
+  parsing of trigger body. Such errors are ignored and corresponding error
+  message is used to construct a more verbose error message which contains
+  name of problematic trigger. This error message is later emitted when
+  one tries to perform DML or some of DDL on this table.
+  Also, if possible, grabs name of the trigger being parsed so it can be
+  used to correctly drop problematic trigger.
+*/
+class Deprecated_trigger_syntax_handler : public Internal_error_handler 
+{
+private:
+
+  char m_message[MYSQL_ERRMSG_SIZE];
+  LEX_STRING *m_trigger_name;
+
+public:
+
+  Deprecated_trigger_syntax_handler() : m_trigger_name(NULL) {}
+
+  virtual bool handle_error(uint sql_errno, const char *message,
+                            MYSQL_ERROR::enum_warning_level level, THD *thd)
+  {
+    if (sql_errno != EE_OUTOFMEMORY &&
+        sql_errno != ER_OUT_OF_RESOURCES)
+    {
+      if(thd->lex->spname)
+        m_trigger_name= &thd->lex->spname->m_name;
+      if (m_trigger_name)
+        my_snprintf(m_message, sizeof(m_message),
+                    "Trigger '%s' has an error in its body: '%s'",
+                    m_trigger_name->str, message);
+      else
+        my_snprintf(m_message, sizeof(m_message),
+                    "Unknown trigger has an error in its body: '%s'",
+                    message);
+      return true;
+    }
+    return false;
+  }
+
+  LEX_STRING *get_trigger_name() { return m_trigger_name; }
+  char *get_error_message() { return m_message; }
+};
+
+
+/**
   Create or drop trigger for table.
 
   @param thd     current thread context (including trigger definition in LEX)
@@ -575,6 +622,8 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables,
   LEX_STRING *trg_connection_cl_name;
   LEX_STRING *trg_db_cl_name;
 
+  if (check_for_broken_triggers())
+    return true;
 
   /* Trigger must be in the same schema as target table. */
   if (my_strcasecmp(table_alias_charset, table->s->db.str,
@@ -848,7 +897,7 @@ static bool rm_trigger_file(char *path, const char *db,
   @param path         char buffer of size FN_REFLEN to be used
                       for constructing path to .TRN file.
   @param db           trigger's database name
-  @param table_name   trigger's name
+  @param trigger_name trigger's name
 
   @retval
     False   success
@@ -1312,12 +1361,11 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
         lex_start(thd);
         thd->spcont= NULL;
 
-        if (parse_sql(thd, & parser_state, creation_ctx))
-        {
-          /* Currently sphead is always deleted in case of a parse error */
-          DBUG_ASSERT(lex.sphead == 0);
-          goto err_with_lex_cleanup;
-        }
+        Deprecated_trigger_syntax_handler error_handler;
+        thd->push_internal_handler(&error_handler);
+        bool parse_error= parse_sql(thd, & parser_state, creation_ctx);
+        thd->pop_internal_handler();
+
         /*
           Not strictly necessary to invoke this method here, since we know
           that we've parsed CREATE TRIGGER and not an
@@ -1328,6 +1376,52 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
         */
         lex.set_trg_event_type_for_tables();
 
+        if (parse_error)
+        {
+          if (!triggers->m_has_unparseable_trigger)
+            triggers->set_parse_error_message(error_handler.get_error_message());
+          /* Currently sphead is always set to NULL in case of a parse error */
+          DBUG_ASSERT(lex.sphead == 0);
+          if (error_handler.get_trigger_name())
+          {
+            LEX_STRING *trigger_name;
+            const LEX_STRING *orig_trigger_name= error_handler.get_trigger_name();
+
+            if (!(trigger_name= alloc_lex_string(&table->mem_root)) ||
+                !(trigger_name->str= strmake_root(&table->mem_root,
+                                                  orig_trigger_name->str,
+                                                  orig_trigger_name->length)))
+              goto err_with_lex_cleanup;
+
+            trigger_name->length= orig_trigger_name->length;
+
+            if (triggers->names_list.push_back(trigger_name,
+                                               &table->mem_root))
+              goto err_with_lex_cleanup;
+          }
+          else
+          {
+            /* 
+               The Table_triggers_list is not constructed as a list of
+               trigger objects as one would expect, but rather of lists of
+               properties of equal length. Thus, even if we don't get the
+               trigger name, we still fill all in all the lists with
+               placeholders as we might otherwise create a skew in the
+               lists. Obviously, this has to be refactored.
+            */
+            LEX_STRING *empty= alloc_lex_string(&table->mem_root);
+            if (!empty)
+              goto err_with_lex_cleanup;
+
+            empty->str= const_cast<char*>("");
+            empty->length= 0;
+            if (triggers->names_list.push_back(empty, &table->mem_root))
+              goto err_with_lex_cleanup;
+          }
+          lex_end(&lex);
+          continue;
+        }
+
         lex.sphead->set_info(0, 0, &lex.sp_chistics, (ulong) *trg_sql_mode);
 
         int event= lex.trg_chistics.event;
@@ -1368,8 +1462,8 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
 
         if (triggers->names_list.push_back(&lex.sphead->m_name,
                                            &table->mem_root))
-            goto err_with_lex_cleanup;
-
+          goto err_with_lex_cleanup;
+        
         if (!(on_table_name= alloc_lex_string(&table->mem_root)))
           goto err_with_lex_cleanup;
 
@@ -1394,9 +1488,8 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
         char fname[NAME_LEN + 1];
         DBUG_ASSERT((!my_strcasecmp(table_alias_charset, lex.query_tables->db, db) ||
                      (check_n_cut_mysql50_prefix(db, fname, sizeof(fname)) &&
-                      !my_strcasecmp(table_alias_charset, lex.query_tables->db, fname))) &&
-                    (!my_strcasecmp(table_alias_charset, lex.query_tables->table_name,
-                                    table_name) ||
+                      !my_strcasecmp(table_alias_charset, lex.query_tables->db, fname))));
+        DBUG_ASSERT((!my_strcasecmp(table_alias_charset, lex.query_tables->table_name, table_name) ||
                      (check_n_cut_mysql50_prefix(table_name, fname, sizeof(fname)) &&
                       !my_strcasecmp(table_alias_charset, lex.query_tables->table_name, fname))));
 #endif
@@ -1680,6 +1773,13 @@ bool Table_triggers_list::drop_all_triggers(THD *thd, char *db, char *name)
 
     while ((trigger= it_name++))
     {
+      /*
+        Trigger, which body we failed to parse during call
+        Table_triggers_list::check_n_load(), might be missing name.
+        Such triggers have zero-length name and are skipped here.
+      */
+      if (trigger->length == 0)
+        continue;
       if (rm_trigname_file(path, db, trigger->str))
       {
         /*
@@ -1903,6 +2003,11 @@ bool Table_triggers_list::change_table_name(THD *thd, const char *db,
   }
   if (table.triggers)
   {
+    if (table.triggers->check_for_broken_triggers())
+    {
+      result= 1;
+      goto end;
+    }
     LEX_STRING old_table_name= { (char *) old_table, strlen(old_table) };
     LEX_STRING new_table_name= { (char *) new_table, strlen(new_table) };
     /*
@@ -1991,6 +2096,9 @@ bool Table_triggers_list::process_triggers(THD *thd,
   sp_head *sp_trigger= bodies[event][time_type];
   SELECT_LEX *save_current_select;
 
+  if (check_for_broken_triggers())
+    return true;
+
   if (sp_trigger == NULL)
     return FALSE;
 
@@ -2070,6 +2178,22 @@ void Table_triggers_list::mark_fields_used(trg_event_type event)
 
 
 /**
+   Signals to the Table_triggers_list that a parse error has occured when
+   reading a trigger from file. This makes the Table_triggers_list enter an
+   error state flagged by m_has_unparseable_trigger == true. The error message
+   will be used whenever a statement invoking or manipulating triggers is
+   issued against the Table_triggers_list's table.
+
+   @param error_message The error message thrown by the parser.
+ */
+void Table_triggers_list::set_parse_error_message(char *error_message)
+{
+  m_has_unparseable_trigger= true;
+  strcpy(m_parse_error_message, error_message);
+}
+
+
+/**
   Trigger BUG#14090 compatibility hook.
 
   @param[in,out] unknown_key       reference on the line with unknown