Save and clear run context before executing a stored function or trigger and restore it afterwards.

This allows us to use statement replication with functions and triggers
The following things are fixed with this patch:
- NOW() and automatic timestamps takes the value from the main event for functions and triggers (which allows these to replicate with statement level logging)
- No side effects for triggers or functions with auto-increment values(), last_insert_id(), rand() or found_rows()
- Triggers can't return result sets

Fixes bugs:
#12480: NOW() is not constant in a trigger
#12481: Using NOW() in a stored function breaks statement based replication
#12482: Triggers has side effects with auto_increment values
#11587: trigger causes lost connection error


mysql-test/r/trigger.result:
  Added test fpr big
mysql-test/t/sp-error.test:
  Changed error message numbers
mysql-test/t/trigger.test:
  Added test for trigger returning result (#11587)
sql/item_func.cc:
  Store the first used seed value for RAND() value.
  (This makes rand() replicatable in functions and triggers)
  Save and clear run context before executing a stored function and restore it afterwards.
  This removes side effects of stored functions for RAND(), auto-increment values and NOW() and makes most stored function replicatable
sql/share/errmsg.txt:
  Reuse error message also for triggers
sql/sp_head.cc:
  If in function or trigger, don't change value of NOW()
  (This allows us to use statement replication with functions that directly or indirectly uses timestamps)
sql/sql_class.cc:
  Added framework for storing and retrieving run context while exceuting triggers or stored functions.
sql/sql_class.h:
  Added framework for storing and retrieving run context while exceuting triggers or stored functions.
sql/sql_parse.cc:
  If in function or trigger, don't change value of NOW()
  (This allows us to use statement replication with functions that directly or indirectly uses timestamps)
sql/sql_trigger.cc:
  Moved process_triggers function from sql_trigger.h
  Use reset/restore sub_statement_state while executing triggers to avoid side effects and make them replicatable
sql/sql_trigger.h:
  Moved process_triggers function from sql_trigger.h
  Use reset/restore sub_statement_state while executing triggers to avoid side effects and make them replicatable
sql/sql_yacc.yy:
  Give error message if trigger can return a result set (Bug #11587)
tests/fork_big2.pl:
  Removed return from end of lines
mysql-test/r/rpl_trigger.result:
  New BitKeeper file ``mysql-test/r/rpl_trigger.result''
mysql-test/t/rpl_trigger.test:
  New BitKeeper file ``mysql-test/t/rpl_trigger.test''

9 files changed, 199 insertions, 107 deletions

diff --git a/sql/item_func.cc b/sql/item_func.cc
index ef1c85f6120..61467595424 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1873,6 +1873,9 @@ bool Item_func_rand::fix_fields(THD *thd,Item **ref)
       Allocate rand structure once: we must use thd->current_arena
       to create rand in proper mem_root if it's a prepared statement or
       stored procedure.
+
+      No need to send a Rand log event if seed was given eg: RAND(seed),
+      as it will be replicated in the query as such.
     */
     if (!rand && !(rand= (struct rand_struct*)
                    thd->current_arena->alloc(sizeof(*rand))))
@@ -1895,16 +1898,16 @@ bool Item_func_rand::fix_fields(THD *thd,Item **ref)
   else
   {
     /*
-      No need to send a Rand log event if seed was given eg: RAND(seed),
-      as it will be replicated in the query as such.
-
       Save the seed only the first time RAND() is used in the query
       Once events are forwarded rather than recreated,
       the following can be skipped if inside the slave thread
     */
-    thd->rand_used=1;
-    thd->rand_saved_seed1=thd->rand.seed1;
-    thd->rand_saved_seed2=thd->rand.seed2;
+    if (!thd->rand_used)
+    {
+      thd->rand_used= 1;
+      thd->rand_saved_seed1= thd->rand.seed1;
+      thd->rand_saved_seed2= thd->rand.seed2;
+    }
     rand= &thd->rand;
   }
   return FALSE;
@@ -4665,10 +4668,9 @@ Item_func_sp::execute(Item **itp)
 {
   DBUG_ENTER("Item_func_sp::execute");
   THD *thd= current_thd;
-  ulong old_client_capabilites;
   int res= -1;
-  bool save_in_sub_stmt= thd->in_sub_stmt;
-  my_bool save_no_send_ok;
+  Sub_statement_state statement_state;
+
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   st_sp_security_context save_ctx;
 #endif
@@ -4679,38 +4681,21 @@ Item_func_sp::execute(Item **itp)
     goto error;
   }
 
-  old_client_capabilites= thd->client_capabilities;
-  thd->client_capabilities &= ~CLIENT_MULTI_RESULTS;
-
-#ifndef EMBEDDED_LIBRARY
-  save_no_send_ok= thd->net.no_send_ok;
-  thd->net.no_send_ok= TRUE;
-#endif
-
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   if (check_routine_access(thd, EXECUTE_ACL, 
 			   m_sp->m_db.str, m_sp->m_name.str, 0, 0))
-    goto error_check;
+    goto error;
   sp_change_security_context(thd, m_sp, &save_ctx);
   if (save_ctx.changed && 
       check_routine_access(thd, EXECUTE_ACL, 
 			   m_sp->m_db.str, m_sp->m_name.str, 0, 0))
     goto error_check_ctx;
 #endif
-  /*
-    Like for SPs, we don't binlog the substatements. If the statement which
-    called this function is an update statement, it will be binlogged; but if
-    it's not (e.g. SELECT myfunc()) it won't be binlogged (documented known
-    problem).
-  */
-
-  tmp_disable_binlog(thd); /* don't binlog the substatements */
-  thd->in_sub_stmt= TRUE;
 
+  thd->reset_sub_statement_state(&statement_state, SUB_STMT_FUNCTION);
   res= m_sp->execute_function(thd, args, arg_count, itp);
+  thd->restore_sub_statement_state(&statement_state);
 
-  thd->in_sub_stmt= save_in_sub_stmt;
-  reenable_binlog(thd);
   if (res && mysql_bin_log.is_open() &&
       (m_sp->m_chistics->daccess == SP_CONTAINS_SQL ||
        m_sp->m_chistics->daccess == SP_MODIFIES_SQL_DATA))
@@ -4723,15 +4708,6 @@ error_check_ctx:
   sp_restore_security_context(thd, m_sp, &save_ctx);
 #endif
 
-  thd->client_capabilities|= old_client_capabilites &  CLIENT_MULTI_RESULTS;
-
-error_check:
-#ifndef EMBEDDED_LIBRARY
-  thd->net.no_send_ok= save_no_send_ok;
-#endif
-
-  thd->client_capabilities|= old_client_capabilites &  CLIENT_MULTI_RESULTS;
-
 error:
   DBUG_RETURN(res);
 }
diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt
index 7ad8e7c3030..d188ee9fd0e 100644
--- a/sql/share/errmsg.txt
+++ b/sql/share/errmsg.txt
@@ -5342,8 +5342,8 @@ ER_SP_DUP_HANDLER 42000
 	eng "Duplicate handler declared in the same block"
 ER_SP_NOT_VAR_ARG 42000
 	eng "OUT or INOUT argument %d for routine %s is not a variable"
-ER_SP_NO_RETSET_IN_FUNC 0A000
-	eng "Not allowed to return a result set from a function"
+ER_SP_NO_RETSET 0A000
+	eng "Not allowed to return a result set from a %s"
 ER_CANT_CREATE_GEOMETRY_OBJECT 22003 
 	eng "Cannot get geometry object from data you send to the GEOMETRY field"
 ER_FAILED_ROUTINE_BREAK_BINLOG
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index d0cd968f4fb..50bb3a84ee7 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -659,7 +659,9 @@ sp_head::execute(THD *thd)
     if (i == NULL)
       break;
     DBUG_PRINT("execute", ("Instruction %u", ip));
-    thd->set_time();		// Make current_time() et al work
+    /* Don't change NOW() in FUNCTION or TRIGGER */
+    if (!thd->in_sub_stmt)
+      thd->set_time();		// Make current_time() et al work
     /*
       We have to set thd->current_arena before executing the instruction
       to store in the instruction free_list all new items, created
@@ -690,8 +692,7 @@ sp_head::execute(THD *thd)
     {
       uint hf;
 
-      switch (ctx->found_handler(&hip, &hf))
-      {
+      switch (ctx->found_handler(&hip, &hf)) {
       case SP_HANDLER_NONE:
 	break;
       case SP_HANDLER_CONTINUE:
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 21df4640f3f..566d4e9d2a2 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -174,7 +174,7 @@ THD::THD()
   :Statement(CONVENTIONAL_EXECUTION, 0, ALLOC_ROOT_MIN_BLOCK_SIZE, 0),
    Open_tables_state(refresh_version),
    lock_id(&main_lock_id),
-   user_time(0), in_sub_stmt(FALSE), global_read_lock(0), is_fatal_error(0),
+   user_time(0), in_sub_stmt(0), global_read_lock(0), is_fatal_error(0),
    rand_used(0), time_zone_used(0),
    last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
    in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
@@ -1836,3 +1836,85 @@ void THD::restore_backup_open_tables_state(Open_tables_state *backup)
   set_open_tables_state(backup);
   DBUG_VOID_RETURN;
 }
+
+
+/****************************************************************************
+  Handling of statement states in functions and triggers.
+
+  This is used to ensure that the function/trigger gets a clean state
+  to work with and does not cause any side effects of the calling statement.
+
+  It also allows most stored functions and triggers to replicate even
+  if they are used items that would normally be stored in the binary
+  replication (like last_insert_id() etc...)
+
+  The following things is done
+  - Disable binary logging for the duration of the statement
+  - Disable multi-result-sets for the duration of the statement
+  - Value of last_insert_id() is reset and restored
+  - Value set by 'SET INSERT_ID=#' is reset and restored
+  - Value for found_rows() is reset and restored
+  - examined_row_count is added to the total
+  - cuted_fields is added to the total
+
+  NOTES:
+    Seed for random() is saved for the first! usage of RAND()
+    We reset examined_row_count and cuted_fields and add these to the
+    result to ensure that if we have a bug that would reset these within
+    a function, we are not loosing any rows from the main statement.
+****************************************************************************/
+
+void THD::reset_sub_statement_state(Sub_statement_state *backup,
+                                    uint new_state)
+{
+  backup->options=         options;
+  backup->in_sub_stmt=     in_sub_stmt;
+  backup->no_send_ok=      net.no_send_ok;
+  backup->enable_slow_log= enable_slow_log;
+  backup->last_insert_id=  last_insert_id;
+  backup->next_insert_id=  next_insert_id;
+  backup->insert_id_used=  insert_id_used;
+  backup->limit_found_rows= limit_found_rows;
+  backup->examined_row_count= examined_row_count;
+  backup->sent_row_count=   sent_row_count;
+  backup->cuted_fields=     cuted_fields;
+  backup->client_capabilities= client_capabilities;
+
+  options&= ~OPTION_BIN_LOG;
+  /* Disable result sets */
+  client_capabilities &= ~CLIENT_MULTI_RESULTS;
+  in_sub_stmt|= new_state;
+  last_insert_id= 0;
+  next_insert_id= 0;
+  insert_id_used= 0;
+  examined_row_count= 0;
+  sent_row_count= 0;
+  cuted_fields= 0;
+
+#ifndef EMBEDDED_LIBRARY
+  /* Surpress OK packets in case if we will execute statements */
+  net.no_send_ok= TRUE;
+#endif
+}
+
+
+void THD::restore_sub_statement_state(Sub_statement_state *backup)
+{
+  options=          backup->options;
+  in_sub_stmt=      backup->in_sub_stmt;
+  net.no_send_ok=   backup->no_send_ok;
+  enable_slow_log=  backup->enable_slow_log;
+  last_insert_id=   backup->last_insert_id;
+  next_insert_id=   backup->next_insert_id;
+  insert_id_used=   backup->insert_id_used;
+  limit_found_rows= backup->limit_found_rows;
+  sent_row_count=   backup->sent_row_count;
+  client_capabilities= backup->client_capabilities;
+
+  /*
+    The following is added to the old values as we are interested in the
+    total complexity of the query
+  */
+  examined_row_count+= backup->examined_row_count;
+  cuted_fields+=       backup->cuted_fields;
+}
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 0b8205dfdb3..77408ba3035 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1031,6 +1031,27 @@ public:
 };
 
 
