Bug #46189 MySQL 5.1.34 returns the result for dropped table.
Submitted: 15 Jul 2009 6:00 Modified: 23 Jul 2009 22:11
Reporter: Amit Bora Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S1 (Critical)
Version:5.1.34, 5.1 bzr OS:Any (Win, Mac)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[15 Jul 2009 6:00] Amit Bora
Description:
Hi,

I have created 4 tables in 'amit' database and then I deleted them. Still I able to get information about the table_schema for the table deleted.
After drop table when I fire a query for table_schema from information_schema.tables it will give me result as 'amit'. I didn't understand why it is happnes.

I am using MySQL version 5.1.34essential.

See the below steps done.

mysql> use amit;
mysql> show tables;
+----------------+
| Tables_in_amit |
+----------------+
| clconf |
| clmstr |
| clmt14 |
| clsync |
+----------------+
6 rows in set (0.00 sec)

mysql> select table_name from information_schema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| clconf |
| clsync |
| clmt14 |
| clmstr |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
| temp |
| tt |
+---------------------------------------+
58 rows in set (0.00 sec)

************************************************** ******************
Tables are present in information_schema.tables beforte delete
************************************************** ******************
mysql> select table_schema from information_schema.tables where table_name='CLSync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.01 sec)

mysql> DROP TABLE amit.CLMT14 ;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE amit.CLSync ;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE amit.CLMstr ;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE amit.CLConf ;
Query OK, 0 rows affected (0.01 sec)

mysql> select table_schema from information_schema.tables where table_name='CLSync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.00 sec)

mysql> select table_schema from information_schema.tables where table_name='clsync';
Empty set (0.00 sec)

mysql> select table_schema from information_schema.tables where table_name='CLCYNC';
Empty set (0.00 sec)

************************************************** ***
I used using uppercase, lowercase letter and above u can see the difference in query.
I have created table using names as - CLSync, CLConf, CLMT14, CLMstr
************************************************** ***

mysql> select table_name from information_schema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
| temp |
| tt |
+---------------------------------------+
54 rows in set (0.00 sec)

************************************************** ******************
Tables are not present in information_schema.tables after delete
************************************************** ******************
But still responding to query for table_schema.

How to repeat:
use the steps given in description.
[15 Jul 2009 6:02] Amit Bora
I have tried the same things number of times and the same result getting.
[15 Jul 2009 6:58] Sveta Smirnova
Thank you for the report.

Verified as described. Can be duplicate of bug #44009 and related to bug #45862.
[15 Jul 2009 6:58] Sveta Smirnova
Moved package description mysql-essential-5.1.34-win32.msi which reporter uses from version field here.
[23 Jul 2009 22:11] Omer Barnir
See bug#25630