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.
In other words, I needed CREATE LOCAL TEMPORARY TABLE. (LOCAL == do not
replicate)
From: Johan De Meersman <vegivamp@stripped>
Date: Mon, 8 Feb 2010 22:15:01 +0100
To: Rick James <rjames@stripped>
Cc: Tyler Poland <tpoland@stripped>, Aodhan Cullen <aodhan@stripped>,
<replication@stripped>
Subject: Re: how to never log any CREATE TEMPORARY TABLE statement to the
binary log
On Mon, Feb 8, 2010 at 8:00 PM, Rick James <rjames@stripped> wrote:
> I, too, got burned big time by a create temp table. We had a dual-master
> setup, with slaves hanging off both masters (only one master was in write
> rotation). The standby master was in read rotation, so it received the
> CREATE TEMPORARY TABLE requests, which were dutifully replicated to the main
> master and all slaves. So was the DROP. It was a big waste, and a big
> burden.
I haven't met the tmptable scenario, so I may be wrong, but since MySQL
replication is statement-based, doesn't not replicating your tmp table
creation entail that your subsequent statements that use it will also fail ?
>
> No -- set sql_log_bin=0; does not work -- it requires (in some versions)
> SUPER. Having web servers connect as SUPER is totally unacceptable.
> (Johan
> -- your 0 and 1 were reversed.)
Oops... What, doesn't everyone read their mails bottom-to-top ? :-)
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
