Bug #3324 Temporary tables and slave shutdown leads to replication failure
Submitted: 29 Mar 2004 7:09 Modified: 29 Mar 2004 8:48
Reporter: [ name withheld ] Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.18-standard OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[29 Mar 2004 7:09] [ name withheld ]
Description:
If a series of queries like the following is executed on the master: 
 
create temporary table temp like perm; 
insert into temp select * from perm where X; 
update temp set A=B; 
replace into perm select * from temp; 
 
and a replication slave is shut down while this is in progress, then on startup 
replication will fail due to the temporary table having been deleted by the 
shutdown. 

How to repeat:
See description. 

Suggested fix:
The "lifetime" and "visibility" of temporary tables should be defined on the 
master, and exactly copied by all the slaves. So it would be better to write 
the CREATE TABLE statement to the binlog as a non-temporary table, with a 
randomized/unique name, and explicitly drop it. Then it should be safe to shut 
down and restart slaves. 
 
Alternatively, if replication of temporary tables is not supported, there 
should be a note to this effect in the manual. 
 
Cheers, Chris.
[29 Mar 2004 7:23] Guilhem Bichot
> Alternatively, if replication of temporary tables is not supported, there
> should be a note to this effect in the manual. 
> Cheers, Chris. 

Hello,
There is a note:
from http://www.mysql.com/doc/en/Replication_Features.html:
"Temporary tables are replicated with the exception of the case that you shut down slave server (not just slave thread) and you have some replicated temporary tables that are used in update statements that have not yet been executed on the slave. (If you shut down the slave, the temporary tables needed by those updates no longer are available when the slave starts again.) To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use this procedure:

   1. Issue a STOP SLAVE statement.
   2. Use SHOW STATUS to check the value of the Slave_open_temp_tables variable.
   3. If the value is 0, issue a mysqladmin shutdown command to shut down the slave.
   4. If the value is not 0, restart the slave threads with START SLAVE.
   5. Repeat the procedure later to see if you have better luck next time. 

We have plans to fix this problem in the near future."
The fix will probably be to simply use row-level logging which we are to implement soon.
Your idea about a randomized permanent table name is good too, but how do you handle the case where a client on the slave wants to create a table with precisely (bad luck) this name?
[29 Mar 2004 7:42] [ name withheld ]
Sorry, I missed the note under Replication Features. How about a link to it 
from the Table Types section?
[29 Mar 2004 7:54] Guilhem Bichot
Duplicate of BUG#352
[29 Mar 2004 7:55] Guilhem Bichot
Well, I'd prefer to not put replication info in too many places in the manual.
The rule is:
if you use replication, you must read the replication chapter entirely.
[29 Mar 2004 7:58] [ name withheld ]
Sorry, I did search for "temporary replication" in the bugs database before I 
filed the issue, and bug #352 didn't show up then (and still doesn't).
[29 Mar 2004 8:12] Guilhem Bichot
No problem that you did not find BUG#352. Its title is
"Slave loses temp tables when it is shutdown/restarted".
We don't require from users that they have a lucky search :)
Until we fix the problem in any way, I suggest you use the randomized name permanent table *on master* (I used something like concat("mytable", unix_timestamp(), connection_id()) in my applications). Then it will work fine on slave.
[29 Mar 2004 8:16] [ name withheld ]
Thanks, I will. 
 
By the way, the wording of the "advanced search" dialog suggests that it will 
find the words anywhere in the bug report, not just the title. If I had 
realised that only the title was being checked, I would have tried a few more 
searches before submitting a new bug report. 
 
Cheers, Chris.
[29 Mar 2004 8:48] Guilhem Bichot
> By the way, the wording of the "advanced search" dialog suggests that
> it will find the words anywhere in the bug report, not just the title. If I had
> realised that only the title was being checked, I would have tried a
> few more searches before submitting a new bug report. 

Yes, it searches anywhere in the bug report, body included. But I found the problem: see the box named "return only bugs with status" in the advanced search; it defaults to "Active". But BUG#352 has status "Deferred" (it's a non-easily fixable bug, so it means "fix is deferred to later"), which apparently is not considered "Active". If I select "All" instead of "Active", I get many more bugs but fortunately BUG#352 is among them on the first page :)