Bug #76493 Binlog statement is not ignored
Submitted: 26 Mar 2015 12:32 Modified: 25 Aug 2015 15:55
Reporter: Dan Lukes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.6.20, 5.6.23, 5.6.25 OS:Any
Assigned to: CPU Architecture:Any

[26 Mar 2015 12:32] Dan Lukes
Description:
Replication slave choke on statement that should be ignored instead.

Master & slave both on version 5.6.20

How to repeat:
MASTER:
Two databases, DB_A and DB_B. There's view named VIEW_A in DB_B

SLAVE:
Replicate_Do_DB: DB_A
Only DB_A is replicated here, there's no DB_B on slave.

INVOKE:
"use DB_B ; CREATE VIEW VIEW_A AS SELECT NULL;" on MASTER. 
Of course, it will be rejected as DB_B.VIEW_A exists already.

Despite the command is related to DB_B which is not replicated, all SLAVEs become confused and stop replication with the following error:

 -------------------
Last_SQL_Error: Query caused different errors on master and slave.     
Error on master: message (format)='Table '%-.192s' already exists' error code=1050 ; 
Error on slave: actual message='no error', error code=0. Default database: 'DB_B'. Query: 'CREATE VIEW view_a AS select NULL
 -------------------

Suggested fix:
Fix: The statement related to DB_B should be ignored as DB_B is not replicated on such slave. 

Workaround 1: Replication can be restarted using SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 trick.

Workaround 2: Don't create objects that exists already.
[30 Mar 2015 10:36] MySQL Verification Team
Hello Dan Lukes,

Thank you for the report and test case.
Observed this behavior on 5.6.23.

Thanks,
Umesh
[30 Mar 2015 10:39] MySQL Verification Team
test results

Attachment: 76493.txt (text/plain), 14.59 KiB.

[30 Mar 2015 11:12] MySQL Verification Team
test results

Attachment: 76493_5.6.25.txt (text/plain), 13.78 KiB.

[25 Aug 2015 15:55] David Moss
Thanks for your feedback, this has been fixed in upcoming versions and the following was noted in the 5.6.27 and 5.7.9 changelogs:

If a CREATE VIEW statement failed, it was being incorrectly written to the binary log even though it did not result in the creation of a partial view. The fix ensures that such statements are not recorded in the binary log. Additionally it was found that when a statement which had failed on a master was received by a slave with an expected error, if the statement was skipped on the slave, for example due to a replication filter, the expected error was being compared with the actual error that happened on the slave. The fix ensures that if a statement with an expected error is received by a slave, if the statement has not been filtered, only then is it compared with the actual error that happened on the slave.