Learn how to a build a cloud-first strategyRegister Now

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

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
0
Rick
Asked:
Rick
  • 5
  • 5
1 Solution
 
UmeshCommented:
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
 
UmeshCommented:
Can you try this from command line?? and see what it reports

Login to MySQL... locate mysql/bin

mysql -uUserName -pSecret

mysql>


0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
UmeshCommented:
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
 
UmeshCommented:
Pls.. send me the table definition and data in sql format :-)
0
 
UmeshCommented:
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
 
RickAuthor Commented:
Excellent! Thank you.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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