WL#3984 (Revise locking of mysql.general_log and mysql.slow_log)

Bug#25422 (Hang with log tables)
Bug 17876 (Truncating mysql.slow_log in a SP after using cursor locks the
          thread)
Bug 23044 (Warnings on flush of a log table)
Bug 29129 (Resetting general_log while the GLOBAL READ LOCK is set causes
           a deadlock)

Prior to this fix, the server would hang when performing concurrent
ALTER TABLE or TRUNCATE TABLE statements against the LOG TABLES,
which are mysql.general_log and mysql.slow_log.

The root cause traces to the following code:
in sql_base.cc, open_table()
  if (table->in_use != thd)
  {
    /* wait_for_condition will unlock LOCK_open for us */
    wait_for_condition(thd, &LOCK_open, &COND_refresh);
  }
The problem with this code is that the current implementation of the
LOGGER creates 'fake' THD objects, like
- Log_to_csv_event_handler::general_log_thd
- Log_to_csv_event_handler::slow_log_thd
which are not associated to a real thread running in the server,
so that waiting for these non-existing threads to release table locks
cause the dead lock.

In general, the design of Log_to_csv_event_handler does not fit into the
general architecture of the server, so that the concept of general_log_thd
and slow_log_thd has to be abandoned:
- this implementation does not work with table locking
- it will not work with commands like SHOW PROCESSLIST
- having the log tables always opened does not integrate well with DDL
operations / FLUSH TABLES / SET GLOBAL READ_ONLY

With this patch, the fundamental design of the LOGGER has been changed to:
- always open and close a log table when writing a log
- remove totally the usage of fake THD objects
- clarify how locking of log tables is implemented in general.

See WL#3984 for details related to the new locking design.

Additional changes (misc bugs exposed and fixed):

1)

mysqldump which would ignore some tables in dump_all_tables_in_db(),
 but forget to ignore the same in dump_all_views_in_db().

2)

mysqldump would also issue an empty "LOCK TABLE" command when all the tables
to lock are to be ignored (numrows == 0), instead of not issuing the query.

3)

Internal errors handlers could intercept errors but not warnings
(see sql_error.cc).

4)

Implementing a nested call to open tables, for the performance schema tables,
exposed an existing bug in remove_table_from_cache(), which would perform:
  in_use->some_tables_deleted=1;
against another thread, without any consideration about thread locking.
This call inside remove_table_from_cache() was not required anyway,
since calling mysql_lock_abort() takes care of aborting -- cleanly -- threads
that might hold a lock on a table.
This line (in_use->some_tables_deleted=1) has been removed.


sql/handler.cc:
  Moved logic for system / log tables in the SQL layer.
sql/handler.h:
  Moved logic for system / log tables in the SQL layer.
sql/lock.cc:
  Revised locking of log tables
sql/log.cc:
  Major cleanup: changed how log tables are locked / written to.
sql/log.h:
  Major cleanup: changed how log tables are locked / written to.
sql/mysql_priv.h:
  performance schema helpers
sql/slave.cc:
  open_ltable() lock flags
sql/sp.cc:
  open_ltable() lock flags
sql/sql_acl.cc:
  open_ltable() lock flags
sql/sql_class.h:
  performance schema helpers
sql/sql_delete.cc:
  log tables cleanup in TRUNCATE
sql/sql_error.cc:
  Internal handlers can also intercept warnings
sql/sql_insert.cc:
  open_ltable() lock flags
sql/sql_parse.cc:
  performance schema helpers
sql/sql_plugin.cc:
  open_ltable() lock flags
sql/sql_rename.cc:
  log tables cleanup in RENAME
sql/sql_servers.cc:
  open_ltable() lock flags
sql/sql_show.cc:
  Move INFORMATION_SCHEMA_NAME to table.cc
sql/sql_table.cc:
  log tables cleanup (admin operations, ALTER TABLE)
sql/sql_udf.cc:
  open_ltable() lock flags
sql/table.cc:
  Implemented TABLE_CATEGORY.
sql/share/errmsg.txt:
  Changed the wording and name of ER_CANT_READ_LOCK_LOG_TABLE
sql/table.h:
  Implemented TABLE_CATEGORY.
storage/csv/ha_tina.cc:
  Moved logic for system / log tables in the SQL layer.
storage/csv/ha_tina.h:
  Moved logic for system / log tables in the SQL layer.
storage/myisam/ha_myisam.cc:
  Moved logic for system / log tables in the SQL layer.
storage/myisam/ha_myisam.h:
  Moved logic for system / log tables in the SQL layer.
client/mysqldump.c:
  Don't lock tables in the ignore list.
  Don't issue empty LOCK TABLES queries.
sql/sql_base.cc:
  log tables cleanup
  performance schema helpers
mysql-test/r/ps.result:
  Adjust test results
mysql-test/r/show_check.result:
  Adjust test results
mysql-test/r/status.result:
  Adjust test results
