List:General Discussion« Previous MessageNext Message »
From:Nick Arnett Date:July 1 2006 12:29am
Subject:DROP TEMPORARY TABLE waiting for table???
View as plain text  
I'm seeing something that I don't think should be happening, but I'm
not sure if it's a MySQL bug.

To allow some of my stored procedures to operate concurrently, I make
a temporary memory copy of some shared tables that are accessed by the
procedures.  The temporary heap table has the same name as the table
I'm copying.  The scenario here is a data warehouse with a bunch of
data marts and some shared dimensions that are in a separate database.
 I'm copying the necessary rows of the commonly used shared dimension
tables, to overcome the problem of stored procedures locking all the
tables they're going to use, which was preventing concurrency.

The problem is that despite this, I'm seeing processes that are stuck
with status "Waiting for table" when they are trying to drop the
temporary table if it exists ("DROP TEMPORARY TABLE IF EXISTS
shared_dimensions.page_dim").  I always drop and recreate it at the
start of a series of analyses, so that they have the most recent copy.

I create the temporary heap table in an independent procedure, so it
can't be locking the table.  There are other procedures using their
own temporary heap table copies of the table I'm copying, but they're
not using the "real" table, only the copy.

So... my question is, why is there any problem dropping a table that
should only be visible to the connection that's trying to drop it?
What's even more bizarre is that I get this problem even when the
temporary table doesn't exist, on a brand-new connection.

I've had this code running for a couple of weeks and just noticed the
problem, so I'm not sure if it cropped up right away or not.  Haven't
had a chance to bounce the server yet.

Any insight appreciated.


Nick Arnett
Messages: 408-904-7198
DROP TEMPORARY TABLE waiting for table???Nick Arnett1 Jul