As I understand it any statement that fails with a foreign key error should not be written to the binlog and subsequently should not execute on the slave. Referring to the InnoDB manual, section 4.5:
"If a statement fails, for example, because of a foreign key violation, then the statement is not written to the binlog, and consequently not replicated to the slave"
Based on this I am wondering why I might get the following entry in my binlog:
#030702 17:17:14 server id 1 log_pos 564513531 Query thread_id=6313269 exec_time=0 error_code=1217
use image;
SET TIMESTAMP=1057191434;
delete from image.image_records where image_collection_id=228;
(where error_code=1217 is, according to http://www.mysql.com/doc/en/Error-returns.html, ER_ROW_IS_REFERENCED).
This then leads into another issue. Because this command was written to the binlog it was executed on the slave. Presumably it resulted in the same return code (1217), as one mght expect with consistent data between master and slave, as the slave replication did not halt. However, it appears that the order in which InnoDB deletes records is non-deterministic, because a different set of rows were removed from the slave than from the master (suggesting they were deleted in a different order).
This again makes me wonder why such a statement could make any permanent change to either the master or the slave, as I would expect the transaction in which it runs to be rolled back when the command fails. Is this an issue in the InnoDB transaction model that is known?
For some background info:
This table uses self-referencing foreign keys (pigs ear joins): row x in the table can reference row y.
There are no cascading delete/update rules on the foreign key
MySQL version is 4.0.12, running on Redhat 7.3
In grepping several gigs of logfiles this is the only occurence of error_code=1217 that I can find.
I've not had a chance to experiment heavily with reproducing this issue but will attempt to do so shortly and raise an appropriate report with suitable schema and data if necessary. For now, if anyone could shed some light I'd be most appreciative.
Regards, Chris Tucker
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
