Temporary table doesn't exist?!?

I'm using the following query to create a temporary table:

CREATE TEMPORARY TABLE _temptable
SELECT _itemroot AS 'ITEM ROOT',
CASE WHEN _umex = 'FT' THEN (1 + 0)
WHEN _umex = 'LB' THEN (1 + 1)
WHEN _umex = 'EA' THEN (1 + 2)
ELSE 'No UM' END AS 'YIELD'
FROM tbl_tnlrpts
WHERE _workcenter = 'EXTPACK' AND _date BETWEEN '2008-03-30' AND '2008-03-30'
ORDER BY _itemroot


This runs OK with no warnings... but when I try to query the newly created table (eg, SELECT * FROM _temptable) I get, "Table db_mydata._temptable doesn't exist". What do you mean, "it doesn't exist"? Where did it go? How can I query it?


Thanks,
Rick
LVL 13
RickAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshSenior Principal Technical Support EngineerCommented:
Please can you post exact error message that you are getting? also post MySQL version
0
RickAuthor Commented:
MySql 5.1

I'm running the above query in MySQL Query Browser.

Error:

The query could not be executed.
! Description
! Table 'db_mydata._temptable' doesn't exist.
ErrorNr. 1146


0
UmeshSenior Principal Technical Support EngineerCommented:
Can you try this from command line?? and see what it reports

Login to MySQL... locate mysql/bin

mysql -uUserName -pSecret

mysql>


0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

RickAuthor Commented:
Humm, it works from the command line.

When I run the first query (to crate the temp table), I get:

Query OK, 3596 rows affected (0.47 sec)
Records: 3596 Duplicates: 0 Warnings: 0

When I run the select query (SELECT * FROM _temptable), I can see all my data.

Why is this not working from MySql query browser?
0
RickAuthor Commented:
In QueryBroser, I use:

CREATE TEMPORARY TABLE _temptable
SELECT _itemroot AS 'ITEM ROOT',
CASE WHEN _umex = 'FT' THEN (1 + 0)
WHEN _umex = 'LB' THEN (1 + 1)
WHEN _umex = 'EA' THEN (1 + 2)
ELSE 'No UM' END AS 'YIELD'
FROM tbl_tnlrpts
WHERE _workcenter = 'EXTPACK' AND _date BETWEEN '2008-03-30' AND '2008-03-30'
ORDER BY _itemroot;
SELECT * FROM _temptable

The temp table is probably being discarded at the ";" right after _itemroot.

Is there a way to make this work from the QueryBroser?


Thanks,
Rick
0
UmeshSenior Principal Technical Support EngineerCommented:
Yes, I was sure that it would work from commandline..

I've never worked on QueryBrowser...No issues..Can you send me the table details and some sample data.. Will try at my end.
0
RickAuthor Commented:
db_tnlrpts is InnoDB

Sample data:

_workcenter         _date                 _itemroot             _umex

EXTPACK          2008-03-30        US786BLK           FT
EXTPACK          2008-03-30        US785WHT          FT
EXTPACK          2008-03-30        UK1212GH           LB
EXTPACK          2008-03-30        UK4091TX            LB
EXTPACK          2008-03-30        LA459BLK            EA
EXTPACK          2008-03-30        LA459WHT           EA
EXTPACK          2008-03-30        OEM331FK            ?
EXTPACK          2008-03-30        OEM332FK            ?


Thanks,
Rick
0
UmeshSenior Principal Technical Support EngineerCommented:
Pls.. send me the table definition and data in sql format :-)
0
UmeshSenior Principal Technical Support EngineerCommented:
I got it..........

Query Browser creates a new connection for every transaction. When a transaction completes, anything associated with the connection is lost. E.g. user variables, temp tables, LAST_INSERT_ID(), etc.

You can disable automatic transactions, and start/commit transactions yourself.

You can also do what you want in the command-line mysql client tool, which keeps connections open persistently until you exit the tool.

Workaround is before your query start put "Start Transaction;" and folowed by your temporary table etc..

Please the attached doc.. its working...
ushastry.PNG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RickAuthor Commented:
Excellent! Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.