Bug #26945 Using explicit temporary tables on master breaks replication on slave restart
Submitted: 8 Mar 2007 3:12 Modified: 31 May 2013 9:48
Reporter: Arjen Lentz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.0,5.1 OS:Any (any)
Assigned to: Jon Stephens CPU Architecture:Any
Tags: replication, restart, slave, temp, temporary, tmp

[8 Mar 2007 3:12] Arjen Lentz
Description:
If master is using explicit temporary tables, then STOP/START SLAVE is ok but actually stopping slave mysqld is likely to break.

Specifically, if any subsequent replicated queries reference the temporary table, this will break replication, since the slave no longer has any tmp tables.

How to repeat:
On master:
CREATE TEMPORARY TABLE rpltmpbreak (i INT);

Wait for slave to replicate this statement, then stop and start mysqld (simply STOP/START SLAVE is not sufficient, naturally)

On master:
INSERT INTO rpltmpbreak VALUES (1);

On slave:
SHOW SLAVE STATUS \G

It will break replication by referencing the no-longer existing tmp table.

Suggested fix:
To resolve this, the slave may need to either make temporary tables created within the slave thread persistent across a server restart.

Simply maintaininh a list so that it can effectively ignore queries that reference them, is probably not effective. Any queries that use the temporary tables to update normal tables could otherwise create an inconsistent slave.

It's not easy, however something must be done as otherwise any application that uses temporary tables on the master essentially has very limited use for replication. Recovery from a restarted slave becomes impossible.

http://bugs.mysql.com/186 is an old bug that is somewhat related, a slave already silently ignores an error from DROP TEMPORARY TABLE ... (if the table doesn't exist). That was of course a perfectly save and sensible fix for that particular corner case.
[8 Mar 2007 13:39] MySQL Verification Team
Thank you for the bug report. Verified as desscribed:
miguel@light:~/dbs/5.0s$ libexec/mysqld --defaults-file=/home/miguel/dbs/5.0s/my.cnf
070308 10:37:15  InnoDB: Started; log sequence number 0 178769
070308 10:37:15 [Note] libexec/mysqld: ready for connections.
Version: '5.0.38-debug'  socket: '/home/miguel/dbs/5.0s/mysql.sock'  port: 3307  Source distribution
070308 10:37:15 [Note] Slave I/O thread: connected to master 'miguel@localhost:3306',  replication started in log 'binlog.000001' at position 438
070308 10:37:15 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000001' at position 438, relay log './hegel-relay-bin.000002' position: 336
070308 10:37:15 [Note] next log './hegel-relay-bin.000003' is not active
070308 10:37:15 [Note] next log './hegel-relay-bin.000004' is currently active
070308 10:37:15 [ERROR] Slave: Error 'Table 'foo.rpltmpbreak' doesn't exist' on query. Default database: 'foo'. Query: 'INSERT INTO rpltmpbreak VALUES (1)', Error_code: 1146
070308 10:37:15 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000001' position 438
[8 Mar 2007 19:03] Kolbe Kegel
Just pointing out the existing documentation from http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

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 used 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 threads with START SLAVE.
   5. Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave.
[22 Sep 2008 1:31] Jon Stephens
Note that the text quoted by Kolbe has changed - see Bug #39339.
[1 Feb 2010 4:18] Zhenxing He
for 5.1+, using row format can be a solution for replication with temporary tables.
[23 May 2013 18:54] Sveta Smirnova
Arjen,

please clarify if row-based format works for you. Because in my opinion this is not a bug now.
[23 May 2013 23:24] Arjen Lentz
Hi Sveta

It is still there - ROW based replication has no impact on this issue.

I don't know what Zhenxing He was referring to in his comment, but there is no indication that it works.

Also see http://dev.mysql.com/doc/refman/5.0/en/replication-features-temptables.html

As it's documented as something that doesn't work, you cannot just say it's not a bug.
You could decide to not fix it (I appreciate it's a difficult issue) but that's a different matter.

Please retry my original sequence to confirm for yourself that it's indeed still there...
How to repeat:

On master:
CREATE TEMPORARY TABLE rpltmpbreak (i INT);

Wait for slave to replicate this statement, then stop and start mysqld (simply STOP/START SLAVE is not sufficient, naturally)

On master:
INSERT INTO rpltmpbreak VALUES (1);

On slave:
SHOW SLAVE STATUS \G

It will break replication by referencing the no-longer existing tmp table.

thanks
[24 May 2013 11:10] Luis Soares
Hi Lentz, 

I think what Zhenxing and Sveta meant was that row based replication 
is not vulnerable.

In row based replication, temporary tables are not replicated at
all. There is no need to replicate them. So the issue you are 
referring to does not exist.

  MASTER> CREATE TEMPORARY TABLE t1 (c1 INT);
  MASTER> INSERT INTO t1 VALUES (1);
  MASTER> CREATE TABLE t2 (c1 IN);
   SLAVE> /* wait for it to replicate t2 and restart server */
  MASTER> INSERT INTO t2 SELECT * FROM t1;
   SLAVE> /* Observe as everything is replicated correctly */

For more details, see the section on "RBL, RBR, and temporary tables." 
in the manual:

http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-usage.html

Cheers!
[27 May 2013 0:12] Arjen Lentz
Hi Luis

Yes indeed in pure ROW (not MIXED), the data is replicated rather then the statement, and thus there is essentially no temporary table on the slave.
The refman page you refer to explains this correctly.

However, the one that was mentioned here in the thread (http://dev.mysql.com/doc/refman/5.6/en/replication-features-temptables.html) does not. While it also mentions the other page, it doesn't do that in the correct context. Rather than futzing around, it could explain that for proper handling of temporary tables, ROW based replication is *required*.

(note though Luis that at the time of this bugreport, 2007, row based replication was so buggy it was unusable. fortunately that has changed.)

Sveta, perhaps you can re-classify this as a documentation bug and assign to the docs team?

Regards,
Arjen.
[27 May 2013 9:57] Luis Soares
Lentz, I am fine with your suggestion! Thanks.
[27 May 2013 16:17] Sveta Smirnova
Since RBR introduced this is not a bug. But I agree, we can describe it better in the user manual. Changing category to "Documentation".
[31 May 2013 9:48] Jon Stephens
Fixed in mysqldoc rev 35299. Closed.