List:Replication« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:August 8 2011 8:46pm
Subject:Re: Problem creating temp tables during replication
View as plain text  
On 8/4/2011 17:58, Alex Frangis wrote:
> 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
>
>

The poster who said you cannot do CREATE TEMPORARY ... ENGINE=MEMORY is 
wrong.

The poster who noticed the "errno: 13" message is on the right track. 
Even though MEMORY tables store their data and their indexes (yes, even 
temporary MEMORY tables may be indexed) they still need to create a .frm 
file within the folder of the database in which you are creating the 
table.  Check that location for proper permissions.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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