[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45806
  • Last Modified:

CREATE TEMPORARY TABLE

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
brisctt
Asked:
brisctt
  • 9
  • 7
1 Solution
 
jcondeCommented:
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
 
briscttAuthor Commented:
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
 
jcondeCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jcondeCommented:
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
 
briscttAuthor Commented:
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
 
jcondeCommented:
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
 
briscttAuthor Commented:
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
 
jcondeCommented:
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
 
jcondeCommented:
you tried another name and got the same results ????

Ok, this is starting to get weird :(

what OS is this on ?
0
 
jcondeCommented:
btw, did you try this:

CREATE TEMPORARY TABLE temp_table1 (a int)

did you get the "already exists" error also ?
0
 
jcondeCommented:
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
 
briscttAuthor Commented:
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
 
briscttAuthor Commented:
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
 
jcondeCommented:
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
 
SqueebeeCommented:
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
 
briscttAuthor Commented:
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
 
briscttAuthor Commented:
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now