Bug #40362 'table already exists' is misleading
Submitted: 27 Oct 2008 20:42 Modified: 28 Oct 2008 15:21
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.67 OS:Windows
Assigned to: CPU Architecture:Any
Tags: qc

[27 Oct 2008 20:42] Peter Laursen
Description:
It seems that the server both thinks and does not think I have a `test` table in `test` database!

How to repeat:
use test;

show tables like 'test%';
/*
Tables_in_test (test%)
----------------------
test                  
test2                 
test_affected         
test_affected_copy    
test_bind_fetch       
test_bind_fetch_uint  
test_bind_result      
test_fetch            
test_fetch_null       
test_result           
test_store_result     
test_table            
test_update           
test_warnings         
testtable */

drop table `test`;
/*
Error Code : 1051
Unknown table 'test'
*/

create table `test`(name varchar(10) not null, type varchar(10) not null);
/*
Error Code : 1050
Table 'test' already exists
*/

Suggested fix:
Not sure!  

Also this server instance is about 18 montht old and has been upgraded lots of time!

But I seem to be 'locked' here in further testing!
[27 Oct 2008 20:45] Peter Laursen
Yy mistake. There was a view named test!
So let us make it a feature request for better error messages instead!

Updated synopsis!
[28 Oct 2008 4:58] Valeriy Kravchuk
Thank you for a problem report. Can't you just check if it is a table or view after getting error 1050 or 1051, like this, for example:

mysql> show tables like 'vvv%';
+-----------------------+
| Tables_in_test (vvv%) |
+-----------------------+
| vvvv                  |
+-----------------------+
1 row in set (0.01 sec)

mysql> drop table vvvv;
ERROR 1051 (42S02): Unknown table 'vvvv'
mysql> create table vvvv (c1 int);
ERROR 1050 (42S01): Table 'vvvv' already exists
mysql> show create table vvvv\G
*************************** 1. row ***************************
                View: vvvv
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `vvvv` AS select 1 AS `a`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.05 sec)

mysql> drop view vvvv;
Query OK, 0 rows affected (0.00 sec)
[28 Oct 2008 8:52] Peter Laursen
I do not understand what I should check!  I think my case is completely identical to yours!

Can we make 1050 'a table or a view with this name alrady exists'?