merge

sql/ha_federated.cc:
  Auto merged
sql/lock.cc:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/sp.cc:
  Auto merged
sql/sql_base.cc:
  Auto merged
sql/sql_handler.cc:
  Auto merged
sql/sql_insert.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_table.cc:
  Auto merged

20 files changed, 332 insertions, 125 deletions

diff --git a/sql/ha_federated.cc b/sql/ha_federated.cc
index d298ebf20cb..fa027ce71b5 100644
--- a/sql/ha_federated.cc
+++ b/sql/ha_federated.cc
@@ -522,7 +522,8 @@ error:
 
 static int parse_url_error(FEDERATED_SHARE *share, TABLE *table, int error_num)
 {
-  char buf[table->s->connect_string.length+1];
+  char buf[FEDERATED_QUERY_BUFFER_SIZE];
+  int buf_len;
   DBUG_ENTER("ha_federated parse_url_error");
   if (share->scheme)
   {
@@ -532,9 +533,11 @@ static int parse_url_error(FEDERATED_SHARE *share, TABLE *table, int error_num)
     my_free((gptr) share->scheme, MYF(0));
     share->scheme= 0;
   }
-
-  strnmov(buf, table->s->connect_string.str, table->s->connect_string.length+1);
-  buf[table->s->connect_string.length]= '\0';
+  buf_len= (table->s->connect_string.length > (FEDERATED_QUERY_BUFFER_SIZE - 1)) 
+    ? FEDERATED_QUERY_BUFFER_SIZE - 1 : table->s->connect_string.length;
+  
+  strnmov(buf, table->s->connect_string.str, buf_len);
+  buf[buf_len]= '\0';
   my_error(error_num, MYF(0), buf);
   DBUG_RETURN(error_num);
 }
