List:Replication« Previous MessageNext Message »
From:Rick James Date:August 8 2011 6:47pm
Subject:Re: Problem creating temp tables during replication
View as plain text  
Temp tables and replication do not work well together.

Case 1:  A temp table that is just used to support a complicated SELECT 
gets replicated to all slaves.  The CREATE TEMPORARY TABLE and the DROP 
TABLE are replicated -- a total waste.

Case 2:  A temp table used to support an INSERT must be replicated.

A nasty situation we had with Case 1:
We had a dual-master (single-writer) situation.  The backup master was 
in "read" rotation behind a load balancer.  This meant the otherwise 
readonly clients needed to be granted CREATE TEMPORARY TABLE, etc.  And 
the temp table was being created and dropped all over the system (both 
masters, all slaves).

(I submitted a bug, but did not get much traction.)


On 8/5/11 11:10 AM, Database System wrote:
> I didn't pay attension to that part. You can either have temporary table or memory
> storage engine table, cannot have both. Because they work differntly.
>
> --- On Fri, 8/5/11, Wagner Bianchi<wagnerbianchijr@stripped>  wrote:
>
>> From: Wagner Bianchi<wagnerbianchijr@stripped>
>> Subject: Re: Problem creating temp tables during replication
>> To: "Eric Bergen"<eric.bergen@stripped>
>> Cc: "Alex Frangis"<afrangis@stripped>, replication@stripped
>> Date: Friday, August 5, 2011, 12:05 PM
>> it seems strange to CREATE TEMP TABLE
>> ... ENGINE=MEMORY...
>>
>> Best wishes,
>> --
>> Wagner Bianchi - +55 (31) 8654 - 9510
>> *LinkedIn*: http://br.linkedin.com/in/wagnerbianchi
>> *Twitter*: @wagnerbianchijr
>> *Skype*: wbianchijr
>>
>>
>>
>> 2011/8/5 Eric Bergen<eric.bergen@stripped>
>>
>>> You're getting permission denied from the filesystem
>> (errno 13). It's
>>> likely that your tmpdir on the slave is pointed to a
>> directory that
>>> mysql doesn't have access to create files in.
>>>
>>> On Fri, Aug 5, 2011 at 8:02 AM, Database System<database100@stripped>
>>> wrote:
>>>> Alex,
>>>>
>>>> Replication runs as system user, so it doesn't
>> need special privilege to
>>> create a table. I think the temporary table is only
>> belonged to the account
>>> who created, so I'm not surprised if it cannot be
>> replicated to a different
>>> server.
>>>> I did test on 5.1. It didn't replicated, but
>> there was no error message,
>>> and the replication is still running without
>> interruption.
>>>> Why do you have to create temporary table?
>>>>
>>>> Lisa
>>>>
>>>> --- On Thu, 8/4/11, Alex Frangis<afrangis@stripped>
>> wrote:
>>>>> From: Alex Frangis<afrangis@stripped>
>>>>> Subject: Problem creating temp tables during
>> replication
>>>>> To: replication@stripped
>>>>> Date: Thursday, August 4, 2011, 4:58 PM
>>>>> Hello All,
>>>>>
>>>>> I am trying to configure mysql replication
>> between two
>>>>> hosts but running into the following issue.
>>>>>
>>>>> /[ERROR] Slave: Error 'Can't create table
>>>>> 'temp_union_884736' (errno: 13)' on query.
>> Default database:
>>>>> 'obfuscated'. Query: 'CREATE TEMPORARY TABLE
>>>>> temp_union_884736 ENGINE=MEMORY SELECT
>> id_location,
>>>>> loc_name, read_key from locations WHERE
>> id_location = -1',
>>>>> Error_code: 1005110705 20:45:27/
>>>>>
>>>>> /[ERROR] Error running query, slave SQL
>> thread aborted. Fix
>>>>> the problem, and restart the slave SQL thread
>> with "SLAVE
>>>>> START". We stopped at log 'mysql-bin.000010'
>> position
>>>>> 457613049/
>>>>>
>>>>> It seems that the slave is failing to
>> replicate because it
>>>>> is unable to create the temporary table
>> 'temp_union_88473',
>>>>> however I am unsure as to the reason for
>> this. The mysql
>>>>> documentation indicates that normally
>> temporary tables are
>>>>> replicated, and does not seem to offer any
>> special way of
>>>>> replicating temporary tables as opposed to
>> regular tables.
>>>>> Could this be a permissions issue with the
>> replication user
>>>>> account? If so what permissions would be
>> necessary?
>>>>> Currently the user has only slave replication
>> privileges on
>>>>> both slave and master servers.
>>>>>
>>>>> Because the application which uses this
>> database may depend
>>>>> on this, or other temporary tables, not
>> replicating them is
>>>>> not an option.
>>>>>
>>>>> Any information on what could be preventing
>> the table from
>>>>> being created on the slave, or how to find
>> such information
>>>>> would be greatly appreciated!
>>>>>
>>>>> Best Regards,
>>>>>
>>>>> - Chris
>>>>>
>>>>>
>>>> --
>>>> MySQL Replication Mailing List
>>>> For list archives: http://lists.mysql.com/replication
>>>> To unsubscribe:
>>> http://lists.mysql.com/replication?unsub=1
>>>>
>>>
>>>
>>> --
>>> Eric Bergen
>>> eric.bergen@stripped
>>> http://www.ebergen.net
>>>
>>> --
>>> MySQL Replication Mailing List
>>> For list archives: http://lists.mysql.com/replication
>>> To unsubscribe:
>>> http://lists.mysql.com/replication?unsub=1
>>>
>>>

-- 
Rick James - MySQL Geek

Thread
Problem creating temp tables during replicationAlex Frangis5 Aug
  • Re: Problem creating temp tables during replicationDatabase System5 Aug
    • Re: Problem creating temp tables during replicationEric Bergen5 Aug
      • Re: Problem creating temp tables during replicationWagner Bianchi5 Aug
        • Re: Problem creating temp tables during replicationDatabase System5 Aug
          • Re: Problem creating temp tables during replicationRick James8 Aug
  • Re: Problem creating temp tables during replicationMySQL)8 Aug
    • Re: Problem creating temp tables during replicationDatabase System8 Aug
      • Re: Problem creating temp tables during replicationMySQL)9 Aug
        • Re: Problem creating temp tables during replicationDatabase System9 Aug
          • Re: Problem creating temp tables during replicationMySQL)9 Aug