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: | |
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
[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.