Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14760

Replication does not take into account SET STATEMENT

    XMLWordPrintable

Details

    Description

      Refined and complete description
      ================================

      While SET STATEMENT @@session.var1=v1, ..., @@session.var_n=vn Query,
      when binlog_format = STATEMENT, is binlogged in verbatim the results of the Query execution are different between master (primary) and slave (replica).
      Unlike master, the slave side refuses to adopt var_1, ..., var_n customization, and
      which is the case of either DML and DDL queries.

      Even though that is by design the behavior does not allow for replicating correctly queries that either implicitly depend on session variables whose values are not included into Query_log_event, like

      SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT);

      (on slave the replicated table may not be of Aria type)
      or the set-statement's query is composed with referencing variables explicitly e.g

      SET STATEMENT query_alloc_block_size = 1024 FOR INSERT INTO t_heap SET i=@@session.query_alloc_block_size + 1;

      (on slave the inserted value of i will be the slave's @@global.query_alloc_block_size + 1)
      Note that employing a separate SET @@session.var = value to prepend Query may not be (it *is* not for the two above cases) a workaround for statement binlog format replication either.

      To fix the issue beyond suggested Query re-writing, Monty and others considered effectively lift the design's constrain.

      After all it looks to be grounded solely on pessimistic assessments of 'security issues' at the slave side execution

      F-10: Replication: Slave threads will ignore SET_VAR hints to avoids security
      issues since slave threads run as root, avoids potential problems with
      variables replicated with SBR.

      Apparently it's far from being so dramatic as the above examples may hint. Specifically storage_engine,query_alloc_block_size and lots of other are security safe.

      I suggest to indeed *revise* that decision, and when that's done *introduce* for backward compatibility a slave side switch. Along that let's also *attend* MDEV-27462 which must review the current list of disallowed variables (in particular consider to append to it binlog_format which is disallowed to change within transaction context which may not be the case on master but it may be so on slave).

      With this measure we also achieve (addresses knielsen's note) consistent results between the slave applier and the binlog "direct" applier executions.
      The replicated with session-variables SET-STATEMENT-FOR-Query could be engaged further on cases when correctness can be accomplished (addresses monty's note) only with more reach execution context than currently Query_log_event is provided with.


      *Former Elena's description:*

      DDL statements sent to slave does not take into account the original environment where the query was executed.
      This is true when using SET STATEMENT or having a non standard value for any variable that is internally used by a DDL, like default_storage_engine.

      --source include/master-slave.inc
       
      SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT);
      SHOW CREATE TABLE t;
       
      --sync_slave_with_master
      SHOW CREATE TABLE t;
       
      --connection master
      DROP TABLE t;
      --source include/rpl_end.inc
      


      Result

      [connection master]
      SET STATEMENT storage_engine=Aria FOR CREATE TABLE t (i INT);
      SHOW CREATE TABLE t;
      Table	Create Table
      t	CREATE TABLE `t` (
        `i` int(11) DEFAULT NULL
      ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
      connection slave;
      SHOW CREATE TABLE t;
      Table	Create Table
      t	CREATE TABLE `t` (
        `i` int(11) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      

      Attachments

        Issue Links

          Activity

            People

              bnestere Brandon Nesterenko
              elenst Elena Stepanova
              Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.