MDEV-27832 disable binary logging for SQL SERVICE.

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.

2 files changed, 50 insertions, 0 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;
+