Bug #17263 incorrect DROP query in temporary tables replication
Submitted: 9 Feb 2006 5:57 Modified: 3 May 2006 10:02
Reporter: Lachlan Mulcahy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.1.18 OS:Any (Any)
Assigned to: Andrei Elkin CPU Architecture:Any

[9 Feb 2006 5:57] Lachlan Mulcahy
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
[2 Apr 2006 15:55] Andrei Elkin
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.
[2 Apr 2006 17:54] Andrei Elkin
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);
[3 Apr 2006 11:14] Andrei Elkin
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.
[13 Apr 2006 15:35] 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/4924
[21 Apr 2006 7:36] 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/5268
[23 Apr 2006 16:42] 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/5363
[23 Apr 2006 17:01] 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/5364
[25 Apr 2006 19:54] 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/5507
[2 May 2006 9:27] Andrei Elkin
fixed 4.1.20,5.0.22,5.1.10-beta
[3 May 2006 10:02] MC Brown
Documented in 4.1, 5.0 and 5.1 changelogs.
[29 May 2006 14:56] 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/6980
[30 May 2006 13:24] Lars Thalmann
This last changeset is actually for BUG#19188.