MDEV-136 Non-blocking "set read_only".

    Handle the 'set read_only=1' in lighter way, than the FLUSH TABLES READ LOCK;
    For the transactional engines we don't wait for operations on that tables to finish.

per-file comments:
 mysql-test/r/read_only_innodb.result
MDEV-136 Non-blocking "set read_only".
       test result updated.
 mysql-test/t/read_only_innodb.test
MDEV-136 Non-blocking "set read_only".
       test case added.
  sql/mysql_priv.h
MDEV-136 Non-blocking "set read_only".
        The close_cached_tables_set_readonly() declared.
  sql/set_var.cc
MDEV-136 Non-blocking "set read_only".
         Call close_cached_tables_set_readonly() for the read_only::set_var.
   sql/sql_base.cc
 MDEV-136 Non-blocking "set read_only".
         Parameters added to the close_cached_tables implementation,
         close_cached_tables_set_readonly declared.
         Prevent blocking on the transactional tables if the
         set_readonly_mode is on.

5 files changed, 85 insertions, 5 deletions

diff --git a/mysql-test/r/read_only_innodb.result b/mysql-test/r/read_only_innodb.result
index 690de085bf9..2633c54a307 100644
--- a/mysql-test/r/read_only_innodb.result
+++ b/mysql-test/r/read_only_innodb.result
@@ -43,6 +43,28 @@ a
 1
 COMMIT;
 UNLOCK TABLES;
+FLUSH STATUS;
+# Expected 0 at the beginning of the test
+show status like 'Opened_tables';
+Variable_name	Value
+Opened_tables	0
+connection con1;
+lock table t1 write;
+connection default;
+set global read_only=1;
+# Expected 1 as the slow_log was reopened
+show status like 'Opened_tables';
+Variable_name	Value
+Opened_tables	1
+connection con1;
+unlock tables;
+connection default;
+SET GLOBAL read_only=0;
+# Expected 2 as the slow_log was reopened
+show status like 'Opened_tables';
+Variable_name	Value
+Opened_tables	2
+UNLOCK TABLES;
 DROP TABLE t1;
 DROP USER test@localhost;
 echo End of 5.1 tests 
diff --git a/mysql-test/t/read_only_innodb.test b/mysql-test/t/read_only_innodb.test
index f8c25fdee1d..ec016c54f63 100644
--- a/mysql-test/t/read_only_innodb.test
+++ b/mysql-test/t/read_only_innodb.test
@@ -75,7 +75,36 @@ BEGIN;
 SELECT * FROM t1;
 COMMIT;
 
+#
+# Tests that LOCK TABLE doesn't block the SET READ_ONLY=1 for the InnoDB tables
+#
+
+connection default;
+UNLOCK TABLES;
+FLUSH STATUS;
+--echo # Expected 0 at the beginning of the test
+show status like 'Opened_tables';
+
+--echo connection con1;
+connection con1;
+lock table t1 write;
+
+--echo connection default;
 connection default;
+set global read_only=1;
+--echo # Expected 1 as the slow_log was reopened
+show status like 'Opened_tables';
+
+--echo connection con1;
+connection con1;
+unlock tables;
+
+--echo connection default;
+connection default;
+SET GLOBAL read_only=0;
+--echo # Expected 2 as the slow_log was reopened
+show status like 'Opened_tables';
+
 UNLOCK TABLES;
 DROP TABLE t1;
 DROP USER test@localhost;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 75f326cca63..815983122ef 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1945,6 +1945,7 @@ void close_performance_schema_table(THD *thd, Open_tables_state *backup);
 
 bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
                          bool wait_for_refresh, bool wait_for_placeholders);
+bool close_cached_tables_set_readonly(THD *thd);
 bool close_cached_connection_tables(THD *thd, bool wait_for_refresh,
                                     LEX_STRING *connect_string,
                                     bool have_lock = FALSE);
diff --git a/sql/set_var.cc b/sql/set_var.cc
index e6c1bf94135..eab66e690f8 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -4408,7 +4408,7 @@ bool sys_var_opt_readonly::update(THD *thd, set_var *var)
     can cause to wait on a read lock, it's required for the client application
     to unlock everything, and acceptable for the server to wait on all locks.
   */
-  if ((result= close_cached_tables(thd, NULL, FALSE, TRUE, TRUE)))
+  if ((result= close_cached_tables_set_readonly(thd)))
     goto end_with_read_lock;
 
   if ((result= make_global_read_lock_block_commit(thd)))
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 628a4bfd919..b8db5a581fd 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -862,8 +862,10 @@ void free_io_cache(TABLE *table)
           and tables must be NULL.
 */
 
-bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
-                         bool wait_for_refresh, bool wait_for_placeholders)
+static bool int_close_cached_tables(THD *thd, TABLE_LIST *tables,
+                         bool have_lock,
+                         bool wait_for_refresh, bool wait_for_placeholders,
+                         bool set_readonly_mode)
 {
   bool result=0;
   DBUG_ENTER("close_cached_tables");
@@ -873,7 +875,10 @@ bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
     VOID(pthread_mutex_lock(&LOCK_open));
   if (!tables)
   {
-    refresh_version++;				// Force close of open tables
+    /* No need to close the open tables if we just set the readonly state */
+    if (!set_readonly_mode)
+      refresh_version++;			// Force close of open tables
+
     while (unused_tables)
     {
 #ifdef EXTRA_DEBUG
@@ -933,7 +938,16 @@ bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
       for (uint idx=0 ; idx < open_cache.records ; idx++)
       {
         TABLE *table=(TABLE*) hash_element(&open_cache,idx);
-        if (table->in_use)
+        /*
+          We don't increment the refresh_version when set_readonly_mode,
+          but we still need non-transactional tables to be reopened.
+          So we set their versions as 'refresh_version - 1', which marks
+          them for the 'needs_reopen_or_table_lock()'
+        */
+        if (set_readonly_mode && !table->file->has_transactions())
+          table->s->version= 0;
+        if (table->in_use &&
+            (!set_readonly_mode || !table->file->has_transactions()))
           table->in_use->some_tables_deleted= 1;
       }
     }
@@ -1038,6 +1052,20 @@ bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
 }
 
 
+bool close_cached_tables(THD *thd, TABLE_LIST *tables, bool have_lock,
+                         bool wait_for_refresh, bool wait_for_placeholders)
+{
+  return int_close_cached_tables(thd, tables, have_lock, wait_for_refresh,
+                                  wait_for_placeholders, FALSE);
+}
+
+
+bool close_cached_tables_set_readonly(THD *thd)
+{
+  return int_close_cached_tables(thd, NULL, FALSE, TRUE, TRUE, TRUE);
+}
+
+
 /*
   Close all tables which match specified connection string or
   if specified string is NULL, then any table with a connection string.