MySQL Bugs: #43992: slave sql thread can't tune own sql_mode via @@global.sql

Bug #43992	slave sql thread can't tune own sql_mode via @@global.sql_mode
Submitted:	31 Mar 2009 17:43	Modified:	11 Apr 2018 13:14
Reporter:	Andrei Elkin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Although sql_mode for the SQL thread is inherited from the @@global it
might change at handling of some of event irrecoverably.
Consider e.g:

Query_log_event::do_apply_event()

  if (sql_mode_inited)
        thd->variables.sql_mode=
          (ulong) ((thd->variables.sql_mode & MODE_NO_DIR_IN_CREATE) |
                   (sql_mode & ~(ulong) MODE_NO_DIR_IN_CREATE));

In effect of executing of the pasted code, the slave sql thread looses
the former @@session that might have set on purpose to some sensible
value (e.g bug#38173 needs it to control reaction on lack of the
default value for a field).

How to repeat:
look into source code;
gdb with br point at the forementioned code.

Suggested fix:
To make sure that sql_mode is restored each time an event handler
needs to change it from the initial value.

[9 Feb 2018 6:50] Sven Sandberg 

We replicate the sql_mode for all statements. There should not be a way to
override that since it would easily make replication go out of sync. The
example given in this bug does not apply any more. If there are other cases
where one workaround would be to set the sql_mode, it has to be fixed in
other ways.