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.

4 files changed, 7 insertions, 8 deletions

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),