Bug #46862 Auto-closing of temporary tables broken by replicate-rewrite-db
Submitted: 21 Aug 2009 21:56 Modified: 23 Aug 2009 13:24
Reporter: Shawn Green Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.72sp1 OS:Any
Assigned to: CPU Architecture:Any

[21 Aug 2009 21:56] Shawn Green
Description:
When a user disconnects from a MySQL server, any temporary tables they had open will be closed. In order to prevent temporary table accumulation on the slave, the master will insert a statement in the binary log similar to 

# at 2227
#090820 19:45:34 server id 4122 log_pos 2227 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1250811934;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test1`.`mytemp5`;

in the binary log for each one of those auto-closed temporary tables. This database-specific command will fail if the user is using --replicate-rewrite-db to modify the current database (in this example, `test1`) on the slave.

The bug is not as much with replicate-rewrite-db but more with how MySQL logs the auto-drop of the temp table when the client connection closes. Of course, you could fix it either way. 

How to repeat:
Setup replication between two isolated instances (single-user) so that you can control and monitor the creation of temporary tables. 

Pick a database from the master (temp1) and use --replicate-rewrite-db to alter the destination path on the slave to a different, existing database (temp2). 

[mysqld]
replicate-rewrite-db='temp1'->'temp2'

Create a temporary table on the master in the target database:
USE temp1;
CREATE TEMPORARY TABLE temp1;

Check both server's (master and slave) tmpdir location for the file(s) for the temporary table. 

Drop your test connection to the master without dropping the table. 

Recheck both tmpdir locations. 

Suggested fix:
Modify the database-explicit DROP TABLE command added to the binary log from this

DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test1`.`mytemp5`;

To a database-implicit command demonstrated here:
USE `test1`;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `mytemp5`;

A harder fix would be to modify --replicate-rewrite-db to properly parse and rewrite cross-database statements.
[23 Aug 2009 13:24] Valeriy Kravchuk
Looks like a duplicate of bug #46861.