+/* class to save context when executing a function or trigger */
+
+/* Defines used for Sub_statement_state::in_sub_stmt */
+
+#define SUB_STMT_TRIGGER 1
+#define SUB_STMT_FUNCTION 2
+
+class Sub_statement_state
+{
+public:
+  ulonglong options;
+  ulonglong last_insert_id, next_insert_id;
+  ulonglong limit_found_rows;
+  ha_rows    cuted_fields, sent_row_count, examined_row_count;
+  ulong client_capabilities;
+  uint in_sub_stmt;
+  bool enable_slow_log, insert_id_used;
+  my_bool no_send_ok;
+};
+
+
 /*
   For each client connection we create a separate thread with THD serving as
   a thread/connection descriptor
@@ -1137,10 +1158,9 @@ public:
   time_t     connect_time,thr_create_time; // track down slow pthread_create
   thr_lock_type update_lock_default;
   delayed_insert *di;
-  my_bool    tablespace_op;	/* This is TRUE in DISCARD/IMPORT TABLESPACE */
   
-  /* TRUE if we are inside of trigger or stored function. */
-  bool in_sub_stmt;
+  /* <> 0 if we are inside of trigger or stored function. */
+  uint in_sub_stmt;
   
   /* container for handler's private per-connection data */
   void *ha_data[MAX_HA];
