Description:
Temporary tables...
Observation 1:
Consider two uses of tmp tables:
1. a complex sequence of commands to build a table that needs to be replicated.
2. an intermediate set of data that is to be consumed by a complex SELECT.
The former needs to be replicated for the whole process to work.
The latter will be wasteful if the CREATE TEMPORARY TABLE is replicated.
Observation 2:
SET SQL_BIN_LOG=0 requires SUPER priv, so it cannot be casually used to control whether CREATE TEMPORARY TABLE is replicated.
Observation 3:
There are times when you need a temp table more than once in a single SELECT. Using a subquery (instead) would require evaluating the subquery multiple times, thereby being a performance drain.
Observation 4:
In our dual-master environment, and with a small setup (eg total of 4 machines), it is tempting to have the backup master in read rotation.
Observation 5:
If a CREATE TEMPORARY TABLE is used to assist a SELECT, and if that SELECT hits the backup master, two statements get replicated to the rest of the machines: CREATE TEMPORARY TABLE and DROP TABLE.
Anecdote 1:
I got burned by Observation 5; could not solve it by Observation 2. The immediate fix was to take the backup slave OOR; the medium-term fix was to use two subqueries.
There ought to be a way for a non-SUPER user to control the replication of CREATE TEMPORARY TABLE (and the DROP).
How to repeat:
Create dual-master environment.
Set one master to read-write, the other to read_only.
Use CREATE TEMPORARY TABLE on the read_only master.
Note (in the binlogs) that that CREATE is wastefully replicated to the read-write master.
Suggested fix:
* An option on CREATE TEMPORARY TABLE to allow/inhibit replication?
* Some SET like SQL_BIN_LOG, but usable by non-SUPER users?
* Do not replicate CREATE TEMPORARY TABLE from a server that is read_only? (This might be the best.)
Don't forget to deal with the implicit DROP.
