Fix for bug #10055 "Using stored function with information_schema causes empty

result set".

To enable full access to contents of I_S tables from stored functions
or statements that use them, we manipulate with thread's open tables
state and ensure that we won't cause deadlock when we open tables by
ignoring flushes and name-locks.
Building of contents of I_S.TABLES no longer requires locking of tables
since we use use handler::info() method with HA_STATUS_AUTO flag instead
of handler::update_auto_increment() for obtaining information about
auto-increment values. But this also means that handlers have to implement
support for HA_STATUS_AUTO flag (particularly InnoDB needs it).


mysql-test/r/alter_table.result:
  Updated test results. This change was caused by the fact that now when
  we build contents of I_S tables (and thus output of SHOW INDEX) we
  don't use instances of tables which may be already opened and locked
  by thread (we always use new instance).
mysql-test/r/information_schema.result:
  Added test which checks how information about current auto-increment value for
  table is reported in INFORMATION_SCHEMA.TABLES view.
mysql-test/r/sp.result:
  Added test for bug #10055 "Using stored function with information_schema causes
  empty result set".
mysql-test/t/information_schema.test:
  Added test which checks how information about current auto-increment value for
  table is reported in INFORMATION_SCHEMA.TABLES view.
mysql-test/t/sp.test:
  Added test for bug #10055 "Using stored function with information_schema causes
  empty result set".
sql/mysql_priv.h:
  close_thread_tables():
    Get rid of 'stopper' argument which is no longer used. Now when we need
    to open and then close some table without touching tables which are already
    opened we use THD::reset_n/restore_backup_open_tables_state() methods.
  open_tables()/open_normal_and_derived_tables():
    Added 'flags' argument to be able open tables even if some has done
    a flush or hold namelock on them.
sql/sp.cc:
  close_proc_table/open_proc_table_for_read/db_find_routine():
    Replaced push_open_tables_state/pop_open_tables_state() methods which
    were saving/restoring current open tables state in/from THD::open_state_list
    with reset_n_backup_open_tables_state/restore_backup_open_tables_state()
    methods which assume that backup storage for this state is allocated on
    stack (or elsewhere) by their caller.
  open_proc_table_for_read():
    Since now we can have several open tables states stacked up we can't rely
    rely on checking whether we have some tables open in previous state.
    Instead we always assume that some tables are open and we need to ignore
    flush while locking mysql.proc. We don't really need 
    MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK in this case since we open mysql.proc table
    only for reading.
sql/sp.h:
  Added declarations of open_proc_table_for_read()/close_proc_table() to be
  able to use them in sql_show.cc.
sql/sql_base.cc:
  close_thread_tables():
    Get rid of 'stopper' argument which is no longer used. Now when we need
    to open and then close some table without touching tables which are already
    opened we use THD::reset_n/restore_backup_open_tables_state() methods.
  open_tables()/open_normal_and_derived_tables():
    Added 'flags' argument to be able open tables even if some has done
    a flush or hold namelock on them.
sql/sql_class.cc:
  Open_tables_state, THD:
    Replaced push_open_tables_state/pop_open_tables_state() methods which
    were saving/restoring current open tables state in/from THD::open_state_list
    with reset_n_backup_open_tables_state/restore_backup_open_tables_state()
    methods which assume that backup storage for this state is allocated on
    stack (or elsewhere) by their caller.
sql/sql_class.h:
  Open_tables_state, THD:
    Replaced push_open_tables_state/pop_open_tables_state() methods which
    were saving/restoring current open tables state in/from THD::open_state_list
    with reset_n_backup_open_tables_state/restore_backup_open_tables_state()
    methods which assume that backup storage for this state is allocated on
    stack (or elsewhere) by their caller.
sql/sql_handler.cc:
  open_tables()/open_normal_and_derived_tables():
    Added 'flags' argument to be able open tables even if some has done
    a flush or hold namelock on them.
sql/sql_prepare.cc:
  open_tables()/open_normal_and_derived_tables():
    Added 'flags' argument to be able open tables even if some has done
    a flush or hold namelock on them.
sql/sql_show.cc:
  get_all_tables():
    Now we use THD::reset_n_/restore_backup_open_tables_state() for 
    saving/restoring open tables state instead of working with it directly
    (This also allows us to have proper content of I_S system tables in
    statements with stored functions and in stored functions). We also
    ignore possible flushes when opening tables (we may create deadlock
    otherwise). Also we do all needed manipulations with LEX in this function
    and not in get_schema_tables_result() now.
  get_schema_tables_record():
    Let us use handler::info() method with HA_STATUS_AUTO flag for obtaining
    information about table's auto-increment value. This allows to avoid locking
    of tables which is needed when we use handler::update_auto_increment() method.
  fill_schema_proc():
    Now we use open_proc_table_for_read/close_proc_table() for access to
    mysql.proc table (so we won't cause deadlock if we already have some
    tables open and locked, this also allows us to have proper content in
    ROUTINES system table in statements using stored functions/in stored
    functions).
  get_schema_tables_result():
    Moved all manipulations with Open_tables_state and LEX needed for
    safe opening of tables to ST_SCHEMA_TABLE::fill_table functions
    (i.e. get_all_tables() and fill_schema_proc()).
sql/sql_update.cc:
  open_tables()/open_normal_and_derived_tables():
    Added 'flags' argument to be able open tables even if some has done
    a flush or hold namelock on them.

1 files changed, 7 insertions, 0 deletions

diff --git a/sql/sp.h b/sql/sp.h
index b8af8d3a321..29ba5625e37 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -90,6 +90,13 @@ void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
 
 extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first);
 
+/*
+  Routines which allow open/lock and close mysql.proc table even when
+  we already have some tables open and locked.
+*/
+TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup);
+void close_proc_table(THD *thd, Open_tables_state *backup);
+
 //
 // Utilities...
 //