@@ -1223,6 +1243,8 @@ public:
   */
   ulonglong  current_insert_id;
   ulonglong  limit_found_rows;
+  ulonglong  options;           /* Bitmap of states */
+  longlong   row_count_func;	/* For the ROW_COUNT() function */
   ha_rows    cuted_fields,
              sent_row_count, examined_row_count;
   table_map  used_tables;
@@ -1246,7 +1268,6 @@ public:
     update auto-updatable fields (like auto_increment and timestamp).
   */
   query_id_t query_id, warn_id;
-  ulonglong  options;
   ulong      thread_id, col_access;
 
   /* Statement id is thread-wide. This counter is used to generate ids */
@@ -1287,7 +1308,8 @@ public:
   bool	     no_warnings_for_error; /* no warnings on call to my_error() */
   /* set during loop of derived table processing */
   bool       derived_tables_processing;
-  longlong   row_count_func;	/* For the ROW_COUNT() function */
+  my_bool    tablespace_op;	/* This is TRUE in DISCARD/IMPORT TABLESPACE */
+
   sp_rcontext *spcont;		// SP runtime context
   sp_cache   *sp_proc_cache;
   sp_cache   *sp_func_cache;
@@ -1495,6 +1517,8 @@ public:
     { return current_arena->is_stmt_prepare() || lex->view_prepare_mode; }
   void reset_n_backup_open_tables_state(Open_tables_state *backup);
   void restore_backup_open_tables_state(Open_tables_state *backup);
