MySQL Bugs: #16122: MySQL outputs contradicting data

Bug #16122	MySQL outputs contradicting data
Submitted:	2 Jan 2006 4:22	Modified:	4 Oct 2008 10:44
Reporter:	[ name withheld ]	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.18-nt/4.1	OS:	Windows (Windows 2k pro/Linux)
Assigned to:		CPU Architecture:	Any

Description:
ok, this is not really a bug which screws up mysql or something, it is just something wrong with the DB structure which causes it to output contradicting data as shown below.

How to repeat:
Copy any InnoDB table (the .frm file) from any database into another database using copy and paste within Windows.
replace (table name) with something you like.
log into mysql with root, select the new database, and execute 
select * from (name of table);
It will output ERROR 1146 (42S02): Table '(name of table)'doesn't exist
then execute 
create table (table name) (a int);
It will output ERROR 1050 (42S01): Table '(name of table)' already exists

Suggested fix:
allow select table to find tables that exist in files but are not part of the db (this can be useful after a crash when the data in the DB structure has been corrupted.

Than you for the bug report. Indeed the error messages are contradictory.

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db21
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db22
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db21
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb1 (id int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> use db22
Database changed
mysql> show tables;
+----------------+
| Tables_in_db22 |
+----------------+
| tb1            |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tb1;
ERROR 1146 (42S02): Table 'db22.tb1' doesn't exist
mysql> create table tb1 (id int) engine=innodb;
ERROR 1050 (42S01): Table 'tb1' already exists
mysql>

However notice that the log error shows the correct error message:

060102 10:10:43060102 10:10:43 [ERROR] Cannot find table db22/tb1 from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem.

Thank you for your interest in MySQL.
If you manipulate with files on the file system, you're on your own.
There are plentiful ways to move a table from one database to another.