I have a master-slave replication for a schema products which is being replicated with binlog_do_db = products.

one of the the tables in the master schema is being archived by calling a stored procedure which is in schema products_archive.The data is moved to a different schema on the same database from products to products_archive.

        INSERT INTO products_archive.dial
        SELECT * FROM products.dial
        WHERE products.dial.dial_datestamp <= DATE(NOW() - INTERVAL 30 DAY);
        DELETE FROM products.dial
        WHERE producst.dial.dial_datestamp <= DATE(NOw() - INTERVAL 30 DAY);

But the slave has only one schema products which is not being archived. Since stored procedure is on the schema which is not being replicated the master should be archived and the slave shouldn't.

But what instead happened is the master archive was successful and the same data got deleted on the slave.

How did the data get purged on the slave as the schema products_archive is not added to list of replicated schemas.

  • Back up a step and state what you want. product_archive lives only on Master? All rows exist in one table or the other on both? Or do you intend to remove 'old' rows from one of the servers? – Rick James Jan 3 '18 at 14:31
  • product_archive schema is on master only.All the rows exist on both master and slave table but i want to archive only master table and not slave.The stored procedure is put in the non-replicated schema products_archive to avoid archiving happening on slave. – shadow0359 Jan 4 '18 at 5:32

It seems what is happening is that your stored procedure deletes rows from products.dial, and these DELETE statements are simply replicated to the slave. It doesn't matter that the stored procedure itself is in a non-replicated schema.

| improve this answer | |
  • Is this how its supposed to work?Is there a way to avoid the delete happening on the slave? – shadow0359 Jan 4 '18 at 5:36
  • Yep, this is how replication works. It's possible to avoid the DELETE statements taking effect on the slave, see e.g. stackoverflow.com/questions/15388907/… – dbdemon Jan 4 '18 at 14:20
  • You are walking into a mine field. – Rick James Jan 4 '18 at 21:52
  • Agreed, this is a minefield! I suppose the main thing to avoid is reusing deleted primary key values. – dbdemon Jan 4 '18 at 22:36

Based on the given code and scenario I would assume you are talking about Transactional Replication and that the command "DELETE FROM products.dial" that took place at the publisher was replicated to the subscriber. If this is the case it means that the table products.dial is defined as an article in your publication and one option that may or may not be applicable in your case is to define this specific article NOT to replicate DELETE commands. This can be done using the stored procedure sp_changearticle

In case this is not a desired/ applicable behaviour for your case then another option can be to direct the data to be deleted at a destination table located at another database (i.e ArchiveDatabase).

Regardless of the specific issue you are raising please be aware of the syntax that can delete and direct the output to a destination table in 1 automatic command avoiding the need for an explicit transaction and that is by using the OUTPUT clause

| improve this answer | |
New contributor
Yaniv Etrogi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.