Puzzled by MySQL information_schema.tables info

Question

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......

You could try to insert the image directly into the question, not everyone follows links.. — Stefan Hegny, Jun 14 '16 at 09:29
Thank you for your suggestion, but I have not enough reputation to insert a image directly(I think so)... /(ㄒoㄒ)/~~ @StefanHegny — Tiger Wang, Jun 14 '16 at 13:11

score 1 · Accepted Answer · answered Jun 14 '16 at 13:35

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

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 
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:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
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.

Yes... you are correct. there is another schema... Thank You Very Much! I'm so sorry for this question, Because of my limited MySQL knowledge, the understanding of the "INFORMATION_SCHEMA.TABLES" is not enough thorough... — Tiger Wang, Jun 15 '16 at 13:27
You can mark the answer as correct then to help others who come and see this question :) — gr1zzly be4r, Jun 15 '16 at 16:47

Puzzled by MySQL information_schema.tables info

1 Answers1