+  void reset_sub_statement_state(Sub_statement_state *backup, uint new_state);
+  void restore_sub_statement_state(Sub_statement_state *backup);
 };
 
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index bbd1f81b7f3..a0a99e5fe09 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1101,11 +1101,11 @@ pthread_handler_decl(handle_one_connection,arg)
       execute_init_command(thd, &sys_init_connect, &LOCK_sys_init_connect);
       if (thd->query_error)
 	thd->killed= THD::KILL_CONNECTION;
+      thd->proc_info=0;
+      thd->set_time();
+      thd->init_for_queries();
     }
 
-    thd->proc_info=0;
-    thd->set_time();
-    thd->init_for_queries();
     while (!net->error && net->vio != 0 &&
            !(thd->killed == THD::KILL_CONNECTION))
     {
@@ -1464,6 +1464,7 @@ bool do_command(THD *thd)
 
 /*
    Perform one connection-level (COM_XXXX) command.
+
   SYNOPSIS
     dispatch_command()
     thd             connection handle
@@ -2044,7 +2045,17 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 
 void log_slow_statement(THD *thd)
 {
-  time_t start_of_query=thd->start_time;
+  time_t start_of_query;
+
+  /*
+    The following should never be true with our current code base,
+    but better to keep this here so we don't accidently try to log a
+    statement in a trigger or stored function
+  */
+  if (unlikely(thd->in_sub_stmt))
+    return;                                     // Don't set time for sub stmt
+
+  start_of_query= thd->start_time;
   thd->end_time();				// Set start time
 
   /*
@@ -5157,10 +5168,8 @@ void mysql_reset_thd_for_next_command(THD *thd)
   DBUG_ENTER("mysql_reset_thd_for_next_command");
   thd->free_list= 0;
   thd->select_number= 1;
-  thd->total_warn_count=0;			// Warnings for this query
   thd->last_insert_id_used= thd->query_start_used= thd->insert_id_used=0;
-  thd->sent_row_count= thd->examined_row_count= 0;
-  thd->is_fatal_error= thd->rand_used= thd->time_zone_used= 0;
+  thd->is_fatal_error= thd->time_zone_used= 0;
   thd->server_status&= ~ (SERVER_MORE_RESULTS_EXISTS | 
                           SERVER_QUERY_NO_INDEX_USED |
                           SERVER_QUERY_NO_GOOD_INDEX_USED);
@@ -5168,6 +5177,12 @@ void mysql_reset_thd_for_next_command(THD *thd)
   if (opt_bin_log)
     reset_dynamic(&thd->user_var_events);
   thd->clear_error();
+  if (!thd->in_sub_stmt)
+  {
+    thd->total_warn_count=0;			// Warnings for this query
+    thd->rand_used= 0;
+    thd->sent_row_count= thd->examined_row_count= 0;
+  }
   DBUG_VOID_RETURN;
 }
 
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 09eeff02de6..053dfdfc990 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -869,3 +869,39 @@ end:
   free_root(&table.mem_root, MYF(0));
   DBUG_RETURN(result);
 }
+
+
+
+bool Table_triggers_list::process_triggers(THD *thd, trg_event_type event,
+                                           trg_action_time_type time_type,
+                                           bool old_row_is_record1)
+{
+  int res= 0;
+
+  if (bodies[event][time_type])
+  {
+    Sub_statement_state statement_state;
+
+    if (old_row_is_record1)
+    {
+      old_field= record1_field;
+      new_field= table->field;
+    }
+    else
+    {
+      new_field= record1_field;
+      old_field= table->field;
+    }
+
+    /*
+      FIXME: We should juggle with security context here (because trigger
+      should be invoked with creator rights).
+    */
+
+    thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER);
+    res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
+    thd->restore_sub_statement_state(&statement_state);
+  }
+
+  return res;
+}
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index e2ed4bcc0f4..d9b39cc3034 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -20,6 +20,7 @@
 
   QQ: Will it be merged into TABLE in future ?
 */
