MySQL Bugs: #46861: Auto-closing of temporary tables broken by replicate-rewrite-db

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 Tavares Correia Junior	CPU Architecture:	Any

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.

Bug #46862 was marked as a duplicate of this one.

Verified as described.

Verified in 5.0 and 5.1 bzr tree.

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`;

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`;

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`;

The patch was pushed to

bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-5.0.72sp1-bug46861

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`;

Pushed to mysql-5.0-bugteam --> mysql-5.1-bugteam --> mysql-pe

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.

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)

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+.

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)

Set to Documenting per IRC discussion with Alfranio.

Also documented in 5.1.39 changelog.

Set to NDI, waiting for push to 5.4 tree.

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)

Also documented bugfix in the 5.4.4 changelog.

Closed.

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)

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)

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)

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

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)