@@ -743,13 +746,12 @@ ha_federated::ha_federated(TABLE *table_arg)
 
 uint ha_federated::convert_row_to_internal_format(byte *record, MYSQL_ROW row)
 {
-  uint num_fields;
   ulong *lengths;
   Field **field;
 
   DBUG_ENTER("ha_federated::convert_row_to_internal_format");
 
-  num_fields= mysql_num_fields(stored_result);
+  // num_fields= mysql_num_fields(stored_result);
   lengths= mysql_fetch_lengths(stored_result);
 
   memset(record, 0, table->s->null_bytes);
@@ -1115,12 +1117,9 @@ bool ha_federated::create_where_from_key(String *to,
   for (int i= 0; i <= 1; i++)
   {
     bool needs_quotes;
-    uint loop_counter= 0;
     KEY_PART_INFO *key_part;
     if (ranges[i] == NULL)
       continue;
-    const byte *key= ranges[i]->key;
-    uint key_length= ranges[i]->length;
 
     if (both_not_null)
     {
@@ -1435,7 +1434,6 @@ const char **ha_federated::bas_ext() const
 
 int ha_federated::open(const char *name, int mode, uint test_if_locked)
 {
-  int rc;
   DBUG_ENTER("ha_federated::open");
 
   if (!(share= get_share(name, table)))
@@ -1753,7 +1751,6 @@ int ha_federated::update_row(const byte *old_data, byte *new_data)
   /* 
     buffers for following strings
   */
-  char error_buffer[FEDERATED_QUERY_BUFFER_SIZE];
   char old_field_value_buffer[STRING_BUFFER_USUAL_SIZE];
   char new_field_value_buffer[STRING_BUFFER_USUAL_SIZE];
   char update_buffer[FEDERATED_QUERY_BUFFER_SIZE];
@@ -1823,10 +1820,8 @@ int ha_federated::update_row(const byte *old_data, byte *new_data)
       where_string.append(FEDERATED_ISNULL);
     else
     {
-      uint o_len;
       (*field)->val_str(&old_field_value,
                         (char*) (old_data + (*field)->offset()));
-      o_len= (*field)->pack_length();
       (*field)->quote_data(&old_field_value);
       where_string.append(old_field_value);
     }
@@ -1964,8 +1959,6 @@ int ha_federated::index_read_idx(byte *buf, uint index, const byte *key,
   int retval;
   char error_buffer[FEDERATED_QUERY_BUFFER_SIZE];
   char index_value[STRING_BUFFER_USUAL_SIZE];
-  char key_value[STRING_BUFFER_USUAL_SIZE];
-  char test_value[STRING_BUFFER_USUAL_SIZE];
   char sql_query_buffer[FEDERATED_QUERY_BUFFER_SIZE];
   String index_string(index_value, 
                       sizeof(index_value),
@@ -2046,7 +2039,6 @@ error:
 /* Initialized at each key walk (called multiple times unlike rnd_init()) */
 int ha_federated::index_init(uint keynr, bool sorted)
 {
-  int error;
   DBUG_ENTER("ha_federated::index_init");
   DBUG_PRINT("info",
              ("table: '%s'  key: %d", table->s->table_name, keynr));
@@ -2153,10 +2145,6 @@ int ha_federated::index_next(byte *buf)
 
 int ha_federated::rnd_init(bool scan)
 {
-  int num_fields, rows;
-  int retval;
-  char error_buffer[FEDERATED_QUERY_BUFFER_SIZE];
-
   DBUG_ENTER("ha_federated::rnd_init");
   /*
     The use of the 'scan' flag is incredibly important for this handler
@@ -2447,7 +2435,6 @@ void ha_federated::info(uint flag)
     }
     if (flag & HA_STATUS_CONST)
     {
-      TABLE_SHARE *share= table->s;
       block_size= 4096;
     }
   }
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 82a82873ad9..342ef245a76 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -109,7 +109,7 @@ Item *create_func_dayofmonth(Item* a)
 
 Item *create_func_dayofweek(Item* a)
 {
-  return new Item_func_weekday(new Item_func_to_days(a),1);
+  return new Item_func_weekday(a, 1);
 }
 
 Item *create_func_dayofyear(Item* a)
@@ -119,7 +119,7 @@ Item *create_func_dayofyear(Item* a)
 
 Item *create_func_dayname(Item* a)
 {
-  return new Item_func_dayname(new Item_func_to_days(a));
+  return new Item_func_dayname(a);
 }
 
 Item *create_func_degrees(Item *a)
@@ -443,7 +443,7 @@ Item *create_func_version(void)
 
 Item *create_func_weekday(Item* a)
 {
-  return new Item_func_weekday(new Item_func_to_days(a),0);
+  return new Item_func_weekday(a, 0);
 }
 
 Item *create_func_year(Item* a)
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 0d9e23ff0a1..7f94c19647e 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1030,16 +1030,17 @@ longlong Item_func_yearweek::val_int()
 }
 
 
-/* weekday() has a automatic to_days() on item */
-
 longlong Item_func_weekday::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  ulong tmp_value=(ulong) args[0]->val_int();
-  if ((null_value=(args[0]->null_value || !tmp_value)))
-    return 0; /* purecov: inspected */
+  TIME ltime;
+  
+  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
+    return 0;
 
-  return (longlong) calc_weekday(tmp_value,odbc_type)+test(odbc_type);
+  return (longlong) calc_weekday(calc_daynr(ltime.year, ltime.month,
+                                            ltime.day),
+                                 odbc_type) + test(odbc_type);
 }
 
 
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 4602088a5f5..8e15acbc1fc 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -257,7 +257,10 @@ public:
     str->set(val_int(), &my_charset_bin);
     return null_value ? 0 : str;
   }
-  const char *func_name() const { return "weekday"; }
+  const char *func_name() const
+  {
+     return (odbc_type ? "dayofweek" : "weekday");
+  }
   enum Item_result result_type () const { return INT_RESULT; }
   void fix_length_and_dec()
   {
diff --git a/sql/lock.cc b/sql/lock.cc
index 0aba7fddb51..0af22cac399 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -93,23 +93,33 @@ static void print_lock_error(int error, const char *);
     flags                       Options:
       MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK      Ignore a global read lock
       MYSQL_LOCK_IGNORE_FLUSH                 Ignore a flush tables.
+      MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN        Instead of reopening altered
+                                              or dropped tables by itself,
+                                              mysql_lock_tables() should
+                                              notify upper level and rely
+                                              on caller doing this.
+    need_reopen                 Out parameter, TRUE if some tables were altered
+                                or deleted and should be reopened by caller.
 
   RETURN
     A lock structure pointer on success.
-    NULL on error.
+    NULL on error or if some tables should be reopen.
 */
 
+/* Map the return value of thr_lock to an error from errmsg.txt */
 static int thr_lock_errno_to_mysql[]=
 { 0, 1, ER_LOCK_WAIT_TIMEOUT, ER_LOCK_DEADLOCK };
 
-MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, uint flags)
+MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count,
+                              uint flags, bool *need_reopen)
 {
   MYSQL_LOCK *sql_lock;
   TABLE *write_lock_used;
   int rc;
-  /* Map the return value of thr_lock to an error from errmsg.txt */
   DBUG_ENTER("mysql_lock_tables");
 
+  *need_reopen= FALSE;
+
   for (;;)
   {
     if (!(sql_lock = get_lock_data(thd,tables,count, 0,&write_lock_used)))
@@ -178,6 +188,11 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count, uint flags)
     thd->locked=0;
 retry:
     sql_lock=0;
+    if (flags & MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN)
+    {
+      *need_reopen= TRUE;
+      break;
+    }
     if (wait_for_tables(thd))
       break;					// Couldn't open tables
   }
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index fac4dc03ad3..7a3c5547232 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -979,7 +979,7 @@ int open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags);
 int simple_open_n_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_and_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables, uint flags);
-int lock_tables(THD *thd, TABLE_LIST *tables, uint counter);
+int lock_tables(THD *thd, TABLE_LIST *tables, uint counter, bool *need_reopen);
 TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
 			    const char *table_name, bool link_in_list);
 bool rm_temporary_table(enum db_type base, char *path);
@@ -987,6 +987,7 @@ void free_io_cache(TABLE *entry);
 void intern_close_table(TABLE *entry);
 bool close_thread_table(THD *thd, TABLE **table_ptr);
 void close_temporary_tables(THD *thd);
+void close_tables_for_reopen(THD *thd, TABLE_LIST *tables);
 TABLE_LIST *find_table_in_list(TABLE_LIST *table,
                                uint offset_to_list,
                                const char *db_name,
@@ -1266,10 +1267,12 @@ extern pthread_t signal_thread;
 extern struct st_VioSSLAcceptorFd * ssl_acceptor_fd;
 #endif /* HAVE_OPENSSL */
 
-MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count, uint flags);
+MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **table, uint count,
+                              uint flags, bool *need_reopen);
 /* mysql_lock_tables() flags bits */
 #define MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK      0x0001
 #define MYSQL_LOCK_IGNORE_FLUSH                 0x0002
+#define MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN        0x0004
 
 void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock);
 void mysql_unlock_read_tables(THD *thd, MYSQL_LOCK *sql_lock);
diff --git a/sql/sp.cc b/sql/sp.cc
index c3d20150561..e979921492f 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -107,7 +107,7 @@ TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
 {
   TABLE_LIST tables;
   TABLE *table;
-  bool refresh;
+  bool not_used;
   DBUG_ENTER("open_proc_table");
 
   /*
@@ -122,7 +122,7 @@ TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
   bzero((char*) &tables, sizeof(tables));
   tables.db= (char*) "mysql";
   tables.table_name= tables.alias= (char*)"proc";
-  if (!(table= open_table(thd, &tables, thd->mem_root, &refresh,
+  if (!(table= open_table(thd, &tables, thd->mem_root, &not_used,
                           MYSQL_LOCK_IGNORE_FLUSH)))
   {
     thd->restore_backup_open_tables_state(backup);
@@ -138,7 +138,7 @@ TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
     could lead to a deadlock if we have other tables opened.
   */
   if (!(thd->lock= mysql_lock_tables(thd, &table, 1,
-                                     MYSQL_LOCK_IGNORE_FLUSH)))
+                                     MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
   {
     close_proc_table(thd, backup);
     DBUG_RETURN(0);
@@ -1265,7 +1265,8 @@ static bool add_used_routine(LEX *lex, Query_arena *arena,
 
 
 /*
-  Add routine to the set of stored routines used by statement.
+  Add routine which is explicitly used by statement to the set of stored
+  routines used by this statement.
 
   SYNOPSIS
     sp_add_used_routine()
@@ -1276,7 +1277,8 @@ static bool add_used_routine(LEX *lex, Query_arena *arena,
       rt_type - routine type (one of TYPE_ENUM_PROCEDURE/...)
 
   NOTES
-    Will also add element to end of 'LEX::sroutines_list' list.
+    Will also add element to end of 'LEX::sroutines_list' list (and will
+    take into account that this is explicitly used routine).
 
     To be friendly towards prepared statements one should pass
     persistent arena as second argument.
@@ -1287,6 +1289,37 @@ void sp_add_used_routine(LEX *lex, Query_arena *arena,
 {
   rt->set_routine_type(rt_type);
   (void)add_used_routine(lex, arena, &rt->m_sroutines_key);
+  lex->sroutines_list_own_last= lex->sroutines_list.next;
+  lex->sroutines_list_own_elements= lex->sroutines_list.elements;
+}
+
+
+/*
+  Remove routines which are only indirectly used by statement from
+  the set of routines used by this statement.
+
+  SYNOPSIS
+    sp_remove_not_own_routines()
+      lex  LEX representing statement
+*/
+
+void sp_remove_not_own_routines(LEX *lex)
+{
+  Sroutine_hash_entry *not_own_rt, *next_rt;
+  for (not_own_rt= *(Sroutine_hash_entry **)lex->sroutines_list_own_last;
+       not_own_rt; not_own_rt= next_rt)
+  {
+    /*
+      It is safe to obtain not_own_rt->next after calling hash_delete() now
+      but we want to be more future-proof.
+    */
+    next_rt= not_own_rt->next;
+    hash_delete(&lex->sroutines, (byte *)not_own_rt);
+  }
+
+  *(Sroutine_hash_entry **)lex->sroutines_list_own_last= NULL;
+  lex->sroutines_list.next= lex->sroutines_list_own_last;
+  lex->sroutines_list.elements= lex->sroutines_list_own_elements;
 }
 
 
@@ -1344,6 +1377,28 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
 
 
 /*
+  Add contents of list representing set of routines to the set of
+  routines used by statement.
+
+  SYNOPSIS
+    sp_update_stmt_used_routines()
+      thd  Thread context
+      lex  LEX representing statement
+      src  List representing set from which routines will be added
+
+  NOTE
+    It will also add elements to end of 'LEX::sroutines_list' list.
+*/
+
+static void sp_update_stmt_used_routines(THD *thd, LEX *lex, SQL_LIST *src)
+{
+  for (Sroutine_hash_entry *rt= (Sroutine_hash_entry *)src->first;
+       rt; rt= rt->next)
+    (void)add_used_routine(lex, thd->stmt_arena, &rt->key);
+}
+
+
+/*
   Cache sub-set of routines used by statement, add tables used by these
   routines to statement table list. Do the same for all routines used
   by these routines.
@@ -1463,7 +1518,7 @@ sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
 {
   Sroutine_hash_entry **last_cached_routine_ptr=
                           (Sroutine_hash_entry **)lex->sroutines_list.next;
-  sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines);
+  sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines_list);
   (void)sp_cache_routines_and_add_tables_aux(thd, lex, 
                                              *last_cached_routine_ptr, FALSE);
 }
diff --git a/sql/sp.h b/sql/sp.h
index c278da863e0..933e5793e4c 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -84,6 +84,7 @@ void sp_get_prelocking_info(THD *thd, bool *need_prelocking,
                             bool *first_no_prelocking);
 void sp_add_used_routine(LEX *lex, Query_arena *arena,
                          sp_name *rt, char rt_type);
+void sp_remove_not_own_routines(LEX *lex);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
 bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, 
                                       bool first_no_prelock);
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index a11907373cd..1a7599d7bbc 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -1909,10 +1909,6 @@ sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
       attached it above in this function).
       Now we'll save the 'tail', and detach it.
     */
-    DBUG_ASSERT(!lex_query_tables_own_last ||
-                lex_query_tables_own_last == m_lex->query_tables_own_last &&
-                prelocking_tables == *(m_lex->query_tables_own_last));
-
     lex_query_tables_own_last= m_lex->query_tables_own_last;
     prelocking_tables= *lex_query_tables_own_last;
     *lex_query_tables_own_last= NULL;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index c7204df2501..38a9d259d24 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1399,7 +1399,6 @@ bool reopen_table(TABLE *table,bool locked)
   tmp.status=		table->status;
   tmp.keys_in_use_for_query= tmp.s->keys_in_use;
   tmp.used_keys= 	tmp.s->keys_for_keyread;
-  tmp.force_index=	tmp.force_index;
 
   /* Get state */
   tmp.s->key_length=	table->s->key_length;
@@ -1430,6 +1429,9 @@ bool reopen_table(TABLE *table,bool locked)
   for (key=0 ; key < table->s->keys ; key++)
     for (part=0 ; part < table->key_info[key].usable_key_parts ; part++)
       table->key_info[key].key_part[part].field->table= table;
+  if (table->triggers)
+    table->triggers->set_table(table);
+
   VOID(pthread_cond_broadcast(&COND_refresh));
   error=0;
 
@@ -1478,7 +1480,7 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
 
   TABLE *table,*next,**prev;
   TABLE **tables,**tables_ptr;			// For locks
-  bool error=0;
+  bool error=0, not_used;
   if (get_locks)
   {
     /* The ptr is checked later */
@@ -1519,7 +1521,8 @@ bool reopen_tables(THD *thd,bool get_locks,bool in_refresh)
     MYSQL_LOCK *lock;
     /* We should always get these locks */
     thd->some_tables_deleted=0;
-    if ((lock= mysql_lock_tables(thd, tables, (uint) (tables_ptr - tables), 0)))
+    if ((lock= mysql_lock_tables(thd, tables, (uint) (tables_ptr - tables),
+                                 0, &not_used)))
     {
       thd->locked_tables=mysql_lock_merge(thd->locked_tables,lock);
     }
@@ -1969,9 +1972,15 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
     /*
       Ignore placeholders for derived tables. After derived tables
       processing, link to created temporary table will be put here.
+      If this is derived table for view then we still want to process
+      routines used by this view.
      */
     if (tables->derived)
+    {
+      if (tables->view)
+        goto process_view_routines;
       continue;
+    }
     if (tables->schema_table)
     {
       if (!mysql_schema_table(thd, thd->lex, tables))
@@ -2003,23 +2012,12 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
         if (query_tables_last_own == &(tables->next_global) &&
             tables->view->query_tables)
           query_tables_last_own= tables->view->query_tables_last;
-        
         /*
-          Again if needed we have to get cache all routines used by this view
-          and add tables used by them to table list.
+          Let us free memory used by 'sroutines' hash here since we never
+          call destructor for this LEX.
         */
-        if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
-            tables->view->sroutines.records)
-        {
-          /* We have at least one table in TL here */
-          if (!query_tables_last_own)
-            query_tables_last_own= thd->lex->query_tables_last;
-          sp_cache_routines_and_add_tables_for_view(thd, thd->lex,
-                                                    tables->view);
-        }
-        /* Cleanup hashes because destructo for this LEX is never called */
         hash_free(&tables->view->sroutines);
-	continue;
+	goto process_view_routines;
       }
 
       if (refresh)				// Refresh in progress
@@ -2031,11 +2029,6 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
 	thd->version=refresh_version;
 	TABLE **prev_table= &thd->open_tables;
 	bool found=0;
-        /*
-          QQ: What we should do if we have started building of table list
-          for prelocking ??? Probably throw it away ? But before we should
-          mark all temporary tables as free? How about locked ?
-        */
 	for (TABLE_LIST *tmp= *start; tmp; tmp= tmp->next_global)
 	{
 	  /* Close normal (not temporary) changed tables */
@@ -2059,6 +2052,18 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
 	pthread_mutex_unlock(&LOCK_open);
 	if (found)
 	  VOID(pthread_cond_broadcast(&COND_refresh)); // Signal to refresh
+        /*
+          Let us prepare for recalculation of set of prelocked tables.
+          First we pretend that we have finished calculation which we
+          were doing currently. Then we restore list of tables to be
+          opened and set of used routines to the state in which they were
+          before first open_tables() call for this statement (i.e. before
+          we have calculated current set of tables for prelocking).
+        */
+        if (query_tables_last_own)
+          thd->lex->mark_as_requiring_prelocking(query_tables_last_own);
+        thd->lex->chop_off_not_own_tables();
+        sp_remove_not_own_routines(thd->lex);
 	goto restart;
       }
       result= -1;				// Fatal error
@@ -2089,6 +2094,21 @@ int open_tables(THD *thd, TABLE_LIST **start, uint *counter, uint flags)
     if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables)
       tables->table->reginfo.lock_type=tables->lock_type;
     tables->table->grant= tables->grant;
+
+process_view_routines:
+    /*
+      Again we may need cache all routines used by this view and add
+      tables used by them to table list.
+    */
+    if (tables->view && !thd->prelocked_mode &&
+        !thd->lex->requires_prelocking() &&
+        tables->view->sroutines_list.elements)
+    {
+      /* We have at least one table in TL here. */
+      if (!query_tables_last_own)
+        query_tables_last_own= thd->lex->query_tables_last;
+      sp_cache_routines_and_add_tables_for_view(thd, thd->lex, tables->view);
+    }
   }
   thd->proc_info=0;
   free_root(&new_frm_mem, MYF(0));              // Free pre-alloced block
@@ -2193,7 +2213,8 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
     {
       DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
       if ((table->reginfo.lock_type= lock_type) != TL_UNLOCK)
-	if (! (thd->lock= mysql_lock_tables(thd, &table_list->table, 1, 0)))
+	if (! (thd->lock= mysql_lock_tables(thd, &table_list->table, 1, 0,
+                                            &refresh)))
 	  table= 0;
     }
   }
@@ -2221,11 +2242,20 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
 
 int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 {
-  DBUG_ENTER("simple_open_n_lock_tables");
   uint counter;
-  if (open_tables(thd, &tables, &counter, 0) ||
-      lock_tables(thd, tables, counter))
-    DBUG_RETURN(-1);				/* purecov: inspected */
+  bool need_reopen;
+  DBUG_ENTER("simple_open_n_lock_tables");
+
+  for ( ; ; ) 
+  {
+    if (open_tables(thd, &tables, &counter, 0))
+      DBUG_RETURN(-1);
+    if (!lock_tables(thd, tables, counter, &need_reopen))
+      break;
+    if (!need_reopen)
+      DBUG_RETURN(-1);
+    close_tables_for_reopen(thd, tables);
+  }
   DBUG_RETURN(0);
 }
 
@@ -2250,10 +2280,20 @@ int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 bool open_and_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   uint counter;
+  bool need_reopen;
   DBUG_ENTER("open_and_lock_tables");
-  if (open_tables(thd, &tables, &counter, 0) ||
-      lock_tables(thd, tables, counter) ||
-      mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+
+  for ( ; ; ) 
+  {
+    if (open_tables(thd, &tables, &counter, 0))
+      DBUG_RETURN(-1);
+    if (!lock_tables(thd, tables, counter, &need_reopen))
+      break;
+    if (!need_reopen)
+      DBUG_RETURN(-1);
+    close_tables_for_reopen(thd, tables);
+  }
+  if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
       (thd->fill_derived_tables() &&
        mysql_handle_derived(thd->lex, &mysql_derived_filling)))
     DBUG_RETURN(TRUE); /* purecov: inspected */
@@ -2321,7 +2361,12 @@ static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table)
     lock_tables()
     thd			Thread handler
     tables		Tables to lock
