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
