Chris,
thank you for the bug report. I was able to repeat the bug.
It is indeed a bug if MySQL does not roll back the whole SQL statement if
the statement provokes a foreign key constraint error. And it should not be
written to the binlog.
The fix is to change in /sql/sql_delete.cc the line 148 from:
error=0;
to
error=1;
I will submit this patch to the maintainer of sql_delete.cc.
Thank you,
Heikki
----- Original Message -----
From: "Chris Tucker" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, July 03, 2003 9:42 PM
Subject: Inconsistent replication of a DELETE in InnoDB
> Hi,
>
> 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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
