MySQL Forums :: Replication :: Replication, Stored Procedures & Temporary Tables


Advanced Search

Replication, Stored Procedures & Temporary Tables
Posted by: Adam Newman ()
Date: July 11, 2012 09:56AM

Hi there,

First of all I wanted to say thanks to the MySQL developers. It's a really great tool and we've achieved so much with it so far.

Recently we encountered an issue with replication and temporary tables and we've been scratching our heads for over a week trying to find a solution.

Simply put, we have a server which runs a stored procedure. This procedure generates a temporary table to generate a dataset. We have replication set up so that all changes made in this schema are replicated to a second system where our slave is running.

When we do a "pull the plug" test on our slave, replication will fail to recover on the slave because there are pending transactions for that table and that table has now vanished.

We tried using options like replicate-wild-ignore-table= and replicate-ignore-table, but these only work for ordinary transactions and not for those passed from a Stored Procedure. I don't understand why this is but this is what our testing has demonstrated.

We don't actually want or need the temporary tables to be replicated to our slave, or any of the stuff that goes on in the Stored Procedure for that matter, but we can't find a way to configure the master and / or slave to stop it.

Some ideas that we had for workarounds were to actively diable binlogging for the duration of the Stored Procedure (which is dangerous if the procedure fails and it doesn't get enabled again). Also there was moving all temporary tables to belong to a different schema, and then explicitly forbidding replication of that schema using replicate-ignore-db= but we haven't tried this to see if it would work, and it would mean a lot of changes to our customers database (~40 Stored Procedures, plus application code).

Is there any way that we can filter the Stored Procedure and/or temporary tables that it creates out of the replication process without filtering the entire schema?

**EDIT**

I forgot to mention i'm using MySQL 5.5.

Best Regards,
Adam



Edited 1 time(s). Last edit at 07/11/2012 10:31AM by Adam Newman.

Options: ReplyQuote


Subject Views Written By Posted
Replication, Stored Procedures & Temporary Tables 2296 Adam Newman 07/11/2012 09:56AM
Re: Replication, Stored Procedures & Temporary Tables 1000 Irfan Ali 07/11/2012 03:35PM
Re: Replication, Stored Procedures & Temporary Tables 1244 Adam Newman 07/12/2012 02:34AM
Re: Replication, Stored Procedures & Temporary Tables 981 Aftab Khan 07/13/2012 02:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.