MDEV-27832 disable binary logging for SQL SERVICE.bb-10.4-MDEV-27233

Binary logging is now disabled for the queries run by SQL SERVICE.
The binlogging can be turned on with the 'SET SQL_LOG_BIN=On' query.

Conflicts:
	sql/sql_prepare.cc

6 files changed, 80 insertions, 11 deletions

diff --git a/mysql-test/suite/plugins/r/test_sql_service.result b/mysql-test/suite/plugins/r/test_sql_service.result
index 00f0411b665..ca7326de51c 100644
--- a/mysql-test/suite/plugins/r/test_sql_service.result
+++ b/mysql-test/suite/plugins/r/test_sql_service.result
@@ -2,6 +2,26 @@ install plugin test_sql_service soname 'test_sql_service';
 show status like 'test_sql_service_passed';
 Variable_name	Value
 Test_sql_service_passed	1
+set global test_sql_service_execute_sql_global= 'create table test.t1 select 1 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_local=  'insert into test.t1 select 2 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_global= 'SET SQL_LOG_BIN=1';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 3 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_global= 'SET SQL_LOG_BIN=0';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 4 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_global= 'SET sql_auto_is_null=1';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 5 as a, @@sql_auto_is_null';
+set global test_sql_service_execute_sql_global= 'SET sql_auto_is_null=0';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 6 as a, @@sql_auto_is_null';
+select * from t1 order by a;
+a	@@SQL_LOG_BIN
+1	0
+2	0
+3	1
+4	0
+5	1
+6	0
+drop table t1;
+SET SQL_LOG_BIN=0;
 set global test_sql_service_run_test= 1;
 show status like 'test_sql_service_passed';
 Variable_name	Value
@@ -68,3 +88,12 @@ drop table t1;
 uninstall plugin test_sql_service;
 Warnings:
 Warning	1620	Plugin is busy and will be uninstalled on shutdown
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Annotate_rows	#	#	insert into test.t1 select 3 as a, @@SQL_LOG_BIN
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE `t1` /* generated by server */
diff --git a/mysql-test/suite/plugins/t/test_sql_service.test b/mysql-test/suite/plugins/t/test_sql_service.test
index b80d78fe6e5..3f2aaa81c4c 100644
--- a/mysql-test/suite/plugins/t/test_sql_service.test
+++ b/mysql-test/suite/plugins/t/test_sql_service.test
@@ -1,4 +1,5 @@
 --source include/not_embedded.inc
+--source include/have_log_bin.inc
 
 if (!$TEST_SQL_SERVICE_SO) {
   skip No TEST_SQL_SERVICE plugin;
@@ -11,6 +12,20 @@ source include/wait_until_count_sessions.inc;
 install plugin test_sql_service soname 'test_sql_service';
 show status like 'test_sql_service_passed';
 
+set global test_sql_service_execute_sql_global= 'create table test.t1 select 1 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_local=  'insert into test.t1 select 2 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_global= 'SET SQL_LOG_BIN=1';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 3 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_global= 'SET SQL_LOG_BIN=0';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 4 as a, @@SQL_LOG_BIN';
+set global test_sql_service_execute_sql_global= 'SET sql_auto_is_null=1';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 5 as a, @@sql_auto_is_null';
+set global test_sql_service_execute_sql_global= 'SET sql_auto_is_null=0';
+set global test_sql_service_execute_sql_global= 'insert into test.t1 select 6 as a, @@sql_auto_is_null';
+select * from t1 order by a;
+drop table t1;
+SET SQL_LOG_BIN=0;
+
 set global test_sql_service_run_test= 1;
 show status like 'test_sql_service_passed';
 
@@ -58,3 +73,9 @@ drop table t1;
 
 uninstall plugin test_sql_service;
 
+# Check that statements were executed/binlogged in correct order.
+source include/show_binlog_events.inc;
+# --replace_column 2 # 5 #
+# --replace_regex /xid=[0-9]+/xid=XX/ /GTID [0-9]+-[0-9]+-[0-9]+/GTID #-#-#/
+# SHOW BINLOG EVENTS LIMIT 3,100;
+
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 21546ddaebf..5606bd0c4ac 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1284,10 +1284,7 @@ void THD::init()
   wsrep_desynced_backup_stage= false;
 #endif /* WITH_WSREP */
 
-  if (variables.sql_log_bin)
-    variables.option_bits|= OPTION_BIN_LOG;
-  else
-    variables.option_bits&= ~OPTION_BIN_LOG;
+  set_binlog_bit();
 
   variables.sql_log_bin_off= 0;
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 7e4fd7749f4..c57f656b1f2 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2832,6 +2832,13 @@ public:
     auto_inc_intervals_forced.empty(); // in case of multiple SET INSERT_ID
     auto_inc_intervals_forced.append(next_id, ULONGLONG_MAX, 0);
   }
