Bug #83617 disabling SQL mode NO_ENGINE_SUBSTITUTION doesn't work for replication
Submitted: 31 Oct 2016 8:41 Modified: 2 Nov 2016 7:17
Reporter: Riccardo Pizzi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.27, 5.6.34 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: replication, sql mode

[31 Oct 2016 8:41] Riccardo Pizzi 
Description:
Disabling SQL mode NO_ENGINE_SUBSTITUTION  only works for DDL issued locally on the node. If a slave has  NO_ENGINE_SUBSTITUTION disabled and a CREATE TABLE comes from its master which specifies an engine not available on the slave, replication breaks.

How to repeat:
On the slave, disable InnoDB and restart replication.
On the master, issue a create table.... engine InnoDB.
Replication breaks on the slave with error 1286:

Worker 7 failed executing transaction '' at master log dbstat02.034383, end_log_pos 99530607; Error 'Unknown storage engine 'innodb'' on query. Default database: 'rick'. Query: 'create table ttable2 (id int not null auto_increment primary key) engine=innodb'

Creating same table directly on slave behaves correctly as per manual description.
[31 Oct 2016 11:42] Riccardo Pizzi 
After checking the source code, it appears that the SQL_MODE used on slave for execution of statements comes from the master's binary log.  Therefore, the SQL_MODE needs to be changed on master. It would be nice to update the manual to reflect the above, as the behaviour is otherwise not what one would expect.
[2 Nov 2016 7:17] MySQL Verification Team 
Hello Riccardo,

Thank you for the report.
Observed this with 5.6.34 build.

Thanks,
Umesh
[2 Nov 2016 7:17] MySQL Verification Team 
test results

Attachment: 83617.results (application/octet-stream, text), 11.56 KiB.
[20 Jun 2019 11:44] Mahendra Korat 
We are also getting the same error once in a while. We have MySQL 5.6.32 on Windows Server 2012 R2 x64bit OS.

Is there any workaround for this?