Solved

CREATE TEMPORARY TABLE

Posted on 2003-11-29
17
45,729 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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 …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now