-    count		umber of opened tables
+    count		Number of opened tables
+    need_reopen         Out parameter which if TRUE indicates that some
+                        tables were dropped or altered during this call
+                        and therefore invoker should reopen tables and
+                        try to lock them once again (in this case
+                        lock_tables() will also return error).
 
   NOTES
     You can't call lock_tables twice, as this would break the dead-lock-free
@@ -2337,7 +2382,7 @@ static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table)
    -1	Error
 */
 
-int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
+int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen)
 {
   TABLE_LIST *table;
 
@@ -2353,6 +2398,8 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
   */
   DBUG_ASSERT(!thd->lex->requires_prelocking() || tables);
 
+  *need_reopen= FALSE;
+
   if (!tables)
     DBUG_RETURN(0);
 
@@ -2385,7 +2432,9 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
       thd->options|= OPTION_TABLE_LOCK;
     }
 
-    if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start), 0)))
+    if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start),
+                                        MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN,
+                                        need_reopen)))
     {
       if (thd->lex->requires_prelocking())
       {
@@ -2465,6 +2514,28 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
 
 
 /*
+  Prepare statement for reopening of tables and recalculation of set of
+  prelocked tables.
+
+  SYNOPSIS
+    close_tables_for_reopen()
+      thd     Thread context
+      tables  List of tables which we were trying to open and lock
+
+*/
+
+void close_tables_for_reopen(THD *thd, TABLE_LIST *tables)
+{
+  thd->lex->chop_off_not_own_tables();
+  sp_remove_not_own_routines(thd->lex);
+  for (TABLE_LIST *tmp= tables; tmp; tmp= tmp->next_global)
+    if (tmp->table && !tmp->table->s->tmp_table)
+      tmp->table= 0;
+  close_thread_tables(thd);
+}
+
+
+/*
   Open a single table without table caching and don't set it in open_list
   Used by alter_table to open a temporary table and when creating
   a temporary table with CREATE TEMPORARY ...
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index fc7d7ac0012..5f065e2a8b8 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -346,6 +346,7 @@ bool mysql_ha_read(THD *thd, TABLE_LIST *tables,
   uint          num_rows;
   byte		*key;
   uint		key_len;
+  bool          not_used;
   DBUG_ENTER("mysql_ha_read");
   DBUG_PRINT("enter",("'%s'.'%s' as '%s'",
                       tables->db, tables->table_name, tables->alias));
@@ -431,7 +432,7 @@ bool mysql_ha_read(THD *thd, TABLE_LIST *tables,
   protocol->send_fields(&list, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);
 
   HANDLER_TABLES_HACK(thd);
-  lock= mysql_lock_tables(thd, &tables->table, 1, 0);
+  lock= mysql_lock_tables(thd, &tables->table, 1, 0, &not_used);
   HANDLER_TABLES_HACK(thd);
 
   if (!lock)
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 77741d0ed3c..3d7ca8059b9 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -1839,6 +1839,7 @@ extern "C" pthread_handler_decl(handle_delayed_insert,arg)
 
     if (di->tables_in_use && ! thd->lock)
     {
+      bool not_used;
       /*
         Request for new delayed insert.
         Lock the table, but avoid to be blocked by a global read lock.
@@ -1850,7 +1851,8 @@ extern "C" pthread_handler_decl(handle_delayed_insert,arg)
         inserts are done.
       */
       if (! (thd->lock= mysql_lock_tables(thd, &di->table, 1,
-                                          MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK)))
+                                          MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK,
+                                          &not_used)))
       {
 	/* Fatal error */
 	di->dead= 1;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 3f1e237b653..d76e8d04ae6 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -180,6 +180,8 @@ void lex_start(THD *thd, uchar *buf,uint length)
   if (lex->sroutines.records)
     my_hash_reset(&lex->sroutines);
   lex->sroutines_list.empty();
+  lex->sroutines_list_own_last= lex->sroutines_list.next;
+  lex->sroutines_list_own_elements= 0;
   DBUG_VOID_RETURN;
 }
 
