Bug #491 If INSERT SELECT fails in a MyISAM table but has changed row, nothing in binlog
Submitted: 24 May 2003 7:40 Modified: 1 Jul 2003 5:38
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0.13 OS:Any (all)
Assigned to: Guilhem Bichot

[24 May 2003 7:40] Guilhem Bichot
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.
[1 Jul 2003 5:38] Guilhem Bichot
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fixed in ChangeSet@1.1489.1.19.