Skip navigation links
14.7.13 Replication and User Privileges »
Section Navigation      [Toggle]

14.7.12. Replication and Temporary Tables

Temporary tables are replicated except in the case where you shut down 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 shut down 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. Instead, use the following procedure:

  1. Issue a STOP SLAVE statement.

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

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

  4. If the value is not 0, restart the slave SQL thread with START SLAVE SQL_THREAD.

  5. Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Douglas Van Hollen on July 31 2012 4:04pm[Delete] [Edit]

Under MySQL 5.1.29 (an RC, I know) you can see in processlist that the creation of temporary tables *is* replicated under ROW-based replication.

I assume this is fixed in later versions. Just FYI.

Add your own comment.