List:Replication« Previous MessageNext Message »
From:Database System Date:August 8 2011 9:13pm
Subject:Re: Problem creating temp tables during replication
View as plain text  
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
> 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
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication?unsub=1
> 
> 
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