A temporary table doesn't create .frm file, even if you say engine=memory.
--- On Mon, 8/8/11, Shawn Green (MySQL) <shawn.l.green@stripped> wrote:
> From: Shawn Green (MySQL) <shawn.l.green@stripped>
> Subject: Re: Problem creating temp tables during replication
> To: "Alex Frangis" <afrangis@stripped>
> Cc: replication@stripped
> Date: Monday, August 8, 2011, 3:46 PM
> On 8/4/2011 17:58, Alex Frangis
> > 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
> > Because the application which uses this database may
> depend on this, or
> > other temporary tables, not replicating them is not an
> > 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
> 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
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe: http://lists.mysql.com/replication?unsub=1