MySQL Bugs: #53437: @@session.sql_bin_log support in substatements is incorrect

Bug #53437	@@session.sql_bin_log support in substatements is incorrect
Submitted:	5 May 2010 15:22	Modified:	13 Jul 2010 10:22
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any

Description:
It should not be possible to change @@session.sql_bin_log within a transaction or inside a substatement.

First may lead to bugs due to the transactional binlog cache, second may lead to problems due to unioned binlog events.

It can be done by implementing a check for Sys_log_bin session variable similar to binlog_format variable:

if (thd->active_transaction() || thd->in_sub_stmt)
  my_error(..);

The need for thd->sql_log_bin_toplevel arose because thd->variables.option_bints & OPTION_BIN_LOG is currently abused: it's both a system variable and an implementation switch. The current approach to this option bit breaks the session variable encapsulation, for example:

(you need to test this with --log-bin on and --log-bin-trust-function-creators):

mysql> select @@session.sql_log_bin;
+-----------------------+
| @@session.sql_log_bin |
+-----------------------+
|                     1 | 
+-----------------------+
1 row in set (0.00 sec)

mysql> show create function f1;
+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode | Create Function                                                                                                                                         | character_set_client | collation_connection | Database Collation |
+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| f1       |          | CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
begin
set @var=3;
insert into t1 (a) values (@var);
return @@session.sql_log_bin;
end | latin1               | latin1_swedish_ci    | latin1_swedish_ci  | 
+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> select f1();
+------+
| f1() |
+------+
|    0 | 
+------+
1 row in set, 1 warning (0.00 sec)

As you can see inside the substatement the value of the session
variable is incorrect: this is because we're inspecting the option bit,
which is switched off because an event union is started.

How to repeat:
Start mysqld with --log-bin:

mysql> create function f1() returns int return @@session.sql_log_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> select f1();
+------+
| f1() |
+------+
|    0 | 
+------+
1 row in set (0.00 sec)

mysql> select @@session.sql_log_bin;
+-----------------------+
| @@session.sql_log_bin |
+-----------------------+
|                     1 | 
+-----------------------+
1 row in set (0.00 sec)

Suggested fix:
Make thd->sql_log_bin_toplevel a thd->variables variable.
Switch the fix_log_bin function around, to update the option bit whenever the variable is set. 
Prohibit changes of @@session.sql_log_bin inside a substatement or in active transaction.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107720

3036 Alfranio Correia	2010-05-07
      BUG#53437 @@session.sql_bin_log support in substatements is incorrect
      
      The thd->variables.option_bits & OPTION_BIN_LOG is currently abused: 
      it's both a system variable and an implementation switch. The current
      approach to this option bit breaks the session variable encapsulation. 
      
      Besides it is allowed to change @@session.sql_bin_log within a
      transaction what may lead to not correctly logging a transaction.
      
      To fix the problems,  we created a thd->variables variable to represent
      the "sql_log_bin" and prohibited its update inside a transaction or
      sub-statement.
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Updated result file. The reason the warnings are removed is
        related to BUG#50312.
     @ mysql-test/suite/binlog/r/binlog_switch_inside_trans.result
        Checks when is possible to change the option @@session.sql_log_bin.
     @ mysql-test/suite/binlog/t/binlog_switch_inside_trans.test
        Checks when is possible to change the option @@session.sql_log_bin.
     @ mysql-test/suite/rpl/r/rpl_non_direct_stm_mixing_engines.result
        Updated the result file with warnings that were being printed
        due to the wrong use of the thd->variables.option_bits and
        sql_log_bin_top_level variables.
     @ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
        Updated the result file with warnings that were being printed
        due to the wrong use of the thd->variables.option_bits and
        sql_log_bin_top_level variables.
     @ sql/share/errmsg-utf8.txt
        Introduces two error messages to notify that the @@session.sql_log_bin
        cannot be changed inside a sub-statement or transaction.
     @ sql/sql_class.cc
        Replaces the variable sql_log_bin_toplevel by the
        (variables.option_bits & OPTION_BIN_LOG).
     @ sql/sql_class.h
        Removes the variable sql_log_bin_toplevel and creates 
        a session variable sql_log_bin.
     @ sql/sys_vars.cc
        Checks when the sql_log_bin can be correctly updated.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109396

3064 Alfranio Correia	2010-05-27
      BUG#53437 @@session.sql_bin_log support in substatements is incorrect
            
      The thd->variables.option_bits & OPTION_BIN_LOG is currently abused: 
      it's both a system variable and an implementation switch. The current
      approach to this option bit breaks the session variable encapsulation. 
            
      Besides it is allowed to change @@session.sql_bin_log within a
      transaction what may lead to not correctly logging a transaction.
            
      To fix the problems,  we created a thd->variables variable to represent
      the "sql_log_bin" and prohibited its update inside a transaction or
      sub-statement.
     @ mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
        Updated result file. The reason the warnings are removed is
        related to BUG#50312.
     @ mysql-test/suite/binlog/r/binlog_switch_inside_trans.result
        Checks when is possible to change the option @@session.sql_log_bin.
     @ mysql-test/suite/binlog/t/binlog_switch_inside_trans.test
        Checks when is possible to change the option @@session.sql_log_bin.
     @ mysql-test/suite/rpl/r/rpl_non_direct_stm_mixing_engines.result
        Updated the result file with warnings that were being printed
        due to the wrong use of the thd->variables.option_bits and
        sql_log_bin_top_level variables.
     @ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
        Updated the result file with warnings that were being printed
        due to the wrong use of the thd->variables.option_bits and
        sql_log_bin_top_level variables.
     @ sql/share/errmsg-utf8.txt
        Introduces two error messages to notify that the @@session.sql_log_bin
        cannot be changed inside a sub-statement or transaction.
     @ sql/sql_base.cc
        Removes the variable sql_log_bin_toplevel and uses
        the session variable sql_log_bin.
     @ sql/sql_class.cc
        Replaces the variable sql_log_bin_toplevel by the
        (variables.option_bits & OPTION_BIN_LOG).
     @ sql/sql_class.h
        Removes the variable sql_log_bin_toplevel and creates 
        a session variable sql_log_bin.
     @ sql/sys_vars.cc
        Checks when the sql_log_bin can be correctly updated.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:marko.makela@oracle.com-20100601134335-ccthwwru23kn09qw) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100601134335-ccthwwru23kn09qw) (pib:16)

Documented change in the 5.5.5 changelog as follows:

      It was possible to set sql_log_bin with session scope inside a
      transaction or subquery.  

Also noted in the description of sql_log_bin in "Server System Variables".

Closed.