MySQL Bugs: #17263: incorrect DROP query in temporary tables replication

Bug #17263	incorrect DROP query in temporary tables replication
Submitted:	9 Feb 2006 6:57	Modified:	3 May 2006 12:02
Reporter:	Lachlan Mulcahy
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	4.1.18	OS:	Any (Any)
Assigned to:	Andrei Elkin	Target Version:

Description:
Binlog records two table DROPs incorrectly.

How to repeat:

mysql> create temporary table foo (id int);
mysql> SET @@session.pseudo_thread_id=30;
mysql> create temporary table foo (id int);
mysql> exit

Here is what is in the binlogs:

#060208 20:03:01 server id 626 log_pos 58299123 Query thread_id=33880640 exec_time=0
error_code=0
SET TIMESTAMP=1139446981;
SET @@session.pseudo_thread_id=33880640;
create temporary table foo (id int);
#060208 20:03:11 server id 626 log_pos 58330912 Query thread_id=30 exec_time=0
error_code=0
SET TIMESTAMP=1139446991;
SET @@session.pseudo_thread_id=30;
create temporary table foo (id int);
#060208 20:03:12 server id 626 log_pos 58344619 Query thread_id=30 exec_time=0
error_code=0
SET TIMESTAMP=1139446992;
SET @@session.pseudo_thread_id=30;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `foo`.`foo`,`foo`.`foo`;

Notice the last statement is trying to remove the table twice.

On the replication server I get this error:

Error 'Not unique table/alias: 'foo'' on query. Default database: 'foo'. Query: 'DROP
/*!40005 TEMPORARY */ TABLE IF EXISTS `foo`.`foo`,`foo`.`foo`'

Suggested fix:
n.a

A customer reported the following replicaton failure scenario.

connection master; 
  create temporary table foo (id int);
  set @@session.pseudo_thread_id=30;
  create temporary table foo (id int);
  quit;

As a result binlog has the trailing statement
'DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `foo`.`foo`,`foo`.`foo`'
which has erroneous semantic and rejected by mysqld with an error 'Not
unique table/alias'.

To get such  erroneous query in binlog it is possible only via playing
around with `pseudo_thread_id'. The latter feature was designed for
internal use of replication algorithm, particularly to deal with temp tables.
Even if this is still possible to fix this particular issue 
we can not avoid the whole general problem. Namely 
modifying of `pseudo_thread_id' on master can easily screw up slave.

Consider an example with two sets of queries on the master:

connection master1;
set @@session.pseudo_thread_id=30;
create temporary table foo (id int);
quit

connection master2;
set @@session.pseudo_thread_id=30;
create temporary table foo (id int);
quit

Slave naturally stops with Error 'Table 'foo' already exists'.

Saying straight I would vote to make an explicit statement in the manual about
danger of using `pseudo_thread_id' in client application with
replication instead of patching the blanket already too short.

I wonder if the customer can find our argumentation reasonable and change its application
not to insist on doing a patch.

Please remove `quit' from master1,master2 connections. It is essencial in my example
that slave accepts the second `create temporary table foo' before any `drop'.
I.e I meant 

connection master1;
set @@session.pseudo_thread_id=30;
create temporary table foo (id int);

connection master2;
set @@session.pseudo_thread_id=30;
create temporary table foo (id int);

Thanks to Lars I know that mysql client can yield the
secondary wrong binlog, i.e consisting of a query like 'drop temporary foo,foo' from
processing the correct one. Note - without involvement `pseudo_thread_id'. 
It is enough to run two connections on master that `create temporary table foo' each. If
after that gained binlog is fed to mysql client the secondary binlog does have the wrong
query.

I guess this is exacty what our customer meant. 
There is no doubt anymore we have a bug to fix.

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/4924

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/5268

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/5363

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/5364

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/5507

fixed 4.1.20,5.0.22,5.1.10-beta

Documented in 4.1, 5.0 and 5.1 changelogs.

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/6980

This last changeset is actually for BUG#19188.