Bug #46861 Auto-closing of temporary tables broken by replicate-rewrite-db
Submitted: 21 Aug 2009 21:52 Modified: 16 Sep 2009 9:50
Reporter: Shawn Green Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Any
Assigned to: Alfranio Junior CPU Architecture:Any

[21 Aug 2009 21:52] 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:25] Valeriy Kravchuk
Bug #46862 was marked as a duplicate of this one.
[24 Aug 2009 8:18] Susanne Ebrecht
Verified as described.

Verified in 5.0 and 5.1 bzr tree.
[25 Aug 2009 15:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81529

2720 Alfranio Correia	2009-08-25
      BUG#46861 Auto-closing of temporary tables broken by replicate-rewrite-db
      
      When a connection is dropped any remaining temporary table is also automatically
      dropped and the SQL statement of this operation is written to the binary log in
      order to drop such tables on the slave and keep the slave in sync. Specifically,
      the current code base creates the following type of statement:
      DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `db`.`table`;
      
      Unfortunately, appending the database to the table name in this manner circumvents
      the replicate-rewrite-db option (and any options that check the current database).
      To solve the issue, we started writing the statement to the binary as follows:
      use `db`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `table`;
[26 Aug 2009 13:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81612

2720 Alfranio Correia	2009-08-26
      BUG#46861 Auto-closing of temporary tables broken by replicate-rewrite-db
      
      When a connection is dropped any remaining temporary table is also automatically
      dropped and the SQL statement of this operation is written to the binary log in
      order to drop such tables on the slave and keep the slave in sync. Specifically,
      the current code base creates the following type of statement:
      DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `db`.`table`;
      
      Unfortunately, appending the database to the table name in this manner circumvents
      the replicate-rewrite-db option (and any options that check the current database).
      To solve the issue, we started writing the statement to the binary as follows:
      use `db`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `table`;
[26 Aug 2009 14:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81619

2720 Alfranio Correia	2009-08-26
      BUG#46861 Auto-closing of temporary tables broken by replicate-rewrite-db
      
      When a connection is dropped any remaining temporary table is also automatically
      dropped and the SQL statement of this operation is written to the binary log in
      order to drop such tables on the slave and keep the slave in sync. Specifically,
      the current code base creates the following type of statement:
      DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `db`.`table`;
      
      Unfortunately, appending the database to the table name in this manner circumvents
      the replicate-rewrite-db option (and any options that check the current database).
      To solve the issue, we started writing the statement to the binary as follows:
      use `db`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `table`;
[26 Aug 2009 17:04] Alfranio Junior
The patch was pushed to

bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-5.0.72sp1-bug46861
[27 Aug 2009 16:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81763

2720 Alfranio Correia	2009-08-27
      BUG#46861 Auto-closing of temporary tables broken by replicate-rewrite-db
      
      When a connection is dropped any remaining temporary table is also automatically
      dropped and the SQL statement of this operation is written to the binary log in
      order to drop such tables on the slave and keep the slave in sync. Specifically,
      the current code base creates the following type of statement:
      DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `db`.`table`;
      
      Unfortunately, appending the database to the table name in this manner circumvents
      the replicate-rewrite-db option (and any options that check the current database).
      To solve the issue, we started writing the statement to the binary as follows:
      use `db`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `table`;
[28 Aug 2009 10:07] Alfranio Junior
Pushed to mysql-5.0-bugteam --> mysql-5.1-bugteam --> mysql-pe
[28 Aug 2009 10:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81844

2721 Alfranio Correia	2009-08-28
      Post-fix for BUG#46861.
[2 Sep 2009 10:25] Bugs System
Pushed into 5.0.86 (revid:joro@sun.com-20090902102337-n5rw8227wwp5cpx8) (version source revid:alfranio.correia@sun.com-20090828092904-o18hk6197src42im) (merge vers: 5.0.86) (pib:11)
[2 Sep 2009 14:01] Jon Stephens
Documented bugfix in the 5.0.86 changelog as follows:

      When using the --replicate-rewrite-db option and the database 
      referenced by this option on the master was the current database 
      when the connection to the slave was closed, any temporary tables 
      existing in this database were not properly dropped.

Set status to Patch Pending, waiting for pushes to 5.1+.
[2 Sep 2009 16:41] Bugs System
Pushed into 5.1.39 (revid:joro@sun.com-20090902154533-8actmfcsjfqovgsb) (version source revid:alfranio.correia@sun.com-20090828094557-oii9hu7be3bpo4d6) (merge vers: 5.1.39) (pib:11)
[11 Sep 2009 9:18] Jon Stephens
Set to Documenting per IRC discussion with Alfranio.
[11 Sep 2009 9:43] Jon Stephens
Also documented in 5.1.39 changelog.

Set to NDI, waiting for push to 5.4 tree.
[14 Sep 2009 16:05] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 9:50] Jon Stephens
Also documented bugfix in the 5.4.4 changelog.

Closed.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[2 Oct 2009 1:41] Paul DuBois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)