MySQL Bugs: #186: If a connection has temporary tables and does RESET MASTER, this breaks replic.

Bug #186	If a connection has temporary tables and does RESET MASTER, this breaks replic.
Submitted:	24 Mar 2003 5:54	Modified:	25 Mar 2003 8:09
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0	OS:	Any (all)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
This is not a serious bug ; not urgent.

Start master and slave, make replication work. Then:

MASTER:
create temporary table t(a int);
reset master;

SLAVE:
stop slave;reset slave;start slave;

MASTER:
exit from the previous connection.

Then as the exit drops a temporary table, 'DROP TEMPORARY TABLE t' will be written to the binlog, and propagated to the slave. But apparently RESET SLAVE makes the slave lose its temporary tables, so the DROP causes :
ERROR: 1051  Unknown table 't'
030324 14:37:03  Slave: error 'Unknown table 't'' on query 'DROP /*!40005 TEMPORARY */ TABLE test.t', error_code=1051
030324 14:37:03  Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'gbichot-bin.001' position 79

How to repeat:
Start master and slave, make replication work. Then:

MASTER:
create temporary table t(a int);
reset master;

SLAVE:
stop slave;reset slave;start slave;

MASTER:
exit from the previous connection.

Suggested fix:
Replace
DROP /*!40005 TEMPORARY */ TABLE test.t
by
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS test.t

Better bug fix:

In a slave, ignore all errors from 'DROP TEMPORARY TABLE'

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fix per Monty's recommendation :
in a slave thread, silently add IF EXISTS to
DROP TEMPORARY TABLE before executing it. Changeset 1.1422.