Description:
If t is a MyISAM table:
suppose an INSERT into t SELECT ...
fails because the second row it tries to insert already exists in 't' and there is a unique index.
Then the command terminates with an error message ('Duplicate entry...'),
but still the table has been partially updated (one row has been inserted).
Currently in that case the INSERT SELECT is not written to the binlog,
so the tables on master and slave silently become different.
How to repeat:
create table t1(a int, unique(a));
insert into t1 values(2);
create table t2(a int);
insert into t2 values(1),(2);
reset master;
insert into t1 select * from t2;
# The above should produce an error, but still be in the binlog;
# verify the binlog :
show binlog events;
# unfortunately the INSERT SELECT is not there...
Suggested fix:
In that case, the master must write to the binlog; the written binlog event will contain an error code (for example, 1062 for "Duplicate entry"); when the slave sees this error code it will stop and wait for the DBA to manually fix things (this is the normal way: the slave does not know what to do because it does not know how it should run this ill query); if this is a normal situation to the DBA he can use --slave-skip-errors=# (everything is explained in the Replication section of our manual).
If the table supports transactions (InnoDB, BDB), no reason to write to the binlog.
In any case if no row was inserted/modified, no reason to write to the binlog.
Description: If t is a MyISAM table: suppose an INSERT into t SELECT ... fails because the second row it tries to insert already exists in 't' and there is a unique index. Then the command terminates with an error message ('Duplicate entry...'), but still the table has been partially updated (one row has been inserted). Currently in that case the INSERT SELECT is not written to the binlog, so the tables on master and slave silently become different. How to repeat: create table t1(a int, unique(a)); insert into t1 values(2); create table t2(a int); insert into t2 values(1),(2); reset master; insert into t1 select * from t2; # The above should produce an error, but still be in the binlog; # verify the binlog : show binlog events; # unfortunately the INSERT SELECT is not there... Suggested fix: In that case, the master must write to the binlog; the written binlog event will contain an error code (for example, 1062 for "Duplicate entry"); when the slave sees this error code it will stop and wait for the DBA to manually fix things (this is the normal way: the slave does not know what to do because it does not know how it should run this ill query); if this is a normal situation to the DBA he can use --slave-skip-errors=# (everything is explained in the Replication section of our manual). If the table supports transactions (InnoDB, BDB), no reason to write to the binlog. In any case if no row was inserted/modified, no reason to write to the binlog.