Get Dropped table info : table_schema information from information_schema.tables

[amit2781]

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_sch ema.tables it will give me result as 'amit'. I didn't understand why it is happnes.

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_sch ema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARA CTER_SET_APPLIC ABILITY |
| COLUMNS |
| COLUMN_PRIVILEG ES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLE S |
| KEY_COLUMN_USAG E |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CON STRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEG ES |
| SESSION_STATUS |
| SESSION_VARIABL ES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAIN TS |
| TABLE_PRIVILEGE S |
| 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_inde x |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_ second |
| time_zone_name |
| time_zone_trans ition |
| time_zone_trans ition_type |
| user |
| temp |
| tt |
+---------------------------------------+
58 rows in set (0.00 sec)

*************** *************** *************** *************** ********
Tables are present in information_sch ema.tables beforte delete
*************** *************** *************** *************** ********
mysql> select table_schema from information_sch ema.tables where table_name='CLS ync';
+--------------+
| 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_sch ema.tables where table_name='CLS ync';
+--------------+
| table_schema |
+--------------+
| amit |
+--------------+
1 row in set (0.00 sec)


mysql> select table_schema from information_sch ema.tables where table_name='cls ync';
Empty set (0.00 sec)

mysql> select table_schema from information_sch ema.tables where table_name='CLC YNC';
Empty set (0.00 sec)

*************** *************** *************** ********
I used using uppercase, lowercase letter for table_name 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_sch ema.table
+---------------------------------------+
| table_name |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARA CTER_SET_APPLIC ABILITY |
| COLUMNS |
| COLUMN_PRIVILEG ES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLE S |
| KEY_COLUMN_USAG E |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CON STRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEG ES |
| SESSION_STATUS |
| SESSION_VARIABL ES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAIN TS |
| TABLE_PRIVILEGE S |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| account |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_inde x |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_ second |
| time_zone_name |
| time_zone_trans ition |
| time_zone_trans ition_type |
| user |
| temp |
| tt |
+---------------------------------------+
54 rows in set (0.00 sec)

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

[SLauren]

Its working fine for me. I am not sure which version of Mysql you are using?

Thanks,
Lauren

[amit2781]

I am using MySQL 5.1.34.