Bug #8055 Trouble with replication from temporary tables and ignores
Submitted: 20 Jan 2005 19:38 Modified: 22 Feb 2005 20:15
Reporter: Dimitrij HIlt Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.23-standard-log OS:Linux (Debian Linux i32)
Assigned to: Guilhem Bichot CPU Architecture:Any

[20 Jan 2005 19:38] Dimitrij HIlt 
Description:
If one or more temporary are excluded from replication, other temporaere tables will be never dropped on slave.

How to repeat:
1) Setup replication from Host A to Host B.
2) Add 'replicate-ignore-table=test.TMP_tableA' to my.cnf on slave.
3) Start both server
4) connect to master.temp
5) create temporary tables TMP_tableA and TMP_tableB and insert anythink into
6 ) Disconnet client from Master.

In Logfile ( querylog ) you will see:
Query       DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`TMP_tableA`,`test`.`TMP_tableB`

but TMP_tableB will never be droped. If you have many clients, then you will get lot of filehandles in tmpdir.
[4 Feb 2005 21:44] Guilhem Bichot 
ChangeSet@1.2179, 2005-02-04 22:43:54+01:00, guilhem@mysql.com
  Fix for BUG#8055 "Trouble with replication from temporary tables and ignores":
  when we close the session's temp tables at session end, we automatically write to binlog *one* DROP TEMPORARY TABLE *per tmp table*.
[5 Feb 2005 21:40] Guilhem Bichot 
Fixed in 4.1.10 and 5.0.3: the master will write:
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`TMP_tableA`;
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`TMP_tableB`;
instead of 
DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`TMP_tableA`,`test`.`TMP_tableB`;
(one statement per table).
[7 Feb 2005 17:34] Guilhem Bichot 
Patch needs some re-working.
[15 Feb 2005 14:24] Guilhem Bichot 
Fixed in 4.1.10 and 5.0.3 (when 4.1.10 will be merged into 5.0.3)
ChangeSet@1.2159.10.1, 2005-02-14 23:47:17+01:00, guilhem@mysql.com
[18 Feb 2005 9:48] Guilhem Bichot 
By the way I forgot to say how it is fixed.
Replication slaves >=4.1.11 will always replicate a DROP TABLE IF EXISTS (the replicate-*-table inclusion/exclusion rules will be ignored for this type of statement).
Master needn't be upgraded, only slave.
[18 Feb 2005 9:50] Guilhem Bichot 
Last but not least, I formerly wrote that the fix was in 4.1.10, but unfortunately it is in 4.1.11.
[22 Feb 2005 20:15] Paul DuBois 
Noted in 4.1.11 changelog.