Re: [Maria-developers] 9d6be81c0da: MDEV-27832 disable binary logging for SQL SERVICE.
Hi, Alexey, On Mar 02, Alexey Botchkov wrote:
revision-id: 9d6be81c0da (mariadb-10.7.2-8-g9d6be81c0da) parent(s): 33fd136c61b author: Alexey Botchkov committer: Alexey Botchkov timestamp: 2022-02-15 13:37:59 +0400 message:
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.
diff --git a/mysql-test/suite/plugins/r/test_sql_service.result b/mysql-test/suite/plugins/r/test_sql_service.result index 00f0411b665..943bb239e5f 100644 --- a/mysql-test/suite/plugins/r/test_sql_service.result +++ b/mysql-test/suite/plugins/r/test_sql_service.result @@ -2,6 +2,19 @@ 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'; +select * from t1 order by a;
this is not a test. Please, enable binlog and see whether queries are actually logged. Also, add a test for, say, sql_auto_is_null: set global test_sql_service_execute_sql_global='set sql_auto_is_null=1'; set global test_sql_service_execute_sql_global='insert test.t1 select 5, @@sql_auto_is_null'; select * from t1 where a=5;
+a @@SQL_LOG_BIN +1 0 +2 0 +3 1 +4 0 +drop table t1; set global test_sql_service_run_test= 1; show status like 'test_sql_service_passed'; Variable_name Value diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index e025147c71e..48681ac0b6f 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -5617,12 +5617,30 @@ class Protocol_local : public Protocol_text THD *new_thd; Security_context empty_ctx;
+ 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, ulonglong *sav_bits) + { + *sav_log_bin= thd->variables.sql_log_bin; + *sav_bits= thd->variables.option_bits; + + if ((thd->variables.sql_log_bin= do_log_bin)) + thd->variables.option_bits|= OPTION_BIN_LOG; + else + thd->variables.option_bits&= ~OPTION_BIN_LOG; + } + void restore_binlog_vars(my_bool &sav_log_bin, ulonglong &sav_bits) + { + do_log_bin= thd->variables.sql_log_bin; + thd->variables.sql_log_bin= sav_log_bin; + thd->variables.option_bits= sav_bits;
that's what sql_auto_is_null test is for.You destroy all other option_bits here. Better don't save/restore option_bits, but use the same if() as in set_binlog_vars(). Or, even better, reuse fix_sql_log_bin_after_update(). I'd suggest to make it a method in THD
+ } protected: bool net_store_data(const uchar *from, size_t length); bool net_store_data_cs(const uchar *from, size_t length, @@ -6230,12 +6248,17 @@ loc_advanced_command(MYSQL *mysql, enum enum_server_command command, Ed_connection con(p->thd); Security_context *ctx_orig= p->thd->security_ctx; MYSQL_LEX_STRING sql_text; + my_bool log_bin_orig; + ulonglong option_bits_orig; + p->set_binlog_vars(&log_bin_orig, &option_bits_orig); + DBUG_ASSERT(current_thd == p->thd); sql_text.str= (char *) arg; sql_text.length= arg_length; p->thd->security_ctx= &p->empty_ctx; result= con.execute_direct(p, sql_text); p->thd->security_ctx= ctx_orig; + p->restore_binlog_vars(log_bin_orig, option_bits_orig); } if (skip_check) result= 0; @@ -6391,6 +6414,9 @@ extern "C" MYSQL *mysql_real_connect_local(MYSQL *mysql) new_thd->security_ctx->skip_grants(); new_thd->query_cache_is_applicable= 0; new_thd->variables.wsrep_on= 0; + new_thd->variables.sql_log_bin= 0; + new_thd->variables.option_bits&= ~OPTION_BIN_LOG; + /* TOSO: decide if we should turn the auditing off for such threads.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
The modified patch uploaded.
https://github.com/MariaDB/server/commit/2fdcfaaba09db9f64681d7dda46b1d8c314...
On Wed, Mar 2, 2022 at 10:40 PM Sergei Golubchik
Hi, Alexey,
On Mar 02, Alexey Botchkov wrote:
revision-id: 9d6be81c0da (mariadb-10.7.2-8-g9d6be81c0da) parent(s): 33fd136c61b author: Alexey Botchkov committer: Alexey Botchkov timestamp: 2022-02-15 13:37:59 +0400 message:
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.
diff --git a/mysql-test/suite/plugins/r/test_sql_service.result b/mysql-test/suite/plugins/r/test_sql_service.result index 00f0411b665..943bb239e5f 100644 --- a/mysql-test/suite/plugins/r/test_sql_service.result +++ b/mysql-test/suite/plugins/r/test_sql_service.result @@ -2,6 +2,19 @@ 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'; +select * from t1 order by a;
this is not a test. Please, enable binlog and see whether queries are actually logged.
Also, add a test for, say, sql_auto_is_null:
set global test_sql_service_execute_sql_global='set sql_auto_is_null=1'; set global test_sql_service_execute_sql_global='insert test.t1 select 5, @@sql_auto_is_null'; select * from t1 where a=5;
+a @@SQL_LOG_BIN +1 0 +2 0 +3 1 +4 0 +drop table t1; set global test_sql_service_run_test= 1; show status like 'test_sql_service_passed'; Variable_name Value diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index e025147c71e..48681ac0b6f 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -5617,12 +5617,30 @@ class Protocol_local : public Protocol_text THD *new_thd; Security_context empty_ctx;
+ 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, ulonglong *sav_bits) + { + *sav_log_bin= thd->variables.sql_log_bin; + *sav_bits= thd->variables.option_bits; + + if ((thd->variables.sql_log_bin= do_log_bin)) + thd->variables.option_bits|= OPTION_BIN_LOG; + else + thd->variables.option_bits&= ~OPTION_BIN_LOG; + } + void restore_binlog_vars(my_bool &sav_log_bin, ulonglong &sav_bits) + { + do_log_bin= thd->variables.sql_log_bin; + thd->variables.sql_log_bin= sav_log_bin; + thd->variables.option_bits= sav_bits;
that's what sql_auto_is_null test is for.You destroy all other option_bits here. Better don't save/restore option_bits, but use the same if() as in set_binlog_vars(). Or, even better, reuse fix_sql_log_bin_after_update(). I'd suggest to make it a method in THD
+ } protected: bool net_store_data(const uchar *from, size_t length); bool net_store_data_cs(const uchar *from, size_t length, @@ -6230,12 +6248,17 @@ loc_advanced_command(MYSQL *mysql, enum enum_server_command command, Ed_connection con(p->thd); Security_context *ctx_orig= p->thd->security_ctx; MYSQL_LEX_STRING sql_text; + my_bool log_bin_orig; + ulonglong option_bits_orig; + p->set_binlog_vars(&log_bin_orig, &option_bits_orig); + DBUG_ASSERT(current_thd == p->thd); sql_text.str= (char *) arg; sql_text.length= arg_length; p->thd->security_ctx= &p->empty_ctx; result= con.execute_direct(p, sql_text); p->thd->security_ctx= ctx_orig; + p->restore_binlog_vars(log_bin_orig, option_bits_orig); } if (skip_check) result= 0; @@ -6391,6 +6414,9 @@ extern "C" MYSQL *mysql_real_connect_local(MYSQL *mysql) new_thd->security_ctx->skip_grants(); new_thd->query_cache_is_applicable= 0; new_thd->variables.wsrep_on= 0; + new_thd->variables.sql_log_bin= 0; + new_thd->variables.option_bits&= ~OPTION_BIN_LOG; + /* TOSO: decide if we should turn the auditing off for such threads.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
participants (2)
-
Alexey Botchkov -
Sergei Golubchik