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.