+
 class Table_triggers_list: public Sql_alloc
 {
   /* Triggers as SPs grouped by event, action_time */
@@ -76,55 +77,7 @@ public:
   bool drop_trigger(THD *thd, TABLE_LIST *table);
   bool process_triggers(THD *thd, trg_event_type event,
                         trg_action_time_type time_type,
-                        bool old_row_is_record1)
-  {
-    int res= 0;
-
-    if (bodies[event][time_type])
-    {
-      bool save_in_sub_stmt= thd->in_sub_stmt;
-#ifndef EMBEDDED_LIBRARY
-      /* Surpress OK packets in case if we will execute statements */
-      my_bool nsok= thd->net.no_send_ok;
-      thd->net.no_send_ok= TRUE;
-#endif
-
-      if (old_row_is_record1)
-      {
-        old_field= record1_field;
-        new_field= table->field;
-      }
-      else
-      {
-        new_field= record1_field;
-        old_field= table->field;
-      }
-
-      /*
-        FIXME: We should juggle with security context here (because trigger
-        should be invoked with creator rights).
-      */
-      /*
-	We disable binlogging, as in SP/functions, even though currently
-        triggers can't do updates. When triggers can do updates, someone
-        should add such a trigger to rpl_sp.test to verify that the update
-        does NOT go into binlog.
-      */
-      tmp_disable_binlog(thd);
-      thd->in_sub_stmt= TRUE;
-
-      res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
-
-      thd->in_sub_stmt= save_in_sub_stmt;
-      reenable_binlog(thd);
-
-#ifndef EMBEDDED_LIBRARY
-      thd->net.no_send_ok= nsok;
-#endif
-    }
-
-    return res;
-  }
+                        bool old_row_is_record1);
   bool get_trigger_info(THD *thd, trg_event_type event,
                         trg_action_time_type time_type,
                         LEX_STRING *trigger_name, LEX_STRING *trigger_stmt,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1d9413d2cf7..f933c00a1c4 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1311,6 +1311,12 @@ create:
               YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES;
             sp->restore_thd_mem_root(YYTHD);
 
+	    if (sp->m_multi_results)
+	    {
+	      my_error(ER_SP_NO_RETSET, MYF(0), "trigger");
+	      YYABORT;
+	    }
+
             /*
               We have to do it after parsing trigger body, because some of
               sp_proc_stmt alternatives are not saving/restoring LEX, so
@@ -1463,8 +1469,7 @@ create_function_tail:
 
 	    if (sp->m_multi_results)
 	    {
-	      my_message(ER_SP_NO_RETSET_IN_FUNC, ER(ER_SP_NO_RETSET_IN_FUNC),
-	                 MYF(0));
+	      my_error(ER_SP_NO_RETSET, MYF(0), "function");
 	      YYABORT;
 	    }
 	    if (sp->check_backpatch(YYTHD))