3

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?

riverrunner
  • 115
  • 9

2 Answers2

2

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.

DF_
  • 3,743
  • 25
  • 34
  • Thanks for the key information - that the table only exists for the current query. My thoughts were that the table existed for the entire session. I had been meaning to try this on another installation as I thought it was a bug. Just tried on two other installations and same result. OK, so now I need to write a few procedures... cheers. – riverrunner Feb 16 '14 at 10:10
  • I'm not sure why this has gone unchallenged, but the statement "Temporary tables only exist for the current query." is untrue, it lasts for the current **session**. See http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-temporary-table, which says: "A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed." Downvoted for incorrect information. – enigment Jan 22 '16 at 13:35
  • 1
    True, but I meant for the phpmyadmin queries (as that was the question) rather than MySql queries since phpmyadmin closes the db connection after executing a query. I'll clarify this in the answer however. – DF_ Jan 22 '16 at 15:16
  • Pinging @eniment as Deif did not – Lightness Races in Orbit Jan 22 '16 at 15:21
0

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

Community
  • 1
  • 1
Paramone
  • 2,634
  • 4
  • 31
  • 58
  • I'm running version 5.6.11 so it's fairly recent. I tried importing create_tables.sql and that didn't fix the problem, even after restarting. Interestingly, if I run both of the above queries in the same command window of phpMyAdmin, then it works. I thought that the temporary tables were supposed to exist for the entirety of the connection. – riverrunner Dec 23 '13 at 22:58
  • OK, I dropped the database, recreated and then restarted the mySQL service. Still no luck and am getting exactly the same error. Thank you and I did check the links you listed, however my issue is with inserting into a TEMPORARY table, all these issues listed are queries associated with ordinary tables. Looks as though it was a separate problem these other users had. Next, I will try updating, even though it's only a minor upgrade, then I'll try on another installation on another machine. In the meantime anyone have any further ideas? – riverrunner Dec 24 '13 at 05:46
  • @riverrunner Ah, I'm sorry I couldn't help you riverrunner. Honestly, I've never gotten this error..- and all the results I've found are inside of my answer. Goodluck! – Paramone Dec 24 '13 at 09:51
  • Thanks @Paramone, if I find something that fixes the situation, I will post it here. – riverrunner Dec 24 '13 at 11:49