Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.1(EOL), 10.2(EOL), 10.6
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
- blocks
-
MDEV-32930 mysqlbinlog --verbose shows incorrect values for timestamps >= 2^31 if binlog was created with mysql56_temporal_format=off
-
- Open
-
- relates to
-
MDEV-27462 SET STATEMENT allows variables that cannot be set per query basis
-
- Stalled
-