mysql-test/t/log_state.test:
  Added tests for Bug#29129
mysql-test/t/ps.test:
  Make the test output deterministic
mysql-test/t/show_check.test:
  Make the test output deterministic
mysql-test/r/log_state.result:
  Changed the default location of the log output to LOG_FILE,
  for backward compatibility with MySQL 5.0
  ---
  Adjust test results
mysql-test/r/log_tables.result:
  cleanup for -ps-protocol
mysql-test/t/log_tables.test:
  cleanup for -ps-protocol
sql/set_var.cc:
  Changed the default location of the log output to LOG_FILE,
  for backward compatibility with MySQL 5.0
  ---
  log tables cleanup

1 files changed, 97 insertions, 33 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0cd46a7c6fb..5b63eac35dd 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -902,8 +902,7 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
     bool found=0;
     for (TABLE_LIST *table= tables; table; table= table->next_local)
     {
-      if ((!table->table || !table->table->s->log_table) &&
-          remove_table_from_cache(thd, table->db, table->table_name,
+      if (remove_table_from_cache(thd, table->db, table->table_name,
                                   RTFC_OWNED_BY_THD_FLAG))
 	found=1;
     }
@@ -951,8 +950,7 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
           are employed by CREATE TABLE as in this case table simply does not
           exist yet.
         */
-	if (!table->s->log_table &&
-            (table->needs_reopen_or_name_lock() && table->db_stat))
+	if (table->needs_reopen_or_name_lock() && table->db_stat)
 	{
 	  found=1;
           DBUG_PRINT("signal", ("Waiting for COND_refresh"));
@@ -2468,8 +2466,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
                                  &state))
   {
     /*
-      Here we flush tables marked for flush. However we never flush log
-      tables here. They are flushed only on FLUSH LOGS.
+      Here we flush tables marked for flush.
       Normally, table->s->version contains the value of
       refresh_version from the moment when this table was
       (re-)opened and added to the cache.
@@ -2486,7 +2483,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
       c1: name lock t2; -- blocks
       c2: open t1; -- blocks
     */
-    if (table->needs_reopen_or_name_lock() && !table->s->log_table)
+    if (table->needs_reopen_or_name_lock())
     {
       DBUG_PRINT("note",
                  ("Found table '%s.%s' with different refresh version",
@@ -2962,10 +2959,9 @@ void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
   for (; table ; table=table->next)
   {
     /*
-      Reopen marked for flush. But close log tables. They are flushed only
-      explicitly on FLUSH LOGS
+      Reopen marked for flush.
     */
-    if (table->needs_reopen_or_name_lock() && !table->s->log_table)
+    if (table->needs_reopen_or_name_lock())
     {
       found=1;
       if (table->db_stat)
@@ -3012,10 +3008,6 @@ void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
   Wait until all threads has closed the tables in the list
   We have also to wait if there is thread that has a lock on this table even
   if the table is closed
-  NOTE: log tables are handled differently by the logging routines.
-        E.g. general_log is always opened and locked by the logger
-        and the table handler used by the logger, will be skipped by
-        this check.
 */
 
 bool table_is_used(TABLE *table, bool wait_for_name_lock)
@@ -3034,10 +3026,10 @@ bool table_is_used(TABLE *table, bool wait_for_name_lock)
          search= (TABLE*) hash_next(&open_cache, (uchar*) key,
                                     key_length, &state))
     {
-      DBUG_PRINT("info", ("share: 0x%lx  locked_by_logger: %d "
+      DBUG_PRINT("info", ("share: 0x%lx  "
                           "open_placeholder: %d  locked_by_name: %d "
                           "db_stat: %u  version: %lu",
-                          (ulong) search->s, search->locked_by_logger,
+                          (ulong) search->s,
                           search->open_placeholder, search->locked_by_name,
                           search->db_stat,
                           search->s->version));
@@ -3049,12 +3041,9 @@ bool table_is_used(TABLE *table, bool wait_for_name_lock)
         - If we are in flush table and we didn't execute the flush
         - If the table engine is open and it's an old version
         (We must wait until all engines are shut down to use the table)
-        However we fo not wait if we encountered a table, locked by the logger.
-        Log tables are managed separately by logging routines.
       */
-      if (!search->locked_by_logger &&
-          (search->locked_by_name && wait_for_name_lock ||
-           (search->is_name_opened() && search->needs_reopen_or_name_lock())))
+      if ( (search->locked_by_name && wait_for_name_lock) ||
+           (search->is_name_opened() && search->needs_reopen_or_name_lock()))
         DBUG_RETURN(1);
     }
   } while ((table=table->next));
@@ -3766,6 +3755,7 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
     thd			Thread handler
     table_list		Table to open is first table in this list
     lock_type		Lock to use for open
+    lock_flags          Flags passed to mysql_lock_table
 
   NOTE
     This function don't do anything like SP/SF/views/triggers analysis done
@@ -3781,7 +3771,8 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
       table_list->table		table
 */
 
-TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
+TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type,
+                   uint lock_flags)
 {
   TABLE *table;
   bool refresh;
@@ -3816,8 +3807,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,
-                                            &refresh)))
+	if (! (thd->lock= mysql_lock_tables(thd, &table_list->table, 1,
+                                            lock_flags, &refresh)))
 	  table= 0;
     }
   }
@@ -4156,11 +4147,6 @@ int lock_tables(THD *thd, TABLE_LIST *tables, uint count, bool *need_reopen)
     DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
     TABLE **start,**ptr;
     uint lock_flag= MYSQL_LOCK_NOTIFY_IF_NEED_REOPEN;
-    
-    /* Ignore GLOBAL READ LOCK and GLOBAL READ_ONLY if called from a logger */
-    if (logger.is_privileged_thread(thd))
-      lock_flag|= (MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK |
-                   MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY);
 
     if (!(ptr=start=(TABLE**) thd->alloc(sizeof(TABLE*)*count)))
       DBUG_RETURN(-1);
@@ -7189,7 +7175,6 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table_name,
       else if (in_use != thd)
       {
         DBUG_PRINT("info", ("Table was in use by other thread"));
-        in_use->some_tables_deleted=1;
         if (table->is_name_opened())
         {
           DBUG_PRINT("info", ("Found another active instance of the table"));
@@ -7625,7 +7610,7 @@ open_system_tables_for_read(THD *thd, TABLE_LIST *table_list,
     if (!table)
       goto error;
 
-    DBUG_ASSERT(table->s->system_table);
+    DBUG_ASSERT(table->s->table_category == TABLE_CATEGORY_SYSTEM);
 
     table->use_all_columns();
     table->reginfo.lock_type= tables->lock_type;
@@ -7692,12 +7677,91 @@ open_system_table_for_update(THD *thd, TABLE_LIST *one_table)
 {
   DBUG_ENTER("open_system_table_for_update");
 
-  TABLE *table= open_ltable(thd, one_table, one_table->lock_type);
+  TABLE *table= open_ltable(thd, one_table, one_table->lock_type, 0);
   if (table)
   {
-    DBUG_ASSERT(table->s->system_table);
+    DBUG_ASSERT(table->s->table_category == TABLE_CATEGORY_SYSTEM);
     table->use_all_columns();
   }
 
   DBUG_RETURN(table);
 }
+
+/**
+  Open a performance schema table.
+  Opening such tables is performed internally in the server
+  implementation, and is a 'nested' open, since some tables
+  might be already opened by the current thread.
+  The thread context before this call is saved, and is restored
+  when calling close_performance_schema_table().
+  @param thd The current thread
+  @param one_table Performance schema table to open
+  @param backup [out] Temporary storage used to save the thread context
+*/
+TABLE *
+open_performance_schema_table(THD *thd, TABLE_LIST *one_table,
+                              Open_tables_state *backup)
+{
+  uint flags= ( MYSQL_LOCK_IGNORE_GLOBAL_READ_LOCK
+              | MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY
+              | MYSQL_LOCK_PERF_SCHEMA );
+
+  DBUG_ENTER("open_performance_schema_table");
+
+  thd->reset_n_backup_open_tables_state(backup);
+
+  TABLE *table= open_ltable(thd, one_table, one_table->lock_type, flags);
+  if (table)
+  {
+    DBUG_ASSERT(table->s->table_category == TABLE_CATEGORY_PERFORMANCE);
+    /* Make sure all columns get assigned to a default value */
+    table->use_all_columns();
+  }
+
+  DBUG_RETURN(table);
+}
+
+/**
+  Close a performance schema table.
+  The last table opened by open_performance_schema_table()
+  is closed, then the thread context is restored.
+  @param thd The current thread
+  @param backup [in] the context to restore.
+*/
+void close_performance_schema_table(THD *thd, Open_tables_state *backup)
+{
+  bool found_old_table;
+
+  if (thd->lock)
+  {
+    /*
+      Note:
+      We do not create explicitly a separate transaction for the
+      performance table I/O, but borrow the current transaction.
+      lock + unlock will autocommit the change done in the
+      performance schema table: this is the expected result.
+      The current transaction should not be affected by this code.
+      TODO: Note that if a transactional engine is used for log tables,
+      this code will need to be revised, as a separate transaction
+      might be needed.
+    */
+    mysql_unlock_tables(thd, thd->lock);
+    thd->lock= 0;
+  }
+
+  safe_mutex_assert_not_owner(&LOCK_open);
+
+  pthread_mutex_lock(&LOCK_open);
+
+  found_old_table= false;
+  while (thd->open_tables)
+    found_old_table|= close_thread_table(thd, &thd->open_tables);
+
+  if (found_old_table)
+    broadcast_refresh();
+
+  pthread_mutex_unlock(&LOCK_open);
+
+  thd->restore_backup_open_tables_state(backup);
+}
+