Bug#12818255: READ-ONLY OPTION DOES NOT ALLOW

INSERTS/UPDATES ON TEMPORARY TABLES
Bug#14294223: CHANGES NOT ALLOWED TO TEMPORARY TABLES ON
READ-ONLY SERVERS

Problem:
========
Running 5.5.14 in read only we can create temporary tables
but can not insert or update records in the table. When we
try we get Error 1290 : The MySQL server is running with the
--read-only option so it cannot execute this statement.

Analysis:
=========
This bug is very specific to binlog being enabled and
binlog-format being stmt/mixed. Standalone server without
binlog enabled or with row based binlog-mode works fine.

How standalone server and row based replication work:
=====================================================
Standalone server and row based replication mark the
transactions as read_write only when they are modifying
non temporary tables as part of their current transaction.

Because of this when code enters commit phase it checks
if a transaction is read_write or not. If the transaction
is read_write and global read only mode is enabled those
transaction will fail with 'server is read only mode'
error.

In the case of statement based mode at the time of writing
to binary log a binlog handler is created and it is always
marked as read_write. In case of temporary tables even
though the engine did not mark the transaction as read_write
but the new transaction that is started by binlog handler is
considered as read_write.

Hence in this case when code enters commit phase it finds
one handler which has a read_write transaction even when
we are modifying temporary table. This causes the server
to throw an error when global read-only mode is enabled.

Fix:
====
At the time of commit in "ha_commit_trans" if a read_write
transaction is found, we should check if this transaction is
coming from a handler other than binlog_handler. This will
ensure that there is a genuine read_write transaction being
sent by the engine apart from binlog_handler and only then
it should be blocked.

6 files changed, 155 insertions, 8 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_dmls_on_tmp_tables_readonly.result b/mysql-test/suite/binlog/r/binlog_dmls_on_tmp_tables_readonly.result
new file mode 100644
index 00000000000..1dfac08e762
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_dmls_on_tmp_tables_readonly.result
@@ -0,0 +1,58 @@
+DROP TABLE IF EXISTS t1 ;
+# READ_ONLY does nothing to SUPER users
+# so we use a non-SUPER one:
+GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;
+connect  con1,localhost,test,,test;
+connection default;
+SET GLOBAL READ_ONLY=1;
+connection con1;
+CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
+# Test INSERTS with autocommit being off and on.
+BEGIN;
+INSERT INTO t1 VALUES (10);
+COMMIT;
+INSERT INTO t1 VALUES (20);
+# Test UPDATES with autocommit being off and on.
+BEGIN;
+UPDATE t1 SET a=30 WHERE a=10;
+COMMIT;
+UPDATE t1 SET a=40 WHERE a=20;
+connection default;
+SET GLOBAL READ_ONLY=0;
+# Test scenario where global read_only is enabled in the middle of transaction.
+# Test INSERT operations on temporary tables, INSERTs should be successful even
+# when global read_only is enabled.
+connection con1;
+BEGIN;
+INSERT INTO t1 VALUES(50);
+connection default;
+SET GLOBAL READ_ONLY=1;
+connection con1;
+SELECT @@GLOBAL.READ_ONLY;
+@@GLOBAL.READ_ONLY
+1
+COMMIT;
+connection default;
+SET GLOBAL READ_ONLY=0;
+# Test UPDATE operations on temporary tables, UPDATEs should be successful even
+# when global read_only is enabled.
+connection con1;
+BEGIN;
+UPDATE t1 SET a=60 WHERE a=50;
+connection default;
+SET GLOBAL READ_ONLY=1;
+connection con1;
+SELECT @@GLOBAL.READ_ONLY;
+@@GLOBAL.READ_ONLY
+1
+COMMIT;
+SELECT * FROM t1;
+a
+30
+40
+60
+# Clean up
+connection default;
+SET GLOBAL READ_ONLY=0;
+disconnect con1;
+DROP USER test@localhost;
diff --git a/mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test b/mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test
new file mode 100644
index 00000000000..30a6471bf61
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_dmls_on_tmp_tables_readonly.test
@@ -0,0 +1,90 @@
+# ==== Purpose ====
+#
+# Check that DMLs are allowed on temporary tables, when server is in read only
+# mode and binary log is enabled with binlog-format being stmt/mixed mode.
+#
+# ==== Implementation ====
+#
+# Start the server with binary log being enabled. Mark the server as read only.
+# Create a non-SUPER user and let the user to create a temporary table and
+# perform DML operations on that temporary table. DMLs should not be blocked
+# with a 'server read-only mode' error.
+#
+# ==== References ====
+#
+# Bug#12818255: READ-ONLY OPTION DOES NOT ALLOW INSERTS/UPDATES ON TEMPORARY
+# TABLES
+# Bug#14294223: CHANGES NOT ALLOWED TO TEMPORARY TABLES ON READ-ONLY SERVERS
+###############################################################################
+--source include/have_log_bin.inc
+--disable_warnings
+DROP TABLE IF EXISTS t1 ;
+--enable_warnings
+
+--enable_connect_log
+--echo # READ_ONLY does nothing to SUPER users
+--echo # so we use a non-SUPER one:
+GRANT CREATE, SELECT, DROP ON *.* TO test@localhost;
+
+connect (con1,localhost,test,,test);
+
+connection default;
+SET GLOBAL READ_ONLY=1;
+
+connection con1;
+CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
+
+--echo # Test INSERTS with autocommit being off and on.
+BEGIN;
+INSERT INTO t1 VALUES (10);
+COMMIT;
+INSERT INTO t1 VALUES (20);
+
+--echo # Test UPDATES with autocommit being off and on.
+BEGIN;
+UPDATE t1 SET a=30 WHERE a=10;
+COMMIT;
+UPDATE t1 SET a=40 WHERE a=20;
+
+connection default;
+SET GLOBAL READ_ONLY=0;
+
+--echo # Test scenario where global read_only is enabled in the middle of transaction.
+--echo # Test INSERT operations on temporary tables, INSERTs should be successful even
+--echo # when global read_only is enabled.
+connection con1;
+BEGIN;
+INSERT INTO t1 VALUES(50);
+
+connection default;
+SET GLOBAL READ_ONLY=1;
+
+connection con1;
+SELECT @@GLOBAL.READ_ONLY;
+COMMIT;
+
+connection default;
+SET GLOBAL READ_ONLY=0;
+
+--echo # Test UPDATE operations on temporary tables, UPDATEs should be successful even
+--echo # when global read_only is enabled.
+connection con1;
+BEGIN;
+UPDATE t1 SET a=60 WHERE a=50;
+
+connection default;
+SET GLOBAL READ_ONLY=1;
+
+connection con1;
+SELECT @@GLOBAL.READ_ONLY;
+COMMIT;
+
+SELECT * FROM t1;
+
+--echo # Clean up
+connection default;
+SET GLOBAL READ_ONLY=0;
+
+disconnect con1;
+DROP USER test@localhost;
+--disable_connect_log
diff --git a/sql/handler.cc b/sql/handler.cc
index 9d57cba73dc..79cf7ac2fd9 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -1279,7 +1279,7 @@ int ha_commit_trans(THD *thd, bool all)
       DEBUG_SYNC(thd, "ha_commit_trans_after_acquire_commit_lock");
     }
 
