List:Replication« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:November 2 2011 2:53pm
Subject:Re: sql_log_bin
View as plain text  
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
> remainders.
>
> 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 
removed rows.

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.
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
sql_log_binDiego Martínez2 Nov
  • Re: sql_log_binOleg Tsarev2 Nov
  • Re: sql_log_binJohan De Meersman2 Nov
    • Re: sql_log_binMarcus Bointon2 Nov
    • Re: sql_log_binMySQL)2 Nov
  • Re: sql_log_binRick James2 Nov
    • Re: sql_log_binMarcus Bointon2 Nov
      • Re: sql_log_binRicardo2 Nov
        • Re: sql_log_binMarcus Bointon2 Nov
          • Re: sql_log_binRicardo2 Nov
            • Re: sql_log_binDiego Martínez3 Nov