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, 63 insertions, 13 deletions

diff --git a/sql/lock.cc b/sql/lock.cc
index 4260a031def..34a2e202f8f 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -117,9 +117,64 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd, TABLE **tables, uint count,
   MYSQL_LOCK *sql_lock;
   TABLE *write_lock_used;
   int rc;
+  uint i;
+  bool log_table_write_query;
+  uint system_count;
+
   DBUG_ENTER("mysql_lock_tables");
 
   *need_reopen= FALSE;
+  system_count= 0;
+
+  log_table_write_query= (is_log_table_write_query(thd->lex->sql_command)
+                         || ((flags & MYSQL_LOCK_PERF_SCHEMA) != 0));
+
+  for (i=0 ; i<count; i++)
+  {
+    TABLE *t= tables[i];
+
+    /* Protect against 'fake' partially initialized TABLE_SHARE */
+    DBUG_ASSERT(t->s->table_category != TABLE_UNKNOWN_CATEGORY);
+
+    /*
+      Table I/O to performance schema tables is performed
+      only internally by the server implementation.
+      When a user is requesting a lock, the following
+      constraints are enforced:
+    */
+    if (t->s->require_write_privileges() &&
+        ! log_table_write_query)
+    {
+      /*
+        A user should not be able to prevent writes,
+        or hold any type of lock in a session,
+        since this would be a DOS attack.
+      */
+      if ((t->reginfo.lock_type >= TL_READ_NO_INSERT)
+          || (thd->lex->sql_command == SQLCOM_LOCK_TABLES))
+      {
+        my_error(ER_CANT_LOCK_LOG_TABLE, MYF(0));
+        DBUG_RETURN(0);
+      }
+    }
+
+    if ((t->s->table_category == TABLE_CATEGORY_SYSTEM) &&
+        (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE))
+    {
+      system_count++;
+    }
+  }
+
+  /*
+    Locking of system tables is restricted:
+    locking a mix of system and non-system tables in the same lock
+    is prohibited, to prevent contention.
+  */
+  if ((system_count > 0) && (system_count < count))
+  {
+    my_error(ER_WRONG_LOCK_OF_SYSTEM_TABLE, MYF(0));
+    DBUG_RETURN(0);
+  }
 
   for (;;)
   {
@@ -439,7 +494,8 @@ void mysql_lock_downgrade_write(THD *thd, TABLE *table,
 {
   MYSQL_LOCK *locked;
   TABLE *write_lock_used;
-  if ((locked = get_lock_data(thd,&table,1,1,&write_lock_used)))
+  if ((locked = get_lock_data(thd, &table, 1, GET_LOCK_UNLOCK,
+                              &write_lock_used)))
   {
     for (uint i=0; i < locked->lock_count; i++)
       thr_downgrade_write_lock(locked->locks[i], new_lock_type);
@@ -698,25 +754,19 @@ static MYSQL_LOCK *get_lock_data(THD *thd, TABLE **table_ptr, uint count,
   TABLE **to, **table_buf;
   DBUG_ENTER("get_lock_data");
 
+  DBUG_ASSERT((flags == GET_LOCK_UNLOCK) || (flags == GET_LOCK_STORE_LOCKS));
+
   DBUG_PRINT("info", ("count %d", count));
   *write_lock_used=0;
-  uint system_count= 0;
   for (i=tables=lock_count=0 ; i < count ; i++)
   {
-    if (table_ptr[i]->s->tmp_table != NON_TRANSACTIONAL_TMP_TABLE)
+    TABLE *t= table_ptr[i];
+
+    if (t->s->tmp_table != NON_TRANSACTIONAL_TMP_TABLE)
     {
-      tables+=table_ptr[i]->file->lock_count();
+      tables+= t->file->lock_count();
       lock_count++;
     }
-    /*
-      Check if we can lock the table. For some tables we cannot do that
-      beacause of handler-specific locking issues.
-    */
-    if (!table_ptr[i]-> file->
-          check_if_locking_is_allowed(thd->lex->sql_command, thd->lex->type,
-                                      table_ptr[i], count, i, &system_count,
-                                      logger.is_privileged_thread(thd)))
-      DBUG_RETURN(0);
   }
 
   /*