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
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