Solved

CREATE TEMPORARY TABLE

Posted on 2003-11-29
17
45,757 Views
Last Modified: 2011-08-18
Having a problem with create temporary table.

I have these queries, and when I go to display in the browser, it gives me the error of "Table 'packets' already exists"

CREATE TEMPORARY TABLE packets
SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber
FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID
WHERE (((PacketDetail.HasDropDown)=1))
ORDER BY PacketDetail.MaterialID ASC;

SELECT materialid, dropdowntext, dropdownid
FROM packetdropdown INNER JOIN packets USING(MaterialID);

Any suggestions?
0
Comment
Question by:brisctt
  • 9
  • 7
17 Comments
 
LVL 7

Expert Comment

by:jconde
ID: 9844415
Hi!

First of all, if you're using 4.0.2 or above, make sure you have "CREATE TEMPORARY TABLES" privilege (Create_tmp_table_priv in mysql.User)

Second, Temporary tables are created and automatically deleted when the connection is closed.

You might want to issue the following query before you execute your CREATE TABLE query:

DROP TEMPORARY TABLE IF EXISTS packets

0
 

Author Comment

by:brisctt
ID: 9844459
Thanks for the info.  I'm using version 4.0.13.  I'm also using phpmyadmin though my ISP, so I'm not sure of the permissions.  I would imagine I would have them, as they are pretty good with stuff like that.

I tried to add the DROP query before my others and it gave me another error of :

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE packets SELECT PacketDetail.MaterialID,

This whole page seems like it should be easier to create than it is.  It doesn't seem that crazy of a query.

Thanks again for the info.
0
 
LVL 7

Expert Comment

by:jconde
ID: 9844550
Ok, to check if you have rights to create temp tables, try doing the following:

CREATE TEMPORARY TABLE temp_table1 (a int)

if that succeeds, then it means you do have rights to create temp tables (which you should because you're not getting access denied errors).

Now, regarding your query ...

I tried the following out and had no problems:

mysql mysql
mysql> DROP TEMPORARY TABLE IF EXISTS packets;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TEMPORARY TABLE packets SELECT User, Host FROM user;
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

And worked as expected ... my query is not a whole lot different than yours which makes me wonder ... is there any chance you're trying to select unexistant columns from PacketDetail ?  What about this being a bug in phpmyadmin ? ... I have no experience with it, but maybe its parsing the create statement and expects "simple" statements that don't support the SELECT clause in them ?


0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 7

Expert Comment

by:jconde
ID: 9844569
Hi!

Here's an example of a query almost exactly the same as yours:

mysql> CREATE TEMPORARY TABLE packet
-> SELECT user.User, user.Host FROM user INNER JOIN user2 on user.Host = user2.Host
-> WHERE user.Host='localhost'
-> ORDER BY user.Host ASC;

Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

So this basically confirms one of the 2 questions I asked in my previous comment.  I'm using 4.0.15, but none of the TEMPORARY tables nor INNER JOIN code has changed between 13 and 15.

Hope this helps a bit ...
0
 

Author Comment

by:brisctt
ID: 9844602
I certainly appreciate all your help.  Everything seems ok with everything, but I still get the "table already exists" error.

Very strange to me.  

I've tried messing with the query abit, and I seem to be getting closer, but not quite yet.  This is what I have for a query, any input would be greatly appreciated.

SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber
FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID
WHERE (((PacketDetail.HasDropDown)=1))
ORDER BY PacketDetail.MaterialID ASC

SELECT PacketDropDown.PacketDropDownID, PacketDropDown.PacketDropText
FROM PacketDropDown RIGHT JOIN PacketDetail ON PacketDropDown.MaterialID = PacketDetail.MaterialID

On the dropdown menu it still shows all the results, but there is a space between them....it should be able to show only what corresponse to the MaterialNumber.
0
 
LVL 7

Expert Comment

by:jconde
ID: 9844621
Is there any chance the table "packets" exists in any other way than a temporary table ? ... if you call "SHOW TABLES" can you see a table called "packet" ?
0
 

Author Comment

by:brisctt
ID: 9844634
I doubled and triple checked the tables already...nothing with packets....I even tried to use another Temp table name and it still gave me the same error.

Pretty crazy.  I know I sure don't understand what's going on with it.

Thanks for the continued help.  

Do you know of another way to get the end result of what I am looking for?
0
 
LVL 7

Expert Comment

by:jconde
ID: 9844637
the only way you could get that error is if the temporary table "packet" was prevously created and if you didn't close the connection.

That's why in my first post I suggested you called:

"DROP TEMPORARY TABLE IF EXISTS packets"

and after the query executes, call your CREATE TEMP TABLE query.  Otherwise, make sure you call your create temp query right _after_ the connection to mysql has been established.  If you plan on calling the same query again, you'll either have to execute the drop temp table query I told you about, or disconnect completely from mysql and reconnect.  Obviously, the easiest is to take the first approach :)

Does the above make any sense ?
0
 
LVL 7

Expert Comment

by:jconde
ID: 9844640
you tried another name and got the same results ????

Ok, this is starting to get weird :(

what OS is this on ?
0
 
LVL 7

Expert Comment

by:jconde
ID: 9844645
btw, did you try this:

CREATE TEMPORARY TABLE temp_table1 (a int)

did you get the "already exists" error also ?
0
 
LVL 7

Expert Comment

by:jconde
ID: 9844653
I forgot to mention one thing ... have you tried

CREATE TEMPORARY TABLE IF NOT EXISTS packets
SELECT .....

?  ... this will probably not work for you as somehow from what I understand mysql thinks the table exists when it really doesn't, but it might give us a clue on whats going on.
0
 

Author Comment

by:brisctt
ID: 9844677
I tried the Create Temp If Not Exists....I didn't get the already exists error, I got another error.

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource
0
 

Author Comment

by:brisctt
ID: 9844688
In response to your OS question.  I'm using 98SE

Also, when I did the CREATE TEMPORARY TABLE temp_table1 (a int) it gave me the same error already exists.
0
 
LVL 7

Accepted Solution

by:
jconde earned 500 total points
ID: 9844738
you're using Win98, but the mysql server is installed somewhere else correct ?

Is there any way you can try this out bypassing all of the phpmyadmin stuff and doing it directly using the console client of mysql in your mysql server ?
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9845410
You can also try working around things by using a non-temporary table.

DROP TABLE IF EXISTS test;

CREATE TABLE test
SELECT * FROM anothertable;

SELECT * FROM test;

DROP TABLE test;

That would have the same effect as a temporary table.
0
 

Author Comment

by:brisctt
ID: 9845672
I put the query below in the MySQL console and it returned data, so it may be the way it interfaces with the Dreamweaver MX and phpmyadmin.  

CREATE TEMPORARY TABLE packets
SELECT PacketDetail.MaterialID, PacketDetail.MaterialNumber
FROM PacketDetail INNER JOIN PacketType ON PacketDetail.PacketType = PacketType.PacketTypeID
WHERE (((PacketDetail.HasDropDown)=1))
ORDER BY PacketDetail.MaterialID ASC;

SELECT materialid, dropdowntext, dropdownid
FROM packetdropdown INNER JOIN packets USING(MaterialID);

I'm going to do some testing and see what else happens.  Thank you everyone for all your help...
0
 

Author Comment

by:brisctt
ID: 9847689
I was able to get a query going through the MySQL console and bypassing the phpmyadmin.  I appreciate all your help.  I still need to figure out how I am going to implement the query via the Dreamweaver MX and phpmyadmin, but thanks to your help I can see that it's not the query itself.

Thank you so much for all your input.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to count in a table in php 22 49
paypal ipn to mysql 3 76
MySQL 5.6.30 - daily outages 46 58
Could you point what's preventing a remote MySQL server to be accessed? 8 35
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question