CODEWITHSUNDEEP
mysqltemp-tables
riverrunner
riverrunner

Reputation: 115

Issue working with temporary table

I enter the following into phpMyAdmin:

CREATE TEMPORARY TABLE test_table (
 item1 VARCHAR(50) NOT NULL
 , item2 DECIMAL(12,2) NOT NULL DEFAULT 0.00
 , item3 DECIMAL(7,2) NOT NULL DEFAULT 0.00
 , item4 INT UNSIGNED NOT NULL DEFAULT 0);

The table is successfully created.

Then I enter the following statement also into phpMyAdmin:

INSERT INTO test_table
 (item1, item2, item3, item4)
 VALUES
 ('lentils', 99.00, 82, 8);

And I receive; "#1146 - Table 'mydatabase.test_table' doesn't exist" Any clues as to what is wrong here?

Upvotes: 3

Views: 2282

Answers (2)

DF_
DF_

Reputation: 3973

Temporary tables only exist for the current PhpMyAdmin query as it closes your DB session once the query has been executed. If you are executing these two queries separately then you will get this error as the MySQL database will drop the temporary table right after the first query has finished executing.

What is the reason for this?

The reason is so you can create functions or procedures that create these temporary tables without clogging up your database as they will be automatically removed once the function or procedure has finished its execution.

The solution would be to execute everything in one query or just use a normal table.

Upvotes: 2

Paramone
Paramone

Reputation: 2724

I've been searching for some answers for your problem, and apparently, It's some sort of bug.

You need to drop your table,restart your MySQL service and re-build the table.

OR

You could rename the table.

(This fixed it for many others.)

Sources:

Bug? #1146 - Table 'xxx.xxxxx' doesn't exist

SQL - AS - table doesn't exist - 1146

http://bytes.com/topic/mysql/answers/706253-table-doesnt-exist-but-im-sure-does

http://mysql-tools.com/articles/mysql/48-table-mysqlproc-doesnt-exist.html

http://ubuntuforums.org/showthread.php?t=2095602

Edit:

It could possibly be that your MySQL Version is 'Outdated'. Try updating.

If your DB is inside PhpMyAdmin,use phpMyAdmin to import "create_tables.sql" from the examples folder of phpMyAdmin SRC: Source - Table-Doesn't-Exist

Upvotes: 0

Related Questions