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
Triage: Triaged: D3 (Medium)

[31 Mar 2009 17:43] Andrei Elkin
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.
[11 Apr 2018 13:14] Erlend Dahl
[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.