07-14-2009
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 5.1.34essential version.
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.
9 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
I am trying to install a new AIX server and I am migrating off of an older AIX server. Does anyone have a quick and easy method for getting the static route information from the old server so I can just maybe copy a file over to the new server ? (1 Reply)
Discussion started by: Docboyeee
1 Replies
2. Shell Programming and Scripting
I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated.
Input:
test_data_1
1 2 90%
4 3 91%
5 4 90%
6 5 90%
9 6 90%
test_data_2
3 5 92%
5 4 92%
7 3 93%
9 2 92%
1 1 92%
...
Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies
3. Shell Programming and Scripting
Please I need to rearrange data acquired by USB port from a sensor network. The information is mixed and I need to convert it into a kind of table. This is my input file:
Node 4D5A joined
Temperature: 27,5
Humidity: 40
Dew Point: 23
No motion detected
LUX: 389
Temperature: 28
Humidity: 41... (5 Replies)
Discussion started by: csecnarf
5 Replies
4. SuSE
I have deleted a database. I restored it again with a backup.
Now we lost some data of two days.
Is it possible to restore this lost data? (5 Replies)
Discussion started by: Eastme
5 Replies
5. Shell Programming and Scripting
Hi all,
How to:
Run a bash script, display on the screen and save all information in a file including error information.
For example:
I have a bash script called test.sh
now I want to run the test.sh and display the output on the screen and save the output including error info to a file.
... (1 Reply)
Discussion started by: Damon sine
1 Replies
6. Shell Programming and Scripting
Hi..
File 1:
1 aa rep
1 dd rep
1 kk rep
2 bb sad
2 ss sad
3 ee dam
File 2
1 apple fruit
2 mango tree
3 lilly flower
output:
1 aaple fruit aa,dd,kk rep (7 Replies)
Discussion started by: empyrean
7 Replies
7. Shell Programming and Scripting
Hey gyuz,
I wanna calculate the number of mapped reads of a bam file in a region of interest. I used this code to do so :
samtools view input.bam chrname:region1 > region1.txt
This will store all the reads from given bam file within the region of interest in region1.txt
Now I have... (5 Replies)
Discussion started by: @man
5 Replies
8. UNIX for Advanced & Expert Users
HI Team
Is there a way to track which user dropped the table in Teradata ?
Please share the query or approach .
Thanks (0 Replies)
Discussion started by: Perlbaby
0 Replies
9. UNIX and Linux Applications
Hello,
I am newbie on mysql and trying to edit my database from terminal under linux.
What I need to do is to change the information written in a cell in table.
Let me explain what I tried:
$ mysql -u mysqluser -p
$ show databases;
$ USE catalogue;
$ show tables ;
$ select * from... (2 Replies)
Discussion started by: baris35
2 Replies