On 11/2/2011 10:24, Johan De Meersman wrote:
> ----- Original Message -----
>> From: "Diego Martínez"<tabarem@stripped>
>> [ A -> B -> C ]
>> I try to put a set session sql_log_bin=0 or set session sql_log_bin=1
>> in triggers in B (before delete value 0, after delete value 1)... not
>> work :(
> That's not entirely surprising, as the statement is already executing when that
> trigger fires :-p
>> C must have all the data, no delete of any kind.
> An interesting problem.
> First of all, you must be very aware that this could cause serious errors in the
> dataset on C. Imagine a statement like'insert into table1 select * from table2'. If no
> deletes on table2 ever propagate to slave C, that statement is going to insert a whole lot
> more into table1 than it did on hosts A and B. That's just the tip of the iceberg.
> The first thing that pops into my mind would be to put failing pre-delete triggers on
> all tables on C, but that is likely to break replication.
> The second option would be to do the replication to C manually - copy the completed
> master logs, pass them through mysqldump, filter the delete statements and then apply the
> You could partially automate that latter step by just running the slave's IO_THREAD
> to accumulate the relay logs, but it would be necessary to add skip-slave-start to your
> startup files to prevent the SQL_THREAD to launch upon service restart.
> Maybe others have other suggestions, but I repeat that you must be very aware of the
> potential for incorrect data.
This sounds as though he wants to keep an extra set of 'shadow' tables
on C. The problem is, in order to do that you will need to create a
trigger for each table to fire during a DELETE command for every table
in his schemas. Those rows being deleted from `mytable` could be copied
to `mytable_deleted` by the TRIGGER before the actual data is removed.
This is a LOT of extra maintenance on your end. New tables created on A
or B (even within your application code) will need to manually be
enhanced on C before you ever start any data modifications. Not only
would you need to have the deletion trigger in place on C but a second
copy of the table (the *_deleted copy) standing by to receive the
All in all, this is an extremely difficult system to even setup
correctly let alone maintain in good working order. I suggest you
revisit the requirement to 'never delete anything from C' and figure out
what it is you are exactly trying to accomplish.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN