MDEV-9266 Creating index on temporaray table breaks replicationbb-mdev-9266

Problem:- Create index was logged into binlog.

Goal:- Operation on temporary table should not be binlog when binlog format
is row.

Solution:-
1st- We should add CF_FORCE_ORIGINAL_BINLOG_FORMAT when there
 is ddl on temp table.
2nd- For optimize, analyze and repair we dont check if binlog format is row
 and this is tmp table, we dont need to log that.

4 files changed, 162 insertions, 12 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_tmp_table_row.result b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result
new file mode 100644
index 00000000000..ab90a36b50a
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_tmp_table_row.result
@@ -0,0 +1,41 @@
+RESET MASTER;
+create temporary table t1(a int, b int);
+#Add index test
+create index index_a on t1(a);
+alter table t1 add index index_b(b);
+insert into t1 values(1,2);
+#drop index test
+drop index index_a on t1;
+drop index index_b on t1;
+#Analyze test
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+#Optimize test
+optimize table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	optimize	status	Table is already up to date
+#Repair test
+repair table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+#Check test
+check table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+#Checksum test
+checksum table t1;
+Table	Checksum
+test.t1	3137317242
+analyze table non_existing;
+Table	Op	Msg_type	Msg_text
+test.non_existing	analyze	Error	Table 'test.non_existing' doesn't exist
+test.non_existing	analyze	status	Operation failed
+optimize table non_existing;
+Table	Op	Msg_type	Msg_text
+test.non_existing	optimize	Error	Table 'test.non_existing' doesn't exist
+test.non_existing	optimize	status	Operation failed
+repair table non_existing;
+Table	Op	Msg_type	Msg_text
+test.non_existing	repair	Error	Table 'test.non_existing' doesn't exist
+test.non_existing	repair	status	Operation failed
diff --git a/mysql-test/suite/binlog/t/binlog_tmp_table_row.test b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test
new file mode 100644
index 00000000000..07ee009b9c3
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_tmp_table_row.test
@@ -0,0 +1,85 @@
+# ==== Purpose ====
+#
+# Test if statements used temporary tables are not binlogged in the case of
+# binlog_format=row
+#
+# ==== Method ====
+#
+# We will see if binlog file size is increased or not, It should be constant for the
+# entire period of test.
+#
+# ==== Related bugs ====
+#
+# Mdev-9266
+#
+source include/have_log_bin.inc;
+source include/have_binlog_format_row.inc;
+
+RESET MASTER;
+
+--let $binlog_size= query_get_value(show binary logs, File_size, 1)
+create temporary table t1(a int, b int);
+
+--echo #Add index test
+create index index_a on t1(a);
+alter table t1 add index index_b(b);
+insert into t1 values(1,2);
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+
+--echo #drop index test
+drop index index_a on t1;
+drop index index_b on t1;
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+
+--echo #Analyze test
+analyze table t1;
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+
+--echo #Optimize test
+optimize table t1;
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+
+--echo #Repair test
+repair table t1;
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+
+--echo #Check test
+check table t1;
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+--echo #Checksum test
+checksum table t1;
+
+--let $current_size= query_get_value(show binary logs, File_size, 1)
+if ($binlog_size != $current_size) {
+  die "Temp table written into binlog";
+}
+
+
+#should not crash the server
+analyze table non_existing;
+optimize table non_existing;
+repair table non_existing;
diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc
index 06a453e1bb7..9310e710bad 100644
--- a/sql/sql_admin.cc
+++ b/sql/sql_admin.cc
@@ -1197,7 +1197,10 @@ bool Sql_cmd_analyze_table::execute(THD *thd)
                          "analyze", lock_type, 1, 0, 0, 0,
                          &handler::ha_analyze, 0);
   /* ! we write after unlocking the table */