@@ -1614,6 +1616,8 @@ st_lex::st_lex()
 {
   hash_init(&sroutines, system_charset_info, 0, 0, 0, sp_sroutine_key, 0, 0);
   sroutines_list.empty();
+  sroutines_list_own_last= sroutines_list.next;
+  sroutines_list_own_elements= 0;
 }
 
 
@@ -2026,6 +2030,8 @@ void st_lex::cleanup_after_one_table_open()
   if (sroutines.records)
     my_hash_reset(&sroutines);
   sroutines_list.empty();
+  sroutines_list_own_last= sroutines_list.next;
+  sroutines_list_own_elements= 0;
 }
 
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index fb3fe4d47f6..381d6573a09 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -861,8 +861,15 @@ typedef struct st_lex
   /*
     List linking elements of 'sroutines' set. Allows you to add new elements
     to this set as you iterate through the list of existing elements.
+    'sroutines_list_own_last' is pointer to ::next member of last element of
+    this list which represents routine which is explicitly used by query.
+    'sroutines_list_own_elements' number of explicitly used routines.
+    We use these two members for restoring of 'sroutines_list' to the state
+    in which it was right after query parsing.
   */
   SQL_LIST sroutines_list;
+  byte     **sroutines_list_own_last;
+  uint     sroutines_list_own_elements;
 
   st_sp_chistics sp_chistics;
   bool only_view;       /* used for SHOW CREATE TABLE/VIEW */
