Solved

CREATE TEMPORARY TABLE

Posted on 2003-11-29
17
45,772 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

729 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