List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 4 2003 11:31pm
Subject:Re: Temporary Tables
View as plain text  
At 23:19 +0000 3/4/03, Mamatha Balasubramanian wrote:
>Thank you once again.
>
>I have a web-interface that does search on a given text and I would 
>have a script that creates a temporary table. So according to you, 
>in my script, I just to need
>create a temporary table and not have to worry about another client 
>using the same web interface (and thereby using the same program). 
>Can you please elaborate a little more on this?

Sure.

You are incorrect. :-)

That is, you're making an assumption that cannot necessarily be made.
If you can guarantee that the web script will establish a new connection,
and the connection will terminate when the script ends, you can indeed
do what you describe above.

But you *cannot* do that if you're running your script in an environment
that uses persistent connections that may be used by successive instances
of the script.  PHP persistent connections fall into this class, for
example.  Several requests might be served by the same instance of the
web server process, and you don't know that one request won't be getting
the connection used by a previous request.  In that case, the connection
won't have closed, and the TEMPORARY table won't have disappeared.

You can guard against this by issuing this query before creating the
TEMPORARY table:

DROP TABLE IF EXISTS tbl_name


>
>Thanks,
>Mamatha
>
>
>
>
>
>>From: Paul DuBois <paul@stripped>
>>To: "Mamatha Balasubramanian" 
>><mamathasmysql@stripped>,mysql@stripped
>>Subject: Re: Temporary Tables
>>Date: Tue, 4 Mar 2003 17:06:30 -0600
>>
>>At 23:00 +0000 3/4/03, Mamatha Balasubramanian wrote:
>>>Hi,
>>>I would like to know how MySQL handles multiple temporary tables?
>>>
>>>1. Can multiple temporary tables be created at the same time?
>>>2. If so, how does MySQL differentiate them - do we need to 
>>>explicitly give them different names inorder to identify them or 
>>>does MySQL provide a timestamp (or use some other means) to 
>>>identify the tables?
>>
>>You can create multiple temporary tables, but they must have different
>>names.
>>
>>A TEMPORARY table can have the same name as a non-TEMPORARY table.
>>The non-TEMPORARY table is hidden to the client that creates the
>>TEMPORARY table as long as the TEMPORARY table exists.
>>A second TEMPORARY table with the same name cannot be created.
>>
>>This is on a connection-specific basis.  Two clients each can create
>>a TEMPORARY table with the same name.  Only the table created by a given
>>client is visible to that client.
>>
>>>
>>>I use MySQL 4.0.7 on Red Hat.
>>>
>>>Thanks,
>>>Mamatha

Thread
Temporary TablesMamatha Balasubramanian5 Mar
  • Re: Temporary TablesPaul DuBois5 Mar
  • Re: Temporary TablesDaniel Kasak5 Mar
Re: Temporary TablesMamatha Balasubramanian5 Mar
  • Re: Temporary TablesPaul DuBois5 Mar
    • Re: Temporary TablesWilliam R. Mussatto5 Mar
      • Re: Temporary TablesPaul DuBois5 Mar
    • Re: Temporary TablesKH Chiu5 Mar
    • Re: Temporary TablesDaniel Kasak5 Mar
      • Re: Temporary TablesPaul DuBois5 Mar
Re: Temporary TablesMamatha Balasubramanian5 Mar