List:Replication« Previous MessageNext Message »
From:Rick James Date:February 8 2010 10:21pm
Subject:Re: how to never log any CREATE TEMPORARY TABLE statement to the
binary log
View as plain text  
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



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