Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    41

    Unanswered: Get Dropped table info : table_schema information from information_schema.tables

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you dropped the tables, but you haven't dropped the database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    41
    Yes, I am not dropped the database.

    But How can we get the table information from the database if they are not present.

    If I do "show tables" for that database or information_schema.tables then i will not see table. When I Restart mysql service and then tried the same query like
    mysql> select table_schema from information_schema.tables where table_name='CLSync';

    It does not give me anything even the database is not dropped.
    Last edited by amitbora27; 07-14-09 at 10:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •