?
Solved

CREATE TEMPORARY TABLE

Posted on 2003-11-29
17
Medium Priority
?
45,787 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 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