Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I got this weird issue today when I dropped a temporary table. I dropped the temporary table and desc the table just to verify. But, the table was not dropped. After some searching I found that:

MySQL allows to create a temporary table with the same name as a permanent table. So the temporary table got dropped and not the permanent table. I got really confused with which table I am working.

MySQL version: 5.1.36-enterprise-gpl-pro-log

This is what I'd tested:

mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

 mysql> create temporary table test(id int);
 Query OK, 0 rows affected (0.00 sec)



mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL


mysql> drop table test;
 Query OK, 0 rows affected (0.00 sec)

 mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

Is this a bug or is there an alternate way to overcome this?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

MySQL allows you to create a temp table with a existing name because they don't have the same "scope". A temporary table is visible in the session only, and it is dropped at session ending. If you have the same name, MySQL "hide" the original table until you drop your temp table.

You can refer to the Temporary Tables section in the MySQL documentation

Max.

share|improve this answer
    
what if i need to insert some records in the permanent table when i have a temporary table with the same name as permanent table in the same session. –  BALA Jul 5 at 8:19
    
You simply can't. You must prefix your table or find a different name. –  Maxime FOUILLEUL Jul 5 at 9:34

Read the manual.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

That means after you create temporary table with the same name as some existing table you can not access normal table, but only the temporary one (during that session). If you think that opens space for an error, use different name for temporary table (e.g. use prefix temp_) - thats simple and does not leave space for confusion.

share|improve this answer

You've answered this question in your first one. You don't "need to insert records in a permanent table...same name.... temporary table... same session" because you have control over the table names!

You simply ensure that the temporary table is given a prefix/suffix tmp_/_tmp or similar as this avoids any confusion (as referred to by @SpeedyGonsales).

If you're making this kind of mistake now, just think how easy it will be to make for somebody new to the system in a year's time! Your organisation (even if that's only you!) should have a naming convention, otherwise you get this sort of SNAFU.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.