+  inline void set_binlog_bit()
+  {
+    if (variables.sql_log_bin)
+      variables.option_bits |= OPTION_BIN_LOG;
+    else
+      variables.option_bits &= ~OPTION_BIN_LOG;
+  }
 
   ulonglong  limit_found_rows;
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 6954ffa3a38..af5dde543d4 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -5350,12 +5350,26 @@ public:
   Security_context empty_ctx;
   ulonglong client_capabilities;
 
+  my_bool do_log_bin;
+
   Protocol_local(THD *thd_arg, THD *new_thd_arg, ulong prealloc) :
     Protocol_text(thd_arg, prealloc),
     cur_data(0), first_data(0), data_tail(&first_data), alloc(0),
-    new_thd(new_thd_arg)
+    new_thd(new_thd_arg), do_log_bin(FALSE)
   {}
  
+  void set_binlog_vars(my_bool *sav_log_bin)
+  {
+    *sav_log_bin= thd->variables.sql_log_bin;
+    thd->variables.sql_log_bin= do_log_bin;
+    thd->set_binlog_bit();
+  }
+  void restore_binlog_vars(my_bool sav_log_bin)
+  {
+    do_log_bin= thd->variables.sql_log_bin;
+    thd->variables.sql_log_bin= sav_log_bin;
+    thd->set_binlog_bit();
+  }
 protected:
   bool net_store_data(const uchar *from, size_t length);
   bool net_store_data_cs(const uchar *from, size_t length,
@@ -5972,6 +5986,9 @@ loc_advanced_command(MYSQL *mysql, enum enum_server_command command,
     Security_context *ctx_orig= p->thd->security_ctx;
     ulonglong cap_orig= p->thd->client_capabilities;
     MYSQL_LEX_STRING sql_text;
+    my_bool log_bin_orig;
+    p->set_binlog_vars(&log_bin_orig);
+
     DBUG_ASSERT(current_thd == p->thd);
     sql_text.str= (char *) arg;
     sql_text.length= arg_length;
@@ -5980,6 +5997,7 @@ loc_advanced_command(MYSQL *mysql, enum enum_server_command command,
     result= con.execute_direct(p, sql_text);
     p->thd->client_capabilities= cap_orig;
     p->thd->security_ctx= ctx_orig;
+    p->restore_binlog_vars(log_bin_orig);
   }
   if (skip_check)
     result= 0;
@@ -6139,8 +6157,8 @@ extern "C" MYSQL *mysql_real_connect_local(MYSQL *mysql)
     new_thd->query_cache_is_applicable= 0;
     new_thd->variables.wsrep_on= 0;
     new_thd->client_capabilities= client_flag;
-    new_thd->variables.option_bits&= ~OPTION_BIN_LOG;
-    new_thd->variables.sql_log_bin_off= 1;
+    new_thd->variables.sql_log_bin= 0;
+    new_thd->set_binlog_bit();
     /*
       TOSO: decide if we should turn the auditing off
       for such threads.
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index 31f29042388..76866e8f567 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -4213,10 +4213,7 @@ static bool fix_sql_log_bin_after_update(sys_var *self, THD *thd,
 {
   DBUG_ASSERT(type == OPT_SESSION);
 
-  if (thd->variables.sql_log_bin)
-    thd->variables.option_bits |= OPTION_BIN_LOG;
-  else
-    thd->variables.option_bits &= ~OPTION_BIN_LOG;
+  thd->set_binlog_bit();
 
   return FALSE;
 }