Bug #88 Some SET commands should be written to the binlog as they affect next updates
Submitted: 21 Feb 2003 6:41 Modified: 27 Sep 2008 8:44
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0 OS:Any (all)
Assigned to: CPU Architecture:Any

[21 Feb 2003 6:41] Guilhem Bichot
Description:
User's example :
--
I'm using MySQL replication(Version 4.0.10)

Master and slave have FOREIGN KEY constration.
and I  back up data with mysqldump.
but mysqldump does not produce table and record invalid order for FK.

So,when I restore data,  I execute "SET FOREIGN_KEY_CHECKS = 0;" on master.

But, this command does not forwarded to Slave.
so, slave fails to insert record.
--

Other examples : SET TABLE_TYPE, SET SQL_MODE

How to repeat:
on master do
set table_type=innodb;
create table test.t(a int);

on slave do 
show table status like 'test.t' 
it is a MyISAM table !
(unless your slave is started with some --default-table-type).

Suggested fix:
Log some SET commands to the binlog.
For TABLE_TYPE, log them for CREATE TABLE (maybe ALTER TABLE, I have to check).
For SQL_MODE, Monty suggests adding SQL_MODE in the header of all Query events
in the binlog.
For SQL_FOREIGN_KEY_CHECKS, this should be logged as user variables are.

In short, to be better defined.
[21 Feb 2003 6:50] Guilhem Bichot
I don't know why, but this "bugs" reporting tool does not
include any hint of who wrote this bug report in the 
email it send to dev-bugs. But the "from" field in this form
is filled, it's just not printed in the email

Guilhem@mysql.com
[11 Mar 2003 6:06] Guilhem Bichot
Annoying bug but will be fixed in 5.0 only (agreed with Monty)
[22 Apr 2003 4:40] Guilhem Bichot
Fixing it is scrum task #755
[26 Apr 2003 7:04] Michael Widenius
This issue is documented in the MySQL manual in the replication section
[20 Dec 2003 6:50] Guilhem Bichot
FOREIGN_KEY_CHECKS works since 4.0.14.
UNIQUE_CHECKS, SQL_AUTO_IS_NULL, SQL_MODE work in 5.0.0
[2 Feb 2004 6:27] Guilhem Bichot
Charsets setting will be replicated in 5.0.x, and others (probably TABLE_TYPE, and SQL_SELECT_LIMIT) too.
[27 Sep 2008 8:44] Konstantin Osipov
Fixed in 5.0, in scope of WL#755