From: Rick James
Date: February 8 2010 10:21pm
Subject: Re: how to never log any CREATE TEMPORARY TABLE statement to the 
 binary log
List-Archive: http://lists.mysql.com/replication/1819
Message-Id: <C795CE7F.268BF%rjames@yahoo-inc.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="B_3348483711_40444960"

--B_3348483711_40444960
Content-type: text/plain;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable

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, eve=
n
if that machine was replicating to others.

In other words, I needed CREATE LOCAL TEMPORARY TABLE.  (LOCAL =3D=3D 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. =A0We had a dual-master
> setup, with slaves hanging off both masters (only one master was in write
> rotation). =A0The standby master was in read rotation, so it received the
> CREATE TEMPORARY TABLE requests, which were dutifully replicated to the m=
ain
> master and all slaves. =A0So was the DROP. =A0It 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 =
?

=A0
>=20
> No -- set sql_log_bin=3D0; does not work -- it requires (in some versions)
> SUPER. =A0Having web servers connect as SUPER is totally unacceptable. =A0(Jo=
han
> -- your 0 and 1 were reversed.)

Oops... What, doesn't everyone read their mails bottom-to-top ? :-)
=A0




--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel



--B_3348483711_40444960--