List:Replication« Previous MessageNext Message »
From:Guilhem Bichot Date:February 9 2010 8:26am
Subject:Re: how to never log any CREATE TEMPORARY TABLE statement to the
binary log
View as plain text  
Hello,

Rick James a écrit, Le 08.02.2010 23:21:
> It is "right" for MySQL (in SBR) to replicate the CREATE and DROP.  It does
> not know what you will be doing with the table.  In my case it was a
> convenience -- Gather a subset of data from some other tables, then  use
> that subset for multiple SELECTs to produce a web page.  The SELECTs were
> not replicated (that is correct), so the temp table was costly but useless
> in the repl stream.  Performance, not "correctness" was my problem.  I
> needed a way for CREATE TEMPORARY TABLE to be local to the one machine, even
> if that machine was replicating to others.

Yes, makes sense.
Starting from MySQL 5.1 there exists row-based replication (mysqld 
--binlog-format=row) which never writes CREATE TEMPORARY to the binlog, 
because the slave does not need to know about temporary tables.
Using this new type of replication could be a good idea or not, to be 
tested.
Using it only in the session which uses the temporary table (with SET 
SESSION BINLOG_FORMAT=ROW) requires SUPER, so won't help here.

> In other words, I needed CREATE LOCAL TEMPORARY TABLE.  (LOCAL == do not
> replicate)

As you probably know, this already exists for certain statements like 
ANALYZE TABLE, OPTIMIZE TABLE, FLUSH TABLES:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
     tbl_name [, tbl_name] ...

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

but not for CREATE TABLE. I guess a reason is that:
- ANALYZE (which requires only INSERT and SELECT privileges) is kind of 
a "no-change" operation
- so allowing the user to not send it to the binary log is not very 
dangerous
- on the other hand, if the user does not send CREATE TEMPORARY TABLE to 
the binlog (using a LOCAL keyword if it was supported), and then 
accidentally sends a statement which updates a real table and depends on 
the temporary table (INSERT INTO real_table SELECT * FROM temp_table), 
then replication is going to stop. So only a user who knows what it is 
doing should be allowed to disable binlogging of CREATE TEMPORARY TABLE, 
which is why SUPER is required (to set SQL_LOG_BIN to 0).

We could say that CREATE LOCAL TEMPORARY TABLE marks the temp table as 
"don't replicate any statement using this table", but then this is 
dangerous again as a statement like "INSERT INTO real_table SELECT * 
FROM temp_table" will not be sent to the slave and slave will go out of 
sync.
Maybe it should be "don't replicate any statement using this table and 
refuse to let it be involved in updates to real tables".

Maybe it is simpler to use row-based replication.

-- 
Mr. Guilhem Bichot <guilhem@stripped>
Sun Microsystems / MySQL, Lead Software Engineer
Bordeaux, France
www.sun.com / www.mysql.com
Thread
how to never log any CREATE TEMPORARY TABLE statement to the binary logAodhan Cullen8 Feb
  • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logJohan De Meersman8 Feb
    • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logSuresh Kuna8 Feb
  • Re: how to never log any CREATE TEMPORARY TABLE statement to thebinary logTyler Poland8 Feb
    • Re: how to never log any CREATE TEMPORARY TABLE statement to thebinary logRick James8 Feb
      • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logAodhan Cullen8 Feb
      • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logJohan De Meersman8 Feb
        • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logRick James8 Feb
          • Re: how to never log any CREATE TEMPORARY TABLE statement to thebinary logGuilhem Bichot9 Feb