MySQL Forums
Forum List  »  Replication

Re: Replication, Stored Procedures & Temporary Tables
Posted by: Aftab Khan
Date: July 13, 2012 02:33AM

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


Ok so you use STATEMENT based replication. The reason it happens:

Temporary tables are replicated except in the case where you stop the slave server (not just the slave threads) and you have replicated temporary tables that are open for use in updates that have not yet been executed on the slave. If you stop the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open, follow these steps:

1) Issue a STOP SLAVE SQL_THREAD statement.

2) Use SHOW STATUS to check the value of the Slave_open_temp_tables variable.

3) If the value is not 0, restart the slave SQL thread with START SLAVE SQL_THREAD and repeat the procedure later.

When the value is 0, issue a mysqladmin shutdown command to stop the slave.

To avoid the hassle, use ROW BASED replication:

"Temporary tables are not replicated when using row-based format because there is no need. In addition, because temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based format."
http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-usage.html



Edited 1 time(s). Last edit at 07/13/2012 02:35AM by Aftab Khan.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Replication, Stored Procedures & Temporary Tables
1337
July 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.