From: Shawn Green (MySQL)
Date: August 8 2011 8:46pm
Subject: Re: Problem creating temp tables during replication
List-Archive: http://lists.mysql.com/replication/2153
Message-Id: <4E404B17.4030903@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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