Tiger Wang Tiger Wang - 1 year ago 96
MySQL Question

Puzzled by MySQL information_schema.tables info

For some unknown reason, the info of 'information_schema.tables' is not match to real tables:

First, find table-name from information_schema.tables, there is one result;
Then, select * from table-name, it shows table not exist! /(愒o愒)/~~

I try to create a new table(MyISAM engine) with same name, it create OK!
Then, find table-name from information_schema.tables, there are two result!
And select * from table-name shows empty set, it's normally.

Until now, table can be use, but can not be drop completely.
If drop table, it return the "First".

As the following image:
all the step

I am searching for a long time on net. But no use. Please help or try to give some ideas how to achieve this......

Answer Source

The image shows nothing wrong to me. When you run this:

TABLE_NAME = 'report_instance_performance_20160614

and get a result, you are probably getting a result for a table of that name in a different schema from where you ran that query. It would be more helpful to run this to see if something were wrong:

TABLE_NAME = 'report_instance_performance_20160614'

My assertion that that table exists in another schema but not that one that you're running in the query from is that you're getting the doesn't exist error when you try and SELECT from it.

Next, when you create a new instance of that table and then run your INFORMATION_SCHEMA.TABLES query, you are getting two results because you've now created a table with that name in your current schema. If that table had existed in your current schema, you would've gotten an error that a table of that name already existed in the schema. This further supports my assertion that report_instance_performance_20160614 exists in a different schema.

Lastly, since the table is newly created and you haven't INSERTed anything into the table before SELECTing from it, it's totally normal that the table would be empty.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download