MySQL Bugs: #3591: Replication sending Errored SQLs to Slave

Bug #3591	Replication sending Errored SQLs to Slave
Submitted:	28 Apr 2004 16:49	Modified:	28 Apr 2004 18:50
Reporter:	Matthew Elzer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Linux (redhat 9)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
I can't be 100% sure of what is happening, but I can tell you what appears to be happening.

When importing in to a table, I have a 2 field UNIQUE key. The fields are named "bom_serial" and "bom_part". It's a BOM system that is used for looking up replacement parts. Since there may be more than one of each part on a unit, we don't want to show those extra parts, and the Unique key keeps those from being added multiple times.

While doing this creates a MySQL "error" of "Duplicate Key", it's a non-fatal error, and expected, thus the code continues to import along. Just having recently set up Replication (been running for several days without issue), I come to find this morning that Replication has stopped with :

    [Last_errno] => 1062
    [Last_error] => Error 'Duplicate entry 'RZUDAP03050018-197290' for key 1' on query 'INSERT INTO `mod_serialtounit_bomtemp` ( `bom_level`, `bom_serial`, `bom_part`, `bom_type`, `bom_parent`) VALUES ('4', 'RZUDAP03050018', '197290', '', '149516')'. Default database: 'rezweb'

It appears to me that Replication has stopped because the server passed a SQL statement that errored locally to the slave.

How to repeat:
I would assume one could setup a similar database with a 2 field key, and try to add the same values multiple times, to see if the slave shuts down with the 2nd or 3rd call.

Suggested fix:
Don't send errored SQL calls to the SLAVE.

I setup this "test", and it didn't fail. 

I can't be sure what caused it, but I know the database was the activly selected database through the entire program. And it's the only program that inserts or deletes records in that table.

Hi!
If an INSERT of one row fails, the replication master never sends it to the slave. So if you get a "duplicate key" error on the slave, it means that some statements suceeded on the master and the same statements failed on slave, with this error. The cause of this, you need to find out. But for sure, a failed one-row INSERT on master never goes to the slave.