@@ -978,6 +985,15 @@ typedef struct st_lex
   {
     return ( query_tables_own_last ? *query_tables_own_last : 0);
   }
+  void chop_off_not_own_tables()
+  {
+    if (query_tables_own_last)
+    {
+      *query_tables_own_last= 0;
+      query_tables_last= query_tables_own_last;
+      query_tables_own_last= 0;
+    }
+  }
   void cleanup_after_one_table_open();
 
   void push_context(Name_resolution_context *context)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 01a3c9e12a0..d9f5c178370 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4033,6 +4033,9 @@ end_with_restore_list:
       goto error;
     }
 
+    if (end_active_trans(thd)) 
+      goto error;
+
     if (!lex->sphead->m_db.str || !lex->sphead->m_db.str[0])
     {
       lex->sphead->m_db.length= strlen(thd->db);
@@ -4287,6 +4290,9 @@ end_with_restore_list:
 				 sp->m_name.str,
                                  lex->sql_command == SQLCOM_ALTER_PROCEDURE, 0))
 	  goto error;
+
+        if (end_active_trans(thd)) 
+          goto error;
 	memcpy(&lex->sp_chistics, &chistics, sizeof(lex->sp_chistics));
         if (!trust_routine_creators &&  mysql_bin_log.is_open() &&
             !sp->m_chistics->detistic &&
@@ -4346,6 +4352,9 @@ end_with_restore_list:
 	if (check_routine_access(thd, ALTER_PROC_ACL, db, name,
                                  lex->sql_command == SQLCOM_DROP_PROCEDURE, 0))
           goto error;
+
+        if (end_active_trans(thd)) 
+          goto error;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 	if (sp_automatic_privileges && !opt_noacl &&
 	    sp_revoke_privileges(thd, db, name, 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 84624d5d5c7..4cda4ed108a 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1103,30 +1103,39 @@ static int mysql_test_update(Prepared_statement *stmt,
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   uint          want_privilege;
 #endif
+  bool need_reopen;
   DBUG_ENTER("mysql_test_update");
 
   if (update_precheck(thd, table_list))
     goto error;
 
-  if (open_tables(thd, &table_list, &table_count, 0))
-    goto error;
-
-  if (table_list->multitable_view)
+  for ( ; ; )
   {
-    DBUG_ASSERT(table_list->view != 0);
-    DBUG_PRINT("info", ("Switch to multi-update"));
-    /* pass counter value */
-    thd->lex->table_count= table_count;
-    /* convert to multiupdate */
-    DBUG_RETURN(2);
+    if (open_tables(thd, &table_list, &table_count, 0))
+      goto error;
+
+    if (table_list->multitable_view)
+    {
+      DBUG_ASSERT(table_list->view != 0);
+      DBUG_PRINT("info", ("Switch to multi-update"));
+      /* pass counter value */
+      thd->lex->table_count= table_count;
+      /* convert to multiupdate */
+      DBUG_RETURN(2);
+    }
+
+    if (!lock_tables(thd, table_list, table_count, &need_reopen))
+      break;
+    if (!need_reopen)
+      goto error;
+    close_tables_for_reopen(thd, table_list);
   }
 
   /*
     thd->fill_derived_tables() is false here for sure (because it is
     preparation of PS, so we even do not check it).
   */
-  if (lock_tables(thd, table_list, table_count) ||
-      mysql_handle_derived(thd->lex, &mysql_derived_prepare))
+  if (mysql_handle_derived(thd->lex, &mysql_derived_prepare))
     goto error;
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index bd0683ff5af..07dfd77fb49 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1850,6 +1850,7 @@ TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
   List_iterator_fast<Item> it(*items);
   Item *item;
   Field *tmp_field;
+  bool not_used;
   DBUG_ENTER("create_table_from_items");
 
   tmp_table.alias= 0;
@@ -1908,8 +1909,15 @@ TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
       DBUG_RETURN(0);
   }
 
+  /*
+    FIXME: What happens if trigger manages to be created while we are
+           obtaining this lock ? May be it is sensible just to disable
+           trigger execution in this case ? Or will MYSQL_LOCK_IGNORE_FLUSH
+           save us from that ?
+  */
   table->reginfo.lock_type=TL_WRITE;
-  if (! ((*lock)= mysql_lock_tables(thd, &table, 1, MYSQL_LOCK_IGNORE_FLUSH)))
+  if (! ((*lock)= mysql_lock_tables(thd, &table, 1,
+                                    MYSQL_LOCK_IGNORE_FLUSH, &not_used)))
   {
     VOID(pthread_mutex_lock(&LOCK_open));
     hash_delete(&open_cache,(byte*) table);
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 053dfdfc990..7342c146045 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -511,6 +511,25 @@ bool Table_triggers_list::prepare_record1_accessors(TABLE *table)
 
 
 /*
+  Adjust Table_triggers_list with new TABLE pointer.
+
+  SYNOPSIS
+    set_table()
+      new_table - new pointer to TABLE instance
+*/
+
+void Table_triggers_list::set_table(TABLE *new_table)
+{
+  table= new_table;
+  for (Field **field= table->triggers->record1_field ; *field ; field++)
+  {
+    (*field)->table= (*field)->orig_table= new_table;
+    (*field)->table_name= &new_table->alias;
+  }
+}
+
+
+/*
   Check whenever .TRG file for table exist and load all triggers it contains.
 
   SYNOPSIS
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index d9b39cc3034..c1d1f8d0e9e 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -98,6 +98,8 @@ public:
     return test(bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE]);
   }
 
+  void set_table(TABLE *new_table);
+
   friend class Item_trigger_field;
   friend void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
                 Table_triggers_list *triggers);
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 8c71f395187..7e20817c7c7 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -134,25 +134,33 @@ int mysql_update(THD *thd,
   SQL_SELECT	*select;
   READ_RECORD	info;
   SELECT_LEX    *select_lex= &thd->lex->select_lex;
+  bool need_reopen;
   DBUG_ENTER("mysql_update");
 
   LINT_INIT(timestamp_query_id);
 
-  if (open_tables(thd, &table_list, &table_count, 0))
-    DBUG_RETURN(1);
-
-  if (table_list->multitable_view)
+  for ( ; ; )
   {
-    DBUG_ASSERT(table_list->view != 0);
-    DBUG_PRINT("info", ("Switch to multi-update"));
-    /* pass counter value */
-    thd->lex->table_count= table_count;
-    /* convert to multiupdate */
-    DBUG_RETURN(2);
+    if (open_tables(thd, &table_list, &table_count, 0))
+      DBUG_RETURN(1);
+
+    if (table_list->multitable_view)
+    {
+      DBUG_ASSERT(table_list->view != 0);
+      DBUG_PRINT("info", ("Switch to multi-update"));
+      /* pass counter value */
+      thd->lex->table_count= table_count;
+      /* convert to multiupdate */
+      DBUG_RETURN(2);
+    }
+    if (!lock_tables(thd, table_list, table_count, &need_reopen))
+      break;
+    if (!need_reopen)
+      DBUG_RETURN(1);
+    close_tables_for_reopen(thd, table_list);
   }
 
-  if (lock_tables(thd, table_list, table_count) ||
-      mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+  if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
       (thd->fill_derived_tables() &&
        mysql_handle_derived(thd->lex, &mysql_derived_filling)))
     DBUG_RETURN(1);
@@ -721,7 +729,6 @@ static table_map get_table_map(List<Item> *items)
 bool mysql_multi_update_prepare(THD *thd)
 {
   LEX *lex= thd->lex;
-  ulong opened_tables;
   TABLE_LIST *table_list= lex->query_tables;
   TABLE_LIST *tl, *leaves;
   List<Item> *fields= &lex->select_lex.item_list;
@@ -735,13 +742,16 @@ bool mysql_multi_update_prepare(THD *thd)
   uint  table_count= lex->table_count;
   const bool using_lock_tables= thd->locked_tables != 0;
   bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
+  bool need_reopen= FALSE;
   DBUG_ENTER("mysql_multi_update_prepare");
 
   /* following need for prepared statements, to run next time multi-update */
   thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
 
+reopen_tables:
+
   /* open tables and create derived ones, but do not lock and fill them */
-  if ((original_multiupdate &&
+  if (((original_multiupdate || need_reopen) &&
        open_tables(thd, &table_list, &table_count, 0)) ||
       mysql_handle_derived(lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE);
@@ -847,20 +857,17 @@ bool mysql_multi_update_prepare(THD *thd)
     }
   }
 
-  opened_tables= thd->status_var.opened_tables;
   /* now lock and fill tables */
-  if (lock_tables(thd, table_list, table_count))
-    DBUG_RETURN(TRUE);
-
-  /*
-    we have to re-call fixfields for fixed items, because lock maybe
-    reopened tables
-  */
-  if (opened_tables != thd->status_var.opened_tables)
+  if (lock_tables(thd, table_list, table_count, &need_reopen))
   {
+    if (!need_reopen)
+      DBUG_RETURN(TRUE);
+
     /*
-      Fields items cleanup(). There are only Item_fields in the list, so we
-      do not do Item tree walking
+      We have to reopen tables since some of them were altered or dropped
+      during lock_tables() or something was done with their triggers.
+      Let us do some cleanups to be able do setup_table() and setup_fields()
+      once again.
     */
     List_iterator_fast<Item> it(*fields);
     Item *item;
@@ -871,12 +878,8 @@ bool mysql_multi_update_prepare(THD *thd)
     for (TABLE_LIST *tbl= table_list; tbl; tbl= tbl->next_global)
       tbl->cleanup_items();
 
-    if (setup_tables(thd, &lex->select_lex.context,
-                     &lex->select_lex.top_join_list,
-                     table_list, &lex->select_lex.where,
-                     &lex->select_lex.leaf_tables, FALSE) ||
-        setup_fields_with_no_wrap(thd, 0, *fields, 2, 0, 0))
-      DBUG_RETURN(TRUE);
+    close_tables_for_reopen(thd, table_list);
+    goto reopen_tables;
   }
 
   /*