MySQL Bugs: #8412: Error codes reported in binary log for CHARACTER SET, FOREIGN_KEY

Bug #8412	Error codes reported in binary log for CHARACTER SET, FOREIGN_KEY_CHECKS
Submitted:	10 Feb 2005 0:03	Modified:	24 Feb 2005 8:49
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.24	OS:
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
Error conditions (duplicate key violations for instance) are reported (via error_code) in the binary log for the environmental SET CHARACTER SET and SET FOREIGN_KEY_CHECKS statements.

This breaks replication.

How to repeat:
CREATE TABLE t1 ( a INT UNIQUE );
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (1),(1);

Examine binary log:

#050209 17:40:58 server id 401  log_pos 146     Query   thread_id=3     exec_time=0    error_code=1062
SET TIMESTAMP=1107992458;
SET FOREIGN_KEY_CHECKS=0;
# at 205
#050209 17:40:58 server id 401  log_pos 205     Query   thread_id=3     exec_time=0    error_code=1062
SET TIMESTAMP=1107992458;
INSERT INTO t1 VALUES (1),(1);
# at 269
#050209 17:40:58 server id 401  log_pos 269     Query   thread_id=3     exec_time=0    error_code=1062
SET TIMESTAMP=1107992458;
SET FOREIGN_KEY_CHECKS=1;

Suggested fix:
Presumably the environmental preservation statements (for CHARACTER SET and FOREIGN_KEY_CHECKS) should not use Query_log_event.

please when you merge, extend your solution to all other SET statements
you may find (some where added in 4.1 which are not in 4.0, for
example), like SET ONE_SHOT etc. Thanks.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fixed in 4.0, 4.1, and 5.0