List:Bugs« Previous MessageNext Message »
From:sasha Date:October 31 2000 8:08pm
Subject:Re: DROP TABLE on temporary tables not replicating
View as plain text  
"Carsten H. Pedersen" wrote:
> 
> > From: sasha@stripped [mailto:sasha@stripped]On
> > Sent: Tuesday, October 31, 2000 4:49 PM
> > Subject: Re: DROP TABLE on temporary tables not replicating
> > Daniel Kerr wrote:
> > >
> > > >Description:
> > >
> > > I recently noticed replication had stopped between two of our
> > > MySQL-3.23.27-beta servers in a master/slave relationship. The slave
> > > aborted replication because of a 'create table' on a temporary
> > table that
> > > already existed. This table had infact been dropped on the
> > master prior to
> > > being recreated but the slave hadn't received the drop table message.
> >
> > That is a bug, DROP TABLE does not get logged if the table is temporary.
> > However, there is a bigger problem - replication of temporary tables is
> > currently broken. If you are not doing INSERT INTO .. SELECT FROM
> > tmp_table, a
> > viable workaround would be to execute SET SQL_LOG_BIN = 0 before CREATE
> > TEMPORARY TABLE - you must have process priv for this. We will be
> > fixing temp
> > tables replication shortly...
> >
> > BTW, mysqlbinlog is the proper (although not yet documented) way
> > to examine the
> > replication logs.
> 
> I'm a bit confused by this... What is the point in
> replicating temporary tables?
> 
> My understanding of temporary tables:
> - they are (should be) used for small result sets, as a
>   way of getting around limits imposed by not having
>   subqueries
> - they only exist for a single client connection, and can
>   only be accessed by that client
> - they are deleted if the client goes away
> - changes to temporary tables should never be written to
>   disk anyway
> 
> Of course updates to non-temporary tables should be logged for
> replication, even if they are based on temporary tables. But
> wouldn't it make more sense to just replicate these updates
> instead of replicating everything that happen to the temporary
> tables?

MySQL replicates one query at a time - so *all* tables must be exactly the same
on the master and on the slave in order for all queries to do *exactly* the same
thing. So for perfect replication you do need to replicate temporary and heap
tables, as well as all the user variables, timestamp value and auto_increment
value.

-- 
MySQL Development Team
   __  ___     ___ ____  __ 
  /  |/  /_ __/ __/ __ \/ /   Sasha Pachev <sasha@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Provo, Utah, USA
       <___/
Thread
DROP TABLE on temporary tables not replicatingDaniel Kerr31 Oct
  • Re: DROP TABLE on temporary tables not replicatingsasha31 Oct
    • RE: DROP TABLE on temporary tables not replicatingCarsten H. Pedersen31 Oct
      • Re: DROP TABLE on temporary tables not replicatingsasha31 Oct