-    if (rw_trans &&
+    if (rw_trans && stmt_has_updated_trans_table(ha_info) &&
         opt_readonly &&
         !(thd->security_ctx->master_access & SUPER_ACL) &&
         !thd->slave_thread)
diff --git a/sql/log.cc b/sql/log.cc
index a7f05905514..e0ba93b0959 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -4562,17 +4562,15 @@ trans_has_updated_trans_table(const THD* thd)
   This function checks if a transactional table was updated by the
   current statement.
 
-  @param thd The client thread that executed the current statement.
+  @param ha_list Registered storage engine handler list.
   @return
     @c true if a transactional table was updated, @c false otherwise.
 */
 bool
-stmt_has_updated_trans_table(const THD *thd)
+stmt_has_updated_trans_table(Ha_trx_info* ha_list)
 {
   Ha_trx_info *ha_info;
-
-  for (ha_info= thd->transaction.stmt.ha_list; ha_info;
-       ha_info= ha_info->next())
+  for (ha_info= ha_list; ha_info; ha_info= ha_info->next())
   {
     if (ha_info->is_trx_read_write() && ha_info->ht() != binlog_hton)
       return (TRUE);
diff --git a/sql/log.h b/sql/log.h
index 7d1c3161ac2..dd09cb41026 100644
--- a/sql/log.h
+++ b/sql/log.h
@@ -25,7 +25,7 @@ class Master_info;
 class Format_description_log_event;
 
 bool trans_has_updated_trans_table(const THD* thd);
-bool stmt_has_updated_trans_table(const THD *thd);
+bool stmt_has_updated_trans_table(Ha_trx_info* ha_list);
 bool use_trans_cache(const THD* thd, bool is_transactional);
 bool ending_trans(THD* thd, const bool all);
 bool ending_single_stmt_trans(THD* thd, const bool all);
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 702cf1d575a..5dbeb1eb4b9 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -2637,7 +2637,8 @@ Query_log_event::Query_log_event(THD* thd_arg, const char* query_arg,
   {
     cache_type= Log_event::EVENT_NO_CACHE;
   }
-  else if (using_trans || trx_cache || stmt_has_updated_trans_table(thd) ||
+  else if (using_trans || trx_cache ||
+           stmt_has_updated_trans_table(thd->transaction.stmt.ha_list) ||
            thd->lex->is_mixed_stmt_unsafe(thd->in_multi_stmt_transaction_mode(),
                                           thd->variables.binlog_direct_non_trans_update,
                                           trans_has_updated_trans_table(thd),