-  if (!res && !m_lex->no_write_to_binlog)
+  if (!res && !m_lex->no_write_to_binlog &&
+          !(thd->is_current_stmt_binlog_format_row() &&
+              first_table->table &&
+              first_table->table->s->tmp_table != NO_TMP_TABLE ))
   {
     /*
       Presumably, ANALYZE and binlog writing doesn't require synchronization
@@ -1254,7 +1257,10 @@ bool Sql_cmd_optimize_table::execute(THD *thd)
                       "optimize", TL_WRITE, 1, 0, 0, 0,
                       &handler::ha_optimize, 0);
   /* ! we write after unlocking the table */
-  if (!res && !m_lex->no_write_to_binlog)
+  if (!res && !m_lex->no_write_to_binlog &&
+          !(thd->is_current_stmt_binlog_format_row() &&
+              first_table->table &&
+              first_table->table->s->tmp_table != NO_TMP_TABLE ))
   {
     /*
       Presumably, OPTIMIZE and binlog writing doesn't require synchronization
@@ -1287,7 +1293,10 @@ bool Sql_cmd_repair_table::execute(THD *thd)
                          &handler::ha_repair, &view_repair);
 
   /* ! we write after unlocking the table */
-  if (!res && !m_lex->no_write_to_binlog)
+  if (!res && !m_lex->no_write_to_binlog &&
+          !(thd->is_current_stmt_binlog_format_row() &&
+              first_table->table &&
+              first_table->table->s->tmp_table != NO_TMP_TABLE ))
   {
     /*
       Presumably, REPAIR and binlog writing doesn't require synchronization
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 4c0be4ebc8b..75f52ed9b87 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -448,6 +448,16 @@ void init_update_queries(void)
   sql_command_flags[SQLCOM_EXECUTE]=   CF_CAN_GENERATE_ROW_EVENTS;
 
   /*
+    The following admin table operations are allowed
+    on log tables.
+  */
+  sql_command_flags[SQLCOM_REPAIR]=    CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
+  sql_command_flags[SQLCOM_OPTIMIZE]|= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
+  sql_command_flags[SQLCOM_ANALYZE]=   CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
+  sql_command_flags[SQLCOM_CHECK]=     CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
+  sql_command_flags[SQLCOM_CHECKSUM]=  CF_REPORT_PROGRESS;
+
+  /*
     We don't want to change to statement based replication for these commands
   */
   sql_command_flags[SQLCOM_ROLLBACK]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
@@ -457,18 +467,23 @@ void init_update_queries(void)
   sql_command_flags[SQLCOM_TRUNCATE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
   /* We don't want to replicate DROP for temp tables in row format */
   sql_command_flags[SQLCOM_DROP_TABLE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  /* We don't want to replicate CREATE/DROP INDEX for temp tables in row format */
+  sql_command_flags[SQLCOM_CREATE_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  sql_command_flags[SQLCOM_DROP_INDEX]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  /* We don't want to replicate OPTIMIZE TABLE for temp tables in row format */
+  sql_command_flags[SQLCOM_OPTIMIZE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  /* We don't want to replicate ANALYZE TABLE for temp tables in row format */
+  sql_command_flags[SQLCOM_ANALYZE]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  /* We don't want to replicate REPAIR TABLE for temp tables in row format */
+  sql_command_flags[SQLCOM_REPAIR]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  /* We don't want to replicate CHECK TABLE for temp tables in row format */
+  sql_command_flags[SQLCOM_CHECK]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+  /* We don't want to replicate CHECKSUM TABLE for temp tables in row format */
+  sql_command_flags[SQLCOM_CHECKSUM]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
+
   /* One can change replication mode with SET */
   sql_command_flags[SQLCOM_SET_OPTION]|= CF_FORCE_ORIGINAL_BINLOG_FORMAT;
 
-  /*
-    The following admin table operations are allowed
-    on log tables.
-  */
-  sql_command_flags[SQLCOM_REPAIR]=    CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
-  sql_command_flags[SQLCOM_OPTIMIZE]|= CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
-  sql_command_flags[SQLCOM_ANALYZE]=   CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
-  sql_command_flags[SQLCOM_CHECK]=     CF_WRITE_LOGS_COMMAND | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
-  sql_command_flags[SQLCOM_CHECKSUM]=  CF_REPORT_PROGRESS;
 
   sql_command_flags[SQLCOM_CREATE_USER]|=       CF_AUTO_COMMIT_TRANS;
   sql_command_flags[SQLCOM_DROP_USER]|=         CF_AUTO_COMMIT_TRANS;