I want to create a temporary table with some select-data.
My (strange) problem is that I have to do the same query more than one time.
CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id))
SELECT id FROM table WHERE xyz;
CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id))
SELECT id FROM table WHERE xyz;
Unfortunately, MySQL executes the second query although the temp-table already exists.
QUESTIONS
- Is there a way to tell MySQL that the creation of the temporary table and the
SELECT
query are just executed if the table does not exist? - Maybe with an if-condition?
- Or can I check to some information schema if a temp-table exists?
Thanks for some help!
Best regards,
Timo
Best Answer
Here is the nature of table creation with
CREATE TEMPORARY TABLE
My guess would be you must have done the following sequence of events:
CREATE TEMPORARY TABLE
CREATE TEMPORARY TABLE
again because he table did not existI have seen this problem victimize many developers, especially when it comes to MySQL Replication. For example, if you run
STOP SLAVE;
, the SQL Thread will close. Any tables create withCREATE TEMPORARY TABLE
will be dropped. When you runSTART SLAVE;
, MySQL Replication breaks instantly because the table needed for INSERTs, UPDATEs, or DELETEs no longer exist. I have had to perform mysqlbinlog dumps on relay logs to locate the originalCREATE TEMPORARY TABLE
statements, run them asCREATE TABLE
in order for the table to remain accessible to other DB Connections, including the SQL Thread whenSTART SLAVE;
is reissued.As for your Questions
SELECT
query are just executed if the table does not exist?You cannot check the information_schema for temp table existence. In the OS, there is no manifested
.frm
indatadir
. You could find it in the folder defined intmpdir
.Here is an example: I will create a temporary table called
test.junk
and try to locate the whereabouts of the table. (Note: I am using MySQL for Windows)The table is not visible in INFORMATION_SCHEMA.TABLES. Let's locate the table in the OS
There is the temp table, in the tmpdir folder. When I closed the mysql client and run
dir C:\Users\redwards\AppData\Local\Temp\*.frm
, the table vanished.RECOMMENDATION
You can do one of two things
CREATE TEMPORARY TABLE IF NOT EXISTS
(This is really a band-aid to improper design but may circumvent the problem).UPDATE 2012-12-18 13:06 EDT
Further Observation #1
If you stayed with the same DB Connection and still have this problem, here something else not previously mentioned: You gave the temp table an index. That should be a UNIQUE INDEX or PRIMARY KEY.
Further Observation #2
You could shift the operation from a
CREATE TABLE ... SELECT
toCREATE TABLE ... INSERT IGNORE
Further Observation #3
If you want to use temp tables, please specify MyISAM. InnoDB has a tendency to leave pigeon holes in the data dictionary with ibdata1 in the event of a crash or upon DB Connection termination.
With these three(3) observations in mind, here is my recommended change